人人都是 DBA（X）资源信息收集脚本汇编 - Dennis Gao

WITH DB_CPU_Stats
AS (
SELECT DatabaseID
,DB_Name(DatabaseID) AS [Database Name]
,SUM(total_worker_time) AS [CPU_Time_Ms]
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY (
SELECT CONVERT(INT, value) AS [DatabaseID]
FROM sys.dm_exec_plan_attributes(qs.plan_handle)
WHERE attribute = N'dbid'
) AS F_DB
GROUP BY DatabaseID
)
SELECT ROW_NUMBER() OVER (
ORDER BY [CPU_Time_Ms] DESC
) AS [CPU Rank]
,[Database Name]
,[CPU_Time_Ms] AS [CPU Time (ms)]
,CAST([CPU_Time_Ms] * 1.0 / SUM([CPU_Time_Ms]) OVER () * 100.0 AS DECIMAL(5, 2)) AS [CPU Percent]
FROM DB_CPU_Stats
WHERE DatabaseID <> 32767 -- ResourceDB
ORDER BY [CPU Rank]
OPTION (RECOMPILE);

DECLARE @ts_now BIGINT = (
SELECT cpu_ticks / (cpu_ticks / ms_ticks)
FROM sys.dm_os_sys_info WITH (NOLOCK)
);

SELECT TOP (256) SQLProcessUtilization AS [SQL Server Process CPU Utilization]
,SystemIdle AS [System Idle Process]
,100 - SystemIdle - SQLProcessUtilization AS [Other Process CPU Utilization]
,DATEADD(ms, - 1 * (@ts_now - [timestamp]), GETDATE()) AS [Event Time]
FROM (
SELECT record.value('(./Record/@id)[1]', 'int') AS record_id
,record.value('(./Record/SchedulerMonitorEvent/SystemHealth/SystemIdle)[1]', 'int') AS [SystemIdle]
,record.value('(./Record/SchedulerMonitorEvent/SystemHealth/ProcessUtilization)[1]', 'int') AS [SQLProcessUtilization]
,[timestamp]
FROM (
SELECT [timestamp]
,CONVERT(XML, record) AS [record]
FROM sys.dm_os_ring_buffers WITH (NOLOCK)
WHERE ring_buffer_type = N'RING_BUFFER_SCHEDULER_MONITOR'
AND record LIKE N'%<SystemHealth>%'
) AS x
) AS y
ORDER BY record_id DESC
OPTION (RECOMPILE);

SELECT TOP (50) DB_NAME(t.[dbid]) AS [Database Name]
,t.[text] AS [Query Text]
,qs.total_worker_time AS [Total Worker Time]
,qs.min_worker_time AS [Min Worker Time]
,qs.total_worker_time / qs.execution_count AS [Avg Worker Time]
,qs.max_worker_time AS [Max Worker Time]
,qs.execution_count AS [Execution Count]
,qs.total_elapsed_time / qs.execution_count AS [Avg Elapsed Time]
,qs.total_logical_reads / qs.execution_count AS [Avg Logical Reads]
,qs.total_physical_reads / qs.execution_count AS [Avg Physical Reads]
,qp.query_plan AS [Query Plan]
,qs.creation_time AS [Creation Time]
FROM sys.dm_exec_query_stats AS qs WITH (NOLOCK)
CROSS APPLY sys.dm_exec_sql_text(plan_handle) AS t
CROSS APPLY sys.dm_exec_query_plan(plan_handle) AS qp
ORDER BY qs.total_worker_time DESC
OPTION (RECOMPILE);

SELECT total_physical_memory_kb / 1024 AS [Physical Memory (MB)]
,available_physical_memory_kb / 1024 AS [Available Memory (MB)]
,total_page_file_kb / 1024 AS [Total Page File (MB)]
,available_page_file_kb / 1024 AS [Available Page File (MB)]
,system_cache_kb / 1024 AS [System Cache (MB)]
,system_memory_state_desc AS [System Memory State]
FROM sys.dm_os_sys_memory WITH (NOLOCK)
OPTION (RECOMPILE);

SELECT physical_memory_in_use_kb / 1024 AS [SQL Server Memory Usage (MB)]
,large_page_allocations_kb
,locked_page_allocations_kb
,page_fault_count
,memory_utilization_percentage
,available_commit_limit_kb
,process_physical_memory_low
,process_virtual_memory_low
FROM sys.dm_os_process_memory WITH (NOLOCK)
OPTION (RECOMPILE);

SELECT @@SERVERNAME AS [Server Name]
,[object_name]
,cntr_value AS [Memory Grants Pending]
FROM sys.dm_os_performance_counters WITH (NOLOCK)
WHERE [object_name] LIKE N'%Memory Manager%' -- Handles named instances
AND counter_name = N'Memory Grants Pending'
OPTION (RECOMPILE);

SELECT [name] AS [Name]
,[configuration_id] AS [Number]
,[minimum] AS [Minimum]
,[maximum] AS [Maximum]
,[is_dynamic] AS [Dynamic]
,[is_advanced] AS [Advanced]
,[value] AS [ConfigValue]
,[value_in_use] AS [RunValue]
,[description] AS [Description]
FROM [master].[sys].[configurations]
WHERE NAME IN (
'Min server memory (MB)'
,'Max server memory (MB)'
);

SELECT *
FROM sys.configurations
WHERE configuration_id IN (
'1543'
,'1544'
)

SELECT CAST(100.0 * SUM(signal_wait_time_ms) / SUM(wait_time_ms) AS NUMERIC(20, 2)) AS [% Signal (CPU) Waits]
,CAST(100.0 * SUM(wait_time_ms - signal_wait_time_ms) / SUM(wait_time_ms) AS NUMERIC(20, 2)) AS [% Resource Waits]
FROM sys.dm_os_wait_stats WITH (NOLOCK)
WHERE wait_type NOT IN (
N'BROKER_EVENTHANDLER'
,N'BROKER_RECEIVE_WAITFOR'
,N'BROKER_TASK_STOP'
,N'BROKER_TO_FLUSH'
,N'BROKER_TRANSMITTER'
,N'CHECKPOINT_QUEUE'
,N'CHKPT'
,N'CLR_AUTO_EVENT'
,N'CLR_MANUAL_EVENT'
,N'CLR_SEMAPHORE'
,N'DBMIRROR_DBM_EVENT'
,N'DBMIRROR_EVENTS_QUEUE'
,N'DBMIRROR_WORKER_QUEUE'
,N'DBMIRRORING_CMD'
,N'DIRTY_PAGE_POLL'
,N'DISPATCHER_QUEUE_SEMAPHORE'
,N'EXECSYNC'
,N'FSAGENT'
,N'FT_IFTS_SCHEDULER_IDLE_WAIT'
,N'FT_IFTSHC_MUTEX'
,N'HADR_CLUSAPI_CALL'
,N'HADR_FILESTREAM_IOMGR_IOCOMPLETION'
,N'HADR_LOGCAPTURE_WAIT'
,N'HADR_NOTIFICATION_DEQUEUE'
,N'HADR_TIMER_TASK'
,N'HADR_WORK_QUEUE'
,N'KSOURCE_WAKEUP'
,N'LAZYWRITER_SLEEP'
,N'LOGMGR_QUEUE'
,N'ONDEMAND_TASK_QUEUE'
,N'PWAIT_ALL_COMPONENTS_INITIALIZED'
,N'QDS_PERSIST_TASK_MAIN_LOOP_SLEEP'
,N'QDS_CLEANUP_STALE_QUERIES_TASK_MAIN_LOOP_SLEEP'
,N'REQUEST_FOR_DEADLOCK_SEARCH'
,N'RESOURCE_QUEUE'
,N'SERVER_IDLE_CHECK'
,N'SLEEP_BPOOL_FLUSH'
,N'SLEEP_DBSTARTUP'
,N'SLEEP_DCOMSTARTUP'
,N'SLEEP_MASTERDBREADY'
,N'SLEEP_MASTERMDREADY'
,N'SLEEP_MASTERUPGRADED'
,N'SLEEP_MSDBSTARTUP'
,N'SLEEP_SYSTEMTASK'
,N'SLEEP_TASK'
,N'SLEEP_TEMPDBSTARTUP'
,N'SNI_HTTP_ACCEPT'
,N'SP_SERVER_DIAGNOSTICS_SLEEP'
,N'SQLTRACE_BUFFER_FLUSH'
,N'SQLTRACE_INCREMENTAL_FLUSH_SLEEP'
,N'SQLTRACE_WAIT_ENTRIES'
,N'WAIT_FOR_RESULTS'
,N'WAITFOR'
,N'WAITFOR_TASKSHUTDOWN'
,N'WAIT_XTP_HOST_WAIT'
,N'WAIT_XTP_OFFLINE_CKPT_NEW_LOG'
,N'WAIT_XTP_CKPT_CLOSE'
,N'XE_DISPATCHER_JOIN'
,N'XE_DISPATCHER_WAIT'
,N'XE_TIMER_EVENT'
)
OPTION (RECOMPILE);

WITH AggregateBufferPoolUsage
AS (
SELECT DB_NAME(database_id) AS [Database Name]
,CAST(COUNT(*) * 8 / 1024.0 AS DECIMAL(10, 2)) AS [CachedSize]
FROM sys.dm_os_buffer_descriptors WITH (NOLOCK)
WHERE database_id <> 32767 -- ResourceDB
GROUP BY DB_NAME(database_id)
)
SELECT ROW_NUMBER() OVER (
ORDER BY CachedSize DESC
) AS [Buffer Pool Rank]
,[Database Name]
,CachedSize AS [Cached Size (MB)]
,CAST(CachedSize / SUM(CachedSize) OVER () * 100.0 AS DECIMAL(5, 2)) AS [Buffer Pool Percent]
FROM AggregateBufferPoolUsage
ORDER BY [Buffer Pool Rank]
OPTION (RECOMPILE);

SELECT OBJECT_NAME(p.[object_id]) AS [Object Name]
,p.index_id
,CAST(COUNT(*) / 128.0 AS DECIMAL(10, 2)) AS [Buffer size(MB)]
,COUNT(*) AS [BufferCount]
,p.[Rows] AS [Row Count]
,p.data_compression_desc AS [Compression Type]
FROM sys.allocation_units AS a WITH (NOLOCK)
INNER JOIN sys.dm_os_buffer_descriptors AS b WITH (NOLOCK) ON a.allocation_unit_id = b.allocation_unit_id
INNER JOIN sys.partitions AS p WITH (NOLOCK) ON a.container_id = p.hobt_id
WHERE b.database_id = CONVERT(INT, DB_ID())
AND p.[object_id] > 100
GROUP BY p.[object_id]
,p.index_id
,p.data_compression_desc
,p.[Rows]
ORDER BY [BufferCount] DESC
OPTION (RECOMPILE);

SELECT TOP (10) mc.[type] AS [Memory Clerk Type]
,CAST((SUM(mc.pages_kb) / 1024.0) AS DECIMAL(15, 2)) AS [Memory Usage (MB)]
FROM sys.dm_os_memory_clerks AS mc WITH (NOLOCK)
GROUP BY mc.[type]
ORDER BY SUM(mc.pages_kb) DESC
OPTION (RECOMPILE);

SELECT TOP (10) [type] AS [Memory Clerk Type]
,SUM(single_pages_kb) / 1024 AS [SPA Memory Usage (MB)]
FROM sys.dm_os_memory_clerks WITH (NOLOCK)
GROUP BY [type]
ORDER BY SUM(single_pages_kb) DESC
OPTION (RECOMPILE);

DBCC MEMORYSTATUS();

DECLARE @MemStat TABLE (
ValueName SYSNAME
,Val BIGINT
);

INSERT INTO @MemStat
EXEC ('DBCC MEMORYSTATUS() WITH TABLERESULTS');

WITH Measures
AS (
SELECT TOP 2 CurrentValue
,ROW_NUMBER() OVER (
ORDER BY OrderColumn
) AS RowOrder
FROM (
SELECT CASE
WHEN (ms.ValueName = 'Target Committed')
THEN ms.Val
WHEN (ms.ValueName = 'Current Committed')
THEN ms.Val
END AS 'CurrentValue'
,0 AS 'OrderColumn'
FROM @MemStat AS ms
) AS MemStatus
WHERE CurrentValue IS NOT NULL
)
SELECT TargetMem.CurrentValue - CurrentMem.CurrentValue
FROM Measures AS TargetMem
JOIN Measures AS CurrentMem ON TargetMem.RowOrder + 1 = CurrentMem.RowOrder;

