In this series of posts, I’m going to cover some of the most common issues that crop up during Health Checks on clients’ SQL Servers. These are all what I consider low-hanging fruit; low-risk, well-documented, and easy to fix by the average server engineer or accidental DBA. Let’s kick things off with something that can sometimes be unintentional, but often is a result of unfounded fear: Patching
Proactive Patching Mentality
Yes, there are still sysadmins out there who subscribe to the “If it ain’t broke, don’t fix it” theory when it comes to server patching. The problem is that IT IS BROKE, otherwise there wouldn’t be a patch. If you don’t believe me, just read through some of the fixes listed for any given SQL Server cumulative update. It’s fine too if you look through the list and decide that none of those conditions apply to your environment and you want to skip an update. As long as you’re keeping up on update releases and not just blindly assuming you don’t need to patch, you’re already in the top 5% of your class.
In the past, a lot of administrators took the approach of only installing Service Packs and not Cumulative Updates. This was because there was a time when SPs were tested more thoroughly than CUs. Cumulative Updates were given lighter testing and also required registration to download. In addition, they were only recommended to be installed when the specific issue they corrected was being experienced. This approach is now no longer relevant because as of SQL Server 2017, there are no Service Packs. Instead, CUs are released on a regular schedule and are tested to the same level as the now-deprecated Service Packs. So if you haven’t been keeping up and you’re waiting to patch your SQL Server 2017 or 2019 instance when SP1 comes out, you’re going to be waiting a long time.
Trust, But Verify
It’s true that some updates will introduce new problems. In fact, the recently released CU2 for SQL Server 2019 has a known issue with SQL Server Agent. The official advice from Microsoft if you use SQL Server Agent is to wait for CU3 or revert to CU1 if CU2 was already installed. This is another example of why testing is so important. Your test environments should be as close to an exact copy of production as possible, including things like Agent jobs. Installing a new CU on a production instance right when it’s released is not something I generally recommend. This is especially true if you haven’t tested it on non-production first. The exception might be if you’re experiencing a serious production issue that is resolved by a hotfix in the CU.
Throughout my career I have had to open tickets with Microsoft Support a number of times. The servers involved had not been patched regularly in nearly every case. A little research generally showed there were unapplied CUs that included fixes for the issues in question. You might be wondering why I would open a support ticket instead of just installing the patch that I was pretty sure would fix the issue anyway. That’s exactly what I would have done if it had been up to me. However, old attitudes towards patching are alive and well, and in those cases my client or boss felt like they would rather be told they had to patch by Microsoft than simply be proactive. It will cost you around $500 if you open a ticket for a known issue that’s already been fixed. Again, my advice is to simply keep your servers patched and avoid most problems to begin with.
How To Keep Up
This might seem like a lot to try to keep up with, and it can be. Fortunately, Brent Ozar provides a great free service in the form of sqlserverupdates.com. Visit the site, read through the updates listed for the versions of SQL Server in your environment to see what you need. Next, click the Subscribe link to sign up for notifications. Whenever Microsoft releases a new update for SQL Server, you’ll get a notification email that typically highlights any high priority hotfixes in the release. Equally helpful will be the emails that alert you if a new update is causing problems for users in the wild. These will help you determine if you should hold off on installing it.
Patching status is just one of the many checks performed during a SqlCS instance Health Check. If you want to learn more about this service and pricing, you can download the Health Check brochure here.
Stay tuned for the next installment where we’ll look at some simple but commonly overlooked SQL Server knobs and dials that can make a big difference in your servers performance.