T-SQL Tuesday #104: Code I Have Written That I Would Hate to Live Without

Bert Wagner (b/t) is hosting T-SQL Tuesday #104. The invitation is to write about code you’ve written that you would hate to live without. For me, this is almost a no-brainer!

My DMV Diagnostic Queries represent a lot of code that I would hate to live without. I use them on a daily basis to gather information about SQL Server instances and databases and to help more quickly understand what configuration and performance issues they have. I’ve been publicly posting these queries since 2009, but I actually started developing them for my own personal use back in 2006. The story about how they came about is kind of interesting…

Back in about August of 2006, I was the sole DBA for NewsGator, which was (at that time) an RSS aggregation company. Our main product/service was the ability to let people “subscribe” to RSS feeds for web sites and blogs, and then have us download the RSS feeds of those sites. We would also manage the “read state” of the RS feeds that you subscribed to, so that as you read through your subscribed content and marked posts as read, we would synchronize your progress across different devices.

I had only been at the company about three months, and we had recently migrated from 32-bit SQL Server 2000 to 64-bit SQL Server 2005 SP1 on a two-node FCI running on new hardware. Performance had been pretty good since the migration, and it was about 4:30PM on a Friday afternoon, when I started making some final quick checks of the health of my instance before getting ready to leave for the weekend.

I noticed that my CPU utilization was running about 90-95%, which was much higher than normal. I tried a few of my standard DBA tricks (at that time) to correct the issue, such as running sp_updatestats, running DBCC FREEPROCCACHE, etc. with no real improvement. I even took the emergency step of “shutting down” the content servers (which were application servers that downloaded the RSS feeds, that typically generated about 90% of my database load). This had no appreciable effect on my CPU utilization.

By now, I was getting worried, since we had a problem that I did not immediately know how to diagnose and correct. By this time, our support team and many of the senior executives in the company were aware that we had a problem because our applications were starting to time out and throw errors. I had a literal parade of different people coming to my desk asking some variation/combination of “What’s wrong with the database?” or “What can we do to help?”.

This got so bad that the CTO/Founder of NewsGator (Greg Reinaker) grabbed a large rolling whiteboard, and wrote something like “Glenn knows there is a problem. He is working on it. Please leave him alone”, which was actually pretty helpful.

So after some time, it ended up being just me and the best developer on the Platform Team (Jeff Tingley) staying late into the night and next morning, on a call with Microsoft Premier Support trying to diagnose and troubleshoot the issue. Eventually, we figured out that our problem was mainly caused by parameter sniffing in one stored procedure where we were getting one very inefficient plan stuck in the plan cache.

The short-term fix was to use a local variable to store an input parameter for that stored procedure to disable parameter sniffing for that stored procedure, and to periodically recompile a few other stored procedures that were also part of the problem. Jeff and I finally left around 3AM, with the system being relatively stable. I was exhausted from the time and the stress of feeling like the fate of the company rested on my shoulders. I was convinced that I was in big trouble and was possibly going to be fired since it had taken us so long to figure out the problem. Little did I know…

As it turned out, my boss’s boss decided to give both Jeff and I a $500.00 bonus, plus we got a big round of applause at a company meeting the next Monday (which I appreciated much more).

This incident was the genesis of my DMV Diagnostic queries. I never wanted to be in that situation again! Anytime there was any application slowdown, people always assumed that it was a database problem (which was not always the case). Having a set of queries that I could run to figure out what was going on with the database and database server was the key to being able to answer the “What’s wrong with the database?” question.

Many thousands of people around the world use my queries on a regular basis, and seem to find them very useful, at least based on the feedback I have gotten over the years. Now you know the story of how they came into being.

Figure 1: Link to Invitation Post

SQL Server Diagnostic Information Queries for July 2018

This month, there are improvements to seven of the the SQL Server 2012 and newer versions of the queries, with a new column that shows whether there is a missing index warning in the cached query plan. A reader named Håkan Winther made this useful suggestion.

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 1SQL 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!

AMD Ryzen Threadripper 2990X CPU-Z Leaks?

On June 5, 2018, during the Computex show in Taiwan, AMD revealed some details about its upcoming 12nm 2nd generation Ryzen Threadripper processor family that will have up to 32 physical cores (and 64 threads with SMT) that will work on existing X399 motherboards. These processors will also have the 2nd generation Ryzen features like Precision Boost 2 and XFR 2 to more aggressively boost clock speeds on multiple threads.

These are due to be released during the 3rd quarter of 2018 (rumor has it during August). My guess is that the flagship processor will have an MSRP of $1599.99… Let’s see how accurate my guess turns out to be!


Figure 1: AMD slide revealed at Computex

Since then, there has been quite a bit of speculation about the specifications and performance of this new family of processors. There have even been some supposed benchmark results and CPU-Z screenshots from the purported flagship AMD Ryzen Threadripper 2990X processor, as shown in Figure 2.


Figure 2: Alleged AMD Ryzen Threadripper 2990X CPU-Z Screenshot


Figure 3 shows an actual AMD Ryzen Threadripper 1950X CPU-Z screenshot (from my personal workstation system). The ID caption at the top of my screenshot shows that it is a validated result.

screenshot of CPU-Z validation for Dump [ffq2d1] - Submitted by  BIGBEAST  - 2018-06-20 22:08:59

Figure 3: Actual AMD Ryzen Threadripper 1950X CPU-Z Screenshot


Looking closely at the font on the Tools button from the purported Threadripper 2990X screenshot in Figure 2, it appears to be different than what you actually see in CPU-Z (and different from the font on the other buttons), which probably means that the screenshot is fake. Regardless of this, the numbers in the doctored screenshot are plausible from what AMD has officially revealed about the new processor.

If you are considering buying/building a new high-end desktop (HEDT) for workstation use, this new processor would be an excellent choice. It supports up to 128GB of RAM, and has 64 PCIe lanes, so you can have multiple M.2 NVMe drives with PCIe 3.0 x4 connectivity.

This easily lets you run multiple instances of SQL Server and multiple concurrent virtual machines.


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

As of May 30, 2018, there has been one Cumulative Update (CU) for the Service Pack 2 branch of SQL Server 2016. There have been a large number of hotfixes in each of these cumulative updates. If you are running on the SQL Server 2016 SP2 branch (which you should be by now), I really think you should be running the latest SQL Server 2016 SP2 Cumulative Update.

Table 1 shows the SQL Server 2016 SP2 CU builds that have been released so far.

BuildDescriptionRelease Date
13.0.5149SP2 CU1May 30, 2018
   

Table 1: SQL Server 2016 SP2 CU Builds

You can follow the KB article link below to see all of the CU builds for the SQL Server 2016 RTM, SQL Server 2016 SP1 and SQL Server 2016 SP2 branches.

SQL Server 2016 Build Versions

Like I have done for other versions and branches of SQL Server, I decided to scan the hotfix list for all of the Cumulative Updates in the SP2 branch, looking for performance and general reliability-related fixes for the SQL Server Database Engine. I came up with the list below, but this listing is completely arbitrary on my part. You may come up with a completely different list, based on what specific SQL Server 2016 features you are using.

Here are the fixes in the SP2 branch:

SQL Server 2016 SP2 Cumulative Update 1 (Build 13.0.5149), 29 total public hot fixes

Performance issues occur in the form of PAGELATCH_EX and PAGELATCH_SH waits in TempDB when you use SQL Server 2016

FIX: Error 9002 when there is no sufficient disk space for critical log growth in SQL Server 2014, 2016, and 2017

FIX: “Cannot use SAVE TRANSACTION within a distributed transaction” error when you execute a stored procedure in SQL Server

PFS page round robin algorithm improvement in SQL Server 2016

FIX: A memory assertion failure occurs and the server is unable to make any new connections in SQL Server

FIX: One worker thread seems to hang after another worker thread is aborted when you run a parallel query in SQL Server

FIX: TDE enabled database backup with compression causes database corruption in SQL Server 2016

FIX: Restore of a TDE compressed backup is unsuccessful when using the VDI client

FIX: Performance is slow for an Always On AG when you process a read query in SQL Server

FIX: Floating point overflow error occurs when you execute a nested natively compiled module that uses EXP functions in SQL Server

FIX: Database cannot be dropped after its storage is disconnected and reconnected in SQL Server

FIX: TDE database goes offline during log flush operations when connectivity issues cause the EKM provider to become inaccessible in SQL Server

FIX: TDE-enabled backup and restore operations are slow if the encryption key is stored in an EKM provider in SQL Server

FIX: Access violation occurs when you query a table with an integer column in SQL Server 2017 and SQL Server 2016

FIX: Parallel redo in a secondary replica of an availability group that contains heap tables generates a runtime assert dump or the SQL Server crashes with an access violation error

FIX: An assertion failure occurs when you execute a nested select query against a columnstore index in SQL Server

FIX: RESTORE HEADERONLY statement for a TDE compressed backup takes a long time to complete in SQL Server

FIX: An access violation occurs when incremental statistics are automatically updated on a table in SQL Server

The reason that I put these lists together is that I want to convince more people to try to keep their SQL Server instances up to date with Cumulative Updates. If you do the proper testing, planning and preparation, I think the risks from installing a SQL Server Cumulative Update are quite low (despite the occasional issues that people run into).

If you install a Cumulative Update or Service Pack on a Production system the day it is released, after doing no testing whatsoever, and then run into problems (and don’t have a plan on how to recover), then I don’t have that much sympathy for you.

On the other hand, if you go through a thoughtful and thorough testing process, and you have a plan for how you will install the CU, and how you would recover if there were any problems, then you are much less likely to have any problems. You are also much more likely to avoid the issues that are fixed by all of the included fixes in the new build of SQL Server. You have done your job as a good DBA.

Finally, Microsoft has changed their official guidance about whether you should install SQL Server Cumulative Updates. As they say, “we now recommend ongoing, proactive installation of CU’s as they become available”.

Speaking at PASS Summit 2018

I am honored to have been selected to present a half-day session at the PASS Summit 2018 in Seattle, WA. My session is Migrating to SQL Server 2017, and here is the abstract:

Migrating to SQL Server 2017

How do you design and implement a safe and successful migration from an older version of SQL Server to SQL Server 2017 with no data loss and virtually no downtime? What if you have a limited hardware budget for the upgrade effort and you are worried about the core-based licensing in SQL Server 2017? How can you choose your hardware wisely in light of the new licensing model? How can you convince your organization that the time is right to upgrade to SQL Server 2017? This session will cover several different methods for migrating your data to SQL Server 2017 while meeting these objectives and minimizing your hardware and licensing costs.

This is a topic that I have presented and taught multiple times, and it is something I am quite passionate about. I think it is also increasingly relevant as SQL Server 2014 will be falling out of Mainstream support on July 9, 2019, and both SQL Server 2008 and SQL Server 2008 R2 will be falling out of Extended Support on July 9, 2019. Next July is sort of a looming deadline for many organizations!

I am also going to be doing a preview version of this session in the first slot of the next 24 Hours of PASS Summit Preview event on June 12th, 2018.

Hopefully I will see you at both events!




SQL Server Diagnostic Information Queries for June 2018

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.

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 1SQL 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!


SQLskills SQL101: The Importance of Maintaining SQL Server

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.





Windows Disk Cleanup

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

SQL Server Diagnostic Information Queries for May 2018

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 1SQL 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!


SQL Server 2017 Cumulative Update 6

On April 17, 2018, Microsoft released SQL Server 2017 CU6, which is Build 14.0.3025.34 I count 39 fixes or improvements in the public fix list.

Remember, there are not going to be any Service Packs for SQL Server 2017. We are still in the monthly release cycle for SQL Server 2017 Cumulative Updates which will last for the first year after release. After that the CU release cycle will change to quarterly until SQL Server 2017 falls out of Mainstream Support on October 11, 2022.

As always, my recommendation is to try to stay as current as possible with your SQL Server Cumulative Updates. That doesn’t mean that you should deploy them to Production the day they are released, with absolutely no testing, but it also doesn’t mean that you should not make any effort to stay current. Try to find a deployment cadence that makes sense for your organization.