Queries are running when you’re not around. They’re wreaking havoc – maybe they’re filling up your TempDB, or causing blocking, or flushing your buffer pool out.
When I wanna see what queries are running, I run sp_BlitzWho:
That shows me who’s running the query, what it’s been waiting on over time, memory grants, blocking, and even the live query plan (if I’m on a supported build.) But of course, that’s only right now.
So to log queries when you’re not looking…
Set up an Agent job to run this every, say, 5-15 minutes:
|
1 2 3 4 |
sp_BlitzWho @OutputDatabaseName = 'DBAtools', @OutputSchemaName = 'dbo', @OutputTableName = 'BlitzWho_Results'; |
That logs currently running queries to a table. Then, when someone asks you what was going on yesterday at 8PM, back when you were drowning your database memories in cheap beer, you can query that table’s output over time:
By default, only 3 days of history are kept in the table. You can control that with the @OutputTableRetentionDays parameter.
I don’t recommend leaving this Agent job running 24/7 on every server you have – if you need activity tracking, you’re better off with a monitoring tool or the Power BI Dashboard for DBAs. Instead, I like setting up the Agent job to run every minute, but disable the job. Then, when a problem is happening, I’ll train my team to enable that Agent job even if I’m not around. That way, I can go back later to see what was happening on the server at the time.
Catching blocking, TempDB usage, or long-running queries
To make these scenarios easier to troubleshoot, sp_BlitzWho has a few filtering parameters:
- @MinElapsedSeconds
- @MinCPUTime
- @MinLogicalReads
- @MinPhysicalReads
- @MinWrites
- @MinTempdbMB
- @MinRequestedMemoryKB
- @MinBlockingSeconds
So that way, if you’re specifically trying to narrow down a query that’s blowing up your TempDB overnight, you can filter it down just to queries using TempDB.
Download it in the latest version of our First Responder Kit. It works in all supported SQL Server versions, from 2008 to Azure SQL DB. Enjoy!
4 Comments. Leave new
“Then, when a problem is happening, I’ll train my team to enable that Agent job even if I’m not around.”
If your team are not very SQL savvy you could setup a shared Microsoft Flow Button to start a collection on-prem. https://flow.microsoft.com/en-us/blog/button-sharing/
I’m gonna hazard a guess that most shops are a lot savvier with SQL than they are with Flow, though….
BlitzWho_Results gets auto created ? or do you create the table itself first ?
Joris – go ahead and either give it a shot, or take a look at the proc code. It’s open source – you can totally give ‘er a read to find out.