Long-running Query Exclusions

cehottlecehottle Posts: 38
edited November 29, 2010 6:06AM in SQL Monitor Previous Versions
I'm not that well versed in RegEx and I'm having a hard time contructing an expression to exclude jobs that have T-SQL containing the word Womaster or "Womaster". Has anyone used this yet and, if so, do you have an example of how this works? Thanks.

Comments

  • I presume you've tried something like ^[Ww]omaster ?

    If you have and it's not working for you then I'll do some investigation to see if we have any issues in this area.

    Thanks
    Chris
    Chris Spencer
    Test Engineer
    Red Gate
  • I added ^[Ww]omaster ?. I previously had \bWomaster\b. I'm still receiving alerts. This is the full SQL process fragment. Thanks.

    SELECT "dbo"."Womaster"."WO #" FROM "dbo"."Womaster" ORDER BY "dbo"."Womaster"."Date Entered" DESC
  • I think I was wrong to suggest the ^ to be honest as that only matches characters at the start of the string.

    Have you tried just Womaster without any special characters. \b matches a backspace and I don't think that would work in your case.

    [Ww]omaster should match both "Womaster" and "womaster"

    Also the embedded help next to the regEx box has some useful tips and a link to a fairly comprehensive Microsoft regEx page.

    Hope this helps.
    Chris
    Chris Spencer
    Test Engineer
    Red Gate
  • So far, I have the following in the exclusion list. I continue to receive alerts for the long queries involving Womaster.

    Database Mail
    Womaster|"Womaster"
    \bWomaster\b
    RESTORE VERIFYONLY
    SQLBackup
    DatabaseMail90
    FROM "dbo"."Womaster"
    sp_readrequest
    SELECT "dbo"."Womaster"."
    ^[Ww]omaster ?
  • It looks to me like your Womaster queries should get excluded. I will do some investigation as this could be a bug. I'll post here when I know more.

    Many thanks
    Chris
    Chris Spencer
    Test Engineer
    Red Gate
  • OK. I was able to exclude the Database Mail queries by adding the sp_readrequest entry, so this does work for that.
  • Sorry for the delay getting back on this one. I've been testing this internally and I'm pretty sure that there is a bug when multiple regex expressions are specified on separate lines. This has been raised as a bug internally and we'll look into fixing it for an upcoming release.

    A workaround might be to make a one line regEx that incorporates all the text that you want excluded. I'm thinking something like this:

    Database Mail|Womaster|RESTORE VERIFYONLY|SQLBackup|DatabaseMail90|sp_readrequest

    This worked when I tried it on my test setup although mine only involved two different words.

    Hope this helps
    Chris
    Chris Spencer
    Test Engineer
    Red Gate
  • I following your instructions for putting the exclusions on one line. The area in SQL Monitor where you place the exclusions only will hold so many characters before it wraps to the next line. THis does appear to be working though. Maybe the use of the | between each exclusion item resolves this. Thanks.
  • The RegEx wrapping to the next line shouldn't matter because as it turns out we only support a single expression. So multiple words/phrases will need separating with the pipe character '|'.

    We'll be changing the embedded help to reflect the functionality in the next version. However, it's possible that in a future version we will change it to work the way you expected it to.

    Many Thanks
    Chris
    Chris Spencer
    Test Engineer
    Red Gate
  • SireSire Posts: 7 Bronze 2
    I have a bug report regarding this.

    When updating to the latest (beta) version about a week ago I started seeing long running query warnings that were excluded successfully in the previous version. The settings were unchanged, and the exclude text was still "IndexOptimize|DatabaseBackup" (not multiple lines).
    I tried removing the text and entering it again.

    Please check this and get back to me. Thanks!
  • Sire wrote:
    The settings were unchanged, and the exclude text was still "IndexOptimize|DatabaseBackup" (not multiple lines).

    That should work as it's the style of regular expressions I have been investigating on this thread. However, I've been using the released version (build 3780).

    What build of SQL Monitor are you running by the way? The final beta build was 3529 so I'm presuming it's that one.

    The released version of SQL Monitor can be found at:
    http://www.red-gate.com/products/sql_monitor/index.htm

    Thanks
    Chris
    Chris Spencer
    Test Engineer
    Red Gate
  • SireSire Posts: 7 Bronze 2
    Using relase version 3780 also.
  • I recommend

    http://www.ultrapico.com/Expresso.htm

    for developing regular expressions in C# syntax (which is what SQL Monitor uses)
    Test Engineer, .NET Division
    Red Gate Software
  • SireSire Posts: 7 Bronze 2
    The reg exp is clearly not the problem here. And btw, the best product is http://www.regexbuddy.com/ ;)
  • Sire wrote:
    When updating to the latest (beta) version about a week ago I started seeing long running query warnings that were excluded successfully in the previous version. The settings were unchanged, and the exclude text was still "IndexOptimize|DatabaseBackup" (not multiple lines).
    Are you able to post the SQL statements that you are trying to match? I'm guessing that these refer to stored procedures?

    Thanks
    Chris
    Chris Spencer
    Test Engineer
    Red Gate
  • SireSire Posts: 7 Bronze 2
    Sire wrote:
    Are you able to post the SQL statements that you are trying to match? I'm guessing that these refer to stored procedures?

    I'm using the famous Ola Hallengren scripts (http://ola.hallengren.com/).

    SQL process fragment that fails to match on the regexp "IndexOptimize|DatabaseBackup":
    -- network protocol: LPC
    set quoted_identifier on
    set arithabort off
    set numeric_roundabort off
    set ansi_warnings on
    set ansi_padding on
    set ansi_nulls on
    set concat_null_yields_null on
    set cursor_close_on_commit off
    set implicit_transactions off
    set language us_english
    set dateformat mdy
    set datefirst 7
    set transaction isolation level read committed
    
    SET QUOTED_IDENTIFIER OFF SET TEXTSIZE 4096
    EXECUTE [dbo].[DatabaseBackup] @Databases = 'USER_DATABASES', @Directory = N'C:\SqlData\Backup', @BackupType = 'FULL', @Verify = 'Y', @CleanupTime = 336, @CheckSum = 'Y'
    
  • I'm checking the IndexOptimize and DatabaseBackup scripts now. I'm finding that the IndexOptimize query is excluded but excluding the DatabaseBackup one is a bit more of a pain.

    On running: EXECUTE [dbo].[DatabaseBackup] @Databases = 'RedGateMonitor', @Directory = N'E:\Backups', @BackupType = 'FULL', @Verify = 'Y', @CleanupTime = 336, @CheckSum = 'Y'

    I'm seeing a long-running query alert raised with a SQL process fragment like this:
    BACKUP DATABASE [RedGateMonitor] TO DISK = N'E:\Backups\CSTEST$SQL2005\RedGateMonitor\FULL\CSTEST$SQL2005_RedGateMonitor_FULL_20101125_155854.bak' WITH CHECKSUM

    There's nothing in this fragment that matches the "IndexOptimize|DatabaseBackup" regex but I'm finding that I can filter out this alert by using "IndexOptimize|BACKUP DATABASE|RESTORE VERIFYONLY".

    Which version of SQL Server are you running these queries on out of interest? Also do you execute these scripts from within jobs or from directly from SSMS?

    Thanks
    Chris
    Chris Spencer
    Test Engineer
    Red Gate
  • SireSire Posts: 7 Bronze 2
    There's nothing in this fragment that matches the "IndexOptimize|DatabaseBackup" regex but I'm finding that I can filter out this alert by using "IndexOptimize|BACKUP DATABASE|RESTORE VERIFYONLY".

    Thanks I will try this. I haven't been getting these warnings for several weeks before updating to the release version, so there might be a "breaking change" on your side? One could argue it shouldn't be necessary to know what subqueries are run by the sp.
    Which version of SQL Server are you running these queries on out of interest? Also do you execute these scripts from within jobs or from directly from SSMS?

    SQL Server 2008 Enterprise 64bit.

    Executing from scheduled jobs.

    Thanks for your help!
  • Sire wrote:
    One could argue it shouldn't be necessary to know what subqueries are run by the sp.

    Yes I agree it would be nice to be able to reliably exclude any sp by name. I think we might be limited here by the SQL Server stored procedure we use to get the query fragments.

    I'll raise an enhancement request to ensure that this limitation gets investigated at some point.

    Thanks
    Chris
    Chris Spencer
    Test Engineer
    Red Gate
  • SireSire Posts: 7 Bronze 2
    I don't consider this an enhancement since it worked in the previous version.

    I consider it a pretty serious bug, one which makes the software almost useless for me, since I get alerts all the time, and can't figure out the exact subquery that causes the alerts.

    I hope you consider making this a priority. Thanks!
Sign In or Register to comment.