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

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

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

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

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

索引

  1. 获取数据库的 CPU 使用率
  2. 过去一段时间里 CPU 利用率的历史情况
  3. 谁用 CPU 工作的时间最长
  4. 服务器上安装了多大的 Memory
  5. SQL Server 进程用了多少 Memory
  6. 是否申请新的 Memory 无法得到
  7. SQL Server 的最大最小 Memory 配置
  8. 通过 Signal Wait 判断是否 CPU 压力过大
  9. 获取数据库的 Buffer 使用率
  10. 查看哪张表占用的 Buffer 最多
  11. 查看 Memory Clerks 使用情况
  12. 查看 Memory 分配状况
  13. 查询 SQL Server 内存承担的压力
  14. 查询 SQL Server 性能计数器
  15. 查询当前的 Batch Requests 计数

获取数据库的 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》系列文章索引：

 序号 

 名称 

1

 人人都是 DBA（I）SQL Server 体系结构

2

 人人都是 DBA（II）SQL Server 元数据

3

 人人都是 DBA（III）SQL Server 调度器

4

 人人都是 DBA（IV）SQL Server 内存管理

5

 人人都是 DBA（V）SQL Server 数据库文件

6

 人人都是 DBA（VI）SQL Server 事务日志

7

 人人都是 DBA（VII）B 树和 B+ 树

8

 人人都是 DBA（VIII）SQL Server 页存储结构

9

 人人都是 DBA（IX）服务器信息收集脚本汇编

10

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

11

 人人都是 DBA（XI）I/O 信息收集脚本汇编

12

 人人都是 DBA（XII）查询信息收集脚本汇编

13

 人人都是 DBA（XIII）索引信息收集脚本汇编

14

 人人都是 DBA（XIV）存储过程信息收集脚本汇编 

15

 人人都是 DBA（XV）锁信息收集脚本汇编

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

posted @   sangmado  阅读(3810)  评论(5)  编辑  收藏  举报
编辑推荐：
· 从 HTTP 原因短语缺失研究 HTTP/2 和 HTTP/3 的设计差异
· AI与.NET技术实操系列：向量存储与相似性搜索在 .NET 中的实现
· 基于Microsoft.Extensions.AI核心库实现RAG应用
· Linux系列：如何用heaptrack跟踪.NET程序的非托管内存泄露
· 开发者必知的日志记录最佳实践
阅读排行：
· TypeScript + Deepseek 打造卜卦网站：技术与玄学的结合
· Manus的开源复刻OpenManus初探
· 写一个简单的SQL生成工具
· AI 智能体引爆开源社区「GitHub 热点速览」
· C#/.NET/.NET Core技术前沿周刊 | 第 29 期（2025年3.1-3.9）
历史上的今天：
2013-12-10 使用 Microsoft Fakes 进行单元测试
点击右上角即可分享
微信分享提示