Releases: BrentOzarULTD/SQL-Server-First-Responder-Kit
2019-04-30: New sp_Blitz Checks, sp_BlitzIndex Skips Databases
This month, lots of improvements and bug fixes, including checking for paused resumable index operations, Evaluation Edition expiration dates, sp_BlitzCache shows the number of duplicated plans for each query, sp_BlitzIndex will skip a list of databases for you, and more.
sp_Blitz Changes
- Improvement: check for First Responder Kit stored procs that are out of date (in case you're updating sp_Blitz, but not its relatives.) (#1994, thanks JeffChulg!)
- Improvement: check for resumable index operations that have been paused. (#2010)
- Improvement: warn on Evaluation Edition expiration dates. (#2017)
sp_BlitzCache Changes
- Improvement: shows the new air_quote_actual plans from sys.dm_exec_query_plan_stats on SQL Server 2019 and Azure SQL DB. (#2019)
- Improvement: show the number of multiple plans for a query in the Warnings column, like "Multiple Plans (105)". (#2026)
- Fix: arithmetic overflow when calculating terribly bad total reads for a plan. (#2018, thanks IanManton for the bug report.)
- Fix: Azure SQL DB wasn't allowed to sort by memory grants or spills because we were checking version numbers rather than sys.all_columns to check if the necessary columns exist. (#2015)
- Fix: the output table wouldn't get created when called with @sortorder = 'all.' The table would get populated if it already existed, it just wouldn't get created for the first time. (#2009)
sp_BlitzFirst Changes
- Improvement: when we call sp_BlitzCache in the 15-minute Agent job for the Power BI Dashboard, we now run it with @SkipAnalysis = 1, @sortorder = 'all'. This runs faster (because it doesn't analyze the plan XML looking for anti-patterns), and simultaneously gets you more query plans because we sort by all methods, every time. (#2009)
sp_BlitzIndex Changes
- Improvement: new @IgnoreDatabases parameter lets you skip a comma-delimited list of database names. We use that same logic to populate databases with over 100 partitions, which means @GetAllDatabases = 1 works normally, but skips databases with over 100 partitions (and shows them in the output list as to why they were skipped.) To examine those, use @Bringthepain = 1. (#1944, thanks Richard Hughes for the idea and Rich Benner for the starting point for the code.)
sp_BlitzLock Changes
- Fix: remove a collation error. (#2014, thanks Gerald-Abacus!)
- Fix: now works (slowly) on Amazon RDS, which doesn't support some tricky hints. (#2037)
sp_BlitzQueryStore Changes
- Fix: fixed error "The multi-part identifier "s.is_cursor" could not be bound." (#2024)
sp_ineachdb Changes
- Fix: now it actually does stuff even if you don't specify @Help = 1. I tell you what, people, this is how I can tell you're not using this proc yet, hahaha. (Pull #2035, thanks Aaron Bertrand.)
For Support
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.
2019-03-20: Bug Fixes and a New sp_BlitzCache String Search
This month, just half a dozen bug fixes, plus a new parameter for sp_BlitzCache to search the plan cache for specific strings.
sp_Blitz Changes
- Fix: ignore backups-on-same-drive rule on Linux since it's harder to detect mount points over there. (#1995, thanks UCJonathan for the bug report and Brandon (bsquidwrd) for the code.)
sp_BlitzCache Changes
- Improvement: new @SlowlySearchPlansFor parameter lets you look for strings in the plan cache. Useful for when you wanna figure out what query is asking for a particular missing index, find out which query is using an index, or which query is calling a function. This one's very new and untested, so if you find stuff, drop us a line in a new issue. (#2000)
sp_BlitzFirst Changes
- Fix: when calling sp_BlitzCache, it was passing an invalid @sortorder parameter, and since sp_BlitzCache started purposely throwing sev 16 errors in the #1945 enhancement, that caused the sp_BlitzFirst Agent jobs to fail.(#1983, thanks Bill Mrazik for the bug report.)
sp_BlitzIndex Changes
- Fix: saving results to table was broken in last month's release. Doh! (#1988, thanks Matthew Monroe for the report & fix.)
- Fix: simplified missing index recommendations - they no longer say "with high impact" or "with low impact." (#1991)
sp_DatabaseRestore Changes
- Fix: the @StoPAt value was not being honored when @ContinueLogs = 1. (#1986, thanks Troy Jennings for the report & fix, and ZLThomps1 for the assist.)
- Fix: if you asked to restore diffs and logs, but no diffs were taken yet, the logs were being skipped too. (#1998, thanks Frederik Vanderhaegen.)
2019-02-19: The Rich Benner Memorial Release
I hereby christen this the Rich Benner Memorial Release. He's still alive, it's just that we'll always remember him for the work he put into this month's version. (I'm kidding, of course. We won't remember him. (I'm kidding. Rich will appreciate the humor in that.))
Across-the-Board Changes
- Improvement: you can now call the procs with @VersionCheckMode = 1, and they'll just set their version number & date output variables, then immediately return without doing any work. (#1949, thanks Jeff Chulg for the great idea and implementation.)
And the code for your copy/pasta delight:
DECLARE @Version VARCHAR(30), @VersionDate DATETIME2;
EXEC dbo.sp_Blitz
@VersionCheckMode = 1,
@Version = @Version OUTPUT,
@VersionDate = @VersionDate OUTPUT ;
SELECT @Version as Version , @VersionDate as VersionDate;
sp_Blitz Changes
- Improvement: new check for SSAS, SSIS, SSRS services running. (#1943, thanks Rich Benner.)
- Improvement: added Azure SQL DB's POOL_LOG_RATE_GOVERNOR as a poison wait. (#1971.)
- Fix: updated out-of-support version list to mark that SQL 2012 pre-SP4 is now an unsupported build. (#1967, thanks Rich.)
- Fix: typo in check IDs where check 222 was reporting that it was 114. (#1964, thanks Rich.)
- Fix: typo in documentation where checks 203 and 224 were swapped. (#1966, thanks Rich.)
sp_BlitzCache Changes
- Improvement: autocorrect for sort orders. If you pass in something that's kinda-sorta-near a real sort order, we correct it. (#1945, thanks Rich Benner.)
- Improvement: Azure SQL DB and Hyperscale compatibility. (#1935)
- Fix: faster performance when you have a lot of missing indexes. We were missing a field in a join between two temp tables. (#1956, thanks Ron MacNeil for the eagle eye.)
- Note: in this release, #1935 also renamed all of the ##bou_ temp tables without bou, so just ##BlitzCacheResults. This was done to reduce the BOU branding in the Consultant Toolkit in case your customers start reading the scripts.
sp_BlitzFirst Changes
- Improvement: in the headline-news result set, batch requests/sec and wait time per core per second are now shown as decimals instead of integers. (#1940, thanks Rich Benner for being the perfect class attendee who actually improves the tools during the class.)
- Improvement: added Azure SQL DB's POOL_LOG_RATE_GOVERNOR as a poison wait. (#1971.)
sp_BlitzIndex Changes
- Improvement: better index naming - removed the table name from the index to tighten up execution plan visualization. (#1938, thanks Rich Benner for doin' it again.)
- Fix: to work around a bug in sys.dm_db_stats_histogram and sys.dm_db_stats_properties, we now only show statistics for tables when your database context is the same as the database you're analyzing. This means if you wanna examine stats, you've gotta be in the same database as the object you want to analyze. (#1947, thanks Morten Abrahamsen.)
- Fix: works again with Azure SQL DB. (#1933, thanks Jacob Golden.)
- Fix: output to table works again. (#1952, thanks Aram Pendley for the bug report and Matt Monroe for the bug fix.)
- Fix: compression information truncation error for over 900 partitions. (#1973.)
2019-01-28: More Cloud Compatibility
sp_Blitz Changes
- Improvements: skipping checks on Azure SQL DB Managed Instance that aren't relevant, like system databases on C, Hekaton enabled, and TDE enabled. (#1919, thanks Jovan Popovic.)
- Fix: if you leave your Amazon EC2 VM name as the default (c'mon, seriously people), we won't flag it as an RDS server. (#1925, thanks Randy Knight.)
- Fix: when alerting on backups written to the same drive as databases, ignore tempdb. (#1916, thanks Henrik Staun Poulsen.)
sp_BlitzBackups Changes
- Improvement: now raises a 16 severity error if it fails due to a nonexistent linked server, making it easier to troubleshoot failures. (#1904, thanks M-Ron.)
sp_BlitzFirst Changes
- Fix: when sp_BlitzFirst is set up in an Agent job to call sp_BlitzCache, it now checks the SQL Server version before trying to sort by memory grants (which isn't possible on older versions/patch-levels.) (#1910, thanks M-Ron.)
sp_BlitzIndex Changes
- Improvement: when you pass in @TableName to do table-level analysis, we added a new result set for statistics histograms at the bottom. (#1900)
- Improvement: in @mode = 2 (listing all the existing indexes), we added columns at the far right with the drop and create TSQL. (#1921, thanks Joshua Higginbotham.)
- Improvement: when @mode = 4 doesn't find any results, it now links to FirstResponderKit.org instead of BrentOzar.com. (Trying to remove unnecessary links to us to make it easier for consultants to copy/paste the results as-is for clients.) (#1905)
- Fix: if you don't set @Bringthepain = 1 and we find >100 partitions, we now raise a level 0 severity error instead of 16. We don't need to pop you over to the messages tab since there's a line about the partitions at the top of the result set anyway. (#1914)
- Fix: @GetAllDatabases = 1 was failing on Amazon RDS when it hit the rdsadmin database because they're doing something fancy with the resource database. (#1927)
sp_DatabaseRestore Changes
- Improvement: validate the @database parameter against the full backup file. (#1894, thanks ShawnCrocker.)
Extraneous syllables
sp_Blitz Improvements
- #1874: brent Fixed Some Capitalization Issues.
sp_BlitzCache Improvements
- #1864: If you're on 2016+, we'll populate function names the way we do stored procedure names.
- #1873: Every once in a while I'd get a weird error about a database not being accessible. I tracked it down to the database property returning a wonky result that didn't match an equality.
sp_BlitzIndex Improvements
- #1881: With special thanks to @jobbish-sql for figuring the query out, and @jadarnel27 for putting together an easy Q&A for the documentation page.
- #1884: Fix for false positives in the heaps with nonclustered primary keys check. If you had a column that, by chance, had the word "rid" in it, it would get flagged. Thanks to @imyourdba for reporting this oddball!
- #1888: Added server uptime information to the header row of final results. Just a minor tweak, since several checks reference it.
sp_BlitzFirst Improvements
- #1891: & #1889 both relate to Managed Instances. One with database sizes returning NULLs, and one weird XE wait to screen out. Big thanks to Some Guy Named Forrest® for having a Managed Instance, and playing doctor with us.
sp_BlitzQueryStore Improvements
- #1873: Same as BlitzCache
- #1892: @tboggiano suggested a couple enhancements: to return resources by max, and list out all the different plans we found for a query.
sp_BlitzLock
- #1890: Added some collation modifiers to the final results.
- #1867: Added code to resolve Agent job and step names involved in deadlocks
- #1866: @jobbish-sql and I spent a fun Saturday afternoon perf tuning this old dog, with some nice final results. It went from running for about 5 minutes to running for around 11 seconds.
sp_ineachdb Improvements
sp_foreachdb Improvements
Nothing this time around
sp_BlitzWho Improvements
Nothing this time around
sp_DatabaseRestore Improvements
Nothing this time around
PowerBI
Nothing this time around
sp_BlitzInMemoryOLTP Improvements
Nothing this time around
sp_BlitzBackups Improvements
Nothing this time around
sp_AllNightLog and sp_AllNightLog_Setup Improvements
Nothing this time around
Get Into Sports Dummy
sp_Blitz Improvements
#1786 - @dallyhorton made a good point: if we're gonna warn you about Change Tracking, we should tell you which database it's enabled for.
#1791 - Who do DMV queries think they are, blocking each other? Get over yourselves.
#1793 - CheckIds shouldn't be twins, I suppose.
#1794 - We now skip growth checks on read only databases. A sensible change.
#1797 - Better support for Managed Instances.
#1799 - Filter out Managed Instance waits
#1802 - Skip alerting that your alerts aren't alerted in RDS
#1814 - 2012SP3 is now officially unsupported. YOU KNOW WHAT WHAT MEANS.
#1839 - Added Managed Instance specific info
#1840 - There's a lot of false positive about Linux. Now there's one less.
#1856 - More complete list of DBCC command sources to ignore
sp_BlitzCache Improvements
#1806 - You know what really helps when you're reading long numbers? Commas.
#1812 - Hopefully avoid some invalid length errors on substrings.
#1819 - Improves checks for cursor problems in XML
#1847 - Joe Obbish discovered that you can have a bigint of statistic modifications. Someone get this guy a stats update, would ya?
#1855 - SQL Server 2014 SP3 added row goal information to query plan XML.
#1858 - Expanded the @StoredProcInfo parameter to also search for triggers and functions.
sp_BlitzIndex Improvements
#1848 - It was pretty weird realizing we didn't collect check constraint information in here. Now we do, and we check to see if you've got a scalar udf in the definition. That's all for now.
sp_BlitzFirst Improvements
#1795 - Something about delta views for multiple servers. Everything named Delta just goes over my head.
#1799 - Like Blitz, not filters out Managed Instance wait stats
#1807 - If you're on a version of SQL Server that can use show you live query statistics, we'll throw in warnings if we detect running queries with cardinality estimation or skewed parallelism issues.
#1815 - Better Azure SQLDB compatibility. For now. Until they change something in a week.
#1836 - Improved startup time calculations for Azure SQLDB. Until they change something in a week.
#1857 - Re-ignoring some CLR waits that we used to ignore and stopped ignoring. Long story.
sp_BlitzWho Improvements
#1829 - You can now write sp_BlitzWho to a table. In another database. With filtering. It a whole thing. We just don't support it < 2012 yet. You should really just upgrade anyway.
sp_BlitzQueryStore Improvements
#1819 - Same cursor stuff as BlitzCache
#1847 - Same stats mod counter stuff as BlitzCache
#1860 - Skip queries that hit sys.master_files in Azure
PowerBI
#1810 - @hfleitas got mobile reports working. Now you can keep your SQL Server in your pocket, where hopefully it won't get all warm and smushy.
sp_BlitzLock
#1841 - Removed duplicate columns from output. How they got in there is a mystery.
#1860 - When you run BlitzLock, the default search path is the system health session XE file. That throws a kinda obtuse error in Azure, where file paths like that have to be URLs that point to Azure Blob Storage. I didn't fix anything, I just give you a more helpful error message.
sp_ineachdb Improvements
Aaron Bertrand was kind enough to open source this script and allow us to distribute it with the FRK. Everyone say thank you to Canada for making Aaron.
sp_AllNightLog and sp_AllNightLog_Setup Improvements
Nothing this time around
sp_DatabaseRestore Improvements
Nothing this time around
sp_BlitzInMemoryOLTP Improvements
Nothing this time around
sp_BlitzBackups Improvements
Nothing this time around
sp_foreachdb Improvements
Nothing this time around
Do you have a better idea?
sp_Blitz Improvements
#1755 - @MisterZeus added a new check for alerts that don't send information out. I mean, is that even an alert?
#1766 - @jadarnel27 added some code to help folks who have a standard (non-sa) db owner not get warned about their standard user being the db owner.
#1782 - @RichBenner added an additional check to prevent trace file missing/moved errors.
sp_BlitzCache Improvements
#1748 - @RichBenner fixed up a divide by zero issue - what a hero!
sp_BlitzIndex Improvements
#1751 - I've attempted to make mode 0 more useful, by including a few more high priority issues. There was just too much missing between Mode 0 and Mode 4 (which gives you everything back that we find).
#1767 - Sometimes checking for duplicates looked like we fired false positives in Mode 0 because we weren't also filtering the initial results by size
#1778 - @arampendley added code to output additional columns from op stats when you write to a table
#1780 - Fixed an issue where the string we cast a number to wasn't long enough, and added some formatting to make long strings easier to read.
sp_BlitzFirst Improvements
Nothing this time around
sp_BlitzWho Improvements
Nothing this time around
sp_AllNightLog and sp_AllNightLog_Setup Improvements
Nothing this time around
sp_BlitzQueryStore Improvements
Nothing this time around - WON'T SOMEONE PLEASE USE THE QUERY STORE?
sp_DatabaseRestore Improvements
Nothing this time around
PowerBI
Nothing this time around
sp_BlitzLock
Nothing this time around
sp_BlitzInMemoryOLTP Improvements
Nothing this time around
sp_BlitzBackups Improvements
Nothing this time around
sp_foreachdb Improvements
Nothing this time around
Still quoting Uncle Buck
sp_Blitz Improvements
#1698 - Now warns you about any SQL Modules that have ANSI_NULLS or QUOTED_IDENTIFIER off. Thanks @MisterZeus!
#1719 - @TheUsernameSelectionSucks pointed out a typo. Model, msdb. Who can tell the difference?
sp_BlitzCache Improvements
#1352 - If you ever looked at the "weight" columns and thought they looked weird, we were right there with you. They should be fixed now. If you find anything really far off, let us know!
#1706 - We now warn you about compilation metrics, like compile time, cpu, and memory, if they surpass certain thresholds.
#1724 - After calling index spools the most passive aggressive plan operator for ages, I finally decided to do something about it. I break them down and report them as missing index requests in the clickable missing index column. The improvements aren't an exact science, but it's a good start.
#1732 - Found some weird cases where missing index counts were off when multiple plans were in the cache for the same query. Fixed!
sp_BlitzFirst Improvements
#1708 - Switched from a static list of ignorable waits to using the temp table we build, so they only have to be maintained in one place.
#1735 - Ignore poison waits unless they're > 1 second.
sp_BlitzIndex Improvements
#1705 - In some of the checks around nonclustered indexes, we were counting disabled and hypotheticals towards the total.
sp_BlitzWho Improvements
#1721 - @osumatt fixed things up so we get all the fancy memory grant column in 2017!
sp_AllNightLog and sp_AllNightLog_Setup Improvements
#1727 - @dalehhirt checked in a change that allows you to ignore databases on the restore side.
sp_BlitzQueryStore Improvements
Nothing this time around - WON'T SOMEONE PLEASE USE THE QUERY STORE?
sp_DatabaseRestore Improvements
Nothing this time around
PowerBI
Nothing this time around
sp_BlitzLock
Nothing this time around
sp_BlitzInMemoryOLTP Improvements
Nothing this time around
sp_BlitzBackups Improvements
Nothing this time around
sp_foreachdb Improvements
Nothing this time around
Better Late Than DEAD
sp_Blitz Improvements
#1664 - We're officially smart enough to not warn people that we're recompiling our own stored procedures.
#1669 - Reworded the stacked instances details. Servers may be installed and not running.
#1687 - @josh-simar has servers linked with AD accounts, and that'll make for a rootin' tootin' bad time when you're trying to display information about them.
#1695 - @josh-simar found another bugerino for servers that have a member_principal_id over 32,767. Personally I have no idea what that means.
sp_BlitzCache Improvements
#1666 - It took like 4 years, but BlitzCache finally got blocked on a busy server. We are officially reading uncommitted data. Hold onto your pantaloons.
sp_BlitzFirst Improvements
#1680 - You now have the power to skip checking server info, with @CheckServerInfo = 0. Thanks to @jeffchulg for the idea!
#1689 - @Adedba loves some memory analysis. We'll now give you the skinny on what your RAM is doing.
#1679 - @jeffchulg coded up the magical ability to change the output type to none, if you don't want any output.
#1676 - @ChrisTuckerNM hit some ran into some XML funk. There were some string concatenation issues.
sp_BlitzIndex Improvements
#1685 - We split the warnings about Heaps into two sections. One for forwarded fetches, and one for deletes.
#1697 - If you wanna examine a single table, we shouldn't be concerned with all them darn fangled partitions that might exist on another table.
#1679 - @jeffchulg coded up the magical ability to change the output type to none, if you don't want any output.
sp_DatabaseRestore Improvements
#1681 - @ShawnCrocker fixed a bug where the backup path for diffs wasn't getting set to null if we weren't restoring any diffs.
#1673 - @reharmsen fixed things up so you fine people can use Standby mode as expected.
#1672 - @marcingminski added the ability to restore from striped backups! Very fancy!
#1671 - @lionicsql coded a feature that will let you restore full and differential backups in standby. Hooray.
sp_BlitzQueryStore Improvements
Nothing this time around - WON'T SOMEONE PLEASE USE THE QUERY STORE?
PowerBI
Nothing this time around
sp_BlitzLock
Nothing this time around
sp_BlitzInMemoryOLTP Improvements
Nothing this time around
sp_BlitzWho Improvements
Nothing this time around
sp_BlitzBackups Improvements
Nothing this time around
sp_AllNightLog and sp_AllNightLog_Setup Improvements
Nothing this time around
sp_foreachdb Improvements
Nothing this time around
Fine Leather Goods
sp_Blitz Improvements
#1620: @AAvella schooled us on Group Policy registry settings. Someday, we'll get our act together as a group.
#1631: We'll now tell you if there's an update available for your SQL Server version. For most of you, that's a yes.
#1633: If your plan cache has been cleared yesterday, we check the size of the userstore_tokenperm cache to see if it's >2gb.
#1634: Update RDS compatibility. We had some recent checks built that didn't get along well with it.
sp_BlitzCache Improvements
#1612: @bertwagner has perfect hair, and perfect syntax. We bow to his knowledge of equals signs.
#1624: @digitalohm let us know about a BlitzCache error when looking at large query plans.
sp_BlitzFirst Improvements
#1623: @stgioia Doesn't need to be warned about log shipping running. It should be running. You should check on that.
sp_BlitzIndex Improvements
#1610: @way0utwest thinks dta indexes are poorly named, and we agree! Now if you look over in the create t-sql column of sp_BlitzIndex, we'll give you rename commands for your dta indexes.
#1626: Brent was doing some documentation digging, and decided we should show you the page latch and io waits in dm_db_index_operational_stats when available. That Brent, always wanting to show you things.
sp_BlitzQueryStore Improvements
#1627: @OmnipotentOwl is working hard and playing harder in Azure, and fixed up our version checks for Managed Instances. Oh, you don't have a Managed Instance? Maybe try being less dull. All the cool kids have one.
#1624: Same as BlitzCache
sp_DatabaseRestore Improvements
#1645: @digitalohm added the ability to choose between executing and printing commands
#1653: @ShawnCrocker Got us on the right path, and switched parts of the code over to use xp_dirtree instead of xp_cmdshell
#1657: @ShawnCrocker Fixed up parts of the code that weren't using CommandExecute.
PowerBI
Nothing this time around
sp_BlitzLock
Nothing this time around
sp_BlitzInMemoryOLTP Improvements
Nothing this time around
sp_BlitzWho Improvements
Nothing this time around
sp_BlitzBackups Improvements
Nothing this time around
sp_AllNightLog and sp_AllNightLog_Setup Improvements
Nothing this time around
sp_foreachdb Improvements
Nothing this time around