First Responder Kit Update: sp_BlitzCache Catches Cursors, Scalar Functions, and More

Every time we take on a client for our SQL Critical Care®, the very first thing we do is run the First Responder Kit scripts: sp_Blitz to get a health check, sp_BlitzFirst to check their wait stats, sp_BlitzIndex to look at their indexes, and sp_BlitzCache to see the most resource-intensive queries. Very often, these scripts surface big problems quickly, and we’re able to get right to the root of the performance and reliability pains.

Sometimes, though, like last week, we run into an issue we’ve never seen before. In that particular case, they were having performance problems with an ISV application that used JDBC for connectivity. JDBC uses cursors to run queries – they don’t look like cursors when you glance at the SQL Server statements, but they’re executed using sp_cursoropen. I know – as a database professional, you probably have a big problem with cursors, as do I, but let’s set that aside because JDBC is what it is.

Cursors are bad, but even worse, you can choose your concurrency options and scroll options. Want to lock rows as you move through ’em? You got it. Want to be able to jump backwards and forwards through the result sets? Well, it’ll consume more resources, but you’re welcome to do it.

In this particular situation, we discovered that many of the app’s queries were run with the wrong options. The ISV asked us to track down the most resource-intensive queries that weren’t using read-only, fast-forward queries.

And that’s why Erik added a new warning into sp_BlitzCache that shows not just cursors, but the concurrency and scrolling options for the cursors. Oh, and table variables. And scalar functions. And much more, as you can read below.

That’s the case with many of the features we add into the First Responder Kit. I kinda look at it as sponsored development of open source projects: clients pay us to relieve their SQL Server pains, and we put that work into tools that everybody can use to reduce their SQL Server pains, completely for free.

sp_BlitzCache Improvements, All @BlitzErik‘s Handiwork

  • #543 – look up execution plans for a stored procedure by name, the new @StoredProcedureName parameter
  • #540 – add warnings for recently compiled plans (last 4 hours)
  • #497 – add warning for CLR functions
  • #482 – add check for compute scalar operators that call functions
  • #422 – add check for table variable use
  • #556 – add and improve cursor checks
  • #532 – fix double warnings on expensive remote queries

sp_BlitzIndex Improvements, All By The Incredible Non-Edible @BlitzErik

  • #529 – add divide-by-zero handling to stats queries
  • #530 – add more clear wording to stats warnings
  • #479 – add a check for filtered stats
  • #531 – don’t get filtered stats on SQL 2005
  • #528 – add schema information to collection and results
  • #527 – faster stats collection with >50k objects
  • #490 – sp_BlitzIndex should be marked to recompile

And also, I fixed a couple of sp_Blitz bugs, so, yeah.

Go download the First Responder Kit now.

 

Previous Post
[Video] Office Hours 2016/10/19 (With Transcriptions)
Next Post
Announcing our 2017 SQL Server Training Scholarship Winners

7 Comments. Leave new

  • Thank you so much for the information and tools!

    Reply
  • Brilliant idea regarding JDBC and sp_cursoropen! In the last 5 years I’ve troubleshot several Java applications with JDBC issues. The worst was an EDI process that was hanging while extracting a zip file, and leaving the SQL transaction uncommitted. JDBC leaving many open/idle connections, and the use of sp_cursoropen made it a real nasty beotch to track down.

    Reply
  • The scholarships are a great way to give back to the world. Thanks for caring.

    Reply
  • The download link goes to page where I need to reorder all my subscriptions.
    Where can I download it directly?

    Reply
    • If you’ve already subscribed, then you got an email with a direct download link last week. If you haven’t gone through that form yet, you’ll need to go through it. Thanks!

      Reply
      • I didn’t get a link either, unless this is the same update from 10/14.
        Like the others, I would like to thank you again for all the good things you put out into the world. It’s amazing to me to watch your team compete at philanthropy.

        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":""}