CNDBA社区
1.查看当前占用内存前10的SQL
select top 10 RIGHT('00000000'+CAST(row_number() over(order by size_in_bytes desc) as varchar(10)),2) num,size_in_bytes,[text] from (
SELECT p.size_in_bytes,[sql].[text]
FROM sys.dm_exec_cached_plans p
cross APPLY sys.dm_exec_sql_text (p.plan_handle) sql
cross apply sys.dm_exec_query_stats
WHERE execution_count>1 and last_execution_time>dateadd(dd,-7,getdate())
) a
2.前10物理读写
select RIGHT('00000000'+CAST(row_number() over(order by avg_local_writes_read desc) as varchar(10)),2),avg_logical_reads,avg_logical_writes,avg_phys_reads,Execution_count,text from (
select top 10
(total_logical_reads + total_logical_writes)/Execution_count as avg_local_writes_read ,
(total_logical_reads/execution_count) as avg_logical_reads,
(total_logical_writes/execution_count) as avg_logical_writes,
(total_physical_reads/execution_count) as avg_phys_reads,
Execution_count,
sql_text.text
from sys.dm_exec_query_stats
cross apply sys.dm_exec_sql_text(sql_handle) as sql_text
WHERE execution_count>1 and last_execution_time>dateadd(dd,-7,getdate())
order by avg_local_writes_read desc
) a
3.耗时前10的SQL
select RIGHT('00000000'+CAST(row_number() over(order by avg_time desc) as varchar(10)),2),avg_time,total_time,total_used_cpu_time,exec_count,exec_sql from (
SELECT top 10
(total_elapsed_time / execution_count)/1000 as avg_time
,total_elapsed_time/1000 as total_time
,total_worker_time/1000 as total_used_cpu_time
,execution_count as exec_count
,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
) as exec_sql
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%' and execution_count>1 and last_execution_time>dateadd(dd,-7,getdate())
ORDER BY avg_time DESC
) a
4.执行次数前10的SQL
select top 10 RIGHT('00000000'+CAST(row_number() over(order by execution_count desc) as varchar(10)),2) num,execution_count,[text] from (
SELECT TOP 100 creation_time as creation_time
,execution_count as execution_count
,st.text
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%' and execution_count>1 and last_execution_time>dateadd(dd,-7,getdate())
ORDER BY execution_count DESC
) a
5.消耗平均CPU的最多的前10个SQL
select RIGHT('00000000'+CAST(row_number() over(order by avg_cpu_cost desc) as varchar(10)),2),avg_cpu_cost,execution_count,query_text from (
SELECT TOP 10
total_worker_time/execution_count AS avg_cpu_cost,
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
where execution_count>1 and last_execution_time>dateadd(dd,-7,getdate())
ORDER BY [avg_cpu_cost] DESC
) a
6.总消耗CPU最多的前10个SQL
select top 10 RIGHT('00000000'+CAST(row_number() over(order by total_CPU_time_ms desc) as varchar(10)),2) num,total_CPU_time_ms,execution_count,avg_CPU_time_ms,text from (
SELECT TOP 20
total_worker_time/1000 AS total_CPU_time_ms,execution_count as execution_count,
qs.total_worker_time/qs.execution_count/1000 AS avg_CPU_time_ms,
max_worker_time /1000 AS max_exec_time_ms,
qt.text AS text
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 and last_execution_time>dateadd(dd,-7,getdate())
ORDER BY total_worker_time DESC
) a
版权声明：本文为博主原创文章，未经博主允许不得转载。
MSSQL
