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.
9 Comments. Leave new
“We fell out of love with WITH CHECKSUM – turns out nobody uses it.”
Shouldn’t they? Seems to be the right thing in 99% of the cases.
I’d second Tobi’s comment, I started using WITH CHECKSUM because of sp_Blitz.
Thanks guys, glad you liked it! We might consider putting it back in in the future, but it just kick started too many awkward conversations over email, and I wanted to have better documentation around the performance impacts first. I hate to have someone change settings in their SQL Server only to have performance go to hell in a handbasket.
I also use WITH CHECKSUM.
Don’t we want to encourage people to use it?
Yeah, but like I mentioned in the comment to Elijah, I need to be able to conclusively say to users first, “This won’t have a significant impact on your backup times.” That may totally be true across the board, but I need to find evidence of that first, and right now I don’t have that. I’d love to see a community member blog about the impact of WITH CHECKSUM on underpowered systems. (hint hint) hahaha….
Are you saying my systems are underpowered? 🙂
Even if it has a significant performance impact (and we don’t yet know that it does), it may still be the right thing to do. But being able to provide some specific documentation about the trade-offs would add a lot of value.
I love the check for user-created statistics. It helped me find HUNDREDS of these that were built before I started at this job.