sql 查询优化

SELECT total_worker_time/1000 N'所用的CPU总时间ms'

,total_elapsed_time/1000N'总花费时间ms'

,(total_elapsed_time/execution_count)/1000N'平均时间ms'

,SUBSTRING(st.text,(qs.statement_start_offset/2)+1,

((CASEstatement_end_offset

WHEN-1THENDATALENGTH(st.text)

ELSEqs.statement_end_offsetEND

-qs.statement_start_offset)/2)+1)N'执行语句'

FROMsys.dm_exec_query_statsASqs

CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) st

SELECT DB_ID(DB.dbid) '数据库名'

, OBJECT_ID(db.objectid) '对象'

, QS.creation_time '编译计划的时间'

, QS.last_execution_time'上次执行计划的时间'

, QS.execution_count '执行的次数'

, QS.total_elapsed_time/ 1000 '占用的总时间（秒）'

, QS.total_physical_reads'物理读取总次数'

, QS.total_worker_time/ 1000 'CPU 时间总量（秒）'

, QS.total_logical_writes'逻辑写入总次数'

, QS.total_logical_readsN'逻辑读取总次数'

, QS.total_elapsed_time/ 1000 N'总花费时间（秒）'

, 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 '执行语句'

FROM sys.dm_exec_query_statsAS QS CROSS APPLY

sys.dm_exec_sql_text(QS.sql_handle) AS ST INNER JOIN

( SELECT *

FROM sys.dm_exec_cached_plans cp CROSSAPPLY

sys.dm_exec_query_plan(cp.plan_handle)

) DB

ON QS.plan_handle= DB.plan_handle

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%'

ORDER BY QS.total_elapsed_time /1000 DESC

sql 查询优化

相关推荐