2012년 3월 9일 금요일 오후 7: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'
2012년 3월 10일 토요일 오전 12:49중재자
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.
2012년 3월 13일 화요일 오후 1: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.
- 편집됨 duanelawrence 2012년 3월 13일 화요일 오후 1:49