[MSSQL] 제일 많은 빈도수 쿼리 조회 스크립트

원래는 MSSQL PROFILER 를 이용해서 모니터링 하면서 조회할 수 있지만, 부하를 일으킬 수 있어, 문제가 된다.
쿼리를 이용해서 제일 많은 빈도수 쿼리를 조회할 수 있습니다.

select top 10
db_name(st.dbid) as dbname, cp.objtype,
qs.total_elapsed_time, creation_time, last_execution_time,
qs.execution_count, text
from sys.dm_exec_query_stats qs
cross apply sys.dm_exec_sql_text(qs.plan_handle)st
join sys.dm_exec_cached_plans cp
on qs.plan_handle = cp.plan_handle
where 1=1
--and creation_time >= dateadd(d, -1, getdate())
--and db_name(st.dbid) is not null
--and cp.objtype = 'proc'
--and db_name(st.dbid) = ''
--and text like '%%'
order by execution_count desc;

 

SELECT TOP 10
deqs.execution_count AS TotalExecutionTime
,OBJECT_NAME(objectid) AS ObjectName
,QueryText = SUBSTRING(
dest.text,
deqs.statement_start_offset/2,
(CASE WHEN deqs.statement_end_offset = -1
THEN len(CONVERT(nvarchar(MAX), dest.text)) * 2
ELSE deqs.statement_end_offset
END - deqs.statement_start_offset)/2)
,DatabaseName = db_name(dest.dbid)
FROM sys.dm_exec_query_stats AS deqs
CROSS APPLY sys.dm_exec_sql_text(deqs.sql_handle) AS dest
ORDER BY deqs.execution_count DESC