Wednesday, June 18, 2014

Reporting on sp_BlitzCache Results

Regularly examining the plan cache in your database instances is a must, as there is a wealth of useful information that helps you determine which statements and/or stored procedures are your biggest performance problems.  Doing such analysis has become much easier with the release of sp_BlitzCache by Brent Ozar Unlimited.  One of the great features of sp_BlitzCache is the fact that you can log the results to a table, allowing for more effective analysis of the results.  I've decided to take it one step further and put together an SSRS report to help dive into the data.  Get the Report RDL.

To harvest the data, I created a scheduled job in SQL Server which is set to run nightly at 7pm.  I keep all of my utility scripts and data in a database called DBADB.  Each instance has its own such database.

EXEC DBADB.dbo.sp_BlitzCache
@results = 'expert',
@output_database_name = 'DBADB',
 @output_schema_name = 'dbo',
@output_table_name = 'BlitzCache'

The report I wrote is based off expert mode results.  There is a plethora of data in the report so it does get fairly wide.  I don't display the values in QueryText or QueryPlan on the report, but in Figure 3 you'll note I provided the syntax to obtain the query plan and/or SQL text in the header.  Copy in the plan_handle or sql_handle, respectively.  Another option is to directly query your BlitzCache table for it.

There are four parameters on the report: Database Name, Sample Time, Query Type and Warnings.  Most column headers allow you to sort so you can zero in on a particular performance metric of concern.  

Figure 1 - Parameters

Figure 2 - Report Data - these are the first set of columns in the report: