SQLskills SQL101: SQL Server Maintenance

Microsoft has a methodology for developing and distributing updates to SQL Server, which they call the Incremental Servicing Model (ISM).  This model has a hierarchy of on-demand hotfixes (HFs), Cumulative Updates (CUs), and Service Packs (SPs) that are used to distribute updates to SQL Server. 

Microsoft’s official policy and guidance about when and whether to apply SQL Server updates changed on March 24, 2016, as described here. It is important that DBAs understand how this update system works whether they are working with traditional on-premises SQL Server or SQL Server running in an Azure VM (or any other IaaS cloud solution such as Amazon EC2).

Why do you need to maintain SQL Server?

Actively maintaining your SQL Server instances by proactively installing CUs and SPs as they become available will make your database server more reliable and possibly perform better. Microsoft has historical CSS data that indicates that a significant percentage of customer issues have already been fixed in a previously released CU, that had not been applied by the customer. My own personal experience as a DBA and consultant reinforces this view.

What happens if I don’t maintain my SQL Server instances?

You are more likely to run into problems that Microsoft has already fixed (because other customers have run into them). If your build of SQL Server is old enough, it may actually become what is called an “unsupported service pack”, which means that Microsoft CSS may be unwilling to fully support you (beyond basic troubleshooting) until you update to a supported service pack level. You don’t want to find yourself in this situation!

Are there any other benefits from updating SQL Server?

Developing a detailed plan for how you test and deploy a SQL Server update, and then actually implementing and updating the plan on a regular basis forces you and your organization to have a plan you also can follow whenever you make any kind of change or update to your database servers or the applications that use them. If you have any sort of HA/DR technology in place, updating SQL Server gives you an opportunity to use it in a planned fashion to minimize your downtime. Doing this on a regular basis validates your HA/DR solution and increases your confidence that it actually works as designed.

Are there any risks from updating SQL Server?

Certainly. Anytime you make any change to a computer system, there is a chance that something can go wrong. That is why you should have a written plan for how you test and deploy a SQL Server update that also includes how to rollback and recover in case something does go wrong. In reality, it is actually quite rare for a SQL Server update to cause a problem, but that doesn’t mean you should not be ready to deal with it if it does happen. Having a detailed plan that you actually follow dramatically decreases the chances of having any issues when you deploy your SQL Server update to Production.

How often does Microsoft release Cumulative Updates?

Microsoft releases Cumulative Updates every eight weeks for the versions of SQL Server that are still in mainstream support. This includes SQL Server 2012, SQL Server 2014, and SQL Server 2016. Currently, the CU release cycles for SQL Server 2012 and SQL Server 2016 are in sync, while SQL Server 2014 releases CUs slightly later. Hopefully, they will get the CU release cycle for all three versions back in sync.

How do I find out about new SQL Server Cumulative Updates?

The first place to look is the SQL Server Release Services blog. You can also check these Microsoft KB articles:

How do I find more information about this subject?

You can watch my Pluralsight courses SQL Server 2012: Installation and Configuration and SQL Server: Installing and Configuring SQL Server 2016, and read my article on SQLPerformance.com, Making the Case for Regular SQL Server Servicing.

You can attend one of our in-person training classes, such as IE0: Immersion Event for the Accidental/Junior DBA or IEHADR: Immersion Event on High Availability and Disaster Recovery. You can also contact me if you have specific questions. And, if you want to find all of our SQLskills SQL101 blog posts – check out: SQLskills.com/help/SQL101

Thanks for reading!

SQL Server 2014 Service Pack 2 Cumulative Update 4

Microsoft has released SQL Server 2014 Service Pack 2 Cumulative Update 4, which is Build 12.0.5540.0. There are 30 hotfixes in the public fix list. In my opinion, you should be on the SP2 branch by now. If you have not made that move, you should be making plans to get on SP2 as soon as possible.

They have also released SQL Server 2014 Service Pack 1 Cumulative Update 11, which is Build 12.0.4502.0. There are 15 hotfixes in the public fix list for this CU.

There is no corresponding CU for the RTM branch, since SQL Server 2014 RTM is no longer a supported Service Pack level.

New Flagship Xeon E5 and E7 Processors

Intel has recently released two, new “flagship” Xeon processors, one for the E7 v4 product family, and one for the E5 v4 product family. The new Intel Xeon E7-8894 v4 processor has 24 physical cores, and runs at a slightly higher base clock speed of 2.4GHz, compared to the 2.2GHz base clock speed of the previous flagship Intel Xeon E7-8890 v4 processor.

All of the other specifications of the E7-8894 v4 are identical to the earlier E7-8890 v4. One big difference between these two processors is the price. The new Xeon E7-8894 v4 is $8898.00 while the older Xeon E7-8890 v4 is $7174.00, which is a 24% price increase. While this seems like a pretty significant price increase by Intel, I think that most organizations that have a need for this type of hardware are not going to be very sensitive to that difference in hardware cost.

From a SQL Server 2016 Enterprise Edition license cost perspective, each physical core license is $7128.00. A four-socket Dell PowerEdge R930 server would require 96 core licenses, which would cost $684,288.00. The added $6,896.00 hardware cost of four E7-8894 v4 processors vs. four E7-8890 v4 processors is pretty trivial. The base clock speed increase is 9%, which means better single-threaded performance, which actually makes that large investment in SQL Server 2016 licenses more acceptable. Getting 9% more CPU capacity and 9% better single-threaded performance for less than 1% of the total hardware and license cost is actually a pretty good ROI. Table 1 shows some comparative metrics for a four-socket system using either of these two processors.

 

Picture1

Table 1: Comparative Metrics for Xeon E7-8894 v4 vs. Xeon E7-8890 v4 Processors

 

Back in Q4 of 2016, Intel made a similar new flagship model introduction in the Xeon E5 v4 product family with the rollout of the Intel Xeon E5-2699A v4 processor. This new flagship SKU has 22 physical cores and a base clock speed of 2.4GHz, compared to the 2.2GHz base clock speed of the previous flagship Intel Xeon E5-2699 v4 processor. Again, all of the other specifications for the E5-2699A v4 are identical to the earlier E5-2699 v4. There was also a significant price increase for this new flagship processor, with the new SKU costing $4938.00 vs. $4115.00 for the older flagship SKU, which represents a 20% price increase. This also seems like a case of price gouging from Intel, but is is actually acceptable from a SQL Server 2016 license cost perspective.

A two-socket Dell PowerEdge R730 server would require 44 core licenses, which would cost $313,632.00. The added $1,646.00 hardware cost of two E5-2699A v4 processors vs. two E5-2699 v4 processors is even more trivial. Table 2 shows some comparative metrics for a two-socket system using either of these two processors.

 

Picture2

Table 2: Comparative Metrics for Xeon E5-2699A v4 vs. Xeon E5-2699 v4 Processors

 

In both cases, my standard guidance about selecting the fastest available processor for a given physical core count for SQL Server usage still stands. The added hardware cost for getting the fastest processor core is really insignificant compared to the total system cost, including licensing costs.

The fact that Intel feels justified in charging 20-24% more for just 9% more performance is just a sad fact that stems from them not currently having any viable competition in the server CPU space from AMD. I really do hope that the next round of AMD Opteron processors based on the Zen microarchitecture are successful, and start to give Intel some decent competition.

Still, as a SQL Server DBA, getting 9% more capacity and 9% better single-threaded CPU performance for less than 1% higher system cost is actually a pretty good deal.

 

 

SQL Server Diagnostic Information Queries for February 2017

This month, there are minor updates for the SQL Server 2012 and newer queries.

Rather than having a separate blog post for each version, I have just put the links for all seven 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 SQL Server vNext, 2016, 2014 and 2012:

SQL Server vNext Diagnostic Information Queries

SQL Server nNext 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

SQL Server 2012 Diagnostic Information Queries

SQL Server 2012 Blank Results Spreadsheet

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

Since SQL Server 2008 R2 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. I did update these queries slightly in January 2017 though.

SQL Server 2008 R2 Diagnostic Information Queries (CY 2017)

SQL Server 2008 R2 Blank Results Spreadsheet

SQL Server 2008 Diagnostic Information Queries (CY 2017)

SQL Server 2008 Blank Results Spreadsheet

SQL Server 2005 Diagnostic Information Queries (CY 2017)

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!

Two Recent Laptops Compared

