We’ve just updated our First Responder Kit to include new versions of these tools:
sp_Blitz® v41 – June 18, 2015:
- Added check 162 for CMEMTHREAD waits on servers with >= 8 logical processors per NUMA node.
- Added check 159 for NUMA nodes reporting dangerously low memory in sys.dm_os_nodes.
- Added check 161 for a high number of cached plans per KB 3026083.
- Fixed a bug in the SkipChecks routines. Reported by Kevin Collins.
- Backup-to-same-drive-as-databases check (93) now includes the number of backups that were done so you can tell if it was a one-off problem, or if all backups are going to the wrong place.
- Bug fixes and improvements.
sp_BlitzCache™ v2.4.6 – June 18, 2015:
- Temporary object cleanup will actually occur – thanks to Bob Klimes for
spotting this - Adding memory grants to expert mode and export to excel mode
- Parent object name is now displayed next to statements that come from a
stored procedure - Run clean up in
##bou_BlitzCacheProcsbefore executing – this should
prevent duplicate records from building up over multiple executions on
the same SPID. - Added a
@sql_handleparameter to filter on queries from a specific
sql_handle or sql_handles - Added support for filtering on database name
How will the sp_BlitzCache™ changes look?
When you have a stored procedure with an expensive statement inside it, you’ll now see the parent procedure’s name in the Query Type column:
Database name filtering works just like you’d expect – use the @database_name parameter to limit the analysis to a single database.
|
1 2 3 |
EXEC sp_BlitzCache @hide_summary = 1, @results = 'expert', @database_name = 'AdventureWorks2014' ; |
And, finally, if you want to look for a single stored procedure or batch of statements, you can grab the SQL handle. A SQL handle identifies a procedure or batch, but they’re only included in the expert mode results. Use @results = 'expert' and scroll very far to the right in the results. You’ll see a bunch of binary gibberish in there – one of the columns will be SQL Handle. Grab that and add a @sql_handle parameter to your execution of sp_BlitzCache™, just like this:
|
1 2 3 |
EXEC sp_BlitzCache @hide_summary = 1, @results = 'expert', @sql_handle = '0x030005006D9E2E1F6CCBE500BBA4000001000000000000000000000000000000000000000000000000000000'; |
What next?
If you’ve already subscribed to email updates about our tools, you got an email this morning with a direct link to the zip file. For the rest of you, hit up the download/EULA page, and enjoy! Hope this makes your database work easier.
24 Comments. Leave new
Hi,
I am getting the next error: ‘invalid object name ‘##bou_BlitzCachProcs’ after running the Stp sp_BlitzCache, details:
Executing sp_BlitzCache v2.4.5
Setting up temporary tables for sp_BlitzCache
Msg 208, Level 16, State 0, Procedure sp_BlitzCache, Line 545
Invalid object name ‘##bou_BlitzCacheProcs’.
How to fix this?
Thanks ahead.
Best Regards,
Fred
Hi Fred! You can start by getting the current version of sp_BlitzCache™ described in the post above. You’re using the last version.
Hello Brent,
Thanks for the quick responce.
You’re right I had installed the latest version in the master database and fired off the pre-latest version from the ‘dba’ database, which i used for this kind of STP’s.
Thanks!!
I like this STP.
Fred
Did the version number in the BlitzCache procedure not get incremented? It still says 2.4.5.
That’s been corrected with a fix for James’s bug below.
Hi Brent,
Firstly, thanks for these scripts – they’re a really great set of tools.
However, I am also getting the issue in sp_BlitzCache that Fred mentioned above, even using the latest version 2.4.6.
In the creation script, it does the drop and create of the two ## tables BEFORE creating the actual procedure. If I run this script in one window, then execute the proc from another window, it works fine. However, as soon as I close the original window (with the create script in it) executing the proc fails, as the two ## tables no longer exist.
Good spot, that’s been fixed and a new version has been tested and uploaded..
Hi Brent
I ran sp_Blitz against a test server residing in a VM. The SQL version is 10.50.1600. This message below is what I received but it also provided Results as well.
Warning: Null value is eliminated by an aggregate or other SET operation.
Msg 102, Level 15, State 1, Line 17
Incorrect syntax near ‘.’.
Msg 102, Level 15, State 1, Line 17
Incorrect syntax near ‘.’.
Warning: Null value is eliminated by an aggregate or other SET operation.
I then ran it against a SQL Server 2012 server and no warnings or messages appeared. Figure there is something wrong with the first server and you might like to see the results.
Thank you for the SP.
David
Do you possibly have a compat level 80 database in the instance where you got the syntax error? I haven’t tested this myself, but I saw an email from Brent earlier today saying that isn’t supported by the current version.
Hi Kendra
Yes that is exactly the case. Thought I would let you know of the issue just in case. You know about it so that is good. SP_blitz did provide information, but also the messages. Working to up shift everything here, but all takes time. Thank you for the response and all the support you guys give to all of us.
David
David – yep, that’s why it’s mentioned at the top of the documentation. 😉
Just a short note no say Thank you to the Brent Ozar Team.
Kind Regards,
Fred
Fred – awww, you’re welcome!
Hello and thanks for this great stored procedure….
I have a question about the following message I found in sp_blitz result:
“The [XYZdb] database file XYZdb_log has a max file size set to 6000MB. If it runs out of space, the database will stop working even though there may be drive space available.”
I found that XYZ_log file on C drive, and it was expanding, and I could not move it because _I think_if I want to move it I have to deattach /attach the database, which is the thing that I could not do. However, for that [XYZdb] I have another log file (XYZdb_log2) on another drive (D:) and with no limit on the max file size …. in this case, is the database [XYZdb] going to stop working if the XYZdb_log file (the one on C:) reached to its limit (6000MB)? in other words, is your message always true regardless of having another size-unlimited log files for the same database?
Abu – why do you have multiple log files for the same database?
I got a SQL Server instance which all its files are on C:\ drive. I moved the databases I could deattach/attach, but one database I was not allowed to take offline (by deattach/attach) and its log file is expanding on C:\ drive where not too much space is left there; so, what I did, I’ve put a max file limit to the log file on C:\ drive (up to 6GB), and created another log file on D:\ drive but with no limit on the file size … did I make the situation worse?! if yes, how can I fix what I’ve mess 🙁 … I don’t know any other ways to solve the space concern, and I don’t know any other we to move databases files other than deattach/attach the database.
Another thing … when I read your reply I felt like having multiple log files for one database is something wrong … is it?!
Please … I’m still waiting for an answer for the above 🙂
Abu – during your next maintenance window, I’d move the database off the C drive and eliminate the extra log file.
Is it possible to run sp_blitz on an Amazon RDS instance? I was not able to add the stored procedure to the master database. I was able to add the stored procedure to a different database. However, when I run the sp I get the following output:
Msg 229, Level 14, State 5, Procedure sp_Blitz, Line 755
The SELECT permission was denied on the object ‘sysjobs’, database ‘msdb’, schema ‘dbo’.
Msg 916, Level 14, State 1, Procedure sp_Blitz, Line 1544
The server principal “sa” is not able to access the database “model” under the current security context.
Adam – yeah, unfortunately it doesn’t work under RDS yet. There’s a lot of things RDS doesn’t support just yet.
Hi Brent,
Getting error Msg 8145, Level 16, State 1, Procedure sp_BlitzCache, Line 0
@results is not a parameter for procedure sp_BlitzCache.
can you please help
Using the latest version and seems like the option is not there
RK – this is an ancient blog post. You’ll wanna read the current documentation for that proc.
Thanks Brent. The link shared in Mail didn’t help.