Chocolate Attack
I get it. You’re a good DBA.
No, you’re a great DBA.
You have alerts for when jobs fail, when they run long, and they’re all emailed to the ENTIRE COMPANY in beautifully formatted CSS.
Have a cigar.
But we all go on vacation sometime.
Overtime
One thing that can make a workload feel artificially sluggish is maintenance.
- Index stuff: Lotsa work, sometimes blocking
- Backup stuff: Native fulls of VLDBs, with compression
- DBCC CHECKDB: Oh, you forgot to run this because you were busy rebuilding indexes
Darn. Gimme that cigar back.
Right now, I’ve got a DBCC CHECK, and a query workload, that both run for one minute and 20 seconds when they run alone.
Overlap
The problem becomes when I run them both at the same time. The query workload runs for about 25 seconds longer, and CHECKDB runs for two and a half minutes.
That’s like, almost twice as long.
Now, it’s okay if you don’t have all those alerts set up. I guess.
Because you can use sp_BlitzFirst to see what’s going on, and get warned:
EXEC sp_BlitzFirst @Seconds = 10, @ExpertMode = 1;
If you can end maintenance tasks and have performance go back to normal-bad, congratulations! You’ve solved today’s problem.
Have a cigar.
Thanks for reading!
Going Further
If this is the kind of SQL Server stuff you love learning about, you’ll love my training. I’m offering a 75% discount to my blog readers if you click from here. I’m also available for consulting if you just don’t have time for that and need to solve performance problems quickly.
Erik, would you say that such running maintenance could also be seen as running (at a moment in time) via the “active expensive queries” tab of Activity Monitor in SSMS? Some people might have more ready access to that (well, a “good DBA” should, right?), though whether it appears there depends also on being both a “modern” version of SSMS and of SQL Server (2016 and above, I believe).
Just stressing that it is indeed often so important to see “what else is running” at the time of a seeming hangup, and sometimes just visiting that simple “active expensive queries” (which really shows ANY running queries or commands) can be of great value.
Next would be something that tracks literally all slow queries, so that even AFTER the fact one can see what else WAS running at the time of some slow query in the PAST. Some tools purport to offer that, but then you dig and find that they only show which were slow “on average”, which doesn’t help when as in this case some queries may be “usually very fast” and only sometimes “slow”. And other tools that might really track all slow queries may prove to be done in such a way as to add unintended impact.
Something of a holy grail, but again at least seeing “what’s running now” (which was your main point) is indeed a valuable insight for many, whatever way they may do it.
Charlie — I don’t know, I don’t use Activity Monitor. I get that it might he all some people have, but it’s just not where I’m focusing my efforts.
And
sp_BlitzFirstwill show other running queries when you run it like I show in the post, because the first thing it will run issp_BlitzWho.For tracking all the other stuff, you’re better off with a monitoring tool, or using Query Store.
Thanks!