November 9, 2017 at 8:23 am
Hi
Is there a way with Extended Events, where I can set up something; should a user/developer run a query via SSMS and takes longer than 30 seconds that it should alert me.
Is this possible with Extended Events?
regards
November 9, 2017 at 12:39 pm
It can be done, but I haven't done it. You have to set up Service Broker. Then you can have the event target be the Broker service and use that to fire off an alert if one has been reached. I don't know all the details that would be involved, but that's how you can get it done.
"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
November 9, 2017 at 12:59 pm
A quick Google search found several good links...
This ones a little old but considering the author... It would still be my 1st full read... Tracking expensive queries with extended events in SQL 2008
And then... Finding One Problem Query With Extended Events
I thought there would be something on https://www.scarydba.com/ ... But didn't see anything... :Whistling:
November 9, 2017 at 1:24 pm
Jason A. Long - Thursday, November 9, 2017 12:59 PMA quick Google search found several good links...
This ones a little old but considering the author... It would still be my 1st full read... Tracking expensive queries with extended events in SQL 2008
And then... Finding One Problem Query With Extended Events
I thought there would be something on https://www.scarydba.com/ ... But didn't see anything... :Whistling:
<sigh> I'll add it to the list of topics to blog about.
"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
November 9, 2017 at 2:31 pm
Grant Fritchey - Thursday, November 9, 2017 1:24 PMJason A. Long - Thursday, November 9, 2017 12:59 PMA quick Google search found several good links...
This ones a little old but considering the author... It would still be my 1st full read... Tracking expensive queries with extended events in SQL 2008
And then... Finding One Problem Query With Extended Events
I thought there would be something on https://www.scarydba.com/ ... But didn't see anything... :Whistling:<sigh> I'll add it to the list of topics to blog about.
Actually, I was looking into building extended events into a query test harness... It's on my "things to do in the VERY near future", list.
If you'd like, I'd be happy to move it your way. If nothing else, I'd value your opinion about which events to include and which to exclude. The list of events has become fairly exhaustive at this point and your insights would be well received.
November 9, 2017 at 2:39 pm
Anchelin - Thursday, November 9, 2017 8:23 AMHiIs there a way with Extended Events, where I can set up something; should a user/developer run a query via SSMS and takes longer than 30 seconds that it should alert me.
Is this possible with Extended Events?
regards
It's not going to be your worst query and I'll recommend that until you find and fix your worst queries, such occasional ad hoc queries shouldn't matter to you. I'll also tell you that your worst queries usually DON'T take longer than 500ms but run thousands of times per hour. Fix those and everyone will be happier and you'll have the head room for someone to run a >30 second ad hoc query with no problems.
--Jeff Moden
Change is inevitable... Change for the better is not.
November 12, 2017 at 1:19 pm
A query may take long to complete just because a log file being expanded.
Or because a bulk load executed at the time applied a tablock.
Long completion time does not always indicate a problem with the query.
You're gonna have too many false positives.
Not to mention - some queries are meant to take longer, and it's not a reason for a concern.
You need to look into the cause of each case.
_____________
Code for TallyGenerator
November 12, 2017 at 7:54 pm
Sergiy - Sunday, November 12, 2017 1:19 PMA query may take long to complete just because a log file being expanded.Or because a bulk load executed at the time applied a tablock.Long completion time does not always indicate a problem with the query.You're gonna have too many false positives.Not to mention - some queries are meant to take longer, and it's not a reason for a concern.You need to look into the cause of each case.
+1 million to that.
I'll also add that the longest running queries aren't usually the worst performance problem. It's all the little code under 100ms that no one thinks can or should be optimized that run 10's of thousands of times per hour. Then there's those nasty of nastys that run very quickly but have to compile every single time they're used... and they take 2 to 22 SECONDS to compile and they run thousands of times per hour. We fixed two of those recently and average CPU usage dropped from 22% to 8-10%. Logical reads followed suit.
--Jeff Moden
Change is inevitable... Change for the better is not.
November 13, 2017 at 8:47 am
In addition to the comments above, in that you may have various amounts of 'noise', I have done exactly this - focusing on queries over 10 seconds..
I then ingest the data from the Extended Events into an ELK stack, which allows me to pull up long running queries by server, database, user (useful for the automated stuff), client name, etc and also brings in Duration, Logical and Physical reads plus other stuff.
November 13, 2017 at 11:52 pm
One of the longest queries I've ever caught started with...
Any guess?
WAITFOR DELAY '00:30:00'
:hehe:
_____________
Code for TallyGenerator
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply