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

原文:

什么？有个 SQL 执行了 8 秒！

哪里出了问题？臣妾不知道啊，得找 DBA 啊。

DBA 人呢？离职了！！擦！！！

程序员在无处寻求帮助时，就得想办法自救，努力让自己变成 "伪 DBA"。

索引

获取数据库的 CPU 使用率

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);

过去一段时间里 CPU 利用率的历史情况

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);

可以查看那个时间点的 CPU 利用率较高。

谁用 CPU 工作的时间最长

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);

或许能找到哪个 SQL 语句占用了最多的 CPU 资源。

服务器上安装了多大的 Memory

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);

SQL Server 进程用了多少 Memory

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);

是否申请新的 Memory 无法得到

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);

如果 Memory Grants Pending 的值一直大于 0，则明确的说明 Memory 存在压力。

SQL Server 的最大最小 Memory 配置

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‘
        )

通过 Signal Wait 判断是否 CPU 压力过大

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);

通常，如果 Signal Waits 超过 10-15%，则说明 CPU 压力过大。

获取数据库的 Buffer 使用率

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);

参考资料：

查看哪张表占用的 Buffer 最多

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);

可以判断哪张表或索引占用的 Buffer 也就是 Memory 最多，可以考虑应用不同的 Compression Type。

参考资料：

查看 Memory Clerks 使用情况

SQL Server 2012 版本

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);

SQL Server 2008 版本

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);

参考资料：

查看 Memory 分配状况

可以直接运行：

DBCC MEMORYSTATUS();

查看 Memory 各项指标的细节。

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;

参考资料：

查询 SQL Server 内存承担的压力

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;

查询 SQL Server 性能计数器

-- 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‘;

查询当前的 Batch Requests 计数

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%‘

《人人都是 DBA》系列文章索引：

本系列文章《》由  发表自，未经作者本人同意禁止任何形式的转载，任何自动或人为的爬虫转载行为均为耍流氓。