SRE文档

深浅模式

在监控页面选择资源监控或引擎监控，并选择查询时间，即可查看相应的监控数据，具体监控项介绍如下。

类别监控项说明
资源监控磁盘空间实例的磁盘空间使用量，包括：磁盘空间总体使用量数据空间使用量日志空间使用量临时文件空间使用量系统文件空间使用量单位：MByte。
IOPS实例的每秒I/O请求次数。单位：次/秒。
当前总连接数实例当前总连接数。
MSSQL实例CPU使用率（占操作系统总数 %）实例的CPU使用率（含操作系统占用）。
SQL Server实例平均每秒钟的输入/流出流量实例每秒钟的输入、输出流量，单位：KB。说明 为了更精确体现SQL Server的网络带宽详情，RDS SQL Server基础版，高可用版和集群版实例直接从Windows网卡中采集流量使用情况。
引擎监控平均每秒事务数每秒钟事务处理数。
平均每秒SQL语句执行次数每秒钟SQL语句执行次数。
缓存命中率缓存池的读命中率。
每秒检查点写入Page数实例中每秒检查点写入Page数。
每秒登录次数实例中每秒登录次数。
平均每秒全表扫描数每秒全表扫描次数。
每秒SQL编译实例中每秒编译的SQL语句数。
每秒锁超时次数实例中每秒锁超时次数。
每秒死锁次数实例中每秒锁定次数。
每秒锁等待次数实例中每秒锁等待次数。

1.监控 ​

1.1数据库连接数 ​

sql
-- 使用中的数据库连接数
select cntr_value from sys.dm_os_performance_counters where counter_name = 'User Connections';
-- 使用中的数据库连接数
select cntr_value from sys.dm_os_performance_counters where counter_name = 'User Connections';

数据库状态 ​

sql
select name, state, state_desc from sys.databases;
select name, state, state_desc from sys.databases;

数据库文件状态 ​

sql
select a.name, b.physical_name, b.state, b.state_desc from sys.databases as a, sys.master_files as b where a.database_id = b.database_id;
select a.name, b.physical_name, b.state, b.state_desc from sys.databases as a, sys.master_files as b where a.database_id = b.database_id;

1.2平均每秒事务数TPS ​

sql
-- 平均每秒事务数:
select cntr_value from sys.dm_os_performance_counters where counter_name = 'Transactions/sec' and instance_name = '_Total'
-- 平均每秒事务数:
select cntr_value from sys.dm_os_performance_counters where counter_name = 'Transactions/sec' and instance_name = '_Total'

1.3缓存命中率 ​

sql
SELECT (a.cntr_value * 1.0 / b.cntr_value) * 100.0 as BufferCacheHitRatio
FROM sys.dm_os_performance_counters a
JOIN (SELECT cntr_value, OBJECT_NAME
FROM sys.dm_os_performance_counters
WHERE counter_name = 'Buffer cache hit ratio base'
AND OBJECT_NAME = 'SQLServer:Buffer Manager') b ON a.OBJECT_NAME = b.OBJECT_NAME
WHERE a.counter_name = 'Buffer cache hit ratio'
AND a.OBJECT_NAME = 'SQLServer:Buffer Manager'
SELECT (a.cntr_value * 1.0 / b.cntr_value) * 100.0 as BufferCacheHitRatio
FROM sys.dm_os_performance_counters a
JOIN (SELECT cntr_value, OBJECT_NAME
FROM sys.dm_os_performance_counters
WHERE counter_name = 'Buffer cache hit ratio base'
AND OBJECT_NAME = 'SQLServer:Buffer Manager') b ON a.OBJECT_NAME = b.OBJECT_NAME
WHERE a.counter_name = 'Buffer cache hit ratio'
AND a.OBJECT_NAME = 'SQLServer:Buffer Manager'

1.3平均每秒SQL编译数 ​

sql
select cntr_value from sys.dm_os_performance_counters where counter_name = 'SQL Compilations/sec';
select cntr_value from sys.dm_os_performance_counters where counter_name = 'SQL Compilations/sec';

平均每秒SQL重编译数 ​

sql
select cntr_value from sys.dm_os_performance_counters where counter_name = 'SQL Re-Compilations/sec';
select cntr_value from sys.dm_os_performance_counters where counter_name = 'SQL Re-Compilations/sec';

1.4每秒全表扫描数 ​

sql
-- 每秒全表扫描数:
select cntr_value from sys.dm_os_performance_counters where counter_name = 'Full Scans/sec';
-- 每秒全表扫描数:
select cntr_value from sys.dm_os_performance_counters where counter_name = 'Full Scans/sec';

平均每秒batch数 ​

sql
select cntr_value from sys.dm_os_performance_counters where counter_name = 'Batch Requests/sec';
select cntr_value from sys.dm_os_performance_counters where counter_name = 'Batch Requests/sec';

每秒用户错误数 ​

sql
select cntr_value from sys.dm_os_performance_counters where counter_name = 'Errors/sec' and instance_name = '_Total';
select cntr_value from sys.dm_os_performance_counters where counter_name = 'Errors/sec' and instance_name = '_Total';

1.5锁 ​

每秒锁等待次数: ​

sql
select cntr_value from sys.dm_os_performance_counters where counter_name = 'Lock Waits/sec'and instance_name = '_Total';
select cntr_value from sys.dm_os_performance_counters where counter_name = 'Lock Waits/sec'and instance_name = '_Total';

每秒锁请求次数 ​

sql
select cntr_value from sys.dm_os_performance_counters where counter_name = 'Lock Requests/sec'and instance_name = '_Total';
select cntr_value from sys.dm_os_performance_counters where counter_name = 'Lock Requests/sec'and instance_name = '_Total';

每秒锁超时次数 ​

sql
select cntr_value from sys.dm_os_performance_counters where counter_name = 'Lock Timeouts/sec'and instance_name = '_Total';
select cntr_value from sys.dm_os_performance_counters where counter_name = 'Lock Timeouts/sec'and instance_name = '_Total';

每秒死锁次数 ​

sql
select cntr_value from sys.dm_os_performance_counters where counter_name = 'Number Of Deadlocks/sec'and instance_name = '_Total';
select cntr_value from sys.dm_os_performance_counters where counter_name = 'Number Of Deadlocks/sec'and instance_name = '_Total';

查看死锁 ​

sql
--查询死锁
SELECT request_session_id spid,OBJECT_NAME(resource_associated_entity_id)tableName
FROM sys.dm_tran_locks
WHERE resource_type='OBJECT '

--杀死死锁
KILL 155

--显示死锁相关信息
exec sp_who2 137
--查询死锁
SELECT request_session_id spid,OBJECT_NAME(resource_associated_entity_id)tableName
FROM sys.dm_tran_locks
WHERE resource_type='OBJECT '

--杀死死锁
KILL 155

--显示死锁相关信息
exec sp_who2 137

死锁跟踪 ​

方法一：SQL code

DBCC TRACEON (3605,1204,1222,-1)  

说明：
3605 
将DBCC的结果输出到错误日志。
1204 返回参与死锁的锁的资源和类型，以及受影响的当前命令。
1222 
返回参与死锁的锁的资源和类型，以及使用了不符合任何 XSD 架构的 XML 格式的受影响的当前命令(比1204更进一步,SQL 
2005及以上可用)。
-1 以全局方式打开指定的跟踪标记。

以上跟踪标志作用域都是全局，即在SQL 
Server运行过程中，会一直发挥作用，直到SQL Server重启。

如 果要确保SQL Server在重启后自动开启这些标志，可以在SQL 
Server服务启动选项中，使用 /T 启动选项指定跟踪标志在启动期
间设置为开。(位于SQL Server配置管理器->SQL 
Server服务->SQL Server->属性->高级->启动参数)

在运行上面的语句后，当SQL 
Server中发生死锁时，已经可以在错误日志中看到了，但还不够直观(和其它信息混在一起)。（SSMS
 -> SQL Server实例 -> 
管理 -> SQL Server日志）
方法一：SQL code

DBCC TRACEON (3605,1204,1222,-1)  

说明：
3605 
将DBCC的结果输出到错误日志。
1204 返回参与死锁的锁的资源和类型，以及受影响的当前命令。
1222 
返回参与死锁的锁的资源和类型，以及使用了不符合任何 XSD 架构的 XML 格式的受影响的当前命令(比1204更进一步,SQL 
2005及以上可用)。
-1 以全局方式打开指定的跟踪标记。

以上跟踪标志作用域都是全局，即在SQL 
Server运行过程中，会一直发挥作用，直到SQL Server重启。

如 果要确保SQL Server在重启后自动开启这些标志，可以在SQL 
Server服务启动选项中，使用 /T 启动选项指定跟踪标志在启动期
间设置为开。(位于SQL Server配置管理器->SQL 
Server服务->SQL Server->属性->高级->启动参数)

在运行上面的语句后，当SQL 
Server中发生死锁时，已经可以在错误日志中看到了，但还不够直观(和其它信息混在一起)。（SSMS
 -> SQL Server实例 -> 
管理 -> SQL Server日志）
二扩展事件会话信息

--扩展事件会话的信息
select * from sys.dm_xe_sessions where name = 'system_health'


SELECT 
	xed.value('@timestamp','datetime')as Creation_Date,  
	xed.query('.')AS Extend_Event  
FROM 
(  
	SELECT CAST([target_data] AS XML)AS Target_Data  
	FROM sys.dm_xe_session_targets AS xt  
	INNER JOIN sys.dm_xe_sessions AS xs  
	ON xs.address= xt.event_session_address  
	WHERE xs.name=N'system_health'  
	AND xt.target_name=N'ring_buffer'
) AS XML_Data  
CROSS APPLY Target_Data.nodes('RingBufferTarget/event[@name="xml_deadlock_report"]')AS XEventData(xed)  
ORDER BY Creation_Date DESC
二扩展事件会话信息

--扩展事件会话的信息
select * from sys.dm_xe_sessions where name = 'system_health'


SELECT 
	xed.value('@timestamp','datetime')as Creation_Date,  
	xed.query('.')AS Extend_Event  
FROM 
(  
	SELECT CAST([target_data] AS XML)AS Target_Data  
	FROM sys.dm_xe_session_targets AS xt  
	INNER JOIN sys.dm_xe_sessions AS xs  
	ON xs.address= xt.event_session_address  
	WHERE xs.name=N'system_health'  
	AND xt.target_name=N'ring_buffer'
) AS XML_Data  
CROSS APPLY Target_Data.nodes('RingBufferTarget/event[@name="xml_deadlock_report"]')AS XEventData(xed)  
ORDER BY Creation_Date DESC
方法三：sql perfiler跟踪
方法三：sql perfiler跟踪

1.6每秒检查点写入Page数 ​

sql
select cntr_value from sys.dm_os_performance_counters where counter_name = 'Checkpoint pages/sec';
select cntr_value from sys.dm_os_performance_counters where counter_name = 'Checkpoint pages/sec';

Lazy writes/sec: ​

sql
select * from sys.dm_os_performance_counters where counter_name = 'Lazy writes/sec';
select * from sys.dm_os_performance_counters where counter_name = 'Lazy writes/sec';

1.7always on状态 ​

sql
select a.name, b.database_state, b.database_state_desc from sys.databases as a, sys.dm_hadr_database_replica_states as b where a.database_id = b.database_id and b.is_local=1;
select a.name, b.database_state, b.database_state_desc from sys.databases as a, sys.dm_hadr_database_replica_states as b where a.database_id = b.database_id and b.is_local=1;

1.8慢查询 ​

sql
---先清除sql server的缓存
dbcc freeProcCache
---先清除sql server的缓存
dbcc freeProcCache
sql
SELECT creation_time N'语句编译时间'
,last_execution_time N'上次执行时间'
,total_physical_reads N'物理读取总次数'
,total_logical_reads/execution_count N'每次逻辑读次数'
,total_logical_reads N'逻辑读取总次数'
,total_logical_writes N'逻辑写入总次数'
,execution_count N'执行次数'
,total_worker_time/1000 N'所用的CPU总时间ms'
,total_elapsed_time/1000 N'总花费时间ms'
,(total_elapsed_time / execution_count)/1000 N'平均时间ms'
,SUBSTRING(st.text, (qs.statement_start_offset/2) + 1,
((CASE statement_end_offset
WHEN -1 THEN DATALENGTH(st.text)
ELSE qs.statement_end_offset END
- qs.statement_start_offset)/2) + 1) N'执行语句'
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) st
where SUBSTRING(st.text, (qs.statement_start_offset/2) + 1,
((CASE statement_end_offset
WHEN -1 THEN DATALENGTH(st.text)
ELSE qs.statement_end_offset END
- qs.statement_start_offset)/2) + 1) not like '%fetch%'
ORDER BY total_elapsed_time / execution_count DESC;
SELECT creation_time N'语句编译时间'
,last_execution_time N'上次执行时间'
,total_physical_reads N'物理读取总次数'
,total_logical_reads/execution_count N'每次逻辑读次数'
,total_logical_reads N'逻辑读取总次数'
,total_logical_writes N'逻辑写入总次数'
,execution_count N'执行次数'
,total_worker_time/1000 N'所用的CPU总时间ms'
,total_elapsed_time/1000 N'总花费时间ms'
,(total_elapsed_time / execution_count)/1000 N'平均时间ms'
,SUBSTRING(st.text, (qs.statement_start_offset/2) + 1,
((CASE statement_end_offset
WHEN -1 THEN DATALENGTH(st.text)
ELSE qs.statement_end_offset END
- qs.statement_start_offset)/2) + 1) N'执行语句'
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) st
where SUBSTRING(st.text, (qs.statement_start_offset/2) + 1,
((CASE statement_end_offset
WHEN -1 THEN DATALENGTH(st.text)
ELSE qs.statement_end_offset END
- qs.statement_start_offset)/2) + 1) not like '%fetch%'
ORDER BY total_elapsed_time / execution_count DESC;

==执行比较慢==

  • 正在执行的慢查询
sql
SELECT  TOP 1 ST.transaction_id AS TransactionID ,
        st.session_id ,
        DB_NAME(DT.database_id) AS DatabaseName ,
        ses.host_name ,
        ses.login_name ,
        ses.status,
        AT.transaction_begin_time AS TransactionStartTime ,
        s.text ,
        c.connect_time ,
        DATEDIFF(second, AT.transaction_begin_time, GETDATE()) "exec_time(s)" ,
        DATEDIFF(minute, AT.transaction_begin_time, GETDATE()) AS Tran_run_time ,
        CASE AT.transaction_type
          WHEN 1 THEN 'Read/Write Transaction'
          WHEN 2 THEN 'Read-Only Transaction'
          WHEN 3 THEN 'System Transaction'
          WHEN 4 THEN 'Distributed Transaction'
        END AS TransactionType ,
        CASE AT.transaction_state
          WHEN 0 THEN 'Transaction Not Initialized'
          WHEN 1 THEN 'Transaction Initialized & Not Started'
          WHEN 2 THEN 'Active Transaction'
          WHEN 3 THEN 'Transaction Ended'
          WHEN 4 THEN 'Distributed Transaction Initiated Commit Process'
          WHEN 5 THEN 'Transaction in Prepared State & Waiting Resolution'
          WHEN 6 THEN 'Transaction Committed'
          WHEN 7 THEN 'Transaction Rolling Back'
          WHEN 8 THEN 'Transaction Rolled Back'
        END AS TransactionState
FROM    sys.dm_tran_session_transactions AS ST
        INNER JOIN sys.dm_tran_active_transactions AS AT ON ST.transaction_id = AT.transaction_id
        INNER JOIN sys.dm_tran_database_transactions AS DT ON ST.transaction_id = DT.transaction_id
        LEFT JOIN sys.dm_exec_connections AS C ON st.session_id = c.session_id
        LEFT JOIN sys.dm_exec_sessions AS ses ON c.session_id = ses.session_id
        CROSS APPLY sys.dm_exec_sql_text(c.most_recent_sql_Handle) s
WHERE   DATEDIFF(second, AT.transaction_begin_time, GETDATE()) > 2
SELECT  TOP 1 ST.transaction_id AS TransactionID ,
        st.session_id ,
        DB_NAME(DT.database_id) AS DatabaseName ,
        ses.host_name ,
        ses.login_name ,
        ses.status,
        AT.transaction_begin_time AS TransactionStartTime ,
        s.text ,
        c.connect_time ,
        DATEDIFF(second, AT.transaction_begin_time, GETDATE()) "exec_time(s)" ,
        DATEDIFF(minute, AT.transaction_begin_time, GETDATE()) AS Tran_run_time ,
        CASE AT.transaction_type
          WHEN 1 THEN 'Read/Write Transaction'
          WHEN 2 THEN 'Read-Only Transaction'
          WHEN 3 THEN 'System Transaction'
          WHEN 4 THEN 'Distributed Transaction'
        END AS TransactionType ,
        CASE AT.transaction_state
          WHEN 0 THEN 'Transaction Not Initialized'
          WHEN 1 THEN 'Transaction Initialized & Not Started'
          WHEN 2 THEN 'Active Transaction'
          WHEN 3 THEN 'Transaction Ended'
          WHEN 4 THEN 'Distributed Transaction Initiated Commit Process'
          WHEN 5 THEN 'Transaction in Prepared State & Waiting Resolution'
          WHEN 6 THEN 'Transaction Committed'
          WHEN 7 THEN 'Transaction Rolling Back'
          WHEN 8 THEN 'Transaction Rolled Back'
        END AS TransactionState
FROM    sys.dm_tran_session_transactions AS ST
        INNER JOIN sys.dm_tran_active_transactions AS AT ON ST.transaction_id = AT.transaction_id
        INNER JOIN sys.dm_tran_database_transactions AS DT ON ST.transaction_id = DT.transaction_id
        LEFT JOIN sys.dm_exec_connections AS C ON st.session_id = c.session_id
        LEFT JOIN sys.dm_exec_sessions AS ses ON c.session_id = ses.session_id
        CROSS APPLY sys.dm_exec_sql_text(c.most_recent_sql_Handle) s
WHERE   DATEDIFF(second, AT.transaction_begin_time, GETDATE()) > 2
  • 排查历史慢查询
sql
SELECT TOP 20
  [Total IO] = (qs.total_logical_reads + qs.total_logical_writes)
  , [Average IO] = (qs.total_logical_reads + qs.total_logical_writes) /
                                            qs.execution_count
  , qs.execution_count
  , SUBSTRING (qt.text,(qs.statement_start_offset/2) + 1,    
  ((CASE WHEN qs.statement_end_offset = -1
    THEN LEN(CONVERT(NVARCHAR(MAX), qt.text)) * 2
    ELSE qs.statement_end_offset
    END - qs.statement_start_offset)/2) + 1) AS [Individual Query]
  , qt.text AS [Parent Query]
  , DB_NAME(qt.dbid) AS DatabaseName
  , qp.query_plan
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) qp
ORDER BY [Average IO]  DESC
SELECT TOP 20
  [Total IO] = (qs.total_logical_reads + qs.total_logical_writes)
  , [Average IO] = (qs.total_logical_reads + qs.total_logical_writes) /
                                            qs.execution_count
  , qs.execution_count
  , SUBSTRING (qt.text,(qs.statement_start_offset/2) + 1,    
  ((CASE WHEN qs.statement_end_offset = -1
    THEN LEN(CONVERT(NVARCHAR(MAX), qt.text)) * 2
    ELSE qs.statement_end_offset
    END - qs.statement_start_offset)/2) + 1) AS [Individual Query]
  , qt.text AS [Parent Query]
  , DB_NAME(qt.dbid) AS DatabaseName
  , qp.query_plan
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) qp
ORDER BY [Average IO]  DESC
  • 或者
sql
SELECT TOP 10 TEXT AS 'SQL Statement'
 ,last_execution_time AS 'Last Execution Time'
 ,(total_logical_reads + total_physical_reads + total_logical_writes) / execution_count AS [Average IO]
 ,(total_worker_time / execution_count) / 1000000.0 AS [Average CPU Time (sec)]
 ,(total_elapsed_time / execution_count) / 1000000.0 AS [Average Elapsed Time (sec)]
 ,execution_count AS "Execution Count"
 ,qp.query_plan AS "Query Plan"
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.plan_handle) st
CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) qp
ORDER BY total_elapsed_time / execution_count DESC
SELECT TOP 10 TEXT AS 'SQL Statement'
 ,last_execution_time AS 'Last Execution Time'
 ,(total_logical_reads + total_physical_reads + total_logical_writes) / execution_count AS [Average IO]
 ,(total_worker_time / execution_count) / 1000000.0 AS [Average CPU Time (sec)]
 ,(total_elapsed_time / execution_count) / 1000000.0 AS [Average Elapsed Time (sec)]
 ,execution_count AS "Execution Count"
 ,qp.query_plan AS "Query Plan"
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.plan_handle) st
CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) qp
ORDER BY total_elapsed_time / execution_count DESC
  • 常用
sql
use master
-- SELECT * FROM dbo.sysprocesses WHERE spid IN (SELECT blocked FROM dbo.sysprocesses where blocked <> 0);
SELECT
    es.session_id,
    database_name=DB_NAME(er.database_id),
    er.cpu_time,
    er.reads,
    er.writes,
    er.logical_reads,
    login_name,
    er.status,
    blocking_session_id,
    wait_type,
    wait_resource,
    wait_time,
    individual_query=SUBSTRING(qt.text,(er.statement_start_offset/2)+1,((CASE  WHEN  er.statement_end_offset=-1 THEN  LEN(CONVERT(NVARCHAR(MAX),qt.text))* 2 ELSE   er.statement_end_offset  END-er.statement_start_offset)/2)+1),
    parent_query=qt.text,
    program_name,
    host_name,
    nt_domain,
    start_time,
    DATEDIFF(MS,er.start_time,GETDATE())as duration,
    (SELECT  query_plan  FROM  sys.dm_exec_query_plan (er.plan_handle))AS  query_plan
FROM
    sys.dm_exec_requests er
    INNER  JOIN  sys.dm_exec_sessions  es  ON er.session_id=es.session_id
    CROSS  APPLY  sys.dm_exec_sql_text (er.sql_handle)AS  qt
WHERE
    es.session_id> 50         
    AND  es.session_Id  NOT  IN(@@SPID)
ORDER BY
    1, 2
use master
-- SELECT * FROM dbo.sysprocesses WHERE spid IN (SELECT blocked FROM dbo.sysprocesses where blocked <> 0);
SELECT
    es.session_id,
    database_name=DB_NAME(er.database_id),
    er.cpu_time,
    er.reads,
    er.writes,
    er.logical_reads,
    login_name,
    er.status,
    blocking_session_id,
    wait_type,
    wait_resource,
    wait_time,
    individual_query=SUBSTRING(qt.text,(er.statement_start_offset/2)+1,((CASE  WHEN  er.statement_end_offset=-1 THEN  LEN(CONVERT(NVARCHAR(MAX),qt.text))* 2 ELSE   er.statement_end_offset  END-er.statement_start_offset)/2)+1),
    parent_query=qt.text,
    program_name,
    host_name,
    nt_domain,
    start_time,
    DATEDIFF(MS,er.start_time,GETDATE())as duration,
    (SELECT  query_plan  FROM  sys.dm_exec_query_plan (er.plan_handle))AS  query_plan
FROM
    sys.dm_exec_requests er
    INNER  JOIN  sys.dm_exec_sessions  es  ON er.session_id=es.session_id
    CROSS  APPLY  sys.dm_exec_sql_text (er.sql_handle)AS  qt
WHERE
    es.session_id> 50         
    AND  es.session_Id  NOT  IN(@@SPID)
ORDER BY
    1, 2

logical_reads:逻辑读，衡量语句的执行开销。如果大于10w，说明此语句开销很大。可以检查下索引是否合理

status：进程的状态。running 表示正在运行，sleeping 表示处于睡眠中，未运行任何语句，suspend 表示等待，runnable 等待cpu 调度

blocking_session_id: 如果不为0,例如 60 。表示52号进程正在被60阻塞。50 进程必须等待60执行完成，才能执行下面的语句

host_name ：发出请求的服务器名

program_name:发出请求的应用程序名

duration: 请求的执行时间

1.9最耗CPU时间的会话 ​

https://learn.microsoft.com/zh-cn/troubleshoot/sql/database-engine/performance/troubleshoot-high-cpu-usage-issues

一般会用到三个视图sys.sysprocesses ,dm_exec_sessions ,dm_exec_requests

sql
SELECT TOP 10
[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 AND DB_NAME(der.[database_id])='DB_name'  -- DB_name 根据自己写
ORDER BY [cpu_time] DESC

---------------------
SELECT TOP 10
   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
SELECT TOP 10
[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 AND DB_NAME(der.[database_id])='DB_name'  -- DB_name 根据自己写
ORDER BY [cpu_time] DESC

---------------------
SELECT TOP 10
   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
  • 查看所有
sql
USE master
--如果要指定数据库就把注释去掉
SELECT * FROM sys.[sysprocesses] WHERE [spid]>50 --AND DB_NAME([dbid])='DB_name'
SELECT COUNT(*) FROM [sys].[dm_exec_sessions] WHERE [session_id]>50
USE master
--如果要指定数据库就把注释去掉
SELECT * FROM sys.[sysprocesses] WHERE [spid]>50 --AND DB_NAME([dbid])='DB_name'
SELECT COUNT(*) FROM [sys].[dm_exec_sessions] WHERE [session_id]>50

查看具体的cpu 耗时sql语句 ​

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

查看CPU数和user scheduler数和最大工作线程数 ​

查看CPU数和user scheduler数和最大工作线程数，检查worker是否用完也可以排查CPU占用情况

sql
 --查看CPU数和user scheduler数目
SELECT cpu_count,scheduler_count FROM sys.dm_os_sys_info

--查看最大工作线程数
SELECT max_workers_count FROM sys.dm_os_sys_info
 --查看CPU数和user scheduler数目
SELECT cpu_count,scheduler_count FROM sys.dm_os_sys_info

--查看最大工作线程数
SELECT max_workers_count FROM sys.dm_os_sys_info

对照下面这个表 各种CPU和SQLSERVER版本组合自动配置的最大工作线程数 CPU数 32位计算机 64位计算机 <=4 256 512 8 288 576 16 352 704 32 480 960

sql
SELECT
 scheduler_address,
scheduler_id,
cpu_id,
status,
current_tasks_count,
current_workers_count,active_workers_count
FROM sys.dm_os_schedulers
SELECT
 scheduler_address,
scheduler_id,
cpu_id,
status,
current_tasks_count,
current_workers_count,active_workers_count
FROM sys.dm_os_schedulers

查询CPU占用高的语句 ​

sql
SELECT TOP 10
    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
SELECT TOP 10
    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

2.0 查询sqlserver 正在执行的sql语句 ​

sql
SELECT     [Spid] = session_Id, ecid, [Database] = DB_NAME(sp.dbid),

 [User] = nt_username, [Status] = er.status, 
 [Wait] = wait_type, 
 [Individual Query] = SUBSTRING(qt.text, er.statement_start_offset / 2, (CASE WHEN er.statement_end_offset = - 1 THEN LEN(CONVERT(NVARCHAR(MAX), qt.text)) 
                      * 2 ELSE er.statement_end_offset END - er.statement_start_offset) / 2),
                       [Parent Query] = qt.text, 
                       Program = program_name, Hostname, 
                       nt_domain, start_time
FROM    
     sys.dm_exec_requests er INNER JOIN  sys.sysprocesses sp ON er.session_id = sp.spid 
     CROSS APPLY sys.dm_exec_sql_text(er.sql_handle) AS qt
WHERE     session_Id > 50 /* Ignore system spids.*/ AND session_Id NOT IN (@@SPID)
SELECT     [Spid] = session_Id, ecid, [Database] = DB_NAME(sp.dbid),

 [User] = nt_username, [Status] = er.status, 
 [Wait] = wait_type, 
 [Individual Query] = SUBSTRING(qt.text, er.statement_start_offset / 2, (CASE WHEN er.statement_end_offset = - 1 THEN LEN(CONVERT(NVARCHAR(MAX), qt.text)) 
                      * 2 ELSE er.statement_end_offset END - er.statement_start_offset) / 2),
                       [Parent Query] = qt.text, 
                       Program = program_name, Hostname, 
                       nt_domain, start_time
FROM    
     sys.dm_exec_requests er INNER JOIN  sys.sysprocesses sp ON er.session_id = sp.spid 
     CROSS APPLY sys.dm_exec_sql_text(er.sql_handle) AS qt
WHERE     session_Id > 50 /* Ignore system spids.*/ AND session_Id NOT IN (@@SPID)
  • 或者查看数据库正在执行的sql语句
sql
SELECT  [Spid] = session_id ,
            ecid ,
            [Database] = DB_NAME(sp.dbid) ,
            [User] = nt_username ,
            [Status] = er.status ,
            [Wait] = wait_type ,
            [Individual Query] = SUBSTRING(qt.text,
                                           er.statement_start_offset / 2,
                                           ( CASE WHEN er.statement_end_offset = -1
                                                  THEN LEN(CONVERT(NVARCHAR(MAX), qt.text))
                                                       * 2
                                                  ELSE er.statement_end_offset
                                             END - er.statement_start_offset )
                                           / 2) ,
            [Parent Query] = qt.text ,
            Program = program_name ,
            hostname ,
            nt_domain ,
            start_time
    FROM    sys.dm_exec_requests er
            INNER JOIN sys.sysprocesses sp ON er.session_id = sp.spid
            CROSS APPLY sys.dm_exec_sql_text(er.sql_handle) AS qt
    WHERE   session_id > 50 -- Ignore system spids.
            AND session_id NOT IN ( @@SPID ) -- Ignore this current statement.
ORDER BY    1 ,
            2
SELECT  [Spid] = session_id ,
            ecid ,
            [Database] = DB_NAME(sp.dbid) ,
            [User] = nt_username ,
            [Status] = er.status ,
            [Wait] = wait_type ,
            [Individual Query] = SUBSTRING(qt.text,
                                           er.statement_start_offset / 2,
                                           ( CASE WHEN er.statement_end_offset = -1
                                                  THEN LEN(CONVERT(NVARCHAR(MAX), qt.text))
                                                       * 2
                                                  ELSE er.statement_end_offset
                                             END - er.statement_start_offset )
                                           / 2) ,
            [Parent Query] = qt.text ,
            Program = program_name ,
            hostname ,
            nt_domain ,
            start_time
    FROM    sys.dm_exec_requests er
            INNER JOIN sys.sysprocesses sp ON er.session_id = sp.spid
            CROSS APPLY sys.dm_exec_sql_text(er.sql_handle) AS qt
    WHERE   session_id > 50 -- Ignore system spids.
            AND session_id NOT IN ( @@SPID ) -- Ignore this current statement.
ORDER BY    1 ,
            2

查看数据库连接情况 ​

sql
select * from sysprocesses where dbid in (select dbid from sysdatabases where name='dbname')
--或者
SELECT * FROM
[Master].[dbo].[SYSPROCESSES] WHERE [DBID] IN ( SELECT 
   [DBID]
FROM 
   [Master].[dbo].[SYSDATABASES]
WHERE 
   NAME='dbname'
)

--或者
select @@spid as 'Connection ID',DB_NAME(dbid) as 'Database',loginame as 'Login Name',hostname as 'Host Name',
nt_username as 'Windows User Name',program_name as 'Program Name' from sys.sysprocesses WHERE dbid>0
select * from sysprocesses where dbid in (select dbid from sysdatabases where name='dbname')
--或者
SELECT * FROM
[Master].[dbo].[SYSPROCESSES] WHERE [DBID] IN ( SELECT 
   [DBID]
FROM 
   [Master].[dbo].[SYSDATABASES]
WHERE 
   NAME='dbname'
)

--或者
select @@spid as 'Connection ID',DB_NAME(dbid) as 'Database',loginame as 'Login Name',hostname as 'Host Name',
nt_username as 'Windows User Name',program_name as 'Program Name' from sys.sysprocesses WHERE dbid>0

查看连接对象 ​

sql
select client_net_address '客户端IP',local_net_address '服务器的IP',* from sys.dm_exec_connections

SELECT * FROM sys.[sysprocesses] WHERE [spid]>50 --AND DB_NAME([dbid])='gposdb'
select client_net_address '客户端IP',local_net_address '服务器的IP',* from sys.dm_exec_connections

SELECT * FROM sys.[sysprocesses] WHERE [spid]>50 --AND DB_NAME([dbid])='gposdb'

查看连接情况 ​

sql
--查看连接到数据库"DB"的连接
SELECT * from master.dbo.sysprocesses WHERE dbid = DB_ID('DB')
--查询某个数据库用户的连接情况
sp_who 'sa'
--查看连接到数据库"DB"的连接
SELECT * from master.dbo.sysprocesses WHERE dbid = DB_ID('DB')
--查询某个数据库用户的连接情况
sp_who 'sa'

2.1 QPS ​

https://learn.microsoft.com/zh-cn/sql/relational-databases/system-dynamic-management-views/sys-dm-os-performance-counters-transact-sql?view=sql-server-2017

dm_os_performance_counters,该视图用于查看数据库的性能指标

sql
with t as (
SELECT  * FROM sys.dm_os_performance_counters
WHERE object_name LIKE '%SQL Statistics%' and counter_name ='Batch Requests/sec' )
select counter_name,cntr_value from t
with t as (
SELECT  * FROM sys.dm_os_performance_counters
WHERE object_name LIKE '%SQL Statistics%' and counter_name ='Batch Requests/sec' )
select counter_name,cntr_value from t

2.2索引 ​

2.2.1查看索引是否丢失 ​

sql
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  TOP 10
         [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;
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  TOP 10
         [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;

3.io ​

sql
SELECT
   DB_NAME(dbid) 'Database Name',
   physical_name 'File Location',
   NumberReads 'Number of Reads',
   BytesRead 'Bytes Read',
   NumberWrites 'Number of Writes',
   BytesWritten 'Bytes Written',   
   IoStallReadMS 'IO Stall Read',
   IoStallWriteMS 'IO Stall Write',
   IoStallMS as 'Total IO Stall (ms)'
FROM
   fn_virtualfilestats(NULL,NULL) fs INNER JOIN
    sys.master_files mf ON fs.dbid = mf.database_id 
    AND fs.fileid = mf.file_id
ORDER BY
   DB_NAME(dbid)
SELECT
   DB_NAME(dbid) 'Database Name',
   physical_name 'File Location',
   NumberReads 'Number of Reads',
   BytesRead 'Bytes Read',
   NumberWrites 'Number of Writes',
   BytesWritten 'Bytes Written',   
   IoStallReadMS 'IO Stall Read',
   IoStallWriteMS 'IO Stall Write',
   IoStallMS as 'Total IO Stall (ms)'
FROM
   fn_virtualfilestats(NULL,NULL) fs INNER JOIN
    sys.master_files mf ON fs.dbid = mf.database_id 
    AND fs.fileid = mf.file_id
ORDER BY
   DB_NAME(dbid)

https://www.modb.pro/db/410923

4.性能检测工具 ​

sql server profiler

https://www.cnblogs.com/knowledgesea/category/373445.html

4.1 SQL Server Profiler是什么 ​

SQL Server Profiler是一个界面，用于创建和管理跟踪并分析和重播跟踪结果。 这些事件保存在一个跟踪文件中，稍后试图诊断问题时，可以对该文件进行分析或用它来重播一系列特定的步骤。同时也可以利用它来对跟着文件进行分析，分析完成后会给出优化建议

  • 查找持续时间最长的查询

在创建跟踪时，勾上TSQL-SQL:BatchCompleted.跟Stored Procedures-RPC:completed。这样就能找出来这个最长时间查询然后对其进行分析优化

select TextData,Duration,CPU from <跟踪的表>
where EventClass=12 -- 等于12表示BatchCompleted事件
and CPU<(0.4*Duration)  --如果cpu的占用时间，小于执行sql语句时间的40%，说明该语句等待时间过长
select TextData,Duration,CPU from <跟踪的表>
where EventClass=12 -- 等于12表示BatchCompleted事件
and CPU<(0.4*Duration)  --如果cpu的占用时间，小于执行sql语句时间的40%，说明该语句等待时间过长
  • 最占用系统资源的查询

就是占用cpu时间，跟读写IO的次数。建议事件包含Connect、Disconnect、ExistingConnection、SQL:BatchCompleted、RPC:completed，列包含writes，reads，cpu

  • 检测死锁

在访问量，并发量都很大的数据库中，如果设计稍不合理，就有可能造成死锁，给系统性能带来影响。事件包含：RPC:Starting、SQL:BatchStarting、Lock：DeadLock（死锁事件）、Lock：DeadLockChaining（死锁的事件序列）

4.2 SQL Server Profiler的使用 ​

  • 第一步

启动SSMS——>【工具】——>【SQL Server Profiler】，即可启动SQL Server Profiler，如图1：

  • 第二步

启动后会再次要求连接被跟踪的数据库，如图2：

  • 第三步

设置跟踪属性，根据界面提示填入相关信息，如图3：

  • 第四步

设置【事件选择】内容，根据图4中的提示，勾选相关内容即可：

  • 第五步

在【事件选择】页面继续勾选显示DatabaseName列，方便显示被跟踪数据库，按图5步骤中操作：

  • 第六步

按图6中步骤，先点击【列筛选器...】在弹出的页面中找到【DatabaseName】选项，然后输入指定数据库名称，这里我们输入AdventureWorks。这是小编本地数据库名称

  • 第七步

按图7中步骤，选中【TextData】的选项，输入select%，其意思是跟踪以select开头的查询语句，%为通配符。点击【确定】后会弹出一个提示框，点击【确定】即可

*　第八步

返回SSMS，选择AdventureWorks数据库，新建一个查询，点击【执行】。如图8：

  • 第九步

返回SQL Server Profiler查看跟踪界面，如图9在跟踪页面上可以看到刚才执行的查询语句

事件分类，申请了语句，应用程序名称，操作系统用户，数据库用户，cpu占用率，读数据库次数，写数据库次说，执行脚本用时，应用程序进程号，开始时间，结束时间

  • 第十步

将当前的跟踪文件另存为跟踪文件Test.trc，如图10：

  • 第十一步

点击SQL Server Profiler菜单栏中的【工具】——>【数据库引擎优化顾问】开始对刚才的Test.trc文件进行分析，如图11：

  • 第十二步

在弹出的页面中，我们开始设置优化顾问。

  1. 在【工作负荷】中找到刚保存的Test.trc文件
  2. 在选择要优化的数据库和表中，我们单独找到需要被分析的表Address

如图12：

  • 第十三步

设置完成后，点击【开始分析】即可，如图13：

  • 第十四步

等分析完成后，在索引建议一栏中的最后一列【定义】中会给出优化建议，这里点开，然后点【复制到剪贴板】即可获取优化建议脚本，返回SSMS粘贴后执行即可完成优化。如图14：

  • 第十五步

这一步是和第十四步功能类似，只是更加智能，由系统自动执行，无需复制粘贴执行脚本。点击数据引擎优化顾问的菜单栏的【操作】——>【应用建议...】，在弹出的对话框如图15，点击确定即可自动执行引擎顾问提供的优化建议。

https://www.cnblogs.com/bhtfg538/archive/2011/01/21/1939706.html

5.SQL Server 的状态值 ​

SQL Server 的管理、监控、效能调校时，我们可能会执行以下的 SQL 指令，去观察 SQL Server 里的状态：

sql
SELECT * FROM sys.sysprocesses;

EXEC sp_who2;

SELECT sqltext.TEXT, req.session_id, req.status, req.command, req.cpu_time, req.blocking_session_id, req.total_elapsed_time
FROM sys.dm_exec_requests req (NOLOCK)
CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS sqltext
SELECT * FROM sys.sysprocesses;

EXEC sp_who2;

SELECT sqltext.TEXT, req.session_id, req.status, req.command, req.cpu_time, req.blocking_session_id, req.total_elapsed_time
FROM sys.dm_exec_requests req (NOLOCK)
CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS sqltext

status 栏位的值，有这几种 : Pending, Runnable, Running, Suspended, Sleeping, Dormant, Background, Spinlock

5.1Pending ​

“pending” (等待)，代表这个 process，既沒有 Thread 可用，也沒有 CPU 可用，正在同时等待这两项系统资源

5.2runnable ​

“runnable”，代表这个 process，有 Thread 可用，但沒有 CPU 可用，所以它正在等待 CPU 这项系统资源

5.3running ​

“running”，代表这个 process，有 Thread 可用，有 CPU 可用

5.4suspended ​

“suspended” (暂停)，代表这个 process，正在「等待」別的 process 执行，等待的系统资源可能是 Disk I/O 或数据库的 Lock

5.5sleeping ​

“sleeping”，代表这个 process，目前没在做任何事，正在等待进一步的指令

5.6dormant ​

“dormant” (暂时搁置)，代表 SQL Server 正在对这个 process 做 reset

5.6background ​

“background”，代表这个 process 正在 SQL Server 背景执行。 即使你看到有很多 “background” process 正在执行，也不必担心

5.7 Spinlock ​

spin lock essentially means that query is in kind of running mode where it is busy waiting in cpu for its own turn.

6.排查步骤 ​

1.排查连接对象 ​

sql
--如果想要指定查询某个数据库，将后面的注释去掉即可
select client_net_address '客户端IP',local_net_address '服务器的IP',* from sys.dm_exec_connections

SELECT * FROM sys.[sysprocesses] WHERE [spid]>50 --AND DB_NAME([dbid])='gposdb'
--如果想要指定查询某个数据库，将后面的注释去掉即可
select client_net_address '客户端IP',local_net_address '服务器的IP',* from sys.dm_exec_connections

SELECT * FROM sys.[sysprocesses] WHERE [spid]>50 --AND DB_NAME([dbid])='gposdb'

各项指标是否正常，是否有阻塞，正常情况下搜索结果应该为空

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

2.查看sql语句占用较大 ​

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

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

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

3.查询CPU占用最高的SQL语句 ​

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

CPU 调度程序在磁盘上等待 ​

sql
SELECT  COUNT(*) Schedulers,
        AVG(work_queue_count) AS [Avg Work Queue Count],
        AVG(pending_disk_io_count) AS [Avg Pending DiskIO Count],
        SUM(work_queue_count) AS [SUM Work Queue Count],
        SUM(pending_disk_io_count) AS [SUM Pending DiskIO Count]
FROM sys.dm_os_schedulers WITH (NOLOCK)
WHERE scheduler_id < 255;

--工作队列和挂起的 diskIO 数量非常多，那么很明显，CPU 调度程序正在等待更多 CPU 或更多磁盘 IO。这时我们需要开始单独调查每个调度程序的线程
执行下面三列
SELECT  COUNT(*) Schedulers,
        AVG(work_queue_count) AS [Avg Work Queue Count],
        AVG(pending_disk_io_count) AS [Avg Pending DiskIO Count],
        SUM(work_queue_count) AS [SUM Work Queue Count],
        SUM(pending_disk_io_count) AS [SUM Pending DiskIO Count]
FROM sys.dm_os_schedulers WITH (NOLOCK)
WHERE scheduler_id < 255;

--工作队列和挂起的 diskIO 数量非常多，那么很明显，CPU 调度程序正在等待更多 CPU 或更多磁盘 IO。这时我们需要开始单独调查每个调度程序的线程
执行下面三列

CPU过去 60 分钟的详细信息 ​

sql
DECLARE @ms_ticks_now BIGINT
SELECT @ms_ticks_now = ms_ticks
FROM sys.dm_os_sys_info;
SELECT TOP 60 record_id
    ,dateadd(ms, - 1 * (@ms_ticks_now - [timestamp]), GetDate()) AS EventTime
    ,[SQLProcess (%)]
    ,SystemIdle
    ,100 - SystemIdle - [SQLProcess (%)] AS [OtherProcess (%)]
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 [SQLProcess (%)]
        ,TIMESTAMP
    FROM (
        SELECT TIMESTAMP
            ,convert(XML, record) AS record
        FROM sys.dm_os_ring_buffers
        WHERE ring_buffer_type = N'RING_BUFFER_SCHEDULER_MONITOR'
            AND record LIKE '%<SystemHealth>%'
        ) AS x
    ) AS y
ORDER BY record_id DESC
DECLARE @ms_ticks_now BIGINT
SELECT @ms_ticks_now = ms_ticks
FROM sys.dm_os_sys_info;
SELECT TOP 60 record_id
    ,dateadd(ms, - 1 * (@ms_ticks_now - [timestamp]), GetDate()) AS EventTime
    ,[SQLProcess (%)]
    ,SystemIdle
    ,100 - SystemIdle - [SQLProcess (%)] AS [OtherProcess (%)]
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 [SQLProcess (%)]
        ,TIMESTAMP
    FROM (
        SELECT TIMESTAMP
            ,convert(XML, record) AS record
        FROM sys.dm_os_ring_buffers
        WHERE ring_buffer_type = N'RING_BUFFER_SCHEDULER_MONITOR'
            AND record LIKE '%<SystemHealth>%'
        ) AS x
    ) AS y
ORDER BY record_id DESC

哪个查询占用了最大 CPU,查看当前正在运行的查询 ​

sql
SELECT
    r.session_id
    ,st.TEXT AS batch_text
    ,SUBSTRING(st.TEXT, statement_start_offset / 2 + 1, (
            (
                CASE
                    WHEN r.statement_end_offset = - 1
                        THEN (LEN(CONVERT(NVARCHAR(max), st.TEXT)) * 2)
                    ELSE r.statement_end_offset
                    END
                ) - r.statement_start_offset
            ) / 2 + 1) AS statement_text
    ,qp.query_plan AS 'XML Plan'
    ,r.cpu_time, r.total_elapsed_time
    ,r.logical_reads, r.writes, r.dop
FROM sys.dm_exec_requests r
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) AS st
CROSS APPLY sys.dm_exec_query_plan(r.plan_handle) AS qp
ORDER BY cpu_time DESC
SELECT
    r.session_id
    ,st.TEXT AS batch_text
    ,SUBSTRING(st.TEXT, statement_start_offset / 2 + 1, (
            (
                CASE
                    WHEN r.statement_end_offset = - 1
                        THEN (LEN(CONVERT(NVARCHAR(max), st.TEXT)) * 2)
                    ELSE r.statement_end_offset
                    END
                ) - r.statement_start_offset
            ) / 2 + 1) AS statement_text
    ,qp.query_plan AS 'XML Plan'
    ,r.cpu_time, r.total_elapsed_time
    ,r.logical_reads, r.writes, r.dop
FROM sys.dm_exec_requests r
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) AS st
CROSS APPLY sys.dm_exec_query_plan(r.plan_handle) AS qp
ORDER BY cpu_time DESC

使用 CPU 的历史查询 ​

sql
SELECT TOP(10) qs.execution_count AS [Execution Count],
    (qs.total_logical_reads)/1000.0 AS [Total Logical Reads in ms],
    (qs.total_logical_reads/qs.execution_count)/1000.0 AS [Avg Logical Reads in ms],
    (qs.total_worker_time)/1000.0 AS [Total Worker Time in ms],
    (qs.total_worker_time/qs.execution_count)/1000.0 AS [Avg Worker Time in ms],
    (qs.total_elapsed_time)/1000.0 AS [Total Elapsed Time in ms],
    (qs.total_elapsed_time/qs.execution_count)/1000.0 AS [Avg Elapsed Time in ms],
    qs.creation_time AS [Creation Time]
    ,t.text AS [Complete Query Text], qp.query_plan AS [Query Plan]
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
WHERE t.dbid = DB_ID()
ORDER BY (qs.total_logical_reads/qs.execution_count) DESC;
SELECT TOP(10) qs.execution_count AS [Execution Count],
    (qs.total_logical_reads)/1000.0 AS [Total Logical Reads in ms],
    (qs.total_logical_reads/qs.execution_count)/1000.0 AS [Avg Logical Reads in ms],
    (qs.total_worker_time)/1000.0 AS [Total Worker Time in ms],
    (qs.total_worker_time/qs.execution_count)/1000.0 AS [Avg Worker Time in ms],
    (qs.total_elapsed_time)/1000.0 AS [Total Elapsed Time in ms],
    (qs.total_elapsed_time/qs.execution_count)/1000.0 AS [Avg Elapsed Time in ms],
    qs.creation_time AS [Creation Time]
    ,t.text AS [Complete Query Text], qp.query_plan AS [Query Plan]
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
WHERE t.dbid = DB_ID()
ORDER BY (qs.total_logical_reads/qs.execution_count) DESC;

