The latest version of our SQL Server health check stored procedure is out today. Here’s what we’ve added in the last couple of versions – big thanks to the folks who keep making this even better for the community:
Changes in v33 – January 20, 2014:
Bob Klimes fixed a bug that Russell Hart introduced in v32, hahaha. Check 59 was false-alarming on Agent jobs that actually had notifications.
Changes in v32 – January 19, 2014:
- Russell Hart fixed a bug in check 59 (Agent jobs without notifications).
- Added @EmailRecipients and @EmailProfile parameters to send the results via Database Mail. Assumes that database mail is already configured correctly. Only sends the main results table, and it will not work well if you also try to use @CheckProcedureCache. Execution plans will not render in email.
- Fixed a bug in checks 108 and 109 that showed poison waits even if they had 0ms of wait time since restart.
- Removed check 120 which warned about backups not using WITH CHECKSUM. We fell out of love with WITH CHECKSUM – turns out nobody uses it.
- Added check 121 – Poison Wait Detected: Serializable Locking – looking for waits with %LCK%R%. Happens when a query uses a combination of lock hints that make the query serializable.
- Added check 122 – User-Created Statistics In Place. There is nothing wrong with creating your own statistics, but it can cause an IO explosion when statistics are updated.
- Added check 123 – Multiple Agent Jobs Starting Simultaneously. Ran into an issue where dozens of jobs started at the exact same time every hour.
Changes in v31 – December 1, 2013:
- Dick Baker, Ambrosetti Ltd (UK) fixed typos in checks 107-109 that looked for the wrong CheckID when skipping checks, plus improved performance while he was in there.
- Dick also improved check 106 (default trace file) so that it will not error out if the user does not have permissions on sys.traces.
- Christoph Muller-Spengler @cms4j added check 118 looking at the top queries in the plan cache for key lookups.
- Philip Dietrich added check 119 for TDE certificates that have not been backed up recently.
- Ricky Lively added @Help to print inline help. I love his approach to it.
- Added check 120 looking for databases that have not had a full backup using the WITH CHECKSUM option in the last 30 days.