This discussion has been locked. The information referenced herein may be inaccurate due to age, software updates, or external references.
You can no longer post new replies to this discussion. If you have a similar question you can start a new discussion in this forum.

Custom Alert - SQL Statements with Abnormal Execution Counts

Description

This alert will tell you when an SQL statement has executed more often than it normally does. This example uses a 200% threshold, meaning the SQL has executed twice as many times as normal. The alert definition uses the last 14 days for each SQL statement as the baseline for execution counts per day. It then compares the number of executions today so far with the baseline to derive the percent increase. It also uses summary data so running it more frequently than once an hour will not have benefit.

Alert Definition

To create the alert, click on Alerts > Manage Alerts tab and create a Custom Alert of type Custom SQL Alert - Multiple Numeric Return. Configure the alert with values similar to these:

Alert Name: SQLs with Abnormal Number of Executions

Execution Interval: 1 Hour

Notification Text: The following list of SQL statements had 200% (twice as many) executions today than normal.

SQL Statement:

select today.sqlhash, 100.0 * today.execs / hist.avg_daily_execs pct_increase

from (

   select sqlhash, avg(daily_execs) avg_daily_execs

   from (

      select sqlhash, convert(varchar,datehour,1) day, sum(execs) daily_execs

      from con_stats_sum_#DBID#

      where period = 'H'

      and datehour >= convert(datetime,convert(varchar,current_timestamp-14.0, 1) + ' 00:00:00.000')

      and datehour < convert(datetime,convert(varchar,current_timestamp, 1) + ' 00:00:00.000')

      group by sqlhash, convert(varchar,datehour,1)) daily

   group by sqlhash) hist

inner join

   (select sqlhash, sum(execs) execs

   from con_stats_sum_#DBID#

   where period = 'H'

   and datehour >= convert(datetime,convert(varchar,current_timestamp, 1) + ' 00:00:00.000')

   group by sqlhash) today on today.sqlhash = hist.sqlhash

where hist.avg_daily_execs > 0

and today.execs / hist.avg_daily_execs > 1

and today.execs >= 10

Execute Against: Repository

Units: % Higher Executions

High Threshold: Min 200, Max empty

Medium Threshold: Min 150, Max 200

  • Is there a way to see the actual SQL query along with the report. Currently, it only generates a parameter number and it's very hard to find the actual query syntax to go along with it. DPA doesn't keep a record of those Parameter number.

    View Alert Status link only provides the parameter values and percentage value of the execution.

  • We collect SQL text and store that in our repo, so yes, you can edit this to do a join to the const_#DBID# table and pulling the ST column in the select clause.

    I haven't played around with it, but you'll want to join con_stats_sum_#DBID#.sqlhash to const_#DBID#.h

  • Hi,

    I tried using custom alert but the return types are numerical, boolean or alert status. There is no option to return a text (in this case the query statement).

    So is there any other way to get this besides manually running the query.

    Prashant

  • Did you try using the multi-numeric custom alert template? I believe it allows you to return one non-numeric + one numeric value.

    Instead of returning the sqlhash, you could join to const table to get the text. You will have to use the substr function or equivalent due to size limitations for alert messages. To get full text, you would be looking at manually running a query.

  • Yes... same error.

    "Alert returned the value [ -- ============================================= -- Author: -- Create date: <08-03-2013> -- Description: -- ============================================= CREATE FUNCTION [dbo. =====which is not of the expected type [Number]"

    Am getting H, ST, PNAME from const_#DBID# in the query.

  • You can only select two values (the first can be alpha-numeric, but the second has to be numeric.

    If you post your query, I can take a look at it.

  • Thanks for the tip. Its working now after I removed the PName column.

    Though it would be nice to have this as well as maybe include the Login name.

    Prashant

  • Looks like I spoke too early. The alert is still broken. When I do a Test Alert it works fine & shows the statement & hash. But the actual mail is not coming. Not sure where the it is broken. Error shown is same "expected number".

    Prashant

  • I think your second value returned is not numeric. You will have to pull back a numeric for the 2nd field. The hash should work for the 2nd field as it is a numeric - try reversing the order of return in the select...