-- 设置查看语句影响行数
SET NOCOUNT OFF
-- 设置查看执行时间和CPU占用时间
SET STATISTICS TIME ON
-- 设置查询对IO的操作情况
SET STATISTICS IO ON
查询正在执行的语句
注意：SqlServer数据库时间以微秒为单位，即1秒=1000毫秒(ms)=1000*1000微秒
SET NOCOUNT OFF;
SET STATISTICS TIME ON;
SET STATISTICS IO ON;
-- 查询正在执行的语句
SELECT TOP 10
st.text AS [执行语句]
,qs.execution_count [执行次数]
,qs.creation_time AS [执行时间]
,(qs.total_logical_reads + qs.total_logical_writes) AS [逻辑读写]
,qs.total_logical_reads AS [逻辑读取]
,qs.total_logical_writes AS [逻辑写入]
,qs.total_physical_reads [物理读取]
,qs.total_elapsed_time AS [执行耗时]
,qs.total_worker_time AS [CPU耗时]
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS st
ORDER BY qs.creation_time DESC
-- 查询当前缓存中批处理或存储过程占用CPU资源的情况
SELECT TOP 50
qs.sql_handle
,COUNT(*) AS [语句个数]
,SUM(qs.execution_count) AS [执行次数]
,SUM(qs.total_worker_time)/1000.0 AS [CPU耗时]
,SUM(qs.total_elapsed_time)/1000.0 AS [执行耗时]
,SUM(qs.total_logical_reads) AS [逻辑读取]
,SUM(qs.total_logical_writes) AS [逻辑写入]
,SUM(qs.total_physical_reads) AS [物理读取]
FROM sys.dm_exec_query_stats AS qs
GROUP BY qs.sql_handle
ORDER BY 4 DESC
--查询执行耗时的语句
SELECT
SS.sum_execution_count
,SS.sum_total_elapsed_time
,SS.sum_total_worker_time
,SS.sum_total_logical_reads
,SS.sum_total_logical_writes
,T.text
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
--查询CPU消耗最高的SQL语句
SELECT TOP 10
TEXT AS [SQL]
,last_execution_time AS [最后执行时间]
,(total_logical_reads + total_physical_reads + total_logical_writes) / execution_count AS [IO平均读写次数]
,(total_worker_time / execution_count) / 1000000.0 AS [CPU平均执行秒数]
,(total_elapsed_time / execution_count) / 1000000.0 AS [平均执行秒数]
,execution_count AS [执行次数]
,qs.total_physical_reads AS [物理读取次数]
,qs.total_logical_writes AS [逻辑写入次数]
,qp.query_plan AS [查询计划]
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语句
DECLARE @begin_time DATETIME = '2018-01-01 00:00:00';
DECLARE @execute_count_limit INT = 500;
WITH tbl AS (
SELECT
--执行次数
QS.execution_count
,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
,ST.text AS [text]
,QS.last_elapsed_time
,QS.min_elapsed_time
,QS.max_elapsed_time
,QS.total_worker_time
,QS.last_worker_time
,QS.max_worker_time
,QS.min_worker_time
FROM sys.dm_exec_query_stats QS
CROSS APPLY sys.dm_exec_sql_text(QS.sql_handle) ST
WHERE 1=1
AND QS.last_execution_time > @begin_time
AND QS.execution_count > @execute_count_limit
--AND ST.text LIKE '%%'
--ORDER BY QS.execution_count DESC
)
SELECT
MAX(execution_count) max_execution_count
,[text]
FROM tbl
WHERE 1=1
AND [text] NOT LIKE '%sp_MSupd_%'
AND [text] NOT LIKE '%sp_MSins_%'
AND [text] NOT LIKE '%sp_MSdel_%'
GROUP BY [text]
ORDER BY 1 DESC
--查找逻辑读取最高的查询(存储过程)
SELECT TOP 25
p.name AS [存储过程]
,deps.total_logical_reads AS [逻辑读总次数]
,deps.total_logical_reads / deps.execution_count AS [逻辑读平均次数]
,deps.execution_count [总执行次数]
,ISNULL(deps.execution_count / DATEDIFF(Second, deps.cached_time, GETDATE()), 0) AS [每秒调用次数]
,deps.total_elapsed_time/1000/1000.0 AS [总消耗时长]
,deps.total_elapsed_time/1000/1000.0/deps.execution_count AS [平均消耗时长]
,deps.cached_time AS [缓存时间]
FROM sys.procedures AS p
INNER JOIN sys.dm_exec_procedure_stats AS deps ON p.[Object_id] = deps.[Object_id]
WHERE 1=1
AND deps.Database_id = DB_ID()
ORDER BY deps.total_elapsed_time DESC;
-- 排查历史慢查询
SELECT TOP 50
DB_NAME(qt.dbid) AS [数据库]
,OBJECT_NAME(qt.objectid, qt.dbid) AS [对象名]
,qs.creation_time AS [创建时间]
,qs.last_execution_time AS [最近执行时间]
,qs.last_elapsed_time AS [最近执行耗时]
,qs.last_worker_time AS [最近CPU耗时]
,qs.last_rows AS [最近影响行数]
,qs.execution_count AS [执行次数]
,qs.total_elapsed_time AS [累计执行耗时]
,(qs.total_elapsed_time / qs.execution_count) AS [平均执行耗时]
,qs.max_elapsed_time AS [最大执行耗时]
,qs.total_worker_time AS [累计CPU耗时]
,(qs.total_worker_time / qs.execution_count) AS [平均CPU耗时]
,qs.max_worker_time AS [最大CPU耗时]
,(qs.total_logical_reads + qs.total_logical_writes) AS [累计逻辑读写]
,(qs.total_logical_reads + qs.total_logical_writes)/qs.execution_count AS [平均逻辑读写]
,qs.min_rows AS [最小影响行数]
,qs.max_rows AS [最大影响行数]
,qs.total_rows AS [累计影响行数]
,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 [独立查询]
,qt.text AS [父级查询]
,qp.query_plan AS [查询计划]
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
WHERE 1=1
AND qt.dbid IS NOT NULL
AND DB_NAME(qt.dbid)!='msdb'
ORDER BY 4 DESC
-- 精简版
SELECT TOP 100
DB_NAME(qt.dbid) AS [数据库]
,OBJECT_NAME(qt.objectid, qt.dbid) AS [对象名]
,qs.execution_count AS [执行次数]
,qs.total_worker_time/1000/1000 AS [CPU总消耗秒数]
,qs.total_worker_time/qs.execution_count/1000/1000.0 AS [CPU平均消耗秒数]
,max_worker_time/1000/1000.0 AS [CPU最大消耗秒数]
,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 [剔除注释]
,qt.text [完整语句]
,last_execution_time AS [最后执行时间]
FROM sys.dm_exec_query_stats qs
WITH(NOLOCK) CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS qt
WHERE 1=1
AND qs.execution_count > 0
-- AND qs.total_worker_time/qs.execution_count/1000 > 1
AND OBJECT_NAME(qt.objectid, qt.dbid) IS NOT NULL
AND DB_NAME(qt.dbid) != 'msdb'
ORDER BY qs.execution_count DESC
--排查历史慢查询
SELECT TOP 50
(qs.total_logical_reads + qs.total_logical_writes) AS [逻辑读写]
,(qs.total_logical_reads + qs.total_logical_writes)/qs.execution_count AS [平均读写]
,qs.execution_count AS [执行次数]
,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 [独立查询]
,qt.text AS [父级查询]
,DB_NAME(qt.dbid) AS [数据库]
,qp.query_plan AS [查询计划]
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
WHERE 1=1
AND qt.dbid IS NOT NULL
AND DB_NAME(qt.dbid)!='msdb'
ORDER BY 2 DESC
-- 查询当前正在执行的慢查询
SELECT TOP 10
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 1=1
AND DATEDIFF(second, AT.transaction_begin_time, GETDATE()) > 2
查询最近修改的存储过程
SELECT
Name
,Create_date
,Modify_Date
FROM sys.objects
WHERE 1=1
AND TYPE in ('U','P', 'V','F', 'TR', 'FN')
ORDER BY Modify_Date DESC;
--查询每秒死锁数量
SELECT *
FROM sys.dm_os_performance_counters
WHERE 1=1
AND counter_name LIKE 'Number of Deadlocksc%';
--查询等待类型
SELECT TOP 10 * FROM SYS.dm_os_wait_stats ORDER BY wait_time_ms DESC
--通过等待类型分析耗时操作
查询数据库连接数
SELECT
*
FROM sysprocesses
WHERE 1=1
AND dbid IN(SELECT dbid FROM sysdatabases WHERE 1=1 AND name='WHGameUserDB')
检索索引碎片
SELECT
DB_NAME(ps.database_id) AS [DbName]
,OBJECT_NAME(ps.OBJECT_ID) AS [DbObject]
,ps.index_id AS [IndexID]
,b.name
,ps.avg_fragmentation_in_percent
FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, NULL) AS ps
INNER JOIN sys.indexes AS b ON ps.OBJECT_ID = b.OBJECT_ID AND ps.index_id = b.index_id
WHERE 1=1
AND ps.database_id = DB_ID('ReportServerTempDB')
ORDER BY ps.avg_fragmentation_in_percent DESC
查看内存结构
SELECT
(physical_memory_in_use_kb/1024) AS MemoryUsed
,(locked_page_allocations_kb/1024) AS LockedPagesUsed
,(total_virtual_address_space_kb/1024) AS VASTotal
,process_physical_memory_low
,process_virtual_memory_low
FROM sys.dm_os_process_memory;
