sp_add_alert @performance_condition where reads on a query exceeds one million

Question

I want to add an alert that will fire anytime a query would exceed 1 million reads.  I can run a trace in SQL Server Profiler that can catch queries with over 1 million reads, the question is how do I do it with an alert?

Sudo code below:

EXECUTE msdb.dbo.sp_add_alert 
  @name = 'QueryReadsExceeding1Mil',
  @notification_message = 'The number of reads on q query > 1000000!',
  @performance_condition = 'SQLServer:WhatDoIPutHere?|>|1000000'

---

Duane Lawrence

Answer 1

Hi,

Not sure why you would want to do something like this. May be you want to catch who is running queries that fetch large volumes of data, in that case aren't you better off looking at sys.dm_exec_query_statsfrom time to time and try to optimize the queries. If you want to catch them when they are happening then logical_reads of sys.dm_exec_requests. But can you explain what is your intention of doing this? There may be better alternatives than what you are trying to achieve.

---

Sankar Reddy

Blog: http://SankarReddy.com/
Twitter: http://twitter.com/SankarReddy13/

Answer 2

You are a genius.  Thank you.

Prior to me arriving at my current employer, select * was used and they typically only needed 3 of the 30 columns being returned.  Needless to say this past practice causing problems as the usage goes up.  When I do a string search with 

SELECT ROUTINE_NAME, ROUTINE_DEFINITION 
    FROM INFORMATION_SCHEMA.ROUTINES 
    WHERE ROUTINE_DEFINITION LIKE '%select * %' 

The list is large.  To get our biggest bang for the effort spent optimizing, I need to find the worst offenders.  As a side note, I have found that our top offenders normally have at least one column of XML data type.

---

Duane Lawrence