Situatie
Pentru cazurile in care avem nevoie sa identificam scripturile sau procedurile a caror rulare dureaza mult, am facut o procedura ce intoarce top 10 scripturi sau proceduri in functie de durata medie a rularii lor.
Procedura returneaza urmatoarele coloane:
server_name – numele serverului pe care este rulata procedura
DatabaseName – numele bazei de care apartine obiectul aflat in top 10
object_name – numele obiectului (procedura sau script. Daca nu este o procedura aici va fi afisat “script”)
AverageDurationSeconds – durata medie a rularii
ObjectText – in aceasta coloana apare bucata de procedura sau de script care afecteaza cel mai mult durata rularii (este folositoare pentru optimizari)
Solutie
create procedure BD_LongQueries_prc
as
with long_queries as
(
select top 100
query_hash,
sum(total_elapsed_time) ElapsedTime
from sys.dm_exec_query_stats
where query_hash <> 0x0
group by query_hash
order by sum(total_elapsed_time) desc
)
select
top 100
@@servername as server_name,
coalesce(db_name(st.dbid), db_name(cast(pa.value AS INT)), ‘Resource’) AS [DatabaseName],
coalesce(object_name(st.objectid, st.dbid), ‘script’) as [object_name],
cast(total_elapsed_time / (execution_count + 0.0) as money) / 1000000 as AverageDurationSeconds,
SUBSTRING(st.[text],(qs.statement_start_offset + 2) / 2,
(CASE
WHEN qs.statement_end_offset = -1 THEN LEN(CONVERT(NVARCHAR(MAX),st.[text])) * 2
ELSE qs.statement_end_offset + 2
END – qs.statement_start_offset) / 2) as sql_text –,
into #temp
from sys.dm_exec_query_stats qs
join long_queries lq
on lq.query_hash = qs.query_hash
cross apply sys.dm_exec_sql_text(qs.sql_handle) st
cross apply sys.dm_exec_query_plan (qs.plan_handle) qp
outer apply sys.dm_exec_plan_attributes(qs.plan_handle) pa
where pa.attribute = ‘dbid’
order by
AverageDurationSeconds DESC
select top 10
server_name, DatabaseName, [object_name], max(AverageDurationSeconds) as AverageDurationSeconds, max(sql_text) as ObjectText
from #temp
group by server_name,DatabaseName,[object_name]
order by 4 desc
drop table #temp
Leave A Comment?