September 6, 2018 at 1:19 am
We have production and development environment both support. I want to configure SQL Server to notify via email such queries which takes longer than 20 seconds, How can I do this? It should capture DB, query, application name, server, user, IP etc. to identify what happens at what time.
Earlier I used WMI events to capture if anyone who made security changes, it generates email and run job to notify operators. But don't know how to capture queries running longer than 20 seconds ....
Namespace: \\.\root\Microsoft\SqlServer\ServerEvents\MSSQLSERVER
query: select * from AUDIT_ADD_DB_USER_EVENT
To check if a user is added in DB, it notifies through email ... Like this I need
Please help.
September 6, 2018 at 1:49 am
look at monitoring tools such as redgate
***The first step is always the hardest *******
September 6, 2018 at 2:02 am
You can adapt the query on this page to return queries that took more than 20 seconds. Create a job to run regularly (maybe every five minutes) and notify you if there are any results. Just bear in mind that you're querying the plan cache, so you'll only be capturing the most recent execution of the query, and if you have Optimize for Ad hoc Workloads enabled, you won't capture single-use queries at all.
John
September 7, 2018 at 2:57 am
Second vote for Redgate SQL Monitor. That will do everything you're asking for.
However, if you really want to build it yourself, look to Extended Events. You can use those to capture only queries that run longer than the value you define, and then output to ETW and do an email from there. It's going to be a lot of work to set up the email part.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply