Top Hdd Usage

Configurare noua (How To)

Situatie

In vederea optimizarii utilizarii resurselor hardware ale serverului de sql, am facut o procedura ce returneaza  query-urile ce folosesc intensiv hdd-ul, ceea ce poate duce la blocaje sau executii lente ale altor query-uri.

Solutie

create procedure TopHddUsage

as

select
q.[text],
SUBSTRING(q.text, (highest_cpu_queries.statement_start_offset/2)+1, ((CASE highest_cpu_queries.statement_end_offset
WHEN -1 THEN DATALENGTH(q.text)
ELSE highest_cpu_queries.statement_end_offset
END – highest_cpu_queries.statement_start_offset)/2) + 1) AS TextQ,

highest_cpu_queries.total_worker_time,
highest_cpu_queries.total_logical_reads,
highest_cpu_queries.last_execution_time,
highest_cpu_queries.execution_count,
q.dbid,
q.objectid,
q.number,
q.encrypted,
highest_cpu_queries.plan_handle
from
(select top 10
qs.last_execution_time,
qs.execution_count,
qs.plan_handle,
qs.total_worker_time,
qs.statement_start_offset,
qs.statement_end_offset,
qs.total_logical_reads
from
sys.dm_exec_query_stats qs
order by qs.total_worker_time desc) as highest_cpu_queries
cross apply sys.dm_exec_sql_text(plan_handle) as q
order by 4 desc

Tip solutie

Permanent
Etichetare:

Voteaza

(5 din 16 persoane apreciaza acest articol)

Despre Autor

Leave A Comment?