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.