Blog

Finding the worst queries in SQL Server is easy, right? You just query one of the dm_exec_* DMVs, bring in the SQL text and the execution plan, and then you spend hours looking at execution plans trying to figure out what’s wrong in the query, much less how to fix it.

All that’s changing – we’re introducing sp_BlitzCache™.

Life Should Be Easy

We try to make things easy for you. sp_Blitz® and sp_BlitzIndex® make life easy – they find help you find configuration and indexing problems. sp_AskBrent® helps you find problems that are hitting your SQL Server right now. We decided to take it one step further and apply our expertise to analyzing the plan cache.

There are a lot of queries that will help you get data out of the plan cache. A lot of those queries are also fairly complicated. They present a lot of data, but they don’t help you interpret the results.

Work Just Got Easier

sp_BlitzCache™ analyzes the top poorly performing queries for many different problems. After finding your bad habits, sp_BlitzCache™ builds a list for easy analysis. Your bad habits are outlined in a summary result set:

A summary of your code sins.

A summary of your code sins.

In addition, each of the queries in the top 50 worst performers will have their warnings called out in a separate warnings column:

Your detailed confession.

Your detailed confession.

Warning!

This will not work on SQL Server 2005. If you want to analyze your plan cache on SQL Server 2005, you can use our legacy plan cache script.

Options

There are a number of different options available with sp_BlitzCache™. If you want a refresher, just run EXEC sp_BlitzCache @get_help = 1. Otherwise, here’s a quick overview:

@get_help – Displays a summary of parameters and columns.

@top – Determines the number of records to retrieve and analyze from sys.dm_exec_query_statssys.dm_exec_procedure_stats, and sys.dm_exec_trigger_stats.

@sort_order – CPU, reads, writes, etc. This controls collection and display.

@use_triggers_anyway – Don’t set this unless you’re crazy. SQL Server 2008R2 and earlier has a bug where trigger count can’t be trusted. It seemed safer to ignore triggers on those versions of SQL Server than give you bad information.

@results – There are three ways to display the detailed results grid. * Narrow will only display averages for CPU, reads, writes, and duration. * Simple, the default, displays totals and averages. * Expert displays all of the information collected by sp_BlitzCache in addition to metrics normally displayed.

If you want to save the results to a table, you can use @output_database_name@output_schema_name@output_table_name to persist the results to a table. If the table doesn’t exist, it’ll be created for you.

Download it now!

Download sp_BlitzCache now!

