Situatie
Pentru cazurile in care avem nevoie sa stim care sunt procedurile rulate (folosite) cel mai des pe un server de sql am creat o procedura stocata ce aduce top 10 cele mai foslosite proceduri de pe serverul de sql , de la ultimul restart de server, si, pentru aceste proceduri din top 10 aduce urmatoarele coloane:
ServerName – numele serverului
DatabaseName – numele bazei de date pe care se ruleaza respectivul script din top 10
ObjectName – numele procedurii
ExecutionCount – de cate ori a fost rulata (indiferent daca a fost rulata cu succes sau a dat vreo eroare)
Solutie
create procedure BD_FrequentQueries_prc
as
with frequent_queries as
(
select top 100
query_hash,
sum(execution_count) executions
from sys.dm_exec_query_stats
where query_hash <> 0x0
group by query_hash
)
select
top 100
@@servername as ServerName,
coalesce(db_name(st.dbid), db_name(cast(pa.value AS INT)), ‘Resource’) AS [DatabaseName],
object_name(st.objectid, st.dbid) as ObjectName,
qs.execution_count as execution_count
into #temp
from sys.dm_exec_query_stats qs
join frequent_queries fq
on fq.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
qs.execution_count desc
select top 10
ServerName,
DatabaseName,
ObjectName,
max(execution_count) as ExecutionCount
from #temp
where ObjectName is not null
group by ServerName, DatabaseName, ObjectName
order by 4 desc
drop table #temp
Leave A Comment?