sqlserver 性能调优脚本

------------------------最耗时的sql---------------------------------------
DECLARE @n INT;
SET @n = 500;

WITH cte1
AS ( SELECT a.* ,
t.*
FROM sys.dm_exec_query_stats a
CROSS APPLY sys.dm_exec_sql_text(a.plan_handle) t
WHERE t.dbid >= 5
)
SELECT t.dbid ,
DB_NAME(t.dbid) AS dbname ,
a.total_worker_time ,
a.avg_time_ms ,
a.execution_count ,
a.cache_count ,
REPLACE(REPLACE(t.text, CHAR(10), ' '), CHAR(13), ' ')
FROM ( SELECT TOP ( @n )
plan_handle ,
SUM(total_worker_time) / 1000 AS total_worker_time ,
SUM(execution_count) AS execution_count ,
COUNT(1) AS cache_count ,
( SUM(total_worker_time) / SUM(execution_count) )
/ 1000 AS avg_time_ms
FROM cte1
GROUP BY plan_handle
ORDER BY avg_time_ms DESC
) a
CROSS APPLY sys.dm_exec_sql_text(a.plan_handle) t
WHERE avg_time_ms > 200
ORDER BY avg_time_ms DESC;

GO

------------------------最频繁的sql---------------------------------------
DECLARE @n INT;
SET @n = 500;
WITH cte1
AS ( SELECT a.* ,
t.*
FROM sys.dm_exec_query_stats a
CROSS APPLY sys.dm_exec_sql_text(a.plan_handle) t
WHERE t.dbid >= 5
)
SELECT t.dbid ,
DB_NAME(t.dbid) AS dbname ,
a.execution_count ,
a.total_worker_time ,
a.avg_time_ms ,
a.cache_count ,
REPLACE(REPLACE(t.text, CHAR(10), ' '), CHAR(13), ' ')
FROM ( SELECT TOP ( @n )
plan_handle ,
SUM(total_worker_time) / 1000 AS total_worker_time ,
SUM(execution_count) AS execution_count ,
COUNT(1) AS cache_count ,
( SUM(total_worker_time) / SUM(execution_count) )
/ 1000 AS avg_time_ms
FROM cte1
GROUP BY plan_handle
ORDER BY avg_time_ms DESC
) a
CROSS APPLY sys.dm_exec_sql_text(a.plan_handle) t
ORDER BY execution_count DESC;
GO

------------------------耗cpu的sql---------------------------------------
DECLARE @n INT;
SET @n = 500;
WITH cte1
AS ( SELECT a.* ,
t.*
FROM sys.dm_exec_query_stats a
CROSS APPLY sys.dm_exec_sql_text(a.plan_handle) t
WHERE t.dbid >= 5
)
SELECT t.dbid ,
DB_NAME(t.dbid) AS dbname ,
a.total_logical_reads ,
a.avg_reads ,
a.total_logical_writes ,
a.avg_writes ,
a.execution_count ,
a.total_worker_time ,
a.avg_time_ms ,
a.cache_count ,
REPLACE(REPLACE(t.text, CHAR(10), ' '), CHAR(13), ' ')
FROM ( SELECT TOP ( @n )
plan_handle ,
SUM(total_logical_reads) AS total_logical_reads ,
( SUM(total_logical_reads) / SUM(execution_count) ) AS avg_reads ,
SUM(total_logical_writes) AS total_logical_writes ,
( SUM(total_logical_writes) / SUM(execution_count) ) AS avg_writes ,
SUM(execution_count) AS execution_count ,
COUNT(1) AS cache_count ,
SUM(total_worker_time) AS total_worker_time ,
( SUM(total_worker_time) / SUM(execution_count) )
/ 1000 AS avg_time_ms
FROM cte1
GROUP BY plan_handle
ORDER BY ( ( SUM(total_logical_reads) / SUM(execution_count) )
+ ( SUM(total_logical_writes) / SUM(execution_count) ) ) DESC
) a
CROSS APPLY sys.dm_exec_sql_text(a.plan_handle) t
ORDER BY ( avg_reads + avg_writes ) DESC;
GO

-----------------------当前数据库可能缺少的索引-------------------------------
SELECT d.* ,
s.avg_total_user_cost ,
s.avg_user_impact ,
s.last_user_seek ,
s.unique_compiles
FROM sys.dm_db_missing_index_group_stats s ,
sys.dm_db_missing_index_groups g ,
sys.dm_db_missing_index_details d
WHERE s.group_handle = g.index_group_handle
AND d.index_handle = g.index_handle
ORDER BY s.avg_user_impact DESC;

-----------------------当前数据库没用到的索引-------------------------------
SELECT tb_name = OBJECT_NAME(a.object_id) ,
idx_name = b.name ,
last_user_update ,
c.colid ,
c.keyno ,
col_name = d.name
INTO #tmp
FROM sys.dm_db_index_usage_stats a
LEFT JOIN sys.indexes b ON a.object_id = b.object_id
AND a.index_id = b.index_id
LEFT JOIN sys.sysindexkeys c ON c.id = a.object_id
AND c.indid = a.index_id
LEFT JOIN syscolumns d ON d.id = c.id
AND d.colid = c.colid
WHERE database_id = DB_ID()
AND last_user_seek IS NULL
AND last_user_scan IS NULL
AND last_user_lookup IS NULL
AND last_user_update IS NOT NULL
ORDER BY tb_name ,
idx_name ,
keyno;
SELECT tb_name ,
idx_name ,
last_user_update ,
keywords = STUFF(( SELECT ',' + col_name
FROM #tmp
WHERE tb_name = a.tb_name
AND idx_name = a.idx_name
ORDER BY tb_name ,
idx_name ,
keyno
FOR
XML PATH('')
), 1, 1, '')
FROM #tmp a
GROUP BY tb_name ,
idx_name ,
last_user_update;
DROP TABLE #tmp;
GO

------------------------------当前数据库索引的使用率--------------
SELECT OBJECT_NAME(object_id) AS table_name ,
( SELECT name
FROM sys.indexes
WHERE object_id = stats.object_id
AND index_id = stats.index_id
) AS index_name ,
*
FROM sys.dm_db_index_usage_stats AS stats
WHERE database_id = DB_ID()
ORDER BY table_name;

--当前数据库指定表的索引使用情况
DECLARE @table AS NVARCHAR(100);
SET @table = 't_ire_candidate';

SELECT ( SELECT name
FROM sys.indexes
WHERE object_id = stats.object_id
AND index_id = stats.index_id
) AS index_name ,
*
FROM sys.dm_db_index_usage_stats AS stats
WHERE object_id = OBJECT_ID(@table)
ORDER BY user_seeks ,
user_scans ,
user_lookups ASC;
GO

-- 查找阻塞，通过 a.blocking_session_id 查看引起阻塞的进程，text是对应的sql脚本
SELECT b.text , -- sql执行计划
b.dbid ,
DB_NAME(b.dbid) AS dbname ,
a.session_id , -- 执行会话id
a.blocking_session_id , -- 引起阻塞的会话id
a.status ,
a.command ,
a.wait_time ,
a.wait_type ,
a.wait_resource ,
a.total_elapsed_time
FROM sys.dm_exec_requests a
CROSS APPLY sys.dm_exec_sql_text(a.sql_handle) b
WHERE session_id IN ( SELECT DISTINCT
request_session_id
FROM sys.dm_tran_locks );