• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 59
  • Last Modified:

Alert for expensive Queries in SQL Server 2016

How to setup an alert for expensive queries in SQL Server 2016?

I am planning to set up some kind of alert system which can trigger if it detects top  expensive queries and send an email to me.

is it possible? how to implement this?
0
Vijay
Asked:
Vijay
  • 2
  • 2
1 Solution
 
Pawan KumarDatabase ExpertCommented:
Do not write yourself any query as there are many queries already available over the web for this kind of requirement. You can refer below from one of the best SQL server expert. (Brent). You need to modify below queries for your purpose as your question is very wide. You can take the long running queries and schedule using the SQL Agent Job.

SELECT  st.text,
        qp.query_plan,
        qs.*
FROM    (
    SELECT  TOP 50 *
    FROM    sys.dm_exec_query_stats
    ORDER BY total_worker_time DESC
) AS qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS st
CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) AS qp
WHERE qs.max_worker_time > 300
      OR qs.max_elapsed_time > 300

Open in new window


https://www.brentozar.com/blitzcache/long-running-queries/

or from Pinal

SELECT TOP 10 SUBSTRING(qt.TEXT, (qs.statement_start_offset/2)+1,
((CASE qs.statement_end_offset
WHEN -1 THEN DATALENGTH(qt.TEXT)
ELSE qs.statement_end_offset
END - qs.statement_start_offset)/2)+1),
qs.execution_count,
qs.total_logical_reads, qs.last_logical_reads,
qs.total_logical_writes, qs.last_logical_writes,
qs.total_worker_time,
qs.last_worker_time,
qs.total_elapsed_time/1000000 total_elapsed_time_in_S,
qs.last_elapsed_time/1000000 last_elapsed_time_in_S,
qs.last_execution_time,
qp.query_plan
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) qt
CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) qp
ORDER BY qs.total_logical_reads DESC -- logical reads
-- ORDER BY qs.total_logical_writes DESC -- logical writes
-- ORDER BY qs.total_worker_time DESC -- CPU time

Open in new window

https://blog.sqlauthority.com/2010/05/14/sql-server-find-most-expensive-queries-using-dmv/
0
 
VijayAuthor Commented:
Thank you Pawan,

Actually i am looking for atleast last 2 weeks history.
I mean most expensive queries for last 2 weeks.
0
 
Pawan KumarDatabase ExpertCommented:
For two weeks please use this -

SELECT  st.text,
        qp.query_plan,
        qs.*
FROM    
(
    SELECT *
    FROM   sys.dm_exec_query_stats
    WHERE  last_execution_time >= DATEADD(day,-14, CAST(GETDATE() AS DATE))  
) AS qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS st
CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) AS qp

Open in new window

0
 
VijayAuthor Commented:
Thank you very much pawan
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now