1.SqlServer慢排查
在监控页面选择资源监控或引擎监控，并选择查询时间，即可查看相应的监控数据，具体监控项介绍如下。
| 类别 | 监控项 | 说明 |
|---|---|---|
| 资源监控 | 磁盘空间 | 实例的磁盘空间使用量，包括：磁盘空间总体使用量数据空间使用量日志空间使用量临时文件空间使用量系统文件空间使用量单位：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数据库连接数
-- 使用中的数据库连接数
select cntr_value from sys.dm_os_performance_counters where counter_name = 'User Connections';
数据库状态
select name, state, state_desc from sys.databases;
数据库文件状态
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
-- 平均每秒事务数:
select cntr_value from sys.dm_os_performance_counters where counter_name = 'Transactions/sec' and instance_name = '_Total'
1.3缓存命中率
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编译数
select cntr_value from sys.dm_os_performance_counters where counter_name = 'SQL Compilations/sec';
平均每秒SQL重编译数
select cntr_value from sys.dm_os_performance_counters where counter_name = 'SQL Re-Compilations/sec';
1.4每秒全表扫描数
-- 每秒全表扫描数:
select cntr_value from sys.dm_os_performance_counters where counter_name = 'Full Scans/sec';
平均每秒batch数
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 = 'Errors/sec' and instance_name = '_Total';
1.5锁
每秒锁等待次数:
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 Requests/sec'and instance_name = '_Total';
每秒锁超时次数
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 = 'Number Of Deadlocks/sec'and instance_name = '_Total';
查看死锁
--查询死锁
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日志）
二扩展事件会话信息
--扩展事件会话的信息
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跟踪
1.6每秒检查点写入Page数
select cntr_value from sys.dm_os_performance_counters where counter_name = 'Checkpoint pages/sec';
Lazy writes/sec:
select * from sys.dm_os_performance_counters where counter_name = 'Lazy writes/sec';
1.7always on状态
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 server的缓存
dbcc freeProcCache
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 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 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 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
- 常用
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时间的会话
一般会用到三个视图sys.sysprocesses ,dm_exec_sessions ,dm_exec_requests
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
- 查看所有
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语句
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占用情况
--查看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
SELECT
scheduler_address,
scheduler_id,
cpu_id,
status,
current_tasks_count,
current_workers_count,active_workers_count
FROM sys.dm_os_schedulers
查询CPU占用高的语句
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语句
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语句
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 * 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 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'
2.1 QPS
dm_os_performance_counters,该视图用于查看数据库的性能指标
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查看索引是否丢失
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
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%，说明该语句等待时间过长
- 最占用系统资源的查询
就是占用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：
- 第十二步
在弹出的页面中，我们开始设置优化顾问。
- 在【工作负荷】中找到刚保存的Test.trc文件
- 在选择要优化的数据库和表中，我们单独找到需要被分析的表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 里的状态：
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.排查连接对象
--如果想要指定查询某个数据库，将后面的注释去掉即可
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 [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语句占用较大
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在等待
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语句
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 调度程序在磁盘上等待
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 分钟的详细信息
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,查看当前正在运行的查询
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 的历史查询
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
----- --总耗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
---平均耗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
如何清理看到的这些语句所运行缓存的清理
首先是清除存储过程相关的缓存语句
DBCC FREEPROCCACHE -----清理缓存然后对该数据库的-会话缓存
DBCC FREESESSIONCACHE ---会话清理最后两个执行的语句是对数据系统清理的语句
DBCC FREESYSTEMCACHE('All') --------系统缓存
DBCC DROPCLEANBUFFERS ---------所有缓存
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
4.索引缺失
6.SPID
6.1根据spid名字
--- 查询db_name中所有spid
select spid from master..sysprocesses where dbid = DB_ID('db_Name')
---spid可以查看sql语句执行
DBCC INPUTBUFFER(spid)
参考文档：
- SQL Server Objects:
https://docs.microsoft.com/zh-cn/sql/relational-databases/performance-monitor/use-sql-server-objects?view=sql-server-2017 - SQLServer:SQL Statistics object:
https://docs.microsoft.com/zh-cn/sql/relational-databases/performance-monitor/sql-server-sql-statistics-object?view=sql-server-2017 - 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 - Database States：
https://docs.microsoft.com/zh-cn/sql/relational-databases/databases/database-states?view=sql-server-2017 - 公有云厂商说明文档
https://support.huaweicloud.com/usermanual-rds/rds_sqlserver_06_0001.html
https://cloud.tencent.com/document/product/238/7524
http://www.360doc.com/content/16/0318/15/11991_543342020.shtml
