If you’re saving the contents of sp_BlitzFirst, sp_BlitzCache, and sp_BlitzWho to tables every 15 minutes, you should definitely install this month’s updates. There are several key improvements for you in here to help make it easier to troubleshoot plan cache rollover due to unparameterized queries, plus way easier to gather query plans that are having parameter sniffing problems.
If you’re not saving this stuff to table, it’s as easy as creating a diagnostic database, and then setting up an Agent job to run this every 15 minutes:
@OutputDatabaseName = 'DBAtools',
@OutputSchemaName = 'dbo',
@OutputTableName = 'BlitzFirst',
@OutputTableNameFileStats = 'BlitzFirst_FileStats',
@OutputTableNamePerfmonStats = 'BlitzFirst_PerfmonStats',
@OutputTableNameWaitStats = 'BlitzFirst_WaitStats',
@OutputTableNameBlitzCache = 'BlitzCache',
@OutputTableNameBlitzWho = 'BlitzWho';
It automatically creates the tables and prunes them to keep just 7 days worth of history by default, and you get tons of useful diagnostic data about why your server was slow last night, and what queries were having parameter sniffing issues around that time.
- Download the updated FirstResponderKit.zip
- Azure Data Studio users with the First Responder Kit extension:
ctrl/command+shift+p, First Responder Kit: Import.
- Download the updated Consultant Toolkit in your account
- EU customers: check your email for the updated version from Gumroad, our European distributor
Consultant Toolkit Changes
Updated the First Responder Kit with this month’s script updates, plus:
- Improvement: added new check on invalid logins, Windows logins that are disabled in AD but still present in SQL Server. (#2387, thanks Jefferson Elias aka JeffChulg.)
- Fix: removed unprintable character from sp_Blitz that presented problems when processing the text file with PowerShell. (#2373, thanks Jefferson Elias and Johan Parlevliet.)
- Improvement: warns about lots of single-use plans in the cache. (#2359, #2390, #2398, thanks Erik Darling.)
- Fix: May’s release broke on SQL Server 2008 & R2 due to a change in the way that 2012 & newer – you know, the versions you’re supposed to be running since they’re supported – show pages in sys.dm_os_memory_clerks. (#2372, thanks West Seattle Coug.)
- Fix: wasn’t showing queries whose duration exceeded the @MinutesBack setting. For example, if you ran it for @MinutesBack = 15, and a query had been running for hours and just happened to finish in the last 15 minutes, it wouldn’t show up. We were using the last_execution_time (date/time) from sys.dm_exec_query_stats, but that records when the query starts, not when it finishes. This also adds a new LastCompletionTime to the sp_BlitzCache output tables – that column will be automatically added (but not backfilled) the next time you run sp_BlitzCache to save to table. (#2377, thanks rrankins and Erik Darling.)
- Fix: when logging sp_BlitzCache to table, QueryPlanHash wasn’t being populated. The column was there, but it was just always null. (#2396)
- Improvement: add check for statistics that were updated in the last 15 minutes because updated statistics cause parameter sniffing emergencies. (#2386) (No, I’m certainly not saying you shouldn’t update statistics – you should – but just be wary of parameter sniffing emergencies immediately after you do.)
- Improvement: when we find running queries with >10000x cardinality misestimations, we show the query plan – and even better, the live query plan from sys.dm_exec_query_statistics_xml when it’s available and patched to the point where it’s not causing crashes. (#2401)
sp_DatabaseRestore: Fixes Awaiting Your Testing
Users have contributed 3 changes/improvements that need testing before we can get ’em into the dev branch. If you can test these by downloading the code and seeing if they work in your environment, please leave a note on the issue with your thoughts on it, and if a user or two agrees that they’re production-worthy, I’ll merge ’em in:
- sp_DatabaseRestore should still open the BackupFiles cursor when @StopAt and @OnlyLogsAfter are specified
- Restore database from multiple backup paths
- Safer string aggregation
When you have questions about how the tools work, talk with the community in the #FirstResponderKit Slack channel. If you need a free invite, hit SQLslack.com. Be patient – it’s staffed with volunteers who have day jobs.
When you find a bug or want something changed, read the contributing.md file.
When you have a question about what the scripts found, first make sure you read the “More Details” URL for any warning you find. We put a lot of work into documentation, and we wouldn’t want someone to yell at you to go read the fine manual. After that, when you’ve still got questions about how something works in SQL Server, post a question at DBA.StackExchange.com and the community (that includes us!) will help. Include exact errors and any applicable screenshots, your SQL Server version number (including the build #), and the version of the tool you’re working with.