SELECT record_id
,dateadd(ms, (y.[timestamp] - tme.ms_ticks), GETDATE()) AS [Notification_Time]
,Notification
FROM (
SELECT record.value('(./Record/@id)[1]', 'int') AS record_id
,record.value('(./Record/ResourceMonitor/Notification)[1]', 'varchar(50)') AS Notification
,TIMESTAMP
FROM (
SELECT TIMESTAMP
,CONVERT(XML, record) AS record
FROM sys.dm_os_ring_buffers
WHERE ring_buffer_type = N'RING_BUFFER_RESOURCE_MONITOR'
) AS x
) AS y
CROSS JOIN sys.dm_os_sys_info tme
ORDER BY record_id DESC;

SELECT dateadd(ms, (rbf.[timestamp] - tme.ms_ticks), GETDATE()) AS [Notification_Time]
,cast(record AS XML).value('(//Record/ResourceMonitor/Notification)[1]', 'varchar(30)') AS [Notification_type]
,cast(record AS XML).value('(//Record/MemoryRecord/MemoryUtilization)[1]', 'bigint') AS [MemoryUtilization %]
,cast(record AS XML).value('(//Record/MemoryNode/@id)[1]', 'bigint') AS [Node Id]
,cast(record AS XML).value('(//Record/ResourceMonitor/IndicatorsProcess)[1]', 'int') AS [Process_Indicator]
,cast(record AS XML).value('(//Record/ResourceMonitor/IndicatorsSystem)[1]', 'int') AS [System_Indicator]
,cast(record AS XML).value('(//Record/MemoryNode/ReservedMemory)[1]', 'bigint') AS [SQL_ReservedMemory_KB]
,cast(record AS XML).value('(//Record/MemoryNode/CommittedMemory)[1]', 'bigint') AS [SQL_CommittedMemory_KB]
,cast(record AS XML).value('(//Record/MemoryNode/AWEMemory)[1]', 'bigint') AS [SQL_AWEMemory]
,cast(record AS XML).value('(//Record/MemoryNode/SinglePagesMemory)[1]', 'bigint') AS [SinglePagesMemory]
,cast(record AS XML).value('(//Record/MemoryNode/MultiplePagesMemory)[1]', 'bigint') AS [MultiplePagesMemory]
,cast(record AS XML).value('(//Record/MemoryRecord/TotalPhysicalMemory)[1]', 'bigint') AS [TotalPhysicalMemory_KB]
,cast(record AS XML).value('(//Record/MemoryRecord/AvailablePhysicalMemory)[1]', 'bigint') AS [AvailablePhysicalMemory_KB]
,cast(record AS XML).value('(//Record/MemoryRecord/TotalPageFile)[1]', 'bigint') AS [TotalPageFile_KB]
,cast(record AS XML).value('(//Record/MemoryRecord/AvailablePageFile)[1]', 'bigint') AS [AvailablePageFile_KB]
,cast(record AS XML).value('(//Record/MemoryRecord/TotalVirtualAddressSpace)[1]', 'bigint') AS [TotalVirtualAddressSpace_KB]
,cast(record AS XML).value('(//Record/MemoryRecord/AvailableVirtualAddressSpace)[1]', 'bigint') AS [AvailableVirtualAddressSpace_KB]
,cast(record AS XML).value('(//Record/@id)[1]', 'bigint') AS [Record Id]
,cast(record AS XML).value('(//Record/@type)[1]', 'varchar(30)') AS [Type]
FROM sys.dm_os_ring_buffers rbf
CROSS JOIN sys.dm_os_sys_info tme
WHERE rbf.ring_buffer_type = 'RING_BUFFER_RESOURCE_MONITOR'
ORDER BY rbf.TIMESTAMP ASC;

-- there are thousands of different counters
SELECT *
FROM sys.dm_os_performance_counters;

SELECT *
FROM sys.dm_os_performance_counters
WHERE counter_name = 'Page Life expectancy'
AND object_name LIKE '%Buffer Manager%';

SELECT *
FROM sys.dm_os_performance_counters
WHERE counter_name LIKE '%Average Wait Time%'
AND instance_name = 'Database';

DECLARE @BRPS BIGINT

SELECT @BRPS = cntr_value
FROM sys.dm_os_performance_counters
WHERE counter_name LIKE 'Batch Requests/sec%'

WAITFOR DELAY '000:00:10'

SELECT (cntr_value - @BRPS) / 10.0 AS "Batch Requests/sec"
FROM sys.dm_os_performance_counters
WHERE counter_name LIKE 'Batch Requests/sec%'