sqlserver问题排查日常脚本

作者：Dreamer
出处：http://www.dreamerlzy.com/blog/article/detail/sql-maintain
说明：本文版权归作者所有，欢迎转载，但未经作者同意时，请在文章页面明显位置给出原文链接，否则保留追究法律责任的权利。
参考： 无

1、查看sql 执行IO详情

SET STATISTICS TIME ON
SET STATISTICS IO ON
SET STATISTICS PROFILE ON	
select * from TicketInfo where CustId=82388 and UnionId=''
SET STATISTICS TIME OFF
SET STATISTICS IO OFF
SET STATISTICS PROFILE OFF

2、查询数据库当前占用内存

select count(*)*8/1024 as 'cache size(MB)',
case database_id
when 32767 then 'ResourceDb'
else DB_NAME(database_id)
end as 'datebase'
from sys.dm_os_buffer_descriptors
group by DB_NAME(database_id),database_id
order by 'cache size(MB)' desc

3、历史耗时sql查询

SELECT TOP 50
qs.total_worker_time,
qs.execution_count,
[Avg. MultiCore/CPU time(sec)] = (cast(qs.total_worker_time as decimal)  / 1000000) / qs.execution_count,
[Total MultiCore/CPU time(sec)] = qs.total_worker_time / 1000000,
[Avg. Elapsed Time(sec)] = qs.total_elapsed_time / 1000000 / qs.execution_count,
[Total Elapsed Time(sec)] = qs.total_elapsed_time / 1000000,
qs.execution_count,
[Avg. I/O] = (total_logical_reads + total_logical_writes) / qs.execution_count,
[Total I/O] = total_logical_reads + total_logical_writes,
Query = SUBSTRING(qt.[text], (qs.statement_start_offset / 2) + 1,
(
(
CASE qs.statement_end_offset
WHEN -1 THEN DATALENGTH(qt.[text])
ELSE qs.statement_end_offset
END - qs.statement_start_offset
) / 2
) + 1
),
Batch = qt.[text],
[DB] = DB_NAME(qt.[dbid]),
qs.last_execution_time,
qp.query_plan
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_sql_text(qs.[sql_handle]) AS qt
CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) AS qp
where qs.execution_count > 5        --more than 5 occurences
ORDER BY [Avg. MultiCore/CPU time(sec)] desc,[Total MultiCore/CPU time(sec)] DESC

4、查看数据库当前锁情况

select str(request_session_id, 4, 0) as spid,
convert(varchar(20), db_name(resource_database_id)) as DB_Name,
case when resource_database_id = db_id() and resource_type = 'OBJECT'
then convert(char(20), object_name(resource_Associated_Entity_id))
else convert(char(20), resource_Associated_Entity_id)
end as object,
convert(varchar(12), resource_type) as resrc_type,
convert(varchar(12), request_type) as req_type,
convert(char(3), request_mode) as mode,
convert(varchar(8), request_status) as status
from sys.dm_tran_locks
order by request_session_id desc;

5、历史耗时sql(包含CPU占用率)

