Long-running Query Exclusions

cehottle

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.

Chris Spencer

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

cehottle

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

Chris Spencer

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

cehottle

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 ?

Chris Spencer

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

cehottle

OK. I was able to exclude the Database Mail queries by adding the sp_readrequest entry, so this does work for that.

Chris Spencer

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

cehottle

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.

Chris Spencer

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

Sire

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!

Chris Spencer

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

Sire

Using relase version 3780 also.

jrg

I recommend

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

for developing regular expressions in C# syntax (which is what SQL Monitor uses)

Sire

The reg exp is clearly not the problem here. And btw, the best product is http://www.regexbuddy.com/

Chris Spencer

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

Sire

Chris Spencer wrote:

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'

Chris Spencer

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

Sire

Chris Spencer wrote:

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.

Chris Spencer wrote:

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!

Chris Spencer

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

Sire

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!