↑ Back to top
  1. Very nice, clean and easy to understand. Another sp_blitzsomething to add to the admin database!

    I tried it on a VM with 2 cores and 8GB memory for the instance (SQL 2008).
    Total Cache plan size: 2441.42 MB

    It took sp_Blitzcache 2.29 min (default settings) to finish and it drove the CPU cores from an average around 8-10% up to 50-60%.during this time.
    So it is maybe not something to run in production during office hours ;)

    Thanks guys for providing another great tool that makes DBA’s around the world look good.

    • Tried it on a Physical, 16 core machine. 3,5GB plan cache.
      One of the cores peaked to 100%, but the average % cpu (all cores) hardly went up.
      No problem..

      But if you run it on a weak 2-core VM with a couple of GB’s of plan cache it looks like it will drive
      CPU up around 50% during the minute or two of running sp_Blitzcache. Tried a couple with the same result.

      • Correct. XML processing in SQL Server is single threaded.

        The larger the execution plans in the cache, the more time this will take to process them. Like all things, there’s a trade off.

        All decisions comes with a trade-off. sp_BlitzCache™ has some CPU load due to the analysis. If you run this on a 2-core VM, that implies you both do and don’t care about performance.

        Addressing your first comment, I suspect there’s an underlying problem with that 2-core VM either due to CPU constraints on the host or within the VM itself – on a single core VM, this only takes 19-25 seconds to run. On a server with a considerably larger plan cache, this still runs in ~30 seconds.

        • Yes, strange that it runs for so long. Gotta look at that.
          Other similar VM’s do perform as you say.

          Stopped caring about vm cores since CPU ready time and sp_blitzindex ;)

          Anyway, sp_BlitzCache is superb. Helped me find a parameter sniffing issue today.

  2. Does it work in SQL Azure? It seems Azure has disabled so many sp_ functions that very few scripts I find online will actually run.

    • Jerry – I admire your ambition! Most folks would have just copy/pasted in the script to their environment and tried it, but you instead took the time to write out an entire comment and wait for someone else to reply. That’s incredible! My hat is off to you, sir.

      • Sigh. Okay I spent the 10 minutes to do it, and just so others know, the answer is that sp_BlitzCache is NOT supported on SQL Azure.

        • 10 minutes? It took you 10 minutes to copy/paste a stored procedure into SSMS and run it? Wow, SQL Azure has gotten even slower than I thought! That’s horrible to hear, man. No wonder you want to performance tune it. Definitely sorry to hear that.

          • Don’t have to be an asshole, when someone asked a valid question, which Jeremiah did not bother to answer in his post.
            I like your website and read your newsletter, but I don’t like assholes. You’re an asshole.

          • Let the record show that I approved this comment rather than deleting it. :-)

          • Wow. Even at this erudite site can we see a thread devolve into a monkey-poo-flinging exercise.

            Lighten up, Ville. Some folks’ humor detectors need calibration.

          • None of us are as terrible as all of us ;)

          • And this is why I love coming to the site…. :)

    • The only things stopping this from running are the lack of sys.configurations, which is used to gather the cost threshold for parallelism. Since Azure is single-threaded (LOL), it’s not needed for Azure testing – you can’t have parallel plans because your database is a Motorola RAZR.

      The blocker is that Azure does not support sys.dm_exec_plan_attributes which is used to find out all kinds of awful things about your execution plans. There are two places where it’s easy to remove, but you lose checks for forced parameterization and cursors. The other place sys.dm_exec_plan_attributes used is to get the database name in the display of plans. Since you only have one database in Azure, this isn’t absolutely necessary, but the code changes would take time.

      I’ve set up a feature request to track this, but I don’t know when or if it’ll happen – it depends on the number of people who want it to happen and it adds to the long term maintenance.

  3. Very cool Jeremiah. Only took me 28 seconds against a 5188.507812 MB plan cache, and with no impact. I’m totally adding this as one of my administrative checks.

    Can we assume that a queries displayed with a blank entry for the warning column are not bad?

    Another great utility from the Ozar Unlimited team. Thanks

  4. Nice tool, Jeremiah. It not only caught my awful plans, but a couple from the tempdb and msdb internals too.

    • Funnily enough, in v2.2 I’ve added a flag to ignore system databases that will be enabled by default. Most people use master as their DBA tools database (probably because people like me default to USE master;). It will exclude queries originating in master, model, msdb, tempdb, and resource – not because we don’t want to know, but because we can’t do anything.

      You should see how awful the system procedures look when you’ve got an instance of SQL Server that you only use for development and writing demos!

  5. As far as I can see, there is a bug in implicit conversions detection. The code is:

    WITH XMLNAMESPACES(‘http://schemas.microsoft.com/sqlserver/2004/07/showplan’ AS p)
    UPDATE #procs
    SET …….
    implicit_conversions = CASE WHEN QueryPlan.exist(‘
    //p:RelOp//ScalarOperator/@ScalarString
    [contains(., "CONVERT_IMPLICIT")]‘) = 1 THEN 1
    WHEN QueryPlan.exist(‘
    //p:PlanAffectingConvert/@Expression
    [contains(., "CONVERT_IMPLICIT")]‘) = 1 THEN 1
    END
    ….

    “p:” is missing before “ScalaOperator/”- should be:

    WITH XMLNAMESPACES(‘http://schemas.microsoft.com/sqlserver/2004/07/showplan’ AS p)
    UPDATE #procs
    SET …….
    implicit_conversions = CASE WHEN QueryPlan.exist(‘
    //p:RelOp//p:ScalarOperator/@ScalarString
    [contains(., "CONVERT_IMPLICIT")]‘) = 1 THEN 1
    WHEN QueryPlan.exist(‘
    //p:PlanAffectingConvert/@Expression
    [contains(., "CONVERT_IMPLICIT")]‘) = 1 THEN 1
    END

    • You are correct! I’ve fixed this in the development version which is nearly ready to ship, so hopefully this should get out to the public in the next week or two.

      Thanks for that fix!

  6. This is probably the single most useful script that I’ve come across – very insightful! Great work guys.

    Thanks

  7. I ran sp_BlitzCache with no params this morning on a production 128-GB-RAM server, hosting a single SQL instance, taking up most of that server memory. I have about 5 databases with a total data size of 800 GB.

    Took about 13 minutes to complete, running on one CPU, as Jeremiah mentioned in the webcast.
    No blocking, no interference with any business processes.

    Gave me a wealth of info, including recommendations for a stored procedure I was asked to tune yesterday, which was suffering from a few of the “sins” in the 1st result set. :-)

    Thank you for this very valuable tool,
    Marios Philippopoulos

Leave a Reply

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

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>

css.php