New Updates for sp_Blitz®, sp_BlitzCache™

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_BlitzCacheProcs before executing – this should
    prevent duplicate records from building up over multiple executions on
    the same SPID.
  • Added a @sql_handle parameter 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:

Presenting-2015-06-19T07-46-27

Database name filtering works just like you’d expect – use the @database_name parameter to limit the analysis to a single database.

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:

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.

,
Previous Post
The Nine Circles of Developer Hell
Next Post
How Do You Manage DBAs? Part 1: Measuring Backups

24 Comments. Leave new

  • Fred Benningshof
    June 22, 2015 6:38 am

    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

    Reply
    • Hi Fred! You can start by getting the current version of sp_BlitzCache™ described in the post above. You’re using the last version.

      Reply
      • Fred Benningshof
        June 22, 2015 7:21 am

        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

        Reply
  • Michael Williamson
    June 22, 2015 9:15 am

    Did the version number in the BlitzCache procedure not get incremented? It still says 2.4.5.

    Reply
  • 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.

    Reply
  • 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

    Reply
    • Kendra Little
      June 22, 2015 1:12 pm

      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.

      Reply
  • 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

    Reply
  • Fred Benningshof
    June 24, 2015 2:02 am

    Just a short note no say Thank you to the Brent Ozar Team.

    Kind Regards,
    Fred

    Reply
  • 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?

    Reply
    • Abu – why do you have multiple log files for the same database?

      Reply
      • 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?!

        Reply
  • Please … I’m still waiting for an answer for the above 🙂

    Reply
  • 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.

    Reply
  • 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

    Reply

Leave a Reply

Your email address will not be published. Required fields are marked *

Fill out this field
Fill out this field
Please enter a valid email address.

Menu
{"cart_token":"","hash":"","cart_data":""}