SQL常用性能统计语句

1.查看SQL语句IO消耗

set statistics io on 
    sql 语句 
set statistics io off

 

 

2.查看SQL语句时间消耗

 

set statistics time on 
     sql 语句
set statistics time off

 

 

3.查看SQL语句索引消耗

 

set statistics profile on 
       sql 语句 
set statistics profile off

 

4.查询某段时间内执行时间超过指定时长的SQL语句

SELECT TOP 10 OBJECT_NAME(qt.objectid, qt.dbId) AS procName,
DB_NAME(qt.dbId) AS [db_name],
qt.text AS SQL_Full,
SUBSTRING(
qt.text,
(qs.statement_start_offset / 2) + 1,
(
(
CASE statement_end_offset
WHEN -1 THEN DATALENGTH(qt.text)
ELSE qs.statement_end_offset
END
- qs.statement_start_offset
) / 2
) + 1
) AS SQL_Part --统计对应的部分语句
,
qs.creation_time,
qs.last_execution_time,
qs.execution_count,
qs.last_elapsed_time / 1000000 AS lastElapsedSeconds,
qs.last_worker_time / 1000000 AS lastCpuSeconds,
CAST(
qs.total_elapsed_time / 1000000.0 / (
CASE
WHEN qs.execution_count = 0 THEN -1
ELSE qs.execution_count
END
) AS DECIMAL(28, 2)
) AS avgDurationSeconds,
CAST(qs.last_logical_reads AS BIGINT) * 1.0 / (1024 * 1024) * 8060 AS
lastLogicReadsMB,
qs.last_logical_reads,
qs.plan_handle
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) AS p
WHERE qs.last_execution_time >= CONVERT(CHAR(10),GETDATE(),120)+' 08:00' --今天8点之后的慢SQL
AND qs.last_elapsed_time >= 3 * 1000 * 1000 --只取执行时间大于 3 秒的记录
AND qt.[text] NOT LIKE '%Proc_DBA%'
ORDER BY
qs.last_worker_time DESC