I have two fairly recent-vintage 13” personal laptops that I use primarily for teaching and presentations that I thought would be interesting to compare from some common performance perspectives. The first one, which is slightly over a year old is a Lenovo Yoga 900, which has a 14nm Intel Core i7-6500U Skylake-U processor, 16GB of RAM, a 512GB Samsung PM871 SATA III SSD, one USB 2.0 port, one USB 3.0 port, one USB-C port and a 3200×1800 touch display.

The newer machine is an HP Spectre x360 13-w023dx, which has a 14nm Intel Core i7-7500U Kaby Lake-U processor, 16GB of RAM, a 512GB Samsung SM961 M.2 NVMe SSD, one USB 3.0 port, two USB-C Thunderbolt 3 ports and a 1080P touch display.

The high-level processor specifications and CPU-Z benchmark results for these two systems are shown below:

 

Processor                     Base Clock      Turbo Clock      Single-threaded CPU          Multi-threaded CPU

Intel Core i7-6500U        2.5GHz            3.1GHz              1467                                    3391

Intel Core i7-7500U        2.7GHz            3.5GHz              1743                                    3958

 

These Skylake-U and Kaby Lake-U processors are quite similar, with the Kaby Lake having an optimized “14nm plus” process technology that lets Intel set the clock speeds slightly higher at the same power usage levels. Kaby Lake also has improved integrated graphics and an improved version of Intel Speed Shift technology that lets Windows 10 throttle up the clock speed of the processor cores even faster than with a Skylake processor.

 

Figure 1: Improved Intel Speed Shift in Kaby Lake

 

The single-threaded CPU-Z 1.78.1 benchmark result is 18.8% higher with the new system, while the multi-threaded CPU-Z benchmark result is 16.7% higher on the new system. I attribute this increase to the higher base and turbo clock speeds, the optimized process technology, and the effect of the improved Intel Speed Shift. The results are shown in figures 2 and 3.

 

image

Figure 2: Intel Core i7-6500U CPU-Z Benchmark Results

 

 

image

Figure 3: Intel Core i7-7500U CPU-Z Benchmark Results

 

Honestly, these current generational CPU performance improvements are slightly better than nothing (but not much), and are certainly not a good enough reason to upgrade from an equivalent Skylake-U system to a Kaby Lake-U system. Where we see a big improvement is with basic storage performance and peripheral connectivity between these two systems.

I was happily surprised that the new HP system came a very fast 512GB Samsung SM961 M.2 NVMe OEM SSD that is equivalent to a Samsung 960 PRO. The reason I was surprised was because some reviews I had read indicated that these HP machines had a much slower Samsung OEM M.2 NVMe SSD. This probably varies by when your machine was manufactured, so perhaps the earliest review machines had the older, slower drives.

As you can see, the difference in the CrystalDiskMark performance between these drives is pretty dramatic.

 

image

Figure 4: 512GB Samsung SM961 M.2 NVMe SSD

 

image

Figure 5: 512GB Samsung PM871 SATA 3 SSD

 

For day to day average PC usage, you probably won’t really notice the difference between a fast SATA 3 SSD and an M.2 PCIe NVMe SSD, but if you are using SQL Server on a laptop, having that extra sequential bandwidth and much better random I/O performance is really noticeable. It is also very nice to have Thunderbolt 3 support, which will allow you to have really fast transfer performance to an appropriate external drive.

So the moral of all this is that the best reason to consider upgrading to a new laptop or new desktop machine for many people are the additional storage and peripheral connectivity options that you can get with a new machine.

 

SQL Server 2012 Cumulative Updates Available

Microsoft has released SQL Server 2012 SP3 CU7 (Build 11.0.6579.0). According to the associated cumulative KB article, KB3205051, 12 issues have been resolved with this update.

Microsoft has also released SQL Server 2012 SP2 CU16 (Build 11.0.5678.0). According to the associated cumulative KB article, KB3205054, 5 issues have been resolved with this update.

If you are running SQL Server 2012, you really should be planning on moving to the SP3 branch (if you haven’t done it already). The SP3 branch will be supported for a longer period of time, and also includes the manageability enhancements that were added in SP3.

Just in case you haven’t noticed, SQL Server 2012 is due to go out of mainstream support on July 11, 2017.

SQL Server 2016 Cumulative Updates Available

Microsoft has released SQL Server 2016 SP1 CU1 (Build 13.0.4411.0). According to the associated cumulative KB article, KB3208177, 63 issues have been resolved with this update. A number of them look to be pretty significant.

Microsoft has also released SQL Server 2016 RTM CU4 (Build 13.0.2193.0). According to the associated cumulative KB article, KB3205052, 65 issues have been resolved with this update.

If you are running SQL Server 2016, you really should be planning on moving to the SP1 branch (if you haven’t done it already) because of all of the manageability enhancements that were added in SP1. Being on SP1 is even more important if you are running SQL Server 2016 Standard Edition, because of all of the feature enhancements that were added in SP1.

Hardware Selection for a Home Lab – Part 1

I have long believed that if you want to be more successful as a developer, database professional, or in any technically-oriented I.T. career, it is a very good idea to have some sort of home lab environment to use for experimentation and learning.

Depending on your goals, needs, budget, available space, etc., there are many different ways to actually go about doing this. Some of these include using actual rack-mount or tower servers (which might be old, used equipment or newer equipment from a server vendor’s outlet), using desktop workstation tower servers built from parts (or purchased used), and even using old laptop machines.

Again, based on your needs, preferences, and available overall infrastructure, it may make more sense to use virtualization, with one or more fairly robust virtualization host machines that can run multiple, concurrent virtual machines (VMs) or to just have a number of “bare metal” machines.

One factor that is often ignored as people think about this is the spouse acceptance factor (SAF), where what you might like to do must be tempered by what your spouse will actually accept in terms of noise, space, heat and power usage. Having a number of rack mount servers running 24 x 7 can have a very negative impact on your electrical bill!

You also need to think about the network and storage infrastructure required to properly support your home lab. At a bare minimum, you will probably want wired Gigabit Ethernet, but you may want to think about having some 10 Gigabit Ethernet capability, especially as 10Gb equipment prices continue to decline. You also might want a decent NAS device to support your lab.

Using a NUC

One increasingly viable method for equipping a home lab is to use one or more Small Form Factor (SFF) machines, such as the Intel NUC series. These are very small footprint, bare-bone kits that include everything you need except RAM, storage, and an operating system. You buy the NUC kit, then buy and install your RAM and storage, install the operating system of your choice (with some important limitations), and then you are ready to go.

These machines are typically based on low power, dual-core laptop processors (with hyper-threading), using laptop SO-DIMM RAM, and have had somewhat limited storage capability, at least for earlier generations. These type of machines have been around for about four years now, with several generations that have been released from Intel and other smaller companies, such as Gigabyte. Each new generation has had various improvements that make a SFF machine a more realistic choice for many lab scenarios.

Intel has recently announced a new generation of NUCs that are based on the 7th generation Kaby Lake processor. The main potential advantage of this new generation is increased storage space and performance. The Core i7 and Core i5 models have 40Gbps Thunderbolt 3 support built-in with an Alpine Ridge controller that connects to a USB-C connector. You can get a primer about the various USB and Thunderbolt connectors and standards here.

Having Alpine Ridge built-in gives you a lot more I/O flexibility and performance. For example, you could plug in an external enclosure that uses USB 3.1 Gen 2 (up to 10 Gbps) or Thunderbolt 3 (up to 40 Gbps) connectivity to get a lot of additional I/O throughput from your NUC.

The two best choices from this generation (for a home lab) are the Intel NUC7i7BNH and the Intel NUC7i5BNH, which are the taller Core i7 and Core i5 versions respectively. The reason why you want the taller models is because they let you install a 2.5” SATA III SSD in addition to an M.2 2280 PCIe 3.0 NVMe storage card, which gives you additional storage space and performance.

The Intel NUC7i7BNH uses the Intel Core i7-7567U processor, which has a base clock speed of 3.5GHz, a Turbo clock speed of 4.0GHz, a TDP of 28W, and a 4MB L3 cache. The Intel NUC7i5BNH uses the Intel Core i5-7260U processor, which has a base clock speed of 2.2 GHz, a Turbo clock speed of 3.4GHz, a TDP of 15W, and a 4MB L3 cache. Both of these processors have integrated Iris Plus graphics with 64GB of eDRAM, which shows up as L4 cache. Whether or not Windows and SQL Server will take advantage of that L4 cache for general computing purposes is unknown to me right now.

A fully loaded Intel NUC7i7BNH would have four logical processor cores, 32GB of 2133MHz DDR4 RAM, wired Gigabit Ethernet, wireless 802.11ac networking, up to a 4TB SATA III SSD, up to a 2TB M.2 PCIe NVMe, plus whatever storage you plugged into the Thunderbolt 3 USB-C port. A system configured like this would be pretty expensive, mainly because of the storage costs for the top of the line components. The ultimate performance bottleneck for most scenarios would be the number of available processor cores, even though these will be pretty fast cores.

Another potential issue is that you might not be able to use a Server operating system such as Windows Server 2016 or Windows Server 2012 R2, not because the OS won’t install, but because of driver issues with the NIC. Quite often, Intel won’t let you install server-class NIC drivers on client-grade NIC hardware. Sometimes people figure out ways to hack around this, but often it is much easier to install Windows 10 Professional, and then use Windows Hyper-V to host VMs.

 

Figure 1: Rear Panel of Intel NUC7i7BNH

 

On the software side of your home lab, there are many free resources available from Microsoft. If you are a student or faculty member at a high school or university, you can take advantage of Microsoft Imagine (formerly called Dreamspark) to get a lot of valuable software and other useful resources (such as a free, three-month subscription to Pluralsight). Another free program is Microsoft Visual Studio Dev Essentials, and yet another free program is Microsoft IT Pro Cloud Essentials.

In future posts, I’ll talk about some other hardware options for a home lab.

 

 

 

Operating System Support for SQL Server Versions

There are currently six major versions of SQL Server that I commonly see being used in Production, along with five major versions of Windows Server. Only certain combinations of SQL Server and Windows Server are officially supported by Microsoft, but tracking down this information is a little tedious.

Table 1 shows these possible combinations and whether they are officially supported by Microsoft. One possibly surprising combination is the fact that SQL Server 2012 is not officially supported on Windows Server 2016. Perhaps this is less surprising if you keep in mind that SQL Server 2012 will fall out of mainstream support on July 11, 2017, which is not that far away.

 

Picture1

Table 1: OS Support for Recent Versions of SQL Server

 

The available links that document this are listed below:

Hardware and Software Requirements for Installing SQL Server 2008 R2

Hardware and Software Requirements for Installing SQL Server 2012

Hardware and Software Requirements for Installing SQL Server 2014

Hardware and Software Requirements for Installing SQL Server (for 2016 and later)

 

If you are getting ready to deploy a new instance of SQL Server 2014 or SQL Server 2016, then you should prefer Windows Server 2016, even though they are also supported on older operating systems. If you are getting ready to deploy a new instance of SQL Server 2008 through SQL Server 2012, then you should prefer Windows Server 2012 R2, even though they are supported on older operating systems.

Finally, if you are getting ready to deploy a new instance of SQL Server 2005, then I feel a little sorry for you! SQL Server 2005 is out of extended support, and it is missing so many useful features that were added in newer versions of SQL Server.

Actually, I recently helped a client deploy some new instances of SQL Server 2005 for some pretty valid business reasons. We ended up deploying to a VM (on new, very fast host hardware) that was running Windows Server 2008 R2, which worked perfectly fine.

 

SQL Server Diagnostic Information Queries for January 2017

This month, I have updated the legacy queries for SQL Server 2005, SQL Server 2008 and SQL Server 2008 R2. There are also minor updates for the SQL Server 2012 and newer queries.

Rather than having a separate blog post for each version, I have just put the links for all six 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 SQL Server vNext, 2016, 2014 and 2012:

SQL Server vNext Diagnostic Information Queries (January 2017)

SQL Server nNext Blank Results Spreadsheet

SQL Server 2016 Diagnostic Information Queries (January 2017)

SQL Server 2016 Blank Results Spreadsheet

SQL Server 2014 Diagnostic Information Queries (January 2017)

SQL Server 2014 Blank Results Spreadsheet

SQL Server 2012 Diagnostic Information Queries (January 2017)

SQL Server 2012 Blank Results Spreadsheet

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

Since SQL Server 2008 R2 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. I did update these queries slightly in January 2017 though.

SQL Server 2008 R2 Diagnostic Information Queries (CY 2017)

SQL Server 2008 R2 Blank Results Spreadsheet

SQL Server 2008 Diagnostic Information Queries (CY 2017)

SQL Server 2008 Blank Results Spreadsheet

SQL Server 2005 Diagnostic Information Queries (CY 2017)

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