SQL Server First Responder Kit Versions Save

sp_Blitz, sp_BlitzCache, sp_BlitzFirst, sp_BlitzIndex, and other SQL Server scripts for health checks and performance tuning.

20201211

3 years ago

sp_BlitzLock gets better identification of parallel deadlocks, sp_DatabaseRestore uses Ola's scripts for logging, and lots of bug fixes this month.

To get the new version:

sp_Blitz Changes

sp_BlitzFirst Changes

  • Improvement: in @ExpertMode = 1, when we show the top files by read & write stalls, show 20 files instead of 5, and also sort them by the worst stalls. (#2707)
  • Fix: ignore FT_IFTSHC_MUTEX waits. (#2697)

sp_BlitzIndex Changes

  • Fix: index suggestions on really long table names could be truncated. (#2680, thanks Ralf Pickel.)
  • Fix: columnstore visualization only worked on tables in the dbo schema. (#2683, thanks Ali Hacks.)
  • Fix: nonclustered columnstore visualization didn't report the correct columns. (#2684, thanks Ali Hacks.)
  • Fix: if you passed in databases to be ignored, they were still counting against the 50-database limit before we made you pass in BringThePain = 1. (#2693, thanks skrishnan31.)
  • Fix: temporal tables had table.schema in the detail names rather than schema.table. (#2694, thanks Mark Hions and Mikey Bronowski.)

sp_BlitzLock Changes

sp_DatabaseRestore Changes:

  • Improvement: commands are now run with CommandExecute so that they get logged if things go wrong. (#2700, thanks Frederik Vanderhaegen.)
  • Fix: the new @SkipBackupsAlreadyInMsdb switch was ignoring transaction logs if you were restoring them on the same server where you were taking backups (since they were already in msdb.) (#2710, thanks Greg Dodd, and you can watch a video of him fixing it.)

sp_ineachdb Changes

  • Fix: now handles database names with spaces in 'em. (#2702, thanks renegm.)

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 me!) 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.

20201114

3 years ago

* - Not really, but we got a chuckle out of Microsoft bundling ADS with SSMS, like the classic toaster-fridge joke.

sp_Blitz Changes

  • Improvement: new check for log backups to NUL, which would break point-in-time recovery. (#2640, thanks DBAdminDB.)
  • Fix: stop alerting about usage of service accounts on the Launchpad service. (#2664, thanks Todd Chitt.)

sp_BlitzCache Changes

  • Improvement: added @SortOrder = 'unused grant', useful for when you have a lot of queries that are all hitting the max grant, and you're looking for low-hanging fruit of the ones that use the least memory. (#2646)
  • Fix: removed extra LOWER calls. (#2633, thanks Maikel van Gorkom)
  • Fix: when outputting the results to table, the PlanCreationTimeHours calculated column now correctly refers to the difference between when the plan was created vs when the data was saved to table, as opposed to the current time (which would have been artificially long). (#2655, thanks Adrian Buckman.)
  • Fix: parameterized queries in different databases were flagged as multiple query plans for the same query. (#2653, thanks Erik Darling.)

sp_BlitzFirst Changes

  • Fix: we were excluding wait types that weren't in our predefined list, which was a problem if you were hitting a new or obscure wait type. (Hello, EC.) (#2631)
  • Fix: when saving sp_BlitzFirst's output to table, we now trim it to 4,000 characters to avoid problems with really long warnings. (#2660, thanks Jefferson Elias aka JeffChulg.)

sp_BlitzIndex Changes

  • Improvement: revamped the sort orders to make the output more clear and actionable, and clarified the wording on some of the warnings. For example, "Cold Calculator" is now "Serial Forcer - Computed Column with Scalar UDF." Also added a new documentation file with the prioritized list of checks. (#2662)
    • @Mode = 0 (default): now lists priorities 1-100, the most urgent things to fix.
    • @Mode = 4: all priorities, 1-255, which includes not-easy-to-fix stuff (like bad clustering key designs or really wide tables), and informational warnings (like hey, you have temporal tables.)
  • Improvement: added @SortDirection parameter that works in combination with the @SortOrder parameter for @Mode = 2, letting you sort by asc or desc. (#2651, thanks David A. Poole.)
  • Fix: statistics updates will now show "never" if they've never been updated. (#2643, thanks Excelosaurus.)
  • Fix: instead of showing 0 reads/writes in the summary, show "Not Tracked" on spatial indexes and "Disabled" on disabled indexes. (#2650)
  • Work in progress, but not really working well yet: on SQL Server 2019, next to missing index requests in the table-level Mode 2 output and Mode 3 output, we show a sample query plan linked to that missing index request. (#2185, thanks Erik Darling.)
  • Fix: statistics warning for low sampling rate was never firing because it was looking for rows < 1 rather than percent sampled < 1. (Caught & fixed this while I was working on #2662.)
  • Fix: statistics checks now work in @GetAllDatabases = 1. (Caught & fixed this while I was working on #2662.)
  • Fix: removed an extra space. (#2673, thanks John McCall.)

sp_BlitzLock Changes

sp_BlitzWho Changes:

  • Fix: was throwing an error on AG readable secondaries when sp_BlitzFirst called sp_BlitzWho due to duplicate temp table names. (#2491, thanks Adrian B.)
  • Fix: we now pad the number of days a query's been running with 2 digits instead of 0, so queries running more than 9 days (!!!) sort to the top of the list. (#2652, thanks Ahmet Rende.)

sp_DatabaseRestore Changes

  • Improvement: @StopAt can now skip full backups, too. (#2600, thanks Sergedess.)
  • Improvement: new @SkipBackupsAlreadyInMsdb setting. When set to 1, we check MSDB for the most recently restored backup from this log path, and skip all backup files prior to that. Useful if you're pulling backups from across a slow network and you don't want to wait to check the restore header of each backup. (#2644, thanks Greg Dodd and Raphael Ducom.)

sp_ineachdb Changes

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 me!) 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.

20201011

3 years ago

In preparation for this month's new Fundamentals of Columnstore class, sp_BlitzIndex has a few cool new tricks! I'll show you how to use 'em for free in the upcoming How I Use the First Responder Kit class, too.

Columnstore indexes are kinda like an index on every column (called a segment), and plus they're kinda partitioned (called a row group). When you want to visualize how the data is stored, call sp_BlitzIndex just for that one table:

The last result set gives you a row per row group, plus columns for each column of the table, showing you what range of data is stored in each row group/segment combo. It helps you better understand what parts of the table SQL Server will be able to eliminate, and what parts it'll still need to dig through. This also affects how we load data and how we manage index rebuilds.

Next, when you want to analyze the sizes of your columnstore dictionaries, we're now breaking those out in the Index Size column. Columnstore indexes used to just show a total LOB size, but now it's broken out between columnstore and dictionary data:

That "Index Size" column shows in @Modes 0, 2, 4, and table-level output. And hey, speaking of sizes, you're probably curious about which indexes & tables are the largest ones in your database. sp_BlitzIndex @Mode = 2 now accepts a @SortOrder parameter. You can now sort by rows or size, plus more stuff too.

To get the new version:

Consultant Toolkit Changes

I updated it to this month's First Responder Kit, but no changes to querymanifest.json or the spreadsheet. If you've customized those, no changes are necessary this month: just copy the /Resources/SQLServerSetup checklist folder over your existing files to replace the SQL scripts.

sp_Blitz Changes

  • Improvement: new check for >= 35 databases being VSS snapshotted at the same time. (#2581)
  • Improvement: if you're outputting the results to database, the @OutputXMLasNVARCHAR parameter now lets you save XML columns of the table output as NVARCHAR. Useful if you're using a single table to store results over linked servers and locally. (#2601, thanks David Prestridge.)
  • Improvement: added warnings for non-default settings for SQL Server 2019's new database scoped configuration options. (#2573, thanks mtndew01.)

sp_BlitzCache Changes

  • Improvement: if you use the @SortOrder parameter, rows won't be shown unless they actually have that problem. For example, if you used @SortOrder = 'spills', we were ordering by spills - but if not a single query plan in cache was having any spills, we were still sorting by that column, and showing plans where Spills = 0. This sounds trivial, but it's actually a big deal for those of us who do a ton of plan cache analysis because now we can glance quickly at the output and go, "Your plan cache has been cleared since the spills problem happened, or the plan has aged out, or it's using a recompile hint. (#2174, thanks Erik Darling.)

sp_BlitzFirst Changes

  • Fix: CHECKDB running wasn't always shown due to a cross apply (rather than outer apply) to query plan DMFs. (#2609, thanks Erik Darling.)

sp_BlitzIndex Changes

  • Improvement: when you specify the @TableName parameter, there's a new result set that visualizes the row groups and segments. (#2584)
  • Improvement: new @SortOrder parameter lets you sort the inventory output from @Mode = 2 by rows, reserved mb, forwarded fetches, lock time, reads, writes, and more. (#2619)
  • Improvement: the Index Size column now separates columnstore index size vs its dictionary size. (#2585)
  • Improvement: the stats histogram output now includes the modification_counter. (#2612, thanks Erik Darling.)
  • Fix: the stats histogram & columnstore visualizations weren't working when sp_BlitzIndex was installed in a database other than master. (#2616, thanks Erik Darling.)

sp_BlitzLock Changes

  • Improvement: added @ExportToExcel parameter like sp_BlitzCache has. When set to 1, we don't show the XML results in the output, making it easy to copy/paste the results to Excel. (#2613, thanks Rebecca Lewis.)

sp_BlitzWho Changes

  • Improvement: added @SortOrder parameter that accepts most of the column names, so you can sort by things like session_id or memory grant. (#2603, thanks Dylan Petrusma.)

sp_DatabaseRestore Changes

  • Fix: if you set @RunRecovery = 0 and also pass in a @DatabaseOwner name, we ignore you and don't bother trying to change the database owner. (#2598, thanks Greg Dodd.)

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 me!) 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.

20200913

3 years ago

Let's give a big round of virtual germ-free applause to Adrian Buckman, Erik Darling, Garry Bargsley, Greg Dodd, Ian Manton, John McCall, Kuldar Andares, Pierre Letter, Stephen Anslow, Tom Butler, and Walden Leverich for volunteering their time to help make your job easier this month. Thanks to them, the September release is chock full of features - I'm not even going to try to sum them up here, and you should read the release notes. Kudos to them!

To get the new version:

Consultant Toolkit Changes

I updated it to this month's First Responder Kit, but no changes to querymanifest.json or the spreadsheet. If you've customized those, no changes are necessary this month: just copy the /Resources/SQLServerSetup checklist folder over your existing files to replace the SQL scripts.

sp_Blitz Changes

  • Improvement: new check looks for Query Store problems where is_query_store_on reports 1, but sys.database_query_store_options doesn't agree. (#2525, thanks Walden Leverich.)
  • Improvement: more readable info in the drive free space checks. (#2557, thanks Pierre Letter.)
  • Fix: sp_Blitz was failing on Amazon RDS SQL Servers because it was trying to run sp_validatelogins, which Amazon doesn't allow. (#2526, thanks Erik Darling.)
  • Now alerts if there's even just 1 memory dump in the last year (as opposed to 5.) (#2564, thanks Garry Bargsley.)

sp_BlitzCache Changes

  • Improvement: @OutputServerName parameter lets you push the sp_BlitzCache results to a central server via linked server. The linked server will need to already exist and have RPC enabled. Linked servers don't support writing via XML, so when writing out to a linked server, the query plan column is created as NVARCHAR(MAX) rather than XML. (#2528, thanks Pierre Letter.)
  • Fix: "Many Duplicate Plans" check was reporting numbers higher than 100%. I didn't do a great fix, but if anybody wants to dig into the real underlying problem, they can take the code from issue 2480 and apply it to this same problem. I'd be glad to take a pull request for that. (#2532, thanks Tom Butler.)
  • Fix: removed unqualified join to get the Microsoft data migration wizard to stop complaining. (#2558, thanks Ian Manton.)

sp_BlitzFirst Changes

  • Improvement: new warnings for queries running with memory grants > 15% of the buffer pool. You can configure that threshold with the @MemoryGrantThresholdPct parameter, too. (#2505, thanks Adrian Buckman.)
  • Fix: the recently-updated-statistics check (ID 44) was being blocked by index rebuild operations due to sys.dm_db_stats_properties ignoring isolation level hints. (#2548, thanks Adrian Buckman.)

sp_BlitzIndex Changes

  • Improvement: missing indexes now show both the data type for the column, and have commas in between the columns. (#2522, thanks Greg Dodd.)
  • Improvement: if lock escalation has been disabled on a table, add text mentioning that. (#2553, thanks Stephen Anslow.)
  • Fix: if Clippy came up with a missing index request more than 4,000 characters long, we were truncating it. Now, we preserve Clippy's insanity in all its blazing glory. (#2533, thanks Kuldar Andares for the report and Greg Dodd for the fix.)
  • Fix: suggested index names are now capped at 128 characters long. This can produce duplicate index names, but frankly, that's the price you should pay for creating column names that long, buddy. (#2552, thanks Greg Dodd.)
  • Fix: @Mode = 3 (missing indexes compilation) had a few extra diagnostic columns in the output left over from working on last month's release. Removed those. (#2539)

sp_BlitzWho Changes

  • Improvement: when you use the @OutputTable parameters, sp_BlitzWho now automatically creates a deltas view like the other procs. The deltas view shows the last collection of each query on a session, so you see the total CPU/reads/time impact of each query. Not collected across multiple sessions - just helps you identify long-running queries that aren't showing up in the plan cache, like stuff with option recompile hints or servers with a lot of memory pressure. (#2315, thanks Adrian Buckman.)
  • Fix: when a database has RCSI turned on, some DMV (don't ask me which one) isn't always reporting all transactions are in RCSI. John McCall came up with a slick workaround to report the right isolation levels. (#2534, thanks John McCall.)

sp_DatabaseRestore Changes

  • Fix: if you pass in an empty string for the @RestoreDatabaseName parameter, we use the existing database name instead. (#2536, thanks Greg Dodd.)

SqlServerVersions Changes

  • Improvement: when creating the SqlServerVersions table, we now also add extended properties for documentation. (#2574, thanks John McCall.)

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 me!) 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.

20200808

3 years ago

A couple of big new features this month: sp_Blitz's @SkipChecksServer parameter lets you centralize a list of checks that you want to skip across different servers, and sp_BlitzIndex's missing index recommendation list now includes the datatypes, making it easier to do index analysis for clients even when you're not connected to their servers. I'll blog about both of those this week in separate posts to get you up to speed.

If you're working directly with the releases on Github, note that the "master" branch has been renamed to "main" instead.

To get the new version:

sp_AllNightLog Changes

  • Fix: if @BackupPath is set to null during installation, throws an error and stops now rather than failing silently. (#2474, thanks Curtis Browne.)

sp_Blitz Changes

  • Improvement: @SkipChecksServer is now implemented, so you can have sp_Blitz check a centralized SkipChecks table for a list of checks, databases, and servers to skip across your entire estate. (#2496, thanks Pierre Letter.)
  • Fix: reprioritize a few of the checks so that I can say with a straight face that you should drop everything and focus if you have alerts between priorities 1-50. (#2492)

sp_BlitzCache Changes

  • Improvement: add plan_generation_num to ExpertMode output and to the logged table results. Adds the column to existing tables if necessary. (#2514)
  • Improvement: add query_hash to the default output. Been using this a lot lately during my parameter sniffing classes. (#2513)
  • Fix: @SortOrder = 'query hash' wasn't working on case-sensitive servers. (#2482, thanks Adrian Buckman.)
  • Fix: USERSTORE_TOKENPERM calculation was incorrect for pages_kb. (#2487, thanks Mike Vernon.)
  • Fix: Single-use plans were reporting over 100%. (#2480, thanks Simon Yeats.)

sp_BlitzIndex Changes

  • Improvement: added new @ShowAllMissingIndexRequests parameter to show 'em even if the query was only run a few times, or had low impact. (#2498, thanks valued customer Greg Dodd.)
  • Improvement: added data types to missing index requests. (#2227 and #2512, thanks Greg Dodd and Oleg Strutinskii.)

sp_BlitzLock Changes

  • Fix: databases & tables with periods in the name wouldn't show correctly in the detail results. (#2452, thanks Filip Cornelissen.)

sp_BlitzWho Changes

  • Fix: sleeping sessions no longer show as accruing wait time. (#2465, thanks Adrian Buckman.)
  • Fix: parallel blocked sessions weren't showing the blocking spid. (#2489, thanks Adrian Buckman.)

sp_ineachdb Changes

SqlServerVersions Changes

  • Fix: The SqlServerVersions table has been updated with a few SQL Server 2012 security patches. Not new, just missed 'em in the original table. (#2481, thanks TheVladdestVlad.)
  • Fix: Uninstall now removes the SqlServerVersions table. (#2502, thanks rebambus, and congrats on your first Github pull request ever!)

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 me!) 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.

20200712

3 years ago

I don't normally do multiple releases per month, but sp_BlitzFirst had a pretty big bug in the last release for those of us who do live performance tuning. In the July release, a well-meaning contributor tried to make sp_BlitzFirst faster when @OutputType = 'NONE', but inadvertently also made it faster when @ExpertMode = 1 - by skipping the output. Oopsie.

If you're working directly with the releases on Github, note that the "master" branch has been renamed to "main" instead.

To get the new version:

sp_BlitzFirst Changes

  • Fix: wait stats weren't being returned when @ExpertMode = 1. (#2463, thanks Rich Benner and Joseph LaBonde.)

sp_BlitzIndex Changes

  • Fix: SQL Server has a bug that ignores isolation level hints on sys.identity_columns (note: no response from Microsoft on that one despite being open for several months, and hello, blue badges, that is why people get so pissed off about feedback.azure.com being like yelling into the void. This is a bug in your product, and you're not even taking the time to read the bugs that get reported. HELLOOOO) Anyhoo, we get blocked by offline index rebuilds. To fix it, we now have a 1-second lock timeout, and sp_BlitzIndex will simply return if there is blocking. (#2176)

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 me!) 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.

20200704

3 years ago

This month, 15 community folks contributed code - I think that's an all-time high for a single month's release! Good work, y'all. Thanks for payin' it forward. The bad news is that they're almost all bug fixes, hahaha. I do love that, though - given enough eyeballs, all bugs are shallow.

If you're working directly with the releases on Github, note that the "master" branch has been renamed to "main" instead.

To get the new version:

Consultant Toolkit Changes

Updated the First Responder Kit with this month's script updates, plus:
  • Improvement: the config file now lets you specify default values for the --upload and --deepdive command line switches. This way, you can pass even less instructions on to your clients - just have 'em run the utility, and you get their diagnostic data in your Amazon S3 bucket within minutes. Read this month's updated documentation for more details.
  • Fix: no arithmetic overflow even if you have truly ginormous bigint sp_BlitzCache metrics. (#2270.)

sp_Blitz Changes

  • Improvement: the installation scripts create the SqlServerVersions table if it doesn't exist, and repopulate it. (#2429, thanks Curtis Browne.)
  • Fix: Update descriptions of operating systems and no longer call Windows 2012 & R2 "pretty modern." (#2418, thanks Randolph West.)
  • Fix: when alerting about tables in the master database, ignore the Dynamics NAV license table. (#2426, thanks Johan Parlevliet.)

sp_BlitzCache Changes

  • Maybe fix: possible arithmetic overflow when checking for many duplicate plans in the cache. Not entirely sure whether this actually fixes it, though, because I couldn't reproduce it. (#2425, thanks smcnaughton, Randolph West, and Santi Swarup Choudhury.)

sp_BlitzFirst Changes

  • Improvement: now runs faster if @OutputType = 'NONE'. (#2423, thanks Jefferson Elias.)
  • Improvement: new check for >10% of your memory being used by the USERSTORE_TOKENPERM cache. (#2134)
  • Fix: last month's new update-stats check would fail if you had a LOT of stats updates at once, like, well, an update-stats job. Doh! Now we just show the first 4,000 characters of updated stats, sorted by rows in the table descending, so you can get a rough idea of when you had big churn. (#2409, thanks CJR aka camaro322hp.)
  • Fix: remove Page Life Expectancy warning. It's a bad metric to monitor in the year 2020. (#2433)
  • Fix: no longer alert on >10000x cardinality UNDER-estimations because they're false alarms when the query just started and hasn't retrieved data yet. (#2438, thanks Nicklas Bjälemark.)
  • Fix: skips last month's new update-stats check when you have >20 databases. (#2439, thanks Will McCardell.)

sp_BlitzIndex Changes

  • Improvement: In-Memory OLTP indexes now have a call to sp_BlitzInMemoryOLTP in the "More Info" column. (#296)
  • Improvement: in the readme, documented the levels of support for specialized index types (columnstore, graph, spatial, temporal tables.) I'm not really actively adding support for those, but just wanted to make it clear in the documentation that they show up in the results, just with varying levels of details. For example, the sizes of most of 'em aren't shown.
  • Fix: for disabled indexes, the "Create TSQL" column now shows a create rather than a drop. (#2447, thanks Filip Cornelissen.)

sp_BlitzLock Changes

  • Fix: now works on case sensitive instances. (#2440, thanks pnauta.)

sp_BlitzWho Changes

  • Fix: handles long database & schema names when creating the output tables. (#2421, thanks Jefferson Elias.)
  • Fix: adds compatibility with Azure SQL DB by avoiding querying sysjobs. (#2435, thanks Jacob Golden.)

sp_DatabaseRestore Changes

  • Improvement: file paths now take a comma-delimited list of paths for striped files. (#2180, thanks CubsRep.)
  • Fix: won't error out if you specify both @StopAt and @OnlyLogsAfter at the same time. (#2348, thanks Greg Dodd.)

sp_ineachdb Changes

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 me!) 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.

20200606

3 years ago

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:

EXEC dbo.sp_BlitzFirst 
  @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.

To get the new version:

Consultant Toolkit Changes

Updated the First Responder Kit with this month's script updates, plus:

sp_Blitz Changes

sp_BlitzCache Changes

  • 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)

sp_BlitzFirst Changes

sp_BlitzLock Changes

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:

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.

20200602

3 years ago

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:

EXEC dbo.sp_BlitzFirst 
  @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.

To get the new version:

Consultant Toolkit Changes

Updated the First Responder Kit with this month's script updates, plus:

sp_Blitz Changes

sp_BlitzCache Changes

  • 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)

sp_BlitzFirst Changes

sp_BlitzLock Changes

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:

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.

20200506

4 years ago

We have quarantined all of the bugs. Many of the bugs. Okay, 7 of the bugs. Hey, we actually added more improvements than we fixed bugs this month! My favorite new things are that sp_BlitzIndex @Mode = 2 now makes it even easier to do disconnected index tuning just purely inside a spreadsheet, and sp_BlitzCache runs faster on big servers.

The "Deprecated" folder now has versions of sp_Blitz, sp_BlitzCache, and sp_BlitzIndex that work on SQL Server 2005. This isn't new code: it just so happened that during one of my classes, a student asked for a 2005 version, and Oddvar Eikli mentioned that he happened to have all of 'em going back years. He graciously shared the versions that still worked with 2005, so I added 'em in. They aren't updated by any means - they're just here for historical purposes for folks who don't have any other choice. Thanks, Oddvar!

To get the new version:

Consultant Toolkit Changes

Updated the First Responder Kit with this month's script updates, plus:
  • The Indexes M2 tab adds a new Forwarded Fetches column. This is useful if you wanna sort by that descending to find the heaps that need to be rebuilt the most urgently.
  • The Indexes M2 tab's Drop TSql and Create TSql columns now use fully qualified database names, making it even easier to copy/paste commands into a client prescription to tell them what indexes to drop, and give them quick undo scripts.

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_Blitz Changes

sp_BlitzCache Changes

  • Improvement: when a plan isn't found in cache, include a link to this DBA.StackExchange.com question so folks understand what could have caused it. (#2347, thanks Ajay Patel.)
  • Improvement: faster memory analysis using sys.dm_os_memory_clerks instead of sys.dm_os_buffer_descriptors. (#2345, thanks Daniel Mrowiec.)
  • Improvement: default output now adds the Remove Plan Handle from Cache column that used to require @ExpertMode = 1. Makes it easier to teach how to fix parameter sniffing issues. (#2346)
  • Fix: allows installation on Azure SQL Managed Instances. Remember, though, we don't support anything in Azure SQL DB or Managed Instances just because MS can/has changed the DMV contents w/o warning and w/o documentation. If it works, great, if it doesn't, getting compatibility is left as an exercise for the reader. (#2353, thanks agentKnipe and Erik Darling.)

sp_BlitzFirst Changes

  • Improvement: @OutputType = 'Top10' now outputs the top 10 wait types since startup in a screenshot-friendly way, so when I'm doing Twitch streams analyzing your wait stats, I don't have to give you as many instructions. (#2342, thanks Nick Kirby.)
  • Fix: if the SQL Server was renamed without doing it right, the global variable @@SERVERNAME wouldn't be correct, so sp_BlitzFirst wasn't deleting history in the sp_BlitzCache table for this server. We now use SERVERPROPERTY('ServerName') instead. (#2320, thanks Sixten Otto and Adrian Buckman.)
  • Fix: wait stats "per core per hour" number was rounding down to small due to extra division, and it wasn't Joy Division. (#2336)
  • Fix: looks like fileproperty() was showing up as DBCC in sys.dm_exec_requests, causing a false positive that CHECKDB was running. Not too sure about this one since I can't test it easily, but if you find an issue with this, holla at ya boy by creating a new issue with what you've discovered. (#2350)

sp_BlitzIndex Changes

  • Improvement: @Mode = 2 now includes a column with Forwarded Fetches. The output to table doesn't, though, because we're not currently doing change detection to see if inserting a new column would break the table. (#2314)
  • Improvement: @Mode = 2 now includes fully qualified database names for the drop & create T-SQL so that you can do easier index tuning with the Consultant Toolkit, fully disconnected, and send clients recommendations that they can execute faster. (#2357)
  • Improvement: Obsessive Constraintive: Serial Forcer warning about UDFs now points to an explanation page. (#2340, thanks C.Hambrick.)
  • Fix: now handles identity seed values bigger than bigint, like DECIMAL(38,0). (#2268, thanks jbarnard84.)

sp_BlitzWho Changes

  • Improvement: for Agent jobs, the Program Name column now shows the Agent job name. (#2328, thanks Jerry Hung.)

Live Stream from the Coding Session

Bored? Want to make fun of my code? Here's the recording of the 3-hour live stream when I worked on a few of this month's fixes:

https://youtu.be/7XluE7Gxl3k

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.