In SQL Monitor, I want to...

Long-running query alert - add an option to exclude queries based on the USER running it

This alert already has the options to exclude queries (via RegEx) based on the process name or SQL commands/objects used. I miss a similar option for excluding queries based on the user running the query.

An example: let's say you have a job that runs SSIS job steps using a proxy->credential->dedicated domain user. The SSIS packages run loads of SQL queries, some taking a long time - but that might be expected and OK (let's say the job is a nightly ETL process for a DW). Without excluding queries based on that user (simple but effective!) you could get a whole lot of long-running query alerts, which can't really be intercepted by the existing two filtering options (at least not good/easy enough).

61 votes
Vote
Sign in
(thinking…)
Sign in with: Facebook Google
Signed in as (Sign out)
You have left! (?) (thinking…)
Blaž Dakskobler shared this idea  ·   ·  Flag idea as inappropriate…  ·  Admin →
Daniel Rothig responded  · 

We’re a bit sceptical about this one – with two RegEx fields, this alert already causes a fair amount of time overhead and cognitive load to get configured.

I’ll leave it open and see how it develops

8 comments

Sign in
(thinking…)
Sign in with: Facebook Google
Signed in as (Sign out)
Submitting...
  • Anonymous commented  ·   ·  Flag as inappropriate

    I'm with Luke on this one. The process name is often vague or can't be easily set, especially in Azure automation. Being able to filter on the user account I'm using would mean I could easily exclude the account that is running certain maintenance jobs, regardless of whether they fall in the expected window or not. We're capturing that user already, but not being able to filter on it makes it something we're just going to be likely to snooze. It's not much better than NewRelic at that point.

  • Luke L commented  ·   ·  Flag as inappropriate

    Another use case I have for this is trying to filter all of the long running queries from my nightly db maintnenace. We use scripts that shell out to SQLCMD from Agent Jobs. Trying to add all fot he approrpaite exceptions for index maintenance and checkDB's can be problematic. However, If I set my SQLCMD -H Workstation option and can filter on that I can easily filter these events since I know they will run for much longer than my normal long running alert timeframe.

    The User, Workstation and database are already captured, but I can't filter on them. I get too many alerts and either turn this alert off or set my threshold much longer than I really should in order to "Quiet the noise".

  • Blaž Dakskobler commented  ·   ·  Flag as inappropriate

    Process name is sometimes to vague. For example, we have quite some jobs with SSIS steps. Process name is always "Microsoft SQL Server". We use various dedicated domain users for the job steps (via proxies) - because of principle of least privilege - so we could actually do some alert "filtering" based on that.

    I imagine there are other cases where dedicated users are used for activities which you know are bound to result in long running queries, and process name / query doesn't help to pin them down.

  • AdminDaniel Rothig (Product Manager for SQL Monitor, Red Gate Software) commented  ·   ·  Flag as inappropriate

    Hi Blaž,

    OK that's reasonable - I am worried about increasing the complexity of the the already quite advanced Long-running query alert, but I'll re-open the suggestion to see what others have to say.

    To help us understand the issue better, it would be useful if you could provide us with additional examples when you have a user that can't also be identified via the program name - this way we can ensure we'll build the right thing.

    Thanks
    Daniel

  • Blaž Dakskobler commented  ·   ·  Flag as inappropriate

    Daniel, if maintenance window could be set per individual alert (including custom ones), it would help (i.e. I don't care about long running queries during ETL, but I want to know about other alerts...). But, in case I only wanted to suppress messages from certain users, that still wouldn't be enough.

    Why decline the whole suggestion on the basis of an alternative partial solution for my example? Adding user as an additional RegEx option for excluding queries would be beneficial by itself. Leave it open and let people vote if they find it useful - some clearly do.

  • Nicsen commented  ·   ·  Flag as inappropriate

    It would be really useful to put an alert exclusion for Long running queries by Host and/or username.
    At the moment only process name is available.

Feedback and Knowledge Base