vladdba / psblitz Goto Github PK

To avoid confusion when running PSBlitz against instances with the same instance id but hosted on different servers - add prepend hostname to the output directory's name

I missed a variable from Brent's last update of sp_BlitzLock that lead to the script failing.

In cases where a database is found to account for at least 2/3 of the sp_BlitzCache records and stats and index fragmentation is retrieved for that database, the two sheets storing that info still end up getting deleted.

Add scripts and output sheet(s) for tempdb usage.

Add -DebugInfo switch that can be passed when running .\PSBlitz.ps1 debug information.

Behavior:
when opting to export to Excel (the default) have PSBlitz first check to see if Excel is installed, and switch to HTML as the output format if it is not installed.

In case PSBlitz has been running for more than 15 minutes up until the execution of sp_BlitzLock, narrow the time range from last 30 days to last 7 days

When Ctrl+C is pressed PSBlitz should:

• cancel current task
• retrieve the BlitzWho data captured up until that point and write it to Excel
• save the excel file with the correct name
• exit

Update the script to handle the "Cached Execution Parameters" column and add it in the sp_BlitzCache output sheets of the Excel file.

If PSBlitz is requested to run an in-depth database-specific check, then set @CheckUserDatabaseObjects = 0 to avoid looking in other user databases' objects. This will also improve execution speed.

Add command-level error handling so that PSBlitz does not terminate execution if only one command encounters an issue.

When running PSBlitz in script mode and providing hostname,port for the server, it errors out with the following message:

PS F:\PSBlitz> .\PSBlitz.ps1 localhost,1450
F:\PSBlitz\PSBlitz.ps1 : Cannot process argument transformation on parameter 'ServerName'.
Cannot convert value to type System.String.
At line:1 char:15
+ .\PSBlitz.ps1 localhost,1450
+               ~~~~~~~~~~~~~~
    + CategoryInfo          : InvalidData: (:) [PSBlitz.ps1], ParameterBindingArgumentTransf
   ormationException
    + FullyQualifiedErrorId : ParameterArgumentTransformationError,PSBlitz.ps1

Update PSBlitz to include the latest release of Brent's First Responder Kit

In case a step fails due to a connection or a command timeout, provide the reason next to the red X.

If an instance is seeing multiple daily deadlocks occurring, they're most likely happening on the same objects during executions of the same or similar queries. There's no reason to spend time getting 30 days worth of deadlocks.

Add "Estimated Response Latency" to the "Instance Info" sheet.
The value will be based on the connectivity test done at the begging of PSBlitz

GetStatsAndIndexInfoForWholeDB.sql fails on SQL Server 2012 because sys.stats does not have the is_incremental column and the sys.dm_db_incremental_stats_properties dynamic management function does not exist.

In order to avoid doing extra cleanup work or leaving objects in tempdb - remove _Deltas view creation portion from spBlitzWho_NonSPLatest.sql

Update PSBlitz to include the latest release of Brent's First Responder Kit

If PSBlitz.ps1 is ran on a computer that has Excel's AutoSave option enabled, all the temporary changes done on the template during execution end up getting saved.
This can cause result inconsistencies (with data from previous executions still being present in the template) or missing sheets if a default check is ran before an in-depth one.

It would be useful to specify in which directory to save the output folder.
Thanks!

• enhancement request -

v2.x.x instead of 2.xx

• Implement background process to collect sp_BlitzWho data every x seconds without interfering with the main script.
• Add new parameter -BlitzWhoDelay, this will be the number of seconds between each run of spBlitzWho_NonSPLatest.sql, defaults to 10.

In GetInstanceInfo.sql:

• rename patch_level to product_level
• add SERVERPROPERTY('ProductUpdateLevel') AS [patch_level]
• adjust Excel template to match

See issue 3201 in the SQL Server First Responder Kit repository for more details.

Current behavior:
The Excel output file is save once at the end of the script.
Desired behavior:
Changes are saved every time a sheet or a section of a sheet is populated with data

There are cases when a user might want to run PSBlitz at the same while their looking at a previously generated output for the same instance. Due to the Excel output file using the PSBlitzOutput_[Instance][_DatabaseName].xlsx naming format, which isn't very unique, there are chances that this might lead to the "Sorry, Excel can’t open two workbooks with the same name" error.

Change in the order of execution so that spBlitzFirst_NonSPLatest.sql gets executed right after spBlitzFirst_NonSPLatest.sql to avoid having to wait for spBlitzCache_NonSPLatest.sql to finish on instances with lots of execution plans.

The title says it all.

Add the application name in the connection string to make PSBlitz queries easier to identify by DBAs monitoring activity on the instance while running PSBlitz.

Add a sheet that contains aggregate sp_BlitzWho data to make things easier for very active instances.

In environments where ping is blocked the connectivity test fails and PSBlitz is unable to proceed.

Update README to contain information about -BlitzWhoDelay introduced with #53

Parameter ConnTimeout will increase the connection timeout (not the execution timeout) in case of slower networks.
If not provided, it will default to the standard 15 seconds

Due to the current file naming style (RunningNow_[ExecutionDate]_SID###.sqlplan) SPIDs running different statements during the execution of PSBlitz can have their previous execution plans overwritten by the latest one.

Reference invalid in in-depth check output xlsx when clicking on the "sp_BlitzCache Recent Comp" link in the Intro sheet.

Error example when most of the database names returned by sp_BlitzCache consist of the -- N/A -- string:

-- N/A -- accounts for at least 2/3 of the records returned by sp_BlitzCache
 -> Getting stats and index info for -- N/A --... x (Failed)

With latest version (v2.2.0), when i execute:
.\PSBlitz.ps1 XXXPRODSQL01\XX1 -IsIndepth Y -CheckDB CORESQL7
i obtain this error:
"Retrieving instance information.
Script execution was interrupted.
Finishing up..."

With the older version v2.1.1 the same command mentioned above works without any problems.

Add -ZipOutput parameter that can be used by:

1. either when running in command line mode like

.\PSBlitz.ps1 Hostname\Instance -ZipOutput Y

2. Or by prompting the user in interactive mode (right click > run with PowerShell).

Add -MaxTimeout to override the 800 seconds (13 minutes) timeout limit applied to sp_BlitzIndex and GetStatsAndIndexInfoForWholeDB.sql.
The parameter will only be available when starting PSBlitz.ps1 in non-interactive mode.