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).
Blaž Dakskobler
shared this idea
This was added in version 10.1.2 – you’ll see the option in the Long Running Query alert configuration screen if you update to that version or later. Thanks for the suggestion (and also to everyone who added comments)!
-
This was added in version 10.1.2 - you'll see the option in the Long Running Query alert configuration screen if you update to that version or later. Thanks for the suggestion (and also to everyone who added comments)!
-
Anonymous
commented
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
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".
-
Hemalatha Krishnamurthy
commented
Hi do we have an update on this SRP-6956. I see that we still do not have the option to exclude host name in SQL Monitor 7
-
Blaž Dakskobler
commented
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.
-
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
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.
-
Thanks for the suggestion - we've logged this as SRP-6956
-
Nicsen
commented
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.