DECLARE @MaxResultCount INT=100 --返回行数
DECLARE @SQLWhere VARCHAR(500)='' --查询条件
DECLARE @MinExecs INT=1 --最少执行次数
DECLARE @MinExecsPerMin INT=1 --最少执行次数/分钟
DECLARE @MinLastRunDate VARCHAR(20)='' --CONVERT(VARCHAR(20),DATEADD(SECOND,-1*5*60,GETDATE()),120) --上次执行时间
DECLARE @Database INT=0 --数据库对应的Id
DECLARE @Search VARCHAR(200)='' --Text Search
DECLARE @SQLSearch VARCHAR(500)=''
DECLARE @DatabaseName VARCHAR(100)='' --数据库名称
DECLARE @SQLOrder VARCHAR(100)=' ORDER BY PercentCPU DESC' --按AvgCPU 降序
IF(@DatabaseName>'')
BEGIN
SELECT @Database=database_id FROM sys.databases WHERE name=@DatabaseName
END
--cpu单位 微秒(μs)
IF(@MinExecs>0)
BEGIN
SET @SQLWhere+= ' And execution_count >='+CAST( @MinExecs AS VARCHAR(10))
END
IF(@MinExecsPerMin>0)
BEGIN
SET @SQLWhere+= ' And (Case When DATEDIFF(mi, creation_time, qs.last_execution_time) > 0 Then CAST((1.00 * execution_count / DATEDIFF(mi, creation_time, qs.last_execution_time)) AS money) Else Null End) >= '+CAST( @MinExecsPerMin AS VARCHAR(10))
END
IF(@MinLastRunDate>'1900-01-01')
BEGIN
SET @SQLWhere+=' And qs.last_execution_time >= '''+@MinLastRunDate+''''
END
IF(@Database>0)
BEGIN
SET @SQLWhere+=' And Cast(pa.value as Int) = '+CAST( @Database AS VARCHAR(10))
END
IF(@Search>'')
BEGIN
SET @SQLSearch='Where SUBSTRING(st.text,
(StatementStartOffset / 2) + 1,
((CASE StatementEndOffset
WHEN -1 THEN DATALENGTH(st.text)
ELSE StatementEndOffset
END - StatementStartOffset) / 2) + 1) Like ''%' + @Search + '%'''
END
DECLARE @SQL VARCHAR(MAX)=''
SET @SQL='SELECT AvgCPU, AvgDuration, AvgReads, AvgCPUPerMinute,
TotalCPU, TotalDuration, TotalReads,
PercentCPU, PercentDuration, PercentReads, PercentExecutions,
ExecutionCount,
ExecutionsPerMinute,
PlanCreationTime, LastExecutionTime,
SUBSTRING(st.text,
(StatementStartOffset / 2) + 1,
((CASE StatementEndOffset
WHEN -1 THEN DATALENGTH(st.text)
ELSE StatementEndOffset
END - StatementStartOffset) / 2) + 1) AS QueryText,
st.Text FullText,
query_plan AS QueryPlan,
PlanHandle,
StatementStartOffset,
StatementEndOffset,
MinReturnedRows,
MaxReturnedRows,
AvgReturnedRows,
TotalReturnedRows,
LastReturnedRows,
DB_NAME(DatabaseId) AS CompiledOnDatabase
FROM (SELECT TOP ('+CAST(@MaxResultCount AS VARCHAR(12))+')
total_worker_time / execution_count AS AvgCPU,
total_elapsed_time / execution_count AS AvgDuration,
total_logical_reads / execution_count AS AvgReads,
Cast(total_worker_time / age_minutes As BigInt) AS AvgCPUPerMinute,
execution_count / age_minutes AS ExecutionsPerMinute,
Cast(total_worker_time / age_minutes_lifetime As BigInt) AS AvgCPUPerMinuteLifetime,
execution_count / age_minutes_lifetime AS ExecutionsPerMinuteLifetime,
total_worker_time AS TotalCPU,
total_elapsed_time AS TotalDuration,
total_logical_reads AS TotalReads,
execution_count ExecutionCount,
CAST(ROUND(100.00 * total_worker_time / t.TotalWorker, 2) AS MONEY) AS PercentCPU,
CAST(ROUND(100.00 * total_elapsed_time / t.TotalElapsed, 2) AS MONEY) AS PercentDuration,
CAST(ROUND(100.00 * total_logical_reads / t.TotalReads, 2) AS MONEY) AS PercentReads,
CAST(ROUND(100.00 * execution_count / t.TotalExecs, 2) AS MONEY) AS PercentExecutions,
qs.creation_time AS PlanCreationTime,
qs.last_execution_time AS LastExecutionTime,
qs.plan_handle AS PlanHandle,
qs.statement_start_offset AS StatementStartOffset,
qs.statement_end_offset AS StatementEndOffset,
qs.min_rows AS MinReturnedRows,
qs.max_rows AS MaxReturnedRows,
CAST(qs.total_rows as MONEY) / execution_count AS AvgReturnedRows,
qs.total_rows AS TotalReturnedRows,
qs.last_rows AS LastReturnedRows,
qs.sql_handle AS SqlHandle,
			Cast(pa.value as Int) DatabaseId
FROM (SELECT *,
CAST((CASE WHEN DATEDIFF(second, creation_time, GETDATE()) > 0 And execution_count > 1
THEN DATEDIFF(second, creation_time, GETDATE()) / 60.0
ELSE Null END) as MONEY) as age_minutes,
CAST((CASE WHEN DATEDIFF(second, creation_time, last_execution_time) > 0 And execution_count > 1
THEN DATEDIFF(second, creation_time, last_execution_time) / 60.0
ELSE Null END) as MONEY) as age_minutes_lifetime
FROM sys.dm_exec_query_stats) AS qs
CROSS JOIN(SELECT SUM(execution_count) TotalExecs,
SUM(total_elapsed_time) TotalElapsed,
SUM(total_worker_time) TotalWorker,
SUM(Cast(total_logical_reads as DECIMAL(38,0))) TotalReads
FROM sys.dm_exec_query_stats) AS t
CROSS APPLY sys.dm_exec_plan_attributes(qs.plan_handle) AS pa
WHERE pa.attribute = ''dbid'' '+@SQLWhere+'
	) sq
CROSS APPLY sys.dm_exec_sql_text(SqlHandle) AS st
CROSS APPLY sys.dm_exec_query_plan(PlanHandle) AS qp  '+@SQLSearch +@SQLOrder
	--PRINT @SQL
	EXEC(@SQL)