October 29, 2013 at 5:17 pm
Hello there!
I've been putting together a process to monitor potential performance issues with regard to slow running queries, see below.
SELECT top 10
st.max_elapsed_time* 0.000001 as max_elapsed_time_in_secs,
ISNULL(st.total_elapsed_time* 0.000001 / st.execution_count , 0)AS avg_elapsed_time_in_secs ,
fn.text,
qp.query_plan AS query_plan,
ISNULL(CASE WHEN cast(qp.query_plan as varchar(max)) like '%missing index%' THEN '1'END , 0) as 'missing_indexes',
ISNULL(CASE WHEN cast(qp.query_plan as varchar(max)) like '%table scan%' THEN '1'END,0) as 'table_scan',
ISNULL(CASE WHEN cast(qp.query_plan as varchar(max)) like '%Nonclustered Index Scan%' THEN '1'END,0) as 'nonclustered Index Scan',
ISNULL(CASE WHEN cast(qp.query_plan as varchar(max)) like '%Clustered Index Scan%' THEN '1'END,0) as 'clustered Index Scan',
ISNULL(CASE WHEN cast(qp.query_plan as varchar(max)) like '%RID Lookup%' THEN '1'END,0) as 'RID_ookup'
FROM sys.dm_exec_query_stats st
cross apply sys. dm_exec_sql_text(st .sql_handle) fn
cross apply sys. dm_exec_query_plan(st.plan_handle) qp
ORDER BY st.max_elapsed_time desc
This query shows the top 10 longest running queries along with is respective execution plan. This query plan is then examined for suggestions of issues like missing indexes and table scans. (I know there was better ways of querying xml but this is a fairly quick and dirty start).
I was planning on running this once a month and using this as a starting point for finding potential problems, hopefully before users. This is a start toward becoming more proactive about keeping a check on performance, aside from making sure maintenance is always running as it should.
My question is, what do others do to be proactive with performance monitoring? 🙂
Thanks
October 30, 2013 at 1:06 am
This:
One of the things you're missing there is that you're looking for the longest running queries, not the highest impact queries. As such, you'll see the query that runs 10 minutes once a week, but not the query that runs in 30 seconds ten times an hour. Which do you think is the bigger problem?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 2 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply