PSBlitz v5.0.0 - optimizations and improved error handling for query execution, Excel and HTML report handling

What's new:

Enhancements

Reduced repetitive code while improving code readability, performance, and error handling

• The following process are now handled in their own dedicated functions:
  • Query execution - #286
  • HTML table generation - #288
  • Writing HTML files to disk - #297
  • Writing data to Excel and saving changes - #292
• Deadlock graph file name generation is now handled in spBlitzLock_NonSPLatest.sql - #296
• Datetime and varbinary conversions to string are now handled in the .sql scripts - #291

The above changes have slimmed down PSBlitz.ps1 to 4751 lines (4313 loc) from 7074 lines (6553 loc).

Result sets updates for stability and report file size

• The following checks/report pages now have hard limits on the generated result sets and/or unnecessary columns have been removed:
  • Index Usage - limited to 10k records - #293
  • (Detailed) Index Diagnostics - limited to 10k records - #293
  • Raw session activity - removed columns that don't change during the execution of a request - #295
  • Stats Info - limited to 10k records ordered by modified percent descending.
  • Index Fragmentation Info - limited to 20k records ordered by avg fragmentation percent descending, size descending.
  • Wait Stats and Perfmon Stats - removed Pattern column - #290

Informational changes

• Added warning about opening and closing Excel during PSblitz execution when Excel output is used - #287
• Added known issue regarding using Excel while PSBlitz is running and Excel output is used - #289

Bug fixes

• You cannot call a method on a null-valued expression. Statement $ExcelSheet.Cells.Item($ExcelStartRow, $ExcelColNum) = $DateForExcel.ToString("yyyy-MM-dd HH:mm:ss") - #285

• Major code rewrite - optimizations and improved error handling for query execution, Excel and HTML report handling by @VladDBA in #298

Full Changelog: v4.5.0...v5.0.0

PSBlitz v4.5.0 - some fail-safes for instances with lots of databases

What's new:

Behavior changes

Issue #279
Starting with this release, if an instance-wide check is being done on an instance with 50 or more databases, PSBlitz will automatically limit the following checks to the database that appears the most in the data returned by the cache related checks:

• Index Summary
• Index Usage Details
• (Detailed) Index Diagnosis

New parameter

The -MaxUsrDBs parameter was introduced to control the cutoff for the behavior change described above. The default is 50, but only change the value if most of those databases don't have too many tables and/or indexes, or you've opted to output to HTML and have enough RAM for PS to handle the excess data. Note that PSBlitz will limit the Index Usage and Index Diagnostics outputs to 30k records if more rows are returned.

New cache related checks

Issues #115, #277
Duplicates and query hash will provide data about queries with duplicate plans and queries with single use plans.

More info added to the "Instance Info" and "Database Info" report pages

Instance Info

Issue #282
Now shows the buffer usage in GB in the "Resource Info" section.

Database Info

Issue #280
Now shows the amount of cached data in GB and the percent of buffer usage on a per-database basis in the "Database Info" section

Additional info captured in the execution log

Issue #278
The execution log now makes a note of the type of authentication used (not the credentials or anything like that) either SQL or trusted - this is purely for debugging purposes.

More info

• Dev - 3AM PR - aka some people really live with 100+ databases on one instance by @VladDBA in #284

Full Changelog: v4.4.1...v4.5.0

PSBlitz v4.4.1 - (Detailed) Index Diagnosis more clear and inclusive Findings

What's new:

Enhancements

• Update PSBlitz to include the October release of Brent's First Responder Kit - #274
  As part of this update, the (Detailed) Index DIagnosis report page will have clearer, more relevant, and inclusive Findings and Findings Groups.
• Documentation: Add clarification about -CheckDB being used for Azure SQL DB - #275

More Info

• Updated sp_Blitz scripts by @VladDBA in #276

Full Changelog: v4.4.0...v4.4.1

PSBlitz v4.4.0

What's new:

Enhancements

• HTM report - make Query Store table sort-able - #272
• Add -CacheMinutesBack parameter to limit plan cache analysis - #270
• Azure SQL DB - If -CheckDB was provided, use it for Azure SQL DB name instead of prompting again - #269

More Info

• 2:45AM PR by @VladDBA in #273

Full Changelog: v4.3.4...v4.4.0

PSBlitz v4.3.4 - now compatible with PowerShell on Linux

What's new:

Enhancements

• Compatibility with PowerShell on Linux - #267
• Use Join-Path for the paths passed to [System.IO.File]::ReadAllText as well - #266

More Info

• PowerShell on Linux compatibility by @VladDBA in #268

Full Changelog: v4.3.3...v4.3.4

PSBlitz v4.3.3

What's new:

Bug fixes:

• Failing to identify Azure SQL environment when the server name doesn't respect the standard format - #264

More Info

• Fix for #264 by @VladDBA in #265

Full Changelog: v4.3.2...v4.3.3

PSBlitz v4.3.2

What's new:

Enhancements

• Use Join-Path for HTML, xdl, sqlplan output file paths - #260
• HTML report - replace "search" with "filter by" in filter textbox - #259

Bug fixes:

• Zip archive naming issue when PSBlitz checks Azure SQL MI - #258

More Info

• Dev by @VladDBA in #261

Full Changelog: v4.3.1...v4.3.2

PSBlitz v4.3.1

What's new:

Enhancements

• Replace path string concatenations with Join-Path - #255
• Cleaned up some redundant code used for output directory name generation

More Info

• Dev by @VladDBA in #257

Full Changelog: v4.3.0...v4.3.1

PSBlitz v4.3.0

What's new:

Enhancements

• Make console output a bit more user friendly - #249
• Excel/HTML report - Deadlock info - add the new max CPU and elapsed time columns - #248
• Update PSBlitz to include the July release of Brent's First Responder Kit - #247
• Instance Info - add user db count for SQL Server and Azure SQL MI - #246
• HTML/Excel - spBlitzIndex_NonSPLatest.sql - remove the "more info" column - #245
• HTML report - additional limit to output of spBlitzIndex_NonSPLatest.sql mode 2 for instance-wide checks - #244
• HTML report - Limit the output of spBlitzIndex_NonSPLatest.sql mode 0 and 4 for instance-wide checks - #243

Bug fixes:

• Excel - Deadlock Plans - "Avg Duration(ms)" is always empty - #250

More Info

• Dev by @VladDBA in #251
• Additional info added to HTML main page for Index Usage by @VladDBA in #252

Full Changelog: v4.2.2...v4.3.0

PSBlitz v4.2.2

What's new:

Enhancements

• HTML report - Limit the output of spBlitzIndex_NonSPLatest.sql mode 2 for instance-wide checks #240

Bug fixes:

• Exception when running PSBlitz with SQL login password provided via -SQLPass #239

More Info

• Minor changes by @VladDBA in #241
• Dev by @VladDBA in #242

Full Changelog: v4.2.1...v4.2.2