Using Windows Resource Monitor to Find Unusual Performance Bottlenecks

I recently had an interesting issue with a new SQL Server 2014 Enterprise Edition instance that was going to be hosting about 200 user databases that each use the SQL Server Filetable feature. The reported problem was that this new instance was taking two-three times longer to restore each full database backup than the restores were taking on a different instance (that had similar hardware and storage resources).

I had several initial ideas about what the problem might be, including:

  1. Windows instant file initialization was not enabled
    1. This would force Windows to have to “zero-initialize” the database data file after it was created during the restore, which slows down the restore
    2. It was already enabled on this instance, so that was not the problem
  2. The transaction log files were particularly large for these user databases
    1. Windows instant file initialization does not work on log files, only on data files
    2. This issue would be present on any database instance, not just one
  3. The VLF counts were particularly high in the log files for these databases
    1. This causes the recovery portion for a full database restore (or crash recovery) to take much longer
    2. None of the VLF counts were above 300, plus this would behave the same on any database instance

After my initial ideas were found to be incorrect, I tried restoring a full backup of one of the user databases to the new database server, making sure to use the Stats = 1 option, so the restore progress statistics would update every 1%. This gives you more immediate and fine-grained feedback about the progress of the restore, which is always a good idea.

While the restore was running, I carefully watched the disk activity in Windows Resource Monitor.  You can sort the columns in Windows Resource Monitor to see the highest Reads and Writes (in bytes/second) while the database restore is running. This will show the reads from the database backup file, then the writes to the database data file(s) and the database log file. It also showed the writes to the filetable files, and then something that I did not expect…

The full database restore went very fast until it got to 92%. Then I saw a huge number of parallel reads by the MsMpEng.exe process (which is the Microsoft Antimalware service), which slowed the restore progress to an absolute crawl, also pegging one of the CPU cores on the instance (so it looks like that service is single-threaded).  This seemed to be a major factor in the slowness of the database restore as MsMpEng.exe process was scanning the 8,684 Filetable documents that were in this particular user database.

The solution to this issue was simply changing the exclusion rules for the Microsoft Antimalware Service to not scan the files in the filetable directories. This one change decreased the database restore time by 56%.

Here is the restore command and the results of the test.

-- Test restore of TestUserDatabase
-- This database uses Filetables
RESTORE DATABASE [TestUserDatabase] 
FROM  DISK = N'T:\BackupRestoreTest\TestUserDatabase_FULL.bak' WITH  FILE = 1,  
MOVE N'TestUserDatabase' TO N'D:\SQLDATA\TestUserDatabase.mdf',  
MOVE N'TestUserDatabase_log' TO N'L:\Logs\TestUserDatabase_log.ldf',  
MOVE N'DOCUMENTS' TO N'D:\SQLDATA\TestUserDatabase_DOCUMENTS',  NOUNLOAD,  STATS = 1;
GO

-- Actual elapsed time 6:21 (Before directory exclusion change)
--Processed 2358856 pages for database 'TestUserDatabase', file 'TestUserDatabase' on file 1.
--Processed 4 pages for database 'TestUserDatabase', file 'TestUserDatabase_log' on file 1.
--Processed 208905 pages for database 'TestUserDatabase', file 'DOCUMENTS' on file 1.
--RESTORE DATABASE successfully processed 2567764 pages in 378.708 seconds (52.971 MB/sec).


-- Actual elapsed time 2:52 (After directory exclusion change)
--Processed 2358856 pages for database 'TestUserDatabase', file 'TestUserDatabase' on file 1.
--Processed 4 pages for database 'TestUserDatabase', file 'TestUserDatabase_log' on file 1.
--Processed 208905 pages for database 'TestUserDatabase', file 'DOCUMENTS' on file 1.
--RESTORE DATABASE successfully processed 2567764 pages in 167.607 seconds (119.688 MB/sec).

 

Figure 1: T-SQL for Database Restore Test

 

One thing you might notice is that the filetable directory is on the same LUN as the PRIMARY data file for this database, which is not really the best location. Ideally this directory would be on a separate LUN from any other SQL Server database files, but this may not always be possible due to economic resource constraints.

Here is some more information about filestream performance:

High-performance FILESTREAM tips and tricks

Best Practices on FILESTREAM implementations

Upcoming Speaking Engagements

I found out on Friday that I will have two speaking slots at the PASS Summit 2015 in Seattle, WA. The annual PASS Summit is always a lot of fun and a great learning and networking opportunity.

 

I'm Speaking Graphic_Large

 

The first session will be a half-day session, which will let me go into much more detail about this subject:

Dr. DMV’s Troubleshooting Toolkit

Dynamic management views (DMVs) and functions allow you to easily see exactly what is happening inside your SQL Server instances and databases, with a high level of detail. You can discover your top wait types, identify the most CPU-intensive stored procedures, find missing indexes, and identify unused indexes, to name just a few examples. This session presents, demonstrates, and explains a complete set of diagnostic DMV queries that you can easily use to detect and diagnose configuration and performance issues in your SQL Server instances and databases. This session goes into exhaustive detail about how to interpret the results of each diagnostic query, including relevant background information about how to properly configure your hardware, storage subsystem, operating system, SQL Server instance, and databases to avoid many performance and scalability issues.

I will also be doing a regular 75 minute session:

High Availability/Disaster Recovery 101

How do you design a SQL Server 2014 infrastructure to meet specific Recovery Time Objective (RTO) and Recovery Point Objective (RPO) service levels? There are many aspects to consider, from technology choices and licensing to policies and procedures. This session outlines and compares the various high availability and disaster recovery technologies available in SQL Server 2014, showing how you can combine them to design and build a solution to help meet your goals. This session also teaches you how to formulate policies that enable effective use of technology in your organization.

The entire SQLskills team will be speaking at PASS Summit 2015, including Paul Randal, Kimberly Tripp, Jonathan Kehayias, Erin Stellato, and Tim Radney.

Before then, I will be speaking at a number of other events, some of which will be remote and some in person.

Pittsburg SQL Server User’s Group on Jun 30 at 5PM Eastern Time                    Dr. DMV’s Troubleshooting Toolkit

Denver WIT on July 8 at 5:30PM Mountain Time                                               Dr. DMV’s Troubleshooting Toolkit

Johannesburg SQL Server User Group on July 14 at 11AM Mountain Time           Dr. DMV’s Troubleshooting Toolkit

BIG PASS User’s Group on August 11 at 7PM Pacific Time                                 Dr. DMV’s Troubleshooting Toolkit

 

On Friday, September 18, 2015 I will be doing a full day pre-con for SQLSaturday #441 in Denver, CO. This should be very interesting and a lot of fun.

Analyzing and Improving I/O Subsystem Performance    

SQL Server is often I/O bound, but proving it to your storage or SAN administrator can be challenging! You will learn about the different types of storage that are available for SQL Server, and how to decide what type of storage to use for different SQL Server workload and file types. You will also learn useful tips and techniques for configuring your storage for the best performance and reliability for your workload. There will be extensive coverage on how to use disk benchmark tools like CrystalDiskMark 4.0, SQLIO and Microsoft DiskSpd, so you can confidently understand the performance that your I/O subsystem can deliver. We’ll also cover methods to effectively measure and monitor your storage performance from an OS and SQL Server perspective so that you will have valuable information and evidence available the next time you have to discuss I/O performance with your storage administrator. You will also learn a number of valuable OS and SQL Server configuration settings that will help you get the best I/O performance possible from your storage subsystem. 

Finally, I’ll be speaking at the Fall 2015 SQLintersection event in Las Vegas.

SQL Server 2014 RTM CU8 Available

Microsoft has released SQL Server 2014 RTM CU8, which is Build 12.0.2546. This cumulative update has 38 public hotfixes by my count. This CU may be less attractive now that Microsoft has also released SQL Server 2014 SP1 CU1. I say that because many people may be wanting to get on the SP1 branch, now that the first CU on top of SP1 has been released, and the branches are back in synch.

The official Microsoft build list for SQL Server 2014 is here.

SQL Server Diagnostic Information Queries for June 2015

This month, I have added a new, separate version of these queries for SQL Server 2016. This version will be improved with more SQL Server 2016-specific  queries and columns in existing queries in the near future.

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 two months ago, and so far, I have not heard any complaints.

Rather than having a separate blog post for each version, I have just put the links for all five 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. 

Here are links to the latest versions of these queries for SQL Server 2016, 2014 and 2012:

SQL Server 2016 Diagnostic Information Queries (June 2015)

SQL Server 2016 Blank Results

SQL Server 2014 Diagnostic Information Queries (June 2015)

SQL Server 2014 Blank Results

SQL Server 2012 Diagnostic Information Queries (June 2015)

SQL Server 2012 Blank Results

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

SQL Server 2008 R2 Diagnostic Information Queries

SQL Server 2008 R2 Blank Results

SQL Server 2008 Diagnostic Information Queries

SQL Server 2008 Blank Results

SQL Server 2005 Diagnostic Information Queries

SQL Server 2005 Blank Results

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 some comments in the script on how to interpret the results after 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 latest 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!

Interesting Upcoming Intel Processors

There has been quite a bit of leaked news and rumors about several future Intel processor families over the past couple of weeks, from what I consider to be pretty reliable sources. I’ll start out with the desktop and mobile processors and then move to the server processors.

Right now, it is a little unclear when the 14nm Intel Broadwell desktop processors will be released. These are a Tick release, built on the current Haswell microarchitecture, that was originally supposed to come out in late 2014. There were stories of early yield problems with the 14nm manufacturing process that caused Intel to delay the release in the desktop space. Low-power, mobile Broadwell processors have been available for several months now. The Core i7 Broadwell-U was released in Q1 of 2015, and these are typically used in high-end Ultrabooks, even through they only have two physical cores (plus hyperthreading).

My guess is that we might see higher power, mobile Broadwell-H and desktop Broadwell-S processors in the June-July 2015 timeframe. These will be compatible with the existing desktop LGA1150 socket and Z97 chipset motherboards that Haswell and Haswell Refresh processors used. The rumored models include the Core i7-5775C and Core i5-5675C. Personally, I plan on skipping Broadwell on the desktop, and waiting for Skylake.

Also in the mobile and desktop space (which is a useful preview of upcoming server processor families), there is news of the upcoming 14nm Skylake family being released in the August-September 2015 timeframe. Skylake is a Tock release (meaning a new microarchitecture) that will require a new LGA-1151 socket and a new Z170 chipset for enthusiast desktop machines. Supposedly, the unlocked enthusiast Skylake-S desktop processors (Core i7-6700K and Core i5-6600K) will be released sometime in August 2015. These are supposed to have at least 10% better performance than the current Haswell Refresh “Devil’s Canyon” processors in that same segment (Core i7-4790K and Core i5-4690K), even though the new processors will have lower power consumption and slightly lower clock speeds.

The mainstream Skylake-H for laptops is due for release in September of 2015, so if you are thinking about a new laptop, you might want to wait a few months. I definitely plan on building at least one Skylake-S desktop, pretty much as soon as they are available.

In the server processor space, there is a lot of recent new information. Back on May 5, 2015, Intel announced the Xeon E7 v3 family (Haswell-EX) that I talked about here. Next out of the gate will be the Xeon E5-4600 v3 family (Haswell-EP for four-socket servers), probably in Q4 2015, which I don’t think will be a good choice for SQL Server usage. This is because of the relatively poor scaling that I have seen in benchmark results for the earlier E5-4600 family processors. If you need to have a four-socket database server, a modern Xeon E7 v3 is a much better choice.

A more interesting introduction will be the 14nm Xeon E5-2600 v4 family (Broadwell-EP for two-socket servers), which will probably show up in Q1 or Q2 of 2016. This will be a Tick release, building on the Haswell microarchitecture that will have up to 22 physical cores and DDR4 2400 support. This processor should work in existing model servers such as the Dell PowerEdge R730.

Later in 2016, we should see the 14nm Xeon E7-4800/8800 v4 family (Broadwell-EX for four and eight-socket servers) that will have up to 24 physical cores.

Finally, in 2017, we should see a new 14nm Skylake server CPU that may merge the E5 and E7 lines into a single family, with up to 28 physical cores that will be part of the Purley platform which is detailed here and in Figure 1 below.

As these new processor families are released, with ever higher physical core counts, I really hope that Intel continues to have lower core count, “frequency-optimized” SKUs, with higher clock speeds and much lower SQL Server license costs.

 

Figure 1: Intel Server Platform Roadmap

Presenting Hardware 201 for the Denver SSUG

I did a live presentation at the May 21, 2015 meeting of the Denver SQL Server User’s Group, which was held at the newly remodeled Microsoft office in the Denver Tech Center.

Here is the abstract for my presentation:

Hardware 201: Selecting and Sizing Database Hardware for OLTP Performance

The foundation of database performance is the underlying server hardware and storage subsystem. Even the best designed and optimized database application can be crippled by an inadequate hardware and storage infrastructure. Recent advances in new processors and chipsets, along with improvements in magnetic and SSD storage have dramatically changed the evaluation and selection process compared to the past. Many database professionals struggle to keep up with new technology and often simply let someone else make their hardware selection and sizing decisions. Unfortunately, the DBA usually gets the blame for any performance issues that crop up later. Don’t let this happen to you! This session covers current and upcoming hardware from Intel and gives you the tools and resources to make better hardware selection decisions to support SQL Server OLTP workloads.

Here is a link to the PDF version of my presentation.

I was actually a last-minute substitution speaker for this meeting, and I was happy that I could help!

We actually did get a big turnout for the event. The Denver SSUG does a good job with their meetings, and they are a lot of fun. It is always a good idea to try to attend your local user’s group meetings, since you are likely to learn something and it is a very good opportunity to network.

If you are a SQL Server UG organizer, you might be interested in knowing that SQLskills has a number of speakers that regularly do remote presentations for user groups around the world. If you are interested in having one of us speak remotely at your event, please drop an e-mail to paul@sqlskills.com

SQL Server Diagnostic Information Queries for May 2015

I revised a number of the queries this month in just the SQL Server 2012 and 2014 versions of the script.  As usual, the main enhancements are additional comments and documentation about the queries, including more information about how to interpret the results, including some more links to relevant blog post articles. 

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 last month, and so far, I have not heard many complaints.

Rather than having a separate blog post for each version, I have just put the links for all five 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. 

Here are links to the latest versions of these queries for SQL Server 2014 and 2012:

SQL Server 2014 Diagnostic Information Queries (May 2015)

SQL Server 2014 Blank Results

SQL Server 2012 Diagnostic Information Queries (May 2015)

SQL Server 2012 Blank Results

 

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

SQL Server 2008 R2 Diagnostic Information Queries

SQL Server 2008 R2 Blank Results

SQL Server 2008 Diagnostic Information Queries

SQL Server 2008 Blank Results

SQL Server 2005 Diagnostic Information Queries

SQL Server 2005 Blank Results

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 some comments in the script on how to interpret the results after 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 latest 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!

Intel Xeon E7 v3 Product Family Released

On May 5, 2015, Intel announced the new E7 v3 Product Family, which is also known as the Haswell-EX, which is meant for four and eight-socket servers. This 22nm processor is a Tock release (which means that it is using a new processor microarchitecture compared to the previous 22nm Ivy Bridge-EX). An updated graphic that outlines the Tick-Tock development history for the Xeon 7xxx and E7 families is shown in Figure 1.

intel-xeon-tick-tock-model

Figure 1: Intel Tick-Tock Development Model

Intel only has 12 different processors in this product family, and of these, only four are really good choices from a licensing cost and performance perspective for SQL Server 2012 and newer. These are shown in Table 1.

Processor Cores Base Clock Turbo Clock L3 Size QPI Speed
E7-8893 v3 4 3.2 GHz 3.5 GHz 45 MB 9.6 GT/s
E7-8891 v3 10 2.8 GHz 3.5 GHz 45 MB 9.6 GT/s
E7-8867 v3 16 2.5 GHz 3.3 GHz 45 MB 9.6 GT/s
E7-8890 v3 18 2.5 GHz 3.3 GHz 45 MB 9.6 GT/s

Table 1: Preferred Xeon E7 v3 Family Processors for SQL Server 2012/2014/2016

I make this assertion because of how SQL Server 2012 and newer core-based licensing works. You pay for the number of physical cores in your system (on non-virtualized servers), and Microsoft does not care whether your processor cores are slow or fast, the license price is the same either way. Given this basic fact, you really should pick processors with faster cores (better single-threaded performance) at the same physical core count, to get the most performance possible for a given licensing cost. In some cases, you may be able get away with a lower core count processor that has faster individual cores, and still be able to support a similar overall total workload at a much lower SQL Server licensing cost.

The other eight processors in the E7 v3 Product Family all have lower specifications (at the same core count) than the four in my preferred list, so they are bad choices in my opinion. If you or someone else in your organization picks one of those other eight processors, you are not making a good choice. I am guessing that some people may argue that the slower processors at a given core count are less expensive, so they make sense in some situations.

My response would be that when you are talking about four or eight, high core count processors, the SQL Server 2012 and newer license costs are going to far outweigh the relatively small difference in the cost of a given processor. The single-threaded performance difference between a 2.1 GHz Xeon E7-8870 v3 ($7,175 each) and a 2.5 GHz Xeon E7-8890 v3 ($4,672 each) is likely to be about 20%, while the difference in total system cost will be much less than 20%.

You may notice that all of my recommended E7 v3 processors are E7-88xx processors, which means they are designed for eight-socket servers. That does not mean that you have to use them in an eight-socket server. You can actually use those E7-88xx processors in four-socket servers, which are much more common and much less expensive.

SQL Server Diagnostic Information Queries for April 2015

I revised several of the queries this month in only the SQL Server 2012 and 2014 versions of the script.  As usual, the main enhancements are additional comments and documentation about the queries, including more information about how to interpret the results. 

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. 

Rather than having a separate blog post for each version, I have just put the links for all five 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. 

Here are links to the latest version for SQL Server 2014 and 2012: 

SQL Server 2014 Diagnostic Information Queries (April 2015)

SQL Server 2014 Blank Results

 

SQL Server 2012 Diagnostic Information Queries (April 2015)

SQL Server 2012 Blank Results

 

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

SQL Server 2008 R2 Diagnostic Information Queries

SQL Server 2008 R2 Blank Results

 

SQL Server 2008 Diagnostic Information Queries

SQL Server 2008 Blank Results

 

SQL Server 2005 Diagnostic Information Queries

SQL Server 2005 Blank Results

 

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 some comments in the script on how to interpret the results after 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 latest 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 2014 RTM Cumulative Update 7 Available

Microsoft has released SQL Server 2014 RTM Cumulative Update 7, which is Build 12.0.2495. This cumulative update has 41 public hot fixes, several of which look pretty significant. I have updated my recent blog post Performance and Stability Related Fixes in Post-SQL Server 2014 RTM Builds to include CU7.

It probably comes as no surprise that I think that you should be making plans to get on this build after you have gone through your planning and testing process. I still run into many systems that are running the original RTM build of SQL Server 2014, which I think is a bad situation.