Situatie
Pentru ca m-am lovit des de probleme de incarcare ale serverului de sql, am facut o procedura ce returneaza query-urile ce utilizeaza cel mai mult procesorul. Poate fi folosita in procesul de optimizare a serverului de sql.
Solutie
create procedure TopCpuUsage
as
select top 10
query_stats.query_hash,
SUM(query_stats.total_worker_time) / SUM(query_stats.execution_count) as avgCPU,
min(query_stats.statement_text) as TextQ
from
(
select qs.*,
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 statement_text
from sys.dm_exec_query_stats as qs
cross apply sys.dm_exec_sql_text(qs.sql_handle) as st
) as query_stats
group by query_stats.query_hash
order by 2 desc;
Leave A Comment?