Ένα από τα πράγματα που κάνω στην δουλειά μου είναι πηγαίνω σε πελάτες και να τους βοηθάω να λύσουν τα προβλήματα τους.
Αυτό σημαίνει ότι έρχομαι αντιμέτωπος με instances που δεν γνωρίζω και πρέπει να μάθω για αυτά.Έτσι εκτελώ αρκετά scripts που έχω γράψει για να συλλέξω τις πληροφορίες που θέλω.
Μέσα σε αυτά κάνω αρκετά μεγάλη χρήση των DMV και ένα από αυτά που αγαπώ καθώς μου δίνουν αρκετές πληροφορίες είναι η sys.dm_exec_query_stats.
Το συγκεκριμένο DMV όπως ξεκάθαρα αναφέρεται στο documentation “Returns aggregate performance statistics for cached query plans in SQL Server. The view contains one row per query statement within the cached plan, and the lifetime of the rows are tied to the plan itself. When a plan is removed from the cache, the corresponding rows are eliminated from this view.”
Παρόλο που μπορεί να υπάρχουν περιπτώσεις που αυτή να μην επιστρέφει δεδομένα ή να επιστρέφει λιγότερα και αρκετοί συνάδελφοι να μιλάνε για bug, εντούτοις ακόμα και αυτό είναι μια ένδειξη για memory pressure με συνέπεια να έχουμε αυτό που λέμε plan cache eviction και θα πρέπει σε αυτή την περίπτωση να δούμε τα δεδομένα που επιστρέφουν οι sys.dm_os_memory_cache_clocks_hands και την sys.dm_os_ring_buffers.
Σε κάθε άλλη περίπτωση μπορώ να βγάλω αρκετά στοιχεία από την χρήση της σε συνδυασμό με άλλεs DMVs. Ας δούμε μερικά ενδεικτικά παραδείγματα αλλά φυσικά μπορείτε να φτιάξετε πολλά περισσότερα.
Find the most expensive database
select db_name(t.dbid) as database_name,
sum(s.execution_count) as execution_count,
sum(s.total_worker_time ) as total_worker_time ,
sum(s.total_physical_reads) as total_physical_reads ,
sum(s.total_logical_writes) as total_logical_writes ,
sum(s.total_logical_reads) as total_logical_reads ,
sum(s.total_clr_time) as total_clr_time ,
sum(s.total_elapsed_time) as total_elapsed_time ,
sum(s.total_rows) as total_rows ,
sum(s.total_dop) as total_dop ,
sum(s.total_grant_kb) as total_grant_kb,
sum(s.total_used_grant_kb) as total_used_grant_kb ,
sum(s.total_ideal_grant_kb) as total_ideal_grant_kb ,
sum(s.total_reserved_threads) as total_reserved_threads ,
sum(s.total_used_threads) as total_used_threads ,
sum(s.total_columnstore_segment_reads) as total_columnstore_segment_reads ,
sum(s.total_columnstore_segment_skips) as total_columnstore_segment_skips ,
sum(s.total_spills) as total_spills
from sys.dm_exec_query_stats as s
cross apply sys.dm_exec_sql_text(s.sql_handle) as t
group by t.dbid
order by 2 descFinding the TOP 10 queries
select top 5 query_stats.query_hash as "query hash",
sum(query_stats.total_worker_time) / sum(query_stats.execution_count) as "avg cpu time",
min(query_stats.statement_text) as "statement text"
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;
select top 10
t.text as 'sql text',
st.execution_count ,
isnull( st.total_elapsed_time / st.execution_count, 0 ) as 'avg excecution time',
st.total_worker_time / st.execution_count as 'avg worker time',
st.total_worker_time,
st.max_logical_reads,
st.max_logical_writes,
st.creation_time,
isnull( st.execution_count / datediff( second, st.creation_time, getdate()), 0 ) as 'calls per second'
from sys.dm_exec_query_stats st
cross apply sys.dm_exec_sql_text( st.sql_handle ) t
order by st.total_elapsed_time descReturning row count aggregates for a query
select qs.execution_count,
substring(qt.text,qs.statement_start_offset/2 +1,
(case when qs.statement_end_offset = -1
then len(convert(nvarchar(max), qt.text)) * 2
else qs.statement_end_offset end -
qs.statement_start_offset
)/2
) as query_text,
qt.dbid, dbname= db_name (qt.dbid), qt.objectid,
qs.total_rows, qs.last_rows, qs.min_rows, qs.max_rows
from sys.dm_exec_query_stats as qs
cross apply sys.dm_exec_sql_text(qs.sql_handle) as qt
where qt.text like '%select%'
order by qs.execution_count desc; Monitor Query Plans
select
highest_cpu_queries.plan_handle,
highest_cpu_queries.total_worker_time,
q.dbid,
q.objectid,
q.number,
q.encrypted,
q.[text]
from
(select top 50
qs.plan_handle,
qs.total_worker_time
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 highest_cpu_queries.total_worker_time desc//antonch