Gasire scripturi rulate des pe serverul de sql

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

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

Tip solutie

Permanent

Follow Us