SqlServer占用CPU过高情况排查

大新软件技术部

已于 2024-09-20 00:24:44 修改

阅读量5.4k

点赞数 17

CC 4.0 BY-SA版权

文章标签： sqlserver 数据库

于 2024-04-10 13:40:11 首次发布

本文链接：https://blog.youkuaiyun.com/yangyifan0/article/details/137592750

一、问题描述

反馈SQLServer中出现CPU使用率过高有许多可能原因，但常见的原因如下最为常见：

由于以下情况，表或索引扫描导致的高逻辑读取：
- 过期统计信息
- 缺少索引
- 设计不佳的查询
工作负载增加

针对此类问题，微软有一套的排查套路，整理记录在此。

近阶段程序运行过程中，发现CPU占用特别高，导致程序运行速度很慢，当时监测的CPU运行情况如下，该版本的SqlServer数据库最多可以使用40个CPU逻辑处理单元，全部100%

二、处理步骤

步骤1，验证 SQL Server 是否导致 CPU 使用率过高

使用以下工具之一检查 SQL Server 进程是否确实导致 CPU 使用率过高：任务管理器：在“进程”选项卡上，检查“64 位版本的 SQL Server Windows NT”的“CPU”列的值是否接近 100%。
性能和资源监视器
计数器：Process/%User Time, % Privileged Time
实例：sqlservr
可以使用以下 PowerShell 脚本在 60 秒的跨度内收集计数器数据：

$serverName = $env:COMPUTERNAME
$Counters = @(
    ("\\$serverName" + "\Process(sqlservr*)\% User Time"), ("\\$serverName" + "\Process(sqlservr*)\% Privileged Time")
)
Get-Counter -Counter $Counters -MaxSamples 30 | ForEach {
    $_.CounterSamples | ForEach {
        [pscustomobject]@{
            TimeStamp = $_.TimeStamp
            Path = $_.Path
            Value = ([Math]::Round($_.CookedValue, 3))
        }
        Start-Sleep -s 2
    }
}

如果 % User Time 始终大于 90%，则是 SQL Server 进程导致 CPU 使用率过高。但是，如果 % Privileged time 始终大于 90%，则是防病毒软件、其他驱动程序或计算机上的其他操作系统组件导致 CPU 使用率过高。

步骤2，确定影响 CPU 使用率的查询

如果 Sqlservr.exe 进程导致 CPU 使用率过高，则最常见的原因是执行表或索引扫描的 SQL Server 查询，其次是排序、哈希操作和循环 (嵌套循环运算符或 WHILE (T-SQL) ) 。要了解查询当前在总 CPU 使用率中的占比，请运行以下语句：

DECLARE @init_sum_cpu_time int,
        @utilizedCpuCount int 
--get CPU count used by SQL Server
SELECT @utilizedCpuCount = COUNT( * )
FROM sys.dm_os_schedulers
WHERE status = 'VISIBLE ONLINE' 
--calculate the CPU usage by queries OVER a 5 sec interval 
SELECT @init_sum_cpu_time = SUM(cpu_time)
FROM sys.dm_exec_requests WAITFOR DELAY '00:00:05'SELECT CONVERT(DECIMAL(5,
         2),
         ((SUM(cpu_time) - @init_sum_cpu_time) / (@utilizedCpuCount * 5000.00)) * 100) AS [CPU FROM Queries AS Percent of Total CPU Capacity]
FROM sys.dm_exec_requests

若要确定当前负责高 CPU 活动的查询，请运行以下语句：

SELECT TOP 10 s.session_id,
           r.status,
           r.cpu_time,
           r.logical_reads,
           r.reads,
           r.writes,
           r.total_elapsed_time / (1000 * 60) 'Elaps M',
           SUBSTRING(st.TEXT, (r.statement_start_offset / 2) + 1,
           ((CASE r.statement_end_offset
                WHEN -1 THEN DATALENGTH(st.TEXT)
                ELSE r.statement_end_offset
            END - r.statement_start_offset) / 2) + 1) AS statement_text,
           COALESCE(QUOTENAME(DB_NAME(st.dbid)) + N'.' + QUOTENAME(OBJECT_SCHEMA_NAME(st.objectid, st.dbid)) 
           + N'.' + QUOTENAME(OBJECT_NAME(st.objectid, st.dbid)), '') AS command_text,
           r.command,
           s.login_name,
           s.host_name,
           s.program_name,
           s.last_request_end_time,
           s.login_time,
           r.open_transaction_count
FROM sys.dm_exec_sessions AS s
JOIN sys.dm_exec_requests AS r ON r.session_id = s.session_id CROSS APPLY sys.Dm_exec_sql_text(r.sql_handle) AS st
WHERE r.session_id != @@SPID
ORDER BY r.cpu_time DESC

如果查询目前未驱动 CPU，可以运行以下语句来查找历史占用大量 CPU 的查询：

SELECT TOP 10 st.text AS batch_text,
    SUBSTRING(st.TEXT, (qs.statement_start_offset / 2) + 1, ((CASE qs.statement_end_offset WHEN - 1 THEN DATALENGTH(st.TEXT) ELSE qs.statement_end_offset END - qs.statement_start_offset) / 2) + 1) AS statement_text,
    (qs.total_worker_time / 1000) / qs.execution_count AS avg_cpu_time_ms,
    (qs.total_elapsed_time / 1000) / qs.execution_count AS avg_elapsed_time_ms,
    qs.total_logical_reads / qs.execution_count AS avg_logical_reads,
    (qs.total_worker_time / 1000) AS cumulative_cpu_time_all_executions_ms,
    (qs.total_elapsed_time / 1000) AS cumulative_elapsed_time_all_executions_ms
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(sql_handle) st
ORDER BY(qs.total_worker_time / qs.execution_count) DESC

步骤 3：更新统计信息

在确定 CPU 占用最高的查询后，请更新这些查询使用的表的“更新统计信息” 。可以使用 sp_updatestats 系统存储过程更新当前数据库中所有用户定义表和内部表的统计信息。

exec sp_updatestats

sp_updatestats系统存储过程针对当前数据库中的所有用户定义表和内部表运行UPDATE STATISTICS。

步骤 4：添加缺失索引

缺少索引可能导致运行速度较慢的查询和 CPU 使用率过高。可以识别缺失的索引并创建这些索引，以改善这种性能影响。

-- Captures the Total CPU time spent by a query along with the query plan and total executions
SELECT
    qs_cpu.total_worker_time / 1000 AS total_cpu_time_ms,
    q.[text],
    p.query_plan,
    qs_cpu.execution_count,
    q.dbid,
    q.objectid,
    q.encrypted AS text_encrypted
FROM
    (SELECT TOP 500 qs.plan_handle,
     qs.total_worker_time,
     qs.execution_count FROM sys.dm_exec_query_stats qs ORDER BY qs.total_worker_time DESC) AS qs_cpu
CROSS APPLY sys.dm_exec_sql_text(plan_handle) AS q
CROSS APPLY sys.dm_exec_query_plan(plan_handle) p
WHERE p.query_plan.exist('declare namespace 
        qplan = "http://schemas.microsoft.com/sqlserver/2004/07/showplan";
        //qplan:MissingIndexes')=1

2、查看已标识查询的执行计划，并通过进行所需的更改来优化查询。以下屏幕截图显示了一个示例，其中 SQL Server 将指出查询的缺失索引。右键单击查询计划的“缺失索引”部分，然后选择“缺少索引详细信息”，在 SQL Server Management Studio 的另一个窗口中创建索引。

3. 使用以下查询检查是否缺少索引，并应用具有高改进度量值的任何建议索引。从输出中具有最高 improvement_measure 值的前 5 或 10 条建议开始。这些索引对性能有最显著的积极影响。确定是否要应用这些索引，并确保对应用程序进行了性能测试。然后，继续应用缺失索引建议，直到获得所需的应用程序性能结果。

SELECT CONVERT(VARCHAR(30), GETDATE(), 126) AS runtime,
    mig.index_group_handle,
    mid.index_handle,
    CONVERT(DECIMAL(28, 1), migs.avg_total_user_cost * migs.avg_user_impact * (migs.user_seeks + migs.user_scans)) AS improvement_measure,
    'CREATE INDEX missing_index_' + CONVERT(VARCHAR, mig.index_group_handle) + '_' + CONVERT(VARCHAR, mid.index_handle) + ' ON ' + mid.statement + ' (' + ISNULL(mid.equality_columns,
        '') + CASE WHEN mid.equality_columns IS NOT NULL
AND mid.inequality_columns IS NOT NULL THEN ','
ELSE ''
END + ISNULL(mid.inequality_columns,
        '') + ')' + ISNULL(' INCLUDE (' + mid.included_columns + ')',
        '') AS create_index_statement,
    migs.*,
    mid.database_id,
    mid.[object_id]
FROM sys.dm_db_missing_index_groups mig
INNER JOIN sys.dm_db_missing_index_group_stats migs ON migs.group_handle = mig.index_group_handle
INNER JOIN sys.dm_db_missing_index_details mid ON mig.index_handle = mid.index_handle
WHERE CONVERT (DECIMAL (28, 1),
               migs.avg_total_user_cost * migs.avg_user_impact * (migs.user_seeks + migs.user_scans)) > 10
ORDER BY migs.avg_total_user_cost * migs.avg_user_impact * (migs.user_seeks + migs.user_scans) DESC

步骤 5：调查并解决参数敏感问题

可以使用 DBCC FREEPROCCACHE 命令释放计划缓存，并检查这是否解决了 CPU 使用率过高的问题。如果问题已修复，则表示是参数敏感问题（PSP，也称为“参数探查问题”）。

使用不带参数的 DBCC FREEPROCCACHE 将从计划缓存中删除所有已编译的计划。这将导致再次编译新的查询执行，从而导致每个新查询的持续时间一次性延长。最佳方法是使用 DBCC FREEPROCCACHE ( plan_handle | sql_handle ) 来识别导致问题的查询，然后解决单个查询或有问题的查询。

将 DBCC FREEPROCCACHE 命令用作临时解决方案，直到应用程序代码修复为止。可以使用 DBCC FREEPROCCACHE (plan_handle) 命令仅删除导致问题的计划。例如，若要查找引用 AdventureWorks 中 Person.Person 表的查询计划，可以使用此查询查找查询句柄。然后，可以使用查询结果第二列中生成的 DBCC FREEPROCCACHE (plan_handle)，从缓存中释放特定查询计划。

SELECT text, 'DBCC FREEPROCCACHE (0x' + CONVERT(VARCHAR (512), plan_handle, 2) + ')' AS dbcc_freeproc_command FROM sys.dm_exec_cached_plans
CROSS APPLY sys.dm_exec_query_plan(plan_handle)
CROSS APPLY sys.dm_exec_sql_text(plan_handle)
WHERE text LIKE '%person.person%'

步骤 6：调查并解决 SARGability 问题

当 SQL Server 引擎可以使用索引查找来加快查询的执行速度时，查询中的谓词将被视为 SARGable (Search ARGument-able)。许多查询设计会阻止 SARGability，并导致表或索引扫描和 CPU 使用率过高。请考虑 AdventureWorks 数据库的以下查询，其中必须检索每个 ProductNumber 并向其应用 SUBSTRING() 函数，然后再将其与字符串文本值进行比较。如你所见，必须先提取表的所有行，然后应用函数，然后才能进行比较。从表中提取所有行意味着表或索引扫描，这会导致 CPU 使用率较高。

# 函数查询方法
SELECT ProductID, Name, ProductNumber
FROM [Production].[Product]
WHERE SUBSTRING(ProductNumber, 0, 4) =  'HN-'
# 谓词模糊方法
SELECT ProductID, Name, ProductNumber
FROM [Production].[Product]
WHERE Name LIKE  'Hex%'
# 谓词计算方法 
SELECT DISTINCT SalesOrderID, UnitPrice, UnitPrice * 0.10 [10% Commission]
FROM [Sales].[SalesOrderDetail]
WHERE UnitPrice * 0.10 > 300

步骤 7：禁用重度跟踪

检查 SQL 跟踪或 XEvent 跟踪，这些跟踪会影响 SQL Server 性能并导致 CPU 使用率过高。例如，如果跟踪重度 SQL Server 活动，使用以下事件可能会导致 CPU 使用率过高：

查询计划 XML 事件 (query_plan_profile, query_post_compilation_showplan, query_post_execution_plan_profile,
query_post_execution_showplan, query_pre_execution_showplan)
语句级事件 (sql_statement_completed, sql_statement_starting, sp_statement_starting, sp_statement_completed)
登录和注销事件 (login, process_login_finish, login_event, logout)
锁定事件 (lock_acquired, lock_cancel, lock_released)
等待事件 (wait_info, wait_info_external)
SQL 审核事件（取决于该组中审核的组和 SQL Server 活动）

运行以下查询以确定活动的 XEvent 或 Server 跟踪：

PRINT '--Profiler trace summary--'
SELECT traceid, property, CONVERT(VARCHAR(1024), value) AS value FROM::fn_trace_getinfo(
    default)
GO
PRINT '--Trace event details--'
SELECT trace_id,
    status,
    CASE WHEN row_number = 1 THEN path ELSE NULL end AS path,
    CASE WHEN row_number = 1 THEN max_size ELSE NULL end AS max_size,
    CASE WHEN row_number = 1 THEN start_time ELSE NULL end AS start_time,
    CASE WHEN row_number = 1 THEN stop_time ELSE NULL end AS stop_time,
    max_files,
    is_rowset,
    is_rollover,
    is_shutdown,
    is_default,
    buffer_count,
    buffer_size,
    last_event_time,
    event_count,
    trace_event_id,
    trace_event_name,
    trace_column_id,
    trace_column_name,
    expensive_event
FROM
    (SELECT t.id AS trace_id,
     row_number() over(PARTITION BY t.id order by te.trace_event_id, tc.trace_column_id) AS row_number,
     t.status,
     t.path,
     t.max_size,
     t.start_time,
     t.stop_time,
     t.max_files,
     t.is_rowset,
     t.is_rollover,
     t.is_shutdown,
     t.is_default,
     t.buffer_count,
     t.buffer_size,
     t.last_event_time,
     t.event_count,
     te.trace_event_id,
     te.name AS trace_event_name,
     tc.trace_column_id,
     tc.name AS trace_column_name,
     CASE WHEN te.trace_event_id in (23, 24, 40, 41, 44, 45, 51, 52, 54, 68, 96, 97, 98, 113, 114, 122, 146, 180) THEN CAST(1 as bit) ELSE CAST(0 AS BIT) END AS expensive_event FROM sys.traces t CROSS APPLY::fn_trace_geteventinfo(t.id) AS e JOIN sys.trace_events te ON te.trace_event_id = e.eventid JOIN sys.trace_columns tc ON e.columnid = trace_column_id) AS x
GO
PRINT '--XEvent Session Details--'
SELECT sess.NAME 'session_name', event_name, xe_event_name, trace_event_id,
    CASE WHEN xemap.trace_event_id IN(23, 24, 40, 41, 44, 45, 51, 52, 54, 68, 96, 97, 98, 113, 114, 122, 146, 180) 
    THEN Cast(1 AS BIT)
ELSE Cast(0 AS BIT)
END AS expensive_event
FROM sys.dm_xe_sessions sess
JOIN sys.dm_xe_session_events evt
ON sess.address = evt.event_session_address
INNER JOIN sys.trace_xe_event_map xemap
ON evt.event_name = xemap.xe_event_name
GO

步骤 8：配置虚拟机

如果使用的是虚拟机，请确保不会过度预配 CPU 并正确配置它们。
解决 ESX/ESXi 虚拟机性能问题 (2001003)

步骤 9：纵向扩展系统以使用更多 CPU

如果单个查询实例的 CPU 占用很低，但所有查询的总体工作负载共同导致 CPU 占用较高，请考虑通过添加更多 CPU 来纵向扩展计算机。使用以下查询找出超过单个执行的平均 CPU 占用和最大 CPU 占用的特定阈值且已在系统上多次运行的查询数：

-- Shows queries where Max and average CPU time exceeds 200 ms and executed more than 1000 times
DECLARE @cputime_threshold_microsec INT = 200*1000
DECLARE @execution_count INT = 1000
SELECT qs.total_worker_time/1000 total_cpu_time_ms,
       qs.max_worker_time/1000 max_cpu_time_ms,
       (qs.total_worker_time/1000)/execution_count average_cpu_time_ms,
       qs.execution_count,
       q.[text]
FROM sys.dm_exec_query_stats qs CROSS APPLY sys.dm_exec_sql_text(plan_handle) AS q
WHERE (qs.total_worker_time/execution_count > @cputime_threshold_microsec
        OR qs.max_worker_time > @cputime_threshold_microsec )
        AND execution_count > @execution_count
ORDER BY  qs.total_worker_time DESC

三、常用sql检查语句：

1、排查连接对象

此脚本可以查看到主机名和连接对象，如果连接对象不属于已知的软件，在防火墙中将此IP禁止掉即可

--如果想要指定查询某个数据库，将后面的注释去掉即可
SELECT * FROM sys.[sysprocesses] WHERE [spid]>50 --AND DB_NAME([dbid])='hisdb'

2、查询sql阻塞

然后使用下面语句看一下各项指标是否正常，是否有阻塞，正常情况下搜索结果应该为空。

SELECT [session_id],
[request_id],
[start_time] AS '开始时间',
[status] AS '状态',
[command] AS '命令',
dest.[text] AS 'sql语句',
DB_NAME([database_id]) AS '数据库名',
[blocking_session_id] AS '正在阻塞其他的ID',
[wait_type] AS '等待资源类型',
[wait_time] AS '等待时间',
[wait_resource] AS '等待的资源',
[reads] AS '物理读次数',
[writes] AS '写次数',
[logical_reads] AS '逻辑读次数',
[row_count] AS '返回结果行数'
FROM sys.[dm_exec_requests] AS der
CROSS APPLY
sys.[dm_exec_sql_text](der.[sql_handle]) AS dest
WHERE [session_id]>50
ORDER BY [cpu_time] DESC

---查看是哪些SQL语句占用较大可以使用下面代码
SELECT TOP 10
dest.[text] AS 'sql语句'
FROM sys.[dm_exec_requests] AS der
CROSS APPLY
sys.[dm_exec_sql_text](der.[sql_handle]) AS dest
WHERE [session_id]>50
ORDER BY [cpu_time] DESC

3、查询worker等待

如果SQLSERVER存在要等待的资源，那么执行下面语句就会显示出会话中有多少个worker在等待

SELECT [session_id],
 [request_id],
 [start_time] AS '开始时间',
 [status] AS '状态',
 [command] AS '命令',
 dest.[text] AS 'sql语句',
 DB_NAME([database_id]) AS '数据库名',
 [blocking_session_id] AS '正在阻塞其他的ID',
 der.[wait_type] AS '等待资源类型',
 [wait_time] AS '等待时间',
 [wait_resource] AS '等待的资源',
 [dows].[waiting_tasks_count] AS '当前等待任务数',
 [reads] AS '物理读次数',
 [writes] AS '写次数',
 [logical_reads] AS '逻辑读次数',
 [row_count] AS '返回结果行数'
 FROM sys.[dm_exec_requests] AS der
 INNER JOIN [sys].[dm_os_wait_stats] AS dows
 ON der.[wait_type]=[dows].[wait_type]
 CROSS APPLY
 sys.[dm_exec_sql_text](der.[sql_handle]) AS dest
 WHERE [session_id]>50
 ORDER BY [cpu_time] DESC

查询CPU占用最高的SQL语句

SELECT total_worker_time/execution_count AS avg_cpu_cost, plan_handle,
   execution_count,
   (SELECT SUBSTRING(text, statement_start_offset/2 + 1,
      (CASE WHEN statement_end_offset = -1
         THEN LEN(CONVERT(nvarchar(max), text)) * 2
         ELSE statement_end_offset
      END - statement_start_offset)/2)
   FROM sys.dm_exec_sql_text(sql_handle)) AS query_text
FROM sys.dm_exec_query_stats
ORDER BY [avg_cpu_cost] DESC

4、索引缺失查询

如果索引缺失的话，需要根据查询结果中的关键信息逐一添加

SELECT DatabaseName = DB_NAME(database_id)
    ,[Number Indexes Missing] = count(*)
FROM sys.dm_db_missing_index_details
GROUP BY DB_NAME(database_id)
ORDER BY 2 DESC;
SELECT  [Total Cost]  = ROUND(avg_total_user_cost * avg_user_impact * (user_seeks + user_scans),0)
    , avg_user_impact
    , TableName = statement
    , [EqualityUsage] = equality_columns
    , [InequalityUsage] = inequality_columns
    , [Include Cloumns] = included_columns
FROM        sys.dm_db_missing_index_groups g
INNER JOIN    sys.dm_db_missing_index_group_stats s ON s.group_handle = g.index_group_handle
INNER JOIN    sys.dm_db_missing_index_details d ON d.index_handle = g.index_handle
ORDER BY [Total Cost] DESC;

5、占用cpu高100%排查

sqlserver占用cpu高100%排查，可根据下面语句查询当前耗时最高的sql语句，以及读写次数。

SELECT TOP 50
 [session_id],
 [request_id],
 [cpu_time],
 [start_time] AS '开始时间',
 [status] AS '状态',
 [command] AS '命令',
 dest.[text] AS 'sql语句',
 DB_NAME([database_id]) AS '数据库名',
 [blocking_session_id] AS '正在阻塞其他会话的会话ID',
 der.[wait_type] AS '等待资源类型',
 [wait_time] AS '等待时间',
 [wait_resource] AS '等待的资源',
 [dows].[waiting_tasks_count] AS '当前正在进行等待的任务数',
 [reads] AS '物理读次数',
 [writes] AS '写次数',
 [logical_reads] AS '逻辑读次数',
 [row_count] AS '返回结果行数'
 FROM sys.[dm_exec_requests] AS der
 INNER JOIN [sys].[dm_os_wait_stats] AS dows
 ON der.[wait_type]=[dows].[wait_type]
 CROSS APPLY
 sys.[dm_exec_sql_text](der.[sql_handle]) AS dest
 WHERE [session_id]>50
 ORDER BY [cpu_time] DESC

四、其他常用sql

--------------------SQL Server启动时间
SELECT sqlserver_start_time FROM sys.dm_os_sys_info;
 
 
--------------------SQL Server版本
select @@version;
 
 
--------------------数据库正在执行的sql
select text,wait_resource,wait_time,wait_type,session_id,blocking_session_id
from sys.dm_exec_requests cross apply sys.dm_exec_sql_text(sql_handle);
 
select request_session_id spid,OBJECT_NAME(resource_associated_entity_id) tableName 
from sys.dm_tran_locks where resource_type='OBJECT';
 
--------------------死锁
select a.text seesion_text,a.wait_type,a.wait_time,a.session_id,a.blocking_session_id,b.text blocking_session_text from  
 (select text,wait_resource,wait_type,wait_time,session_id,blocking_session_id from sys.dm_exec_requests  
 cross apply sys.dm_exec_sql_text(sql_handle) where wait_time>1000) a left join 
 (select c.session_id,t.text from sys.dm_exec_connections as c cross apply sys.dm_exec_sql_text(most_recent_sql_handle) as t) b  
 on b.session_id=a.blocking_session_id;
 
 
--------------------获取一次性缓存计划的数量
SELECT objtype, cacheobjtype, 
  AVG(usecounts) AS Avg_UseCount, 
  SUM(refcounts) AS AllRefObjects, 
  SUM(CAST(size_in_bytes AS bigint))/1024/1024 AS Size_MB
FROM sys.dm_exec_cached_plans
WHERE objtype = 'Adhoc' AND usecounts = 1
GROUP BY objtype, cacheobjtype;
 
SELECT objtype, cacheobjtype, 
  AVG(usecounts) AS Avg_UseCount, 
  SUM(refcounts) AS AllRefObjects, 
  SUM(CAST(size_in_bytes AS bigint))/1024/1024 AS Size_MB
FROM sys.dm_exec_cached_plans
WHERE objtype = 'Adhoc' AND usecounts > 1
GROUP BY objtype, cacheobjtype;
 
 
--------------------操作系统内存建议
-- You want to see "Available physical memory is high"
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 Physical_memory_in_use_MB, 
    large_page_allocations_kb/1024 Large_page_allocations_MB, 
    locked_page_allocations_kb/1024 Locked_page_allocations_MB,
    virtual_address_space_reserved_kb/1024 VAS_reserved_MB, 
    virtual_address_space_committed_kb/1024 VAS_committed_MB, 
    virtual_address_space_available_kb/1024 VAS_available_MB,
    page_fault_count Page_fault_count,
    memory_utilization_percentage Memory_utilization_percentage, 
    process_physical_memory_low Process_physical_memory_low, 
    process_virtual_memory_low Process_virtual_memory_low
FROM sys.dm_os_process_memory;
 
 
--------------------恢复日志描述模型，复用等，日志大小和日志文件大小
SELECT db.[name] AS [Database Name], SUSER_SNAME(db.owner_sid) AS [Database Owner], db.recovery_model_desc AS [Recovery Model], 
db.log_reuse_wait_desc AS [Log Reuse Wait Description], 
CAST((CAST(ls.cntr_value AS FLOAT)/1024)AS DECIMAL(18,2)) AS [Log Size (MB)],
CAST((CAST(lu.cntr_value AS FLOAT)/1024)AS DECIMAL(18,2)) AS [Log Used (MB)],
CAST(CAST(lu.cntr_value AS FLOAT) / CAST(ls.cntr_value AS FLOAT)AS DECIMAL(18,2)) * 100 AS [Log Used %], 
db.[compatibility_level] AS [DB Compatibility Level], 
db.page_verify_option_desc AS [Page Verify Option],db.is_auto_update_stats_on,
db.is_auto_update_stats_async_on,
db.is_auto_close_on, db.is_auto_shrink_on
FROM sys.databases AS db WITH (NOLOCK)
INNER JOIN sys.dm_os_performance_counters AS lu WITH (NOLOCK)
ON db.name = lu.instance_name
INNER JOIN sys.dm_os_performance_counters AS ls WITH (NOLOCK) 
ON db.name = ls.instance_name
WHERE lu.counter_name LIKE N'Log File(s) Used Size (KB)%' 
AND ls.counter_name LIKE N'Log File(s) Size (KB)%'
AND ls.cntr_value > 0
ORDER BY db.[name] OPTION (RECOMPILE);
 
 
 
 
--------------------按索引优势列出的所有数据库缺少索引
SELECT CONVERT(decimal(18,2), user_seeks * avg_total_user_cost * (avg_user_impact * 0.01)) AS [index_advantage],  
migs.last_user_seek, mid.[statement] AS [Database.Schema.Table],
mid.equality_columns, mid.inequality_columns, mid.included_columns,
migs.unique_compiles, migs.user_seeks, migs.avg_total_user_cost, migs.avg_user_impact
FROM sys.dm_db_missing_index_group_stats AS migs WITH (NOLOCK)
INNER JOIN sys.dm_db_missing_index_groups AS mig WITH (NOLOCK)
ON migs.group_handle = mig.index_group_handle
INNER JOIN sys.dm_db_missing_index_details AS mid WITH (NOLOCK)
ON mig.index_handle = mid.index_handle
ORDER BY index_advantage DESC OPTION (RECOMPILE);
 
 
--------------------查看CPU占用量最高的会话及SQL语句
select spid,cmd,cpu,physical_io,memusage,
(select top 1 [text] from ::fn_get_sql(sql_handle)) sql_text
from master..sysprocesses order by cpu desc,physical_io desc
 
 
--------------------查看缓存重用次数少，内存占用大的SQL语句
SELECT TOP 100 usecounts, objtype, p.size_in_bytes,[sql].[text] 
FROM sys.dm_exec_cached_plans p OUTER APPLY sys.dm_exec_sql_text (p.plan_handle) sql 
ORDER BY usecounts,p.size_in_bytes  desc
 
 
--------------------查看哪些进程在使用
select * from sysprocesses


-----------------查找数据库中内存占用高的sql语句。------------------
SELECT s2.dbid,
s1.sql_handle,
(SELECT TOP 1 SUBSTRING(s2.text,statement_start_offset / 2+1,
((CASE WHEN statement_end_offset = -1
THEN (LEN(CONVERT(nvarchar(max),s2.text)) * 2)
ELSE statement_end_offset END) - statement_start_offset) / 2+1)) AS sql_statement,
execution_count,
plan_generation_num,
last_execution_time,
total_worker_time,
last_worker_time,
min_worker_time,
max_worker_time,
total_physical_reads,
last_physical_reads,
min_physical_reads,
max_physical_reads,
total_logical_writes,
last_logical_writes,
min_logical_writes,
max_logical_writes
FROM sys.dm_exec_query_stats AS s1
CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS s2
WHERE s2.objectid is null
ORDER BY last_worker_time desc,s1.sql_handle, s1.statement_start_offset, s1.statement_end_offset;

-------------查询当前高CPU活动------
select top 10 s.session_id,
r.status,r.cpu_time,r.logical_reads,r.reads,r.writes,r.total_elapsed_time/(1000*60) 'elaps m',
SUBSTRING(st.text,(r.statement_start_offset/2)+1,((case r.statement_end_offset when -1 then datalength(st.text) else r.statement_end_offset end -r.statement_start_offset)/2)+1) as statement_text,
coalesce(quotename(db_name(st.dbid))+N'.'+quotename(object_schema_name(st.objectid,st.dbid))+N'.'+quotename(object_name(st.objectid,st.dbid)),'') as command_text,
r.command,s.login_name,s.host_name,s.program_name,s.last_request_end_time,s.login_time,r.open_transaction_count
from sys.dm_exec_sessions as s 
join sys.dm_exec_requests as r on r.session_id=s.session_id 
cross apply sys.dm_exec_sql_text(sql_handle) st
order by r.cpu_time desc 



----------查询历史占用大量CPU情况----------------
select top 10 st.text as batch_text,
SUBSTRING(st.text,(qs.statement_start_offset/2)+1,
((case qs.statement_end_offset when -1 then datalength(st.text) else qs.statement_end_offset end -qs.statement_start_offset)/2)+1) as statement_text,
(qs.total_worker_time/1000) / qs.execution_count as avg_cpu_time_ms,
(qs.total_elapsed_time/1000) / qs.execution_count as avg_elapsed_time_ms,
qs.total_logical_reads / qs.execution_count as avg_logical_reads_ms,
(qs.total_worker_time /1000) as cumulative_cpu_time_all_executions_ms,
(qs.total_elapsed_time/1000) as aumulative_elapsed_time_all_executions_ms
from sys.dm_exec_query_stats qs
cross apply sys.dm_exec_sql_text(sql_handle) st
order by (qs.total_worker_time/qs.execution_count) desc


--使用以下语句查找出什么语句占内存最高，针对占内存高的语句进行优化
 
SELECT SS.SUM_EXECUTION_COUNT,
T.TEXT,
SS.SUM_TOTAL_ELAPSED_TIME,
SS.SUM_TOTAL_WORKER_TIME,
SS.SUM_TOTAL_LOGICAL_READS,
SS.SUM_TOTAL_LOGICAL_WRITES
FROM (SELECT S.PLAN_HANDLE,
SUM(S.EXECUTION_COUNT)SUM_EXECUTION_COUNT,
SUM(S.TOTAL_ELAPSED_TIME)SUM_TOTAL_ELAPSED_TIME,
SUM(S.TOTAL_WORKER_TIME)SUM_TOTAL_WORKER_TIME,
SUM(S.TOTAL_LOGICAL_READS)SUM_TOTAL_LOGICAL_READS,
SUM(S.TOTAL_LOGICAL_WRITES)SUM_TOTAL_LOGICAL_WRITES
FROM SYS.DM_EXEC_QUERY_STATS S
GROUP BY S.PLAN_HANDLE
) AS SS
CROSS APPLY SYS.dm_exec_sql_text(SS.PLAN_HANDLE)T
ORDER BY SUM_TOTAL_LOGICAL_READS DESC
 
--方法三：
--大的运算，一般是报表之类的， 找一下最近运行超过3秒的sql，按消耗秒数降序看下就差不多了
SELECT TOP 10 OBJECT_NAME(qt.objectid, qt.dbId)  AS procName,
       DB_NAME(qt.dbId)                   AS [db_name],
       qt.text                            AS SQL_Full,
       SUBSTRING(
           qt.text,
           (qs.statement_start_offset / 2) + 1,
           (
               (
                   CASE statement_end_offset
                        WHEN -1 THEN DATALENGTH(qt.text)
                        ELSE qs.statement_end_offset
                   END 
                   - qs.statement_start_offset
               ) / 2
           ) + 1
       )                                  AS SQL_Part --统计对应的部分语句
       ,
       qs.creation_time,
       qs.last_execution_time,
       qs.execution_count,
       qs.last_elapsed_time / 1000000     AS lastElapsedSeconds,
       qs.last_worker_time / 1000000      AS lastCpuSeconds,
       CAST(
           qs.total_elapsed_time / 1000000.0 / (
               CASE 
                    WHEN qs.execution_count = 0 THEN -1
                    ELSE qs.execution_count
               END
           ) AS DECIMAL(28, 2)
       )                                  AS avgDurationSeconds,
       CAST(qs.last_logical_reads AS BIGINT) * 1.0 / (1024 * 1024) * 8060 AS 
       lastLogicReadsMB,
       qs.last_logical_reads,
       qs.plan_handle
FROM   sys.dm_exec_query_stats qs
       CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS qt
CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) AS p
WHERE  qs.last_execution_time >= CONVERT(CHAR(10),GETDATE(),120)+' 08:00'	--今天8点之后的慢SQL
       AND qs.last_elapsed_time >= 3 * 1000 * 1000			                --只取执行时间大于 3 秒的记录
       AND qt.[text] NOT LIKE '%Proc_DBA%'
ORDER BY
       qs.last_worker_time DESC