总耗CPU最多的前个SQL ​

sql
----- --总耗CPU最多的前个SQL:
SELECT TOP 20
    total_worker_time/1000 AS [总消耗CPU 时间(ms)],execution_count [运行次数],
    qs.total_worker_time/qs.execution_count/1000 AS [平均消耗CPU 时间(ms)],
    last_execution_time AS [最后一次执行时间],max_worker_time /1000 AS [最大执行时间(ms)],
    SUBSTRING(qt.text,qs.statement_start_offset/2+1, 
        (CASE WHEN qs.statement_end_offset = -1 
        THEN DATALENGTH(qt.text) 
        ELSE qs.statement_end_offset END -qs.statement_start_offset)/2 + 1) 
    AS [使用CPU的语法], qt.text [完整语法],
    qt.dbid, dbname=db_name(qt.dbid),
    qt.objectid,object_name(qt.objectid,qt.dbid) ObjectName
FROM sys.dm_exec_query_stats qs WITH(nolock)
CROSS apply sys.dm_exec_sql_text(qs.sql_handle) AS qt
WHERE execution_count>1
ORDER BY  total_worker_time DESC
----- --总耗CPU最多的前个SQL:
SELECT TOP 20
    total_worker_time/1000 AS [总消耗CPU 时间(ms)],execution_count [运行次数],
    qs.total_worker_time/qs.execution_count/1000 AS [平均消耗CPU 时间(ms)],
    last_execution_time AS [最后一次执行时间],max_worker_time /1000 AS [最大执行时间(ms)],
    SUBSTRING(qt.text,qs.statement_start_offset/2+1, 
        (CASE WHEN qs.statement_end_offset = -1 
        THEN DATALENGTH(qt.text) 
        ELSE qs.statement_end_offset END -qs.statement_start_offset)/2 + 1) 
    AS [使用CPU的语法], qt.text [完整语法],
    qt.dbid, dbname=db_name(qt.dbid),
    qt.objectid,object_name(qt.objectid,qt.dbid) ObjectName
FROM sys.dm_exec_query_stats qs WITH(nolock)
CROSS apply sys.dm_exec_sql_text(qs.sql_handle) AS qt
WHERE execution_count>1
ORDER BY  total_worker_time DESC

平均耗CPU最多的前个SQL ​

sql
---平均耗CPU最多的前个SQL:
SELECT TOP 20
    total_worker_time/1000 AS [总消耗CPU 时间(ms)],execution_count [运行次数],
    qs.total_worker_time/qs.execution_count/1000 AS [平均消耗CPU 时间(ms)],
    last_execution_time AS [最后一次执行时间],min_worker_time /1000 AS [最小执行时间(ms)],
    max_worker_time /1000 AS [最大执行时间(ms)],
    SUBSTRING(qt.text,qs.statement_start_offset/2+1, 
        (CASE WHEN qs.statement_end_offset = -1 
        THEN DATALENGTH(qt.text) 
        ELSE qs.statement_end_offset END -qs.statement_start_offset)/2 + 1) 
    AS [使用CPU的语法], qt.text [完整语法],
    qt.dbid, dbname=db_name(qt.dbid),
    qt.objectid,object_name(qt.objectid,qt.dbid) ObjectName
FROM sys.dm_exec_query_stats qs WITH(nolock)
CROSS apply sys.dm_exec_sql_text(qs.sql_handle) AS qt
WHERE  execution_count>1
ORDER BY (qs.total_worker_time/qs.execution_count/1000) DESC
---平均耗CPU最多的前个SQL:
SELECT TOP 20
    total_worker_time/1000 AS [总消耗CPU 时间(ms)],execution_count [运行次数],
    qs.total_worker_time/qs.execution_count/1000 AS [平均消耗CPU 时间(ms)],
    last_execution_time AS [最后一次执行时间],min_worker_time /1000 AS [最小执行时间(ms)],
    max_worker_time /1000 AS [最大执行时间(ms)],
    SUBSTRING(qt.text,qs.statement_start_offset/2+1, 
        (CASE WHEN qs.statement_end_offset = -1 
        THEN DATALENGTH(qt.text) 
        ELSE qs.statement_end_offset END -qs.statement_start_offset)/2 + 1) 
    AS [使用CPU的语法], qt.text [完整语法],
    qt.dbid, dbname=db_name(qt.dbid),
    qt.objectid,object_name(qt.objectid,qt.dbid) ObjectName
FROM sys.dm_exec_query_stats qs WITH(nolock)
CROSS apply sys.dm_exec_sql_text(qs.sql_handle) AS qt
WHERE  execution_count>1
ORDER BY (qs.total_worker_time/qs.execution_count/1000) DESC

如何清理看到的这些语句所运行缓存的清理

首先是清除存储过程相关的缓存语句

sql
DBCC FREEPROCCACHE -----清理缓存
DBCC FREEPROCCACHE -----清理缓存

然后对该数据库的-会话缓存

sql
DBCC FREESESSIONCACHE ---会话清理
DBCC FREESESSIONCACHE ---会话清理

最后两个执行的语句是对数据系统清理的语句

DBCC FREESYSTEMCACHE('All') --------系统缓存 DBCC DROPCLEANBUFFERS ---------所有缓存

SQL阻塞进程 ​

sql
SELECT  t1.request_session_id AS 'wait_sid' ,
        t1.resource_type AS '锁类型' ,
        DB_NAME(resource_database_id) AS '库明称' ,
        t1.request_mode AS 'wait锁类型' ,
        t2.wait_duration_ms AS 'wait_time_ms' ,
        ( SELECT    text
          FROM      sys.dm_exec_requests AS r
                    CROSS APPLY sys.dm_exec_sql_text(r.sql_handle)
          WHERE     r.session_id = t1.request_session_id
        ) AS 'wait_run_batch' ,
        ( SELECT    SUBSTRING(qt.text, r.statement_start_offset / 2 + 1,
                              ( CASE WHEN r.statement_end_offset = -1
                                     THEN DATALENGTH(qt.text)
                                     ELSE r.statement_end_offset
                                END - r.statement_start_offset ) / 2 + 1)
          FROM      sys.dm_exec_requests AS r
                    CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) AS qt
          WHERE     r.session_id = t1.request_session_id
        ) AS 'wait 运行的SQL语句' ,
        t2.blocking_session_id AS '锁定sid' ,
        ( SELECT    text
          FROM      sys.sysprocesses AS p
                    CROSS APPLY sys.dm_exec_sql_text(p.sql_handle)
          WHERE     p.spid = t2.blocking_session_id
        ) AS '锁定SQL'
FROM    sys.dm_tran_locks AS t1
        INNER JOIN sys.dm_os_waiting_tasks AS t2 ON t1.lock_owner_address = t2.resource_address
SELECT  t1.request_session_id AS 'wait_sid' ,
        t1.resource_type AS '锁类型' ,
        DB_NAME(resource_database_id) AS '库明称' ,
        t1.request_mode AS 'wait锁类型' ,
        t2.wait_duration_ms AS 'wait_time_ms' ,
        ( SELECT    text
          FROM      sys.dm_exec_requests AS r
                    CROSS APPLY sys.dm_exec_sql_text(r.sql_handle)
          WHERE     r.session_id = t1.request_session_id
        ) AS 'wait_run_batch' ,
        ( SELECT    SUBSTRING(qt.text, r.statement_start_offset / 2 + 1,
                              ( CASE WHEN r.statement_end_offset = -1
                                     THEN DATALENGTH(qt.text)
                                     ELSE r.statement_end_offset
                                END - r.statement_start_offset ) / 2 + 1)
          FROM      sys.dm_exec_requests AS r
                    CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) AS qt
          WHERE     r.session_id = t1.request_session_id
        ) AS 'wait 运行的SQL语句' ,
        t2.blocking_session_id AS '锁定sid' ,
        ( SELECT    text
          FROM      sys.sysprocesses AS p
                    CROSS APPLY sys.dm_exec_sql_text(p.sql_handle)
          WHERE     p.spid = t2.blocking_session_id
        ) AS '锁定SQL'
FROM    sys.dm_tran_locks AS t1
        INNER JOIN sys.dm_os_waiting_tasks AS t2 ON t1.lock_owner_address = t2.resource_address

4.索引缺失 ​

6.SPID ​

6.1根据spid名字 ​

sql
--- 查询db_name中所有spid
select spid from master..sysprocesses where dbid = DB_ID('db_Name')

---spid可以查看sql语句执行
DBCC  INPUTBUFFER(spid)
--- 查询db_name中所有spid
select spid from master..sysprocesses where dbid = DB_ID('db_Name')

---spid可以查看sql语句执行
DBCC  INPUTBUFFER(spid)

7.死锁跟踪 ​

7.1利用服务器端跟踪 ​

创建脚本 ​

sql
-- 针对那个库进行
use hantest
go

    --创建跟踪文件返回值
    declare @rc int
    --创建一个跟踪句柄
    declare @TraceID int
    --创建跟踪文件路径
    declare @TraceFilePath nvarchar(500)
    set @TraceFilePath=N'D:\DBA_TOOLS\db_deadLock_log'
    --跟踪文件的大小
    declare @maxfilesize bigint
    set @maxfilesize=200
    --设置停止的时间
    declare @EndTime datetime
    set @EndTime=null
    --设置系统默认的操作
    declare @options int
    set @options=2
    --设置默认滚动文件的数目
    declare @filecount int
    set @filecount=5

    exec @rc=sp_trace_Create
    @TraceID output,
    @options,
    @TraceFilePath,
    @maxfilesize,
    @EndTime,
    @filecount
    if(@rc=0)


    declare @on bit  
    set @on = 1  
    --下述语句中的148指的是locks:deadlock graph事件(参见sys.trace_events),12指的是spid列(参见sys.trace_columns)  
    exec sp_trace_setevent @TraceID, 148, 12, @on    
    exec sp_trace_setevent @TraceID, 148, 11, @on  
    exec sp_trace_setevent @TraceID, 148, 4, @on  
    exec sp_trace_setevent @TraceID, 148, 14, @on  
    exec sp_trace_setevent @TraceID, 148, 26, @on  
    exec sp_trace_setevent @TraceID, 148, 64, @on  
    exec sp_trace_setevent @TraceID, 148, 1, @on  
    -- 启动跟踪  
    exec sp_trace_setstatus @TraceID, 1  
    -- 记录下跟踪ID，后面使用  
    select TraceID = @TraceID  
    goto finish  
    error:   
    select ErrorCode=@rc  
    finish:   
go
-- 针对那个库进行
use hantest
go

    --创建跟踪文件返回值
    declare @rc int
    --创建一个跟踪句柄
    declare @TraceID int
    --创建跟踪文件路径
    declare @TraceFilePath nvarchar(500)
    set @TraceFilePath=N'D:\DBA_TOOLS\db_deadLock_log'
    --跟踪文件的大小
    declare @maxfilesize bigint
    set @maxfilesize=200
    --设置停止的时间
    declare @EndTime datetime
    set @EndTime=null
    --设置系统默认的操作
    declare @options int
    set @options=2
    --设置默认滚动文件的数目
    declare @filecount int
    set @filecount=5

    exec @rc=sp_trace_Create
    @TraceID output,
    @options,
    @TraceFilePath,
    @maxfilesize,
    @EndTime,
    @filecount
    if(@rc=0)


    declare @on bit  
    set @on = 1  
    --下述语句中的148指的是locks:deadlock graph事件(参见sys.trace_events),12指的是spid列(参见sys.trace_columns)  
    exec sp_trace_setevent @TraceID, 148, 12, @on    
    exec sp_trace_setevent @TraceID, 148, 11, @on  
    exec sp_trace_setevent @TraceID, 148, 4, @on  
    exec sp_trace_setevent @TraceID, 148, 14, @on  
    exec sp_trace_setevent @TraceID, 148, 26, @on  
    exec sp_trace_setevent @TraceID, 148, 64, @on  
    exec sp_trace_setevent @TraceID, 148, 1, @on  
    -- 启动跟踪  
    exec sp_trace_setstatus @TraceID, 1  
    -- 记录下跟踪ID，后面使用  
    select TraceID = @TraceID  
    goto finish  
    error:   
    select ErrorCode=@rc  
    finish:   
go

运行上述语句后，每当SQL Server中发生死锁事件,都会自动往文件D:\DBA_TOOLS\db_deadLock_log\deadlockdetect.trc中插入一条记录

开机启动 ​

sql
--默认重启实例后，跟踪会取消，所以写一个SP做实例启动执行
use master

go
create proc StartBlackBoxTrace
as
begin
                --默认开启追踪所有的SQL 执行语句，文件文件路径为默认
               --创建跟踪文件返回值
            declare @rc int
            --创建一个跟踪句柄
            declare @TraceID int
            --创建跟踪文件路径
            declare @TraceFilePath nvarchar(500)
            set @TraceFilePath=N'D:\DBA_TOOLS\db_deadLock_log'
            --跟踪文件的大小
            declare @maxfilesize bigint
            set @maxfilesize=200
            --设置停止的时间
            declare @EndTime datetime
            set @EndTime=null
            --设置系统默认的操作
            declare @options int
            set @options=2
            --设置默认滚动文件的数目
            declare @filecount int
            set @filecount=5

            exec @rc=sp_trace_Create
            @TraceID output,
            @options,
            @TraceFilePath,
            @maxfilesize,
            @EndTime,
            @filecount
            if(@rc=0)
            select  @TraceID


            declare @on bit  
            set @on = 1  
            --下述语句中的148指的是locks:deadlock graph事件(参见sys.trace_events),12指的是spid列(参见sys.trace_columns)  
            exec sp_trace_setevent @TraceID, 148, 12, @on    
            exec sp_trace_setevent @TraceID, 148, 11, @on  
            exec sp_trace_setevent @TraceID, 148, 4, @on  
            exec sp_trace_setevent @TraceID, 148, 14, @on  
            exec sp_trace_setevent @TraceID, 148, 26, @on  
            exec sp_trace_setevent @TraceID, 148, 64, @on  
            exec sp_trace_setevent @TraceID, 148, 1, @on  
            -- 启动跟踪  
            exec sp_trace_setstatus @TraceID, 1  
END
GO
--将该存储过程设置为SQL Server服务启动时自动启动
EXEC sp_procoption
'StartBlackBoxTrace','STARTUP','ON'
print 'ok'
GO
--默认重启实例后，跟踪会取消，所以写一个SP做实例启动执行
use master

go
create proc StartBlackBoxTrace
as
begin
                --默认开启追踪所有的SQL 执行语句，文件文件路径为默认
               --创建跟踪文件返回值
            declare @rc int
            --创建一个跟踪句柄
            declare @TraceID int
            --创建跟踪文件路径
            declare @TraceFilePath nvarchar(500)
            set @TraceFilePath=N'D:\DBA_TOOLS\db_deadLock_log'
            --跟踪文件的大小
            declare @maxfilesize bigint
            set @maxfilesize=200
            --设置停止的时间
            declare @EndTime datetime
            set @EndTime=null
            --设置系统默认的操作
            declare @options int
            set @options=2
            --设置默认滚动文件的数目
            declare @filecount int
            set @filecount=5

            exec @rc=sp_trace_Create
            @TraceID output,
            @options,
            @TraceFilePath,
            @maxfilesize,
            @EndTime,
            @filecount
            if(@rc=0)
            select  @TraceID


            declare @on bit  
            set @on = 1  
            --下述语句中的148指的是locks:deadlock graph事件(参见sys.trace_events),12指的是spid列(参见sys.trace_columns)  
            exec sp_trace_setevent @TraceID, 148, 12, @on    
            exec sp_trace_setevent @TraceID, 148, 11, @on  
            exec sp_trace_setevent @TraceID, 148, 4, @on  
            exec sp_trace_setevent @TraceID, 148, 14, @on  
            exec sp_trace_setevent @TraceID, 148, 26, @on  
            exec sp_trace_setevent @TraceID, 148, 64, @on  
            exec sp_trace_setevent @TraceID, 148, 1, @on  
            -- 启动跟踪  
            exec sp_trace_setstatus @TraceID, 1  
END
GO
--将该存储过程设置为SQL Server服务启动时自动启动
EXEC sp_procoption
'StartBlackBoxTrace','STARTUP','ON'
print 'ok'
GO

查看traces ​

sql
--查看
select * from sys.traces
--查看
select * from sys.traces

暂停和停止及删除服务器端跟踪 ​

sql
--删除
exec sp_trace_setstatus 2, 0
exec sp_trace_setstatus 2, 2
--删除
exec sp_trace_setstatus 2, 0
exec sp_trace_setstatus 2, 2

如果要启动上面的服务端跟踪，可运行下面的语句：

exec sp_trace_setstatus 2, 1 --第一个参数2表示 TraceID，可以通过select * from sys.traces查看跟踪ID。第二个参数表示将状态改为1，即启动

如果要停止上面的服务器端跟踪，可运行下面的语句：

exec sp_trace_setstatus 1, 0 --第一个参数表示TraceID,即步骤1中的输出参数。第二个参数表示将状态改为0，即暂停

如果要移除上面的服务器端跟踪，可运行下面的语句：

exec sp_trace_setstatus 1, 2 --第一个参数表示TraceID,即步骤1中的输出参数。第二个参数表示将状态改为2，即停止

查看跟踪文件内容 ​

对于上面生成的跟踪文件(D:/DBA_TOOLS/db_deadLock_lo/deadlockdetect.trc),可通过两种方法查看：

1).执行t-sql命令

sql
select * from fn_trace_gettable('D:/DBA_TOOLS/db_deadLock_lo/deadlockdetect.trc',1)
select * from fn_trace_gettable('D:/DBA_TOOLS/db_deadLock_lo/deadlockdetect.trc',1)

结果中的Extricate列即以XML的形式返回死锁的详细信息。

2).在SQL Server Profiler中打开。

依次 进入Profiler -> 打开跟踪文件 ->选择D:/DBA_TOOLS/db_deadLock_lo/deadlockdetect.trc，就可以看到以图形形式展现的死锁信息

7.2死锁Trace Flag ​

文档，https://learn.microsoft.com/en-us/sql/t-sql/database-console-commands/dbcc-traceon-trace-flags-transact-sql?view=sql-server-2017

SQL Server 跟死锁相关的Trace Flag是 1204 和 1222，两个Trace Flag的Scope都是global only，两者记录的信息基本相同，都会把造成死锁的两个事务、抢占的资源、死锁类型和命令记录下来。前者是以文本格式记录，后者是以XML格式记录的，可以同时打开这两个追踪标志，记录的数据都存储在错误日志（Error Log）中。

微软的官方文档对这两个Trace Falg的定义是：

  • 1204：Returns the resources and types of locks participating in a deadlock and also the current command affected.
  • 1222：Returns the resources and types of locks that are participating in a deadlock and also the current command affected, in an XML format that does not comply with any XSD schema.

7.3 慢查询开启 ​

1.打开ssm

2.点击你要记录慢查询日志的数据库-- 右键属性-- 查询存储 （query stone）

image-20240805101840540

而慢查询有几种方式体现

1 查询时间长

2 使用物理I/O 多

3 内存占用多少

参考文档： ​

  1. SQL Server Objects: https://docs.microsoft.com/zh-cn/sql/relational-databases/performance-monitor/use-sql-server-objects?view=sql-server-2017
  2. SQLServer:SQL Statistics object: https://docs.microsoft.com/zh-cn/sql/relational-databases/performance-monitor/sql-server-sql-statistics-object?view=sql-server-2017
  3. sys.dm_os_performance_counters 返回内容相关： https://docs.microsoft.com/zh-cn/sql/relational-databases/system-dynamic-management-views/sys-dm-os-performance-counters-transact-sql?view=sql-server-2017
  4. Database States： https://docs.microsoft.com/zh-cn/sql/relational-databases/databases/database-states?view=sql-server-2017
  5. 公有云厂商说明文档 https://support.huaweicloud.com/usermanual-rds/rds_sqlserver_06_0001.htmlhttps://cloud.tencent.com/document/product/238/7524

http://www.360doc.com/content/16/0318/15/11991_543342020.shtml

https://developer.aliyun.com/profile/z6fkuw7gdpnnm/article_1?spm=a2c6h.12873639.article-detail.84.f0d8635bRqGdEM