09 Maret 2012 19:15
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'
10 Maret 2012 0:49Moderator
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.
13 Maret 2012 13:37
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 withSELECT ROUTINE_NAME, ROUTINE_DEFINITION
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.
- Diedit oleh duanelawrence 13 Maret 2012 13:49