none
sp_add_alert @performance_condition where reads on a query exceeds one million

    질문

  • 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

    2012년 3월 9일 금요일 오후 7:15

답변

  • 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/

    • 답변으로 제안됨 Peja Tao 2012년 3월 12일 월요일 오전 9:01
    • 답변으로 표시됨 Peja Tao 2012년 3월 20일 화요일 오전 1:26
    2012년 3월 10일 토요일 오전 12:49
    중재자

모든 응답

  • 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/

    • 답변으로 제안됨 Peja Tao 2012년 3월 12일 월요일 오전 9:01
    • 답변으로 표시됨 Peja Tao 2012년 3월 20일 화요일 오전 1:26
    2012년 3월 10일 토요일 오전 12:49
    중재자
  • 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


    • 편집됨 duanelawrence 2012년 3월 13일 화요일 오후 1:49
    2012년 3월 13일 화요일 오후 1:37