SQLskills SQL101: The Importance of Maintaining SQL Server

By: Glenn Berry
Posted on: May 14, 2018 3:13 pm

SQLskills has an ongoing initiative to blog about basic topics, which we’re calling SQL101. We’re all blogging about things that we often see done incorrectly, technologies used the wrong way, or where there are many misunderstandings that lead to serious problems. If you want to find all of our SQLskills SQL101 blog posts, check out SQLskills.com/help/SQL101.

When I look at many SQL Server instances in the wild, I still see a large percentage of instances that are running extremely old builds of SQL Server for whatever major of version of SQL Server is installed. This is despite years of cajoling and campaigning by myself and many others (such as Aaron Bertrand), and an official guidance change by Microsoft (where they now recommend ongoing, proactive installation of Service Packs and Cumulative Updates as they become available).

Microsoft has a helpful KB article for all versions of SQL Server that explains how to find and download the latest build of SQL Server for each major version:

Where to find information about the latest SQL Server builds

Here is my commentary on where you should try to be for each major recent version of SQL Server:


SQL Server 2017

SQL Server 2017 and newer will use the “Modern Servicing Model”, which does away with Service Packs. Instead, Microsoft will release Cumulative Updates (CU) using a new schedule of one every month for the first year after release, and then one every quarter for next four years after that.

Not only does Microsoft correct product defects in CUs, they also very frequently release new features and other product improvements in CUs. Given that, you should really try to be on the latest CU as soon as you are able to properly test and deploy it.

SQL Server 2017 Build Versions

Performance and Stability Fixes in SQL Server 2017 CU Builds

Reasons to Upgrade to SQL Server 2017


SQL Server 2016

SQL Server 2016 and older use the older “incremental servicing model”, where each new Service Pack is a new baseline (or branch) that has it’s own Cumulative Updates that are released every eight weeks. Microsoft corrects product defects in both Service Packs and in CUs, and they also very frequently release new features and other product improvements in both CUs and Service Packs.

As a special bonus, Microsoft has also gotten into the very welcome habit of actually backporting some features and improvements from newer versions of SQL Server into Service Packs for older versions of SQL Server. The latest example of this was SQL Server 2016 Service Pack 2 which has a number of improvements backported from SQL Server 2017.

SQL Server 2016 Build Versions

Performance and Stability Related Fixes in Post-SQL Server 2016 SP1 Builds

Performance and Stability Related Fixes in Post-SQL Server 2016 SP2 Builds

SQL Server 2016 Service Pack 2 Release Notes

SQL Server 2014

SQL Server 2014 will fall out of Mainstream Support from Microsoft on July 9, 2019. If you are running SQL Server 2014, you really should be on at least SQL Server 2014 SP2 (which got many improvements backported from SQL Server 2016), and ideally, you should be on the latest SP2 Cumulative Update. You should also be on the lookout for SQL Server 2014 SP3 which is due to be released sometime in 2018, which is very likely to have even more backported improvements.

If you are on SQL Server 2014 or SQL Server 2012, Microsoft has a very useful KB article that covers recommended updates and configuration options for high performance workloads. A number of these configuration options are already included if you are on the latest SP or newer for either SQL Server 2012 or SQL Server 2014.

SQL Server 2014 Build Versions

Performance and Stability Related Fixes in Post-SQL Server 2014 SP2 Builds

Hidden Performance and Manageability Improvements in SQL Server 2012/2014

SQL Server 2014 Service Pack 2 is now Available !!!

SQL Server 2012

SQL Server 2012 fell out of Mainstream Support from Microsoft on July 11, 2017. If you are running SQL Server 2012, you really should be on SQL Server 2012 SP4, ideally with the Spectre/Meltdown security update applied on top of SP4. Similar to SQL Server 2014 SP2, SQL Server 2014 SP4 also included a number of product improvements that were backported from SQL Server 2016.

SQL Server 2012 SP3 build versions

Performance and Stability Related Fixes in Post-SQL Server 2012 SP3 Builds

SQL Server 2012 Service Pack 4 (SP4) Released!

So just to recap, here are my recommendations by major version:

SQL Server 2017: Latest CU as soon as you can test and deploy

SQL Server 2016: Latest SP and CU as soon as you can test and deploy. Try to at least be on SQL Server 2016 SP2.

SQL Server 2014: Latest SP and CU as soon as you can test and deploy. Try to at least be on SQL Server 2014 SP2 (and SP3 when it is released).

SQL Server 2012: SP4 plus the security hotfix for Spectre/Meltdown.





Posted in: SQL Server 2012, SQL Server 2014, SQL Server 2016, SQL101
Be the first to Reply!

Windows Disk Cleanup

By: Glenn Berry
Posted on: May 3, 2018 3:39 pm

One very useful tool for freeing up disk space is the built-in Disk Cleanup application in Windows. You can start this application by simply typing Disk Cleanup at your Windows Start menu. Once it starts, you will need to pick which drive you want to scan and clean up. In most cases, you will want to choose your boot drive, which will be C: for most people.


image

Figure 1: Disk Cleanup: Drive Selection Screen


image

Figure 2: Initial Disk Cleanup Screen


You should always click on the “Clean up system files” button to get a complete picture (and find the larger files that you may be able to delete). After clicking on the button, you’ll have to wait for the scan to complete. If you have a magnetic drive, this might take a while, while if you have an Intel Optane 900P drive, it will be extremely quick!


image

Figure 3: Disk Cleanup after clicking on Cleanup system files

In many cases, you will find an entry for “Previous Windows installation(s)” that may be 25-50GB in size, which is pretty significant when you have a smaller boot drive and are running low on disk space. In Windows 10, you will get those when you install the semi-annual updates such as the recent “Windows 10 April 2018” update.

You just need to be aware that if you let Disk Cleanup delete the previous Windows installations, you won’t be able to go back to a previous version by uninstalling the latest version. The tool will display a special confirmation dialog to make sure you know this.

If you choose to delete everything that the tool offers up, it is not uncommon to get 50-75GB of space back. Again, the disk cleanup process will take quite a while with a magnetic drive.

You can start Disk Cleanup from a command line, as Microsoft explains here.


You can also use the Windows Storage Sense feature to automatically do some of the disk cleanup for on all of your drives.


image

Figure 4: Storage Sense Display


image

Figure 5: Storage Sense Configuration

Posted in: Storage, Windows 10
3 Comments

SQL Server Diagnostic Information Queries for May 2018

By: Glenn Berry
Posted on: May 2, 2018 8:45 am

May 3, 2018 Update: I have added a SQL Server 2016 SP2 version of the queries, since Microsoft back-ported so many new queries from SQL Server 2017 to SQL Server 2016 SP2.

This month, there are more minor updates to the SQL Server 2012 and newer versions of the queries, primarily in the comments and documentation. I have added some additional columns to many existing queries, and tried to get the newer versions uniformly updated and synchronized. There are also two new queries for the SQL Server 2012 set, to pull back new information that is exposed in SQL Server 2012 SP4.

I have also developed a T-SQL script that you can use to check whether your instance of SQL Server has been patched to mitigate against the Spectre/Meltdown CPU vulnerability. This works for SQL Server 2008 through SQL Server 2017, for on-premises and cloud-based VM (IaaS) usage. You can get the query for this here.

I often make additional minor updates to the queries periodically during the month, so if you are in doubt, downloading the latest version is always a good idea.

Rather than having a separate blog post for each version, I have just put the links for all eight major versions in this single post. There are two separate links for each version. The first one on the top left is the actual diagnostic query script, and the one below on the right is the matching blank results spreadsheet, with labeled tabs that correspond to each query in the set.

Here are links to the latest versions of these queries for Azure SQL Database, SQL Server 2017, 2016 SP2 2016, and 2014:

Azure SQL Database Diagnostic Information Queries

Azure SQL Database Blank Results Spreadsheet

SQL Server 2017 Diagnostic Information Queries

SQL Server 2017 Blank Results Spreadsheet

SQL Server 2016 SP2 Diagnostic Information Queries

SQL Server 2016 SP2 Blank Results Spreadsheet

SQL Server 2016 Diagnostic Information Queries

SQL Server 2016 Blank Results Spreadsheet

SQL Server 2014 Diagnostic Information Queries

SQL Server 2014 Blank Results Spreadsheet

Here are links to the most recent versions of these scripts for SQL Server 2012 and older:

Since SQL Server 2012 and older are out of Mainstream support from Microsoft (and because fewer of my customers are using these old versions of SQL Server), I am not going to be updating the scripts for these older versions of SQL Server every single month going forward.  I started this policy a while ago, and so far, I have not heard any complaints.

SQL Server 2012 Diagnostic Information Queries

SQL Server 2012 Blank Results Spreadsheet

SQL Server 2008 R2 Diagnostic Information Queries

SQL Server 2008 R2 Blank Results Spreadsheet

SQL Server 2008 Diagnostic Information Queries

SQL Server 2008 Blank Results Spreadsheet

SQL Server 2005 Diagnostic Information Queries

SQL Server 2005 Blank Results Spreadsheet

The basic instructions for using these queries is that you should run each query in the set, one at a time (after reading the directions for that query). It is not really a good idea to simply run the entire batch in one shot, especially the first time you run these queries on a particular server, since some of these queries can take some time to run, depending on your workload and hardware. I also think it is very helpful to run each query, look at the results (and my comments on how to interpret the results) and think about the emerging picture of what is happening on your server as you go through the complete set. I have quite a few comments and links in the script on how to interpret the results after each query.

After running each query, you need to click on the top left square of the results grid in SQL Server Management Studio (SSMS) to select all of the results, and then right-click and select “Copy with Headers” to copy all of the results, including the column headers to the Windows clipboard. Then you paste the results into the matching tab in the blank results spreadsheet.

About half of the queries are instance specific and about half are database specific, so you will want to make sure you are connected to a database that you are concerned about instead of the master system database. Running the database-specific queries while being connected to the master database is a very common mistake that I see people making when they run these queries.

Note: These queries are stored on Dropbox. I occasionally get reports that the links to the queries and blank results spreadsheets do not work, which is most likely because Dropbox is blocked wherever people are trying to connect. I am not planning on moving these to Github any time soon.

I also occasionally get reports that some of the queries simply don’t work. This usually turns out to be an issue where people have some of their user databases in 80 compatibility mode, which breaks many DMV queries, or that someone is running an incorrect version of the script for their version of SQL Server.

It is very important that you are running the correct version of the script that matches the major version of SQL Server that you are running. There is an initial query in each script that tries to confirm that you are using the correct version of the script for your version of SQL Server. If you are not using the correct version of these queries for your version of SQL Server, some of the queries are not going to work correctly.

If you want to understand how to better run and interpret these queries, you should consider listening to my three related Pluralsight courses, which are SQL Server 2014 DMV Diagnostic Queries – Part 1 ,  SQL Server 2014 DMV Diagnostic Queries – Part 2 and SQL Server 2014 DMV Diagnostic Queries – Part 3. All three of these courses are pretty short and to the point, at 67, 77, and 68 minutes respectively. Listening to these three courses is really the best way to thank me for maintaining and improving these scripts…

Please let me know what you think of these queries, and whether you have any suggestions for improvements. Thanks!


Posted in: Azure SQL Database, DMV Queries, SQL Server 2005, SQL Server 2008, SQL Server 2008 R2, SQL Server 2012, SQL Server 2014, SQL Server 2016, SQL Server 2017
3 Comments