Getting the Best Performance From an Intel DC P3700 Flash Storage Card

I recently had the opportunity to work on a new Dell PowerEdge R720 system that has two, 2TB Intel DC P3700 PCIe Flash Storage Cards installed. This particular card is the largest capacity model of the high-end P3700 series (Intel has lower-end P3600 and P3500 cards in this same family). As with most flash storage, larger capacity devices typically have much better performance than lower capacity devices from the same product family because there are more NANDs to read and write to and there are more channels to use.

Initially, I was somewhat disappointed by the CrystalDiskMark results for this device, as shown in Figure 1. These results are not terrible, especially compared to most SANs or a single 6Gbps SAS/SATA SSD, but they were not nearly as good as I was expecting.

It turns out that Windows Server 2012 R2 has native NVMe support, with some generic, default drivers. These drivers let Windows recognize and use an NVMe device, but they do not give the best performance. Installing the native Intel drivers makes a huge difference in performance from these cards.

You will need to download and install the drivers first (which will require a reboot), and then you will want to download and install the Intel Solid State Drive Data Center Tool (which is a command-line only tool), so you can check out the card and update the firmware if necessary. The links for those two items are below:

Intel Solid-State Drive Data Center Family for PCIe Drivers
Intel Solid-State Drive Data Center Tool

You should also confirm that you are using the Windows High Performance Power Plan and that your BIOS is not using any power management settings that affect the voltage supplied to the PCIe slots in your server. Setting the BIOS power management to OS control or high performance is usually what you need to do, but check your server documentation.

clip image002 thumb Getting the Best Performance From an Intel DC P3700 Flash Storage Card

Figure 1: CrystalDiskMark Results with Default Microsoft Driver

Here are the relevant results in text form:

Sequential Read :   682.778 MB/s
Sequential Write :   700.335 MB/s
        
Random Read 4KB (QD=32) :   381.311 MB/s [ 93093.6 IOPS]
Random Write 4KB (QD=32) :   282.259 MB/s [ 68910.9 IOPS]

After installing the native Intel drivers and updating the firmware, CrystalDiskMark looks much better! This is SAN-humbling performance from a single PCIe card that is relatively affordable.

clip image0025 thumb Getting the Best Performance From an Intel DC P3700 Flash Storage Card

Figure 2: CrystalDiskMark Results with Native Intel Driver

Here are the relevant results in text form:

Sequential Read :  1547.714 MB/s
Sequential Write :  2059.734 MB/s
        
Random Read 4KB (QD=32) :   646.816 MB/s [157914.2 IOPS]
Random Write 4KB (QD=32) :   419.740 MB/s [102475.6 IOPS]

This is a pretty dramatic difference in performance and it is another reason why database professionals should be paying attention to the details of their hardware and storage subsystem. Little details like this are easy to miss, and I have seen far too many busy server administrators not notice them.

Setting Your Page Verify Database Option to CHECKSUM

One thing I still run into quite often are SQL Server 2005 and newer databases that have their Page Verify database option set to TORN_PAGE or NONE. The most common reason for this is that an older database that was originally created in SQL Server 2000 or older was upgraded to SQL Server 2005 or newer, and the Page Verify Option was left at the older and less effective TORN_PAGE value. I also run into instances where people have changed the Page Verify database option to NONE, thinking that this would have a dramatic beneficial effect on performance (which is not true).

From BOL: “When CHECKSUM is enabled for the PAGE_VERIFY database option, the SQL Server Database Engine calculates a checksum over the contents of the whole page, and stores the value in the page header when a page is written to disk. When the page is read from disk, the checksum is recomputed and compared to the checksum value that is stored in the page header. This helps provide a high level of data-file integrity.”

Paul Randal talked about some of the myths around page verify here. Kendra Little wrote a good post that demonstrates how CHECKSUM reacts to corruption here.

In my opinion, all of your databases should be using CHECKSUM for their Page Verify database option. You can easily query sys.databases to find out the status of the Page Verify database option for all of your databases with this query:

-- Get value of page verify option for all databases
SELECT name, page_verify_option_desc
FROM sys.databases;

 

If you have just a few databases, it is pretty easy to run code like this for each one, to change this option:

-- T-SQL to change Page Verify option to CHECKSUM for a single database
USE [master]
GO
ALTER DATABASE [AdventureWorks] SET PAGE_VERIFY CHECKSUM  WITH NO_WAIT;
GO

 

If you have a large number of databases that need to be changed, you can write a query to generate the ALTER DATABASE statements for you, like this:

-- Generate ALTER DATABASE statements to change Page Verify option to CHECKSUM
SELECT N'ALTER DATABASE [' + db.name + N'] SET PAGE_VERIFY CHECKSUM  WITH NO_WAIT;'
FROM sys.databases AS db 
WHERE db.page_verify_option_desc <> N'CHECKSUM';

 

After you run this query, you can copy the rows from your results grid in SSMS to a new query window, and then run your ALTER DATABASE statements when you are ready, without having to write all of the T-SQL code yourself.

Keep in mind that just changing the setting to CHECKSUM does not instantly add CHECKSUMs to your existing data pages in the database. In order for this to happen, you have to read each page into memory, make some sort of change and then write it back out to the storage subsystem. This can happen from normal INSERT/UPDATE/DELETE activity over time, or from rebuilding your indexes.

Here are some useful links on this subject:

Checksum in SQL2005

How to tell if the IO subsystem is causing corruptions?

Inside The Storage Engine: Does turning on page checksums discard any torn-page protection?

Checksum and tempdb

Performance impact of enabling page checksum and default trace

Analyzing I/O Performance from SQLSaturday #300

I had the opportunity to present Analyzing I/O Performance at SQLSaturday #300 in Kansas City, MO on September 13, 2014. I think the session went well, judging by the amount and type of questions that I got during and after the presentation, along with the written feedback forms that I read afterwards.

The overall event was well-run, and well-attended, with some good barbeque for lunch. I have a lot of respect for the organizers and volunteers for SQLSaturday events.

You can get a PDF version of my deck here, and the queries that I ran here.

One side benefit of this event was a chance to drive my red Tesla back and forth between Parker, CO and Kansas City, MO, using the free Tesla Supercharger network (and a 50 amp circuit at my sister’s house in Topeka, KS). I talk a little about this trip in these two blog posts:

Tesla Road Trip to SQLSaturday #300 in Kansas City

Tesla Model S Road Trip Results

SQL Server Diagnostic Information Queries for September 2014

I revised a number of the queries this month in all five versions of the script. I have also added several new queries to the SQL Server 2012 and SQL Server 2014 versions of the script. Here are the current query counts for each version:

SQL Server 2014         72 Queries

SQL Server 2012         69 Queries

SQL Server 2008 R2    65 Queries

SQL Server 2008         59 Queries

SQL Server 2005         51 Queries

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 query script, and the one below on the right is the matching blank results spreadsheet.  

SQL Server 2005 Diagnostic Information Queries

SQL Server 2005 Blank Results

SQL Server 2008 Diagnostic Information Queries

SQL Server 2008 Blank Results

SQL Server 2008 R2 Diagnostic Information Queries

SQL Server 2008 R2 Blank Results

SQL Server 2012 Diagnostic Information Queries

SQL Server 2012 Blank Results

SQL Server 2014 Diagnostic Information Queries

SQL Server 2014 Blank Results

The basic idea is that you should run each query in the set, one at a time (after reading the directions). 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.

You need to click on the top left square of the results grid in 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. There are also some comments on how to interpret the results after each query.

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.

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.

It is also 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 want to understand how to better run and interpret these queries, you should consider listening to my latest Pluralsight course, which is SQL Server 2014 DMV Diagnostic Queries – Part 1. This course is short and to the point (only 67 minutes), and I think you will enjoy it!

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

SQL Server 2012 SP1 CU12 and SP2 CU2 Released

Microsoft has released SQL Server 2012 Service Pack 1 CU12 (Build 11.0.3470) and SQL Server 2012 Service Pack 2 CU2 (Build 11.0.5548). Both of these CUs (which were released on the same day) have a significant number of valuable hotfixes. SP1 CU12 has 14 hotfixes, and SP2 CU2 has 50 hotfixes.

Needless to say, I think you should be planning on getting to the SQL Server 2012 SP2 branch if you are not already there. You should not just go to SP2 RTM (Build 11.0.5058), but all the way to SP2 CU2.

I am hoping that more and more of the CU-averse individuals and organizations will be convinced to start to install SQL Server Cumulative Updates after seeing this new Microsoft KB article:

Recommended updates and configuration options for SQL Server 2012 and SQL Server 2014 used with high-performance workloads

Intel Xeon E5-2600 v3 Product Family and SQL Server

Intel finally announced their latest 22nm Xeon E5 v3 Product Family (Haswell-EP) today, which includes 27 different processor models (SKUs) for both one and two-socket servers. These SKUs go from four-core models, all the way up to eighteen-core models. This is an Intel Tock release, meaning a new microarchitecture, but still using the 22nm manufacturing process. We have previously seen the release of Haswell in the mobile space, mainstream desktop and enthusiast desktop space, so now it is time for one and two-socket servers to get Haswell-EP.

With SQL Server 2012/2014 Enterprise Edition, you must use core-based licensing, with a minimum of four physical cores per processor. Each one of those core licenses is relatively expensive, so you want to get the highest performance possible out of each physical core. When you are selecting a processor for SQL Server 2012/2014 it is foolish, false economy to select a lower-end, slower processor (with the same core-count) as a higher-end processor (with the same core count) in order to save a fairly small amount of money on hardware costs. Microsoft charges the same per core license cost regardless of the performance of the core.

Table 1 shows the “best” processor models for SQL Server, at the different physical counts, where you would get the best performance for a given core count. Keep in mind, as you go down in your core count per processor, from 18 to 16 for example, you would be saving twice that amount in core license costs with a two-socket server with both processor sockets populated. Saving the cost of four Enterprise Edition core licenses would pretty much pay for the base hardware cost of a nicely equipped server (not including any high-end flash storage).

Processor Cores Base Clock Turbo Clock L3 Cache
E5-2699 v3 18 2.3GHz 3.6GHz 45MB
E5-2698 v3 16 2.3GHz 3.6GHz 40MB
E5-2697 v3 14 2.6GHz 3.6GHz 35MB
E5-2690 v3 12 2.6GHz 3.5GHz 30MB
E5-2660 v3 10 2.6GHz 3.3GHz 25MB
E5-2667 v3 8 3.2GHz 3.6GHz 20MB
E5-2643 v3 6 3.4GHz 3.7GHz 20MB
E5-2637 v3 4 3.5GHz

3.7GHz 15MB

Table 1: Selected Intel Xeon E5-2600 v3 Processor Specifications

 

Intel claims that the Haswell-EP processors have an improved Turbo Boost, so that they will spend more time with more cores running close to or at full Turbo clock speed. One processor model I really like, especially for budget-minded organizations is the six-core E5-2643 v3, which has a very high Base and Turbo Clock speed, along with 20MB of L3 cache (the same as the eight-core E5-2667 v3).

You also don’t want to forget that both SQL Server 2012 and SQL Server 2014 Standard Edition still have an artificially low core count restriction of four sockets or 16 physical cores (whichever is lower). SQL Server 2012 Standard Edition has a RAM limit of 64GB for the database Engine, while SQL Server 2014 Standard Edition has a RAM limit of 128GB. These limits are all per instance, not per server.

These processors require new model servers, since they are not electrically or physically compatible with the preceding E5-2600 or E5-2600 v2 Product Families. All of the major server vendors have also announced new models that will use the Haswell-EP processor.

Small Enhancement to Microsoft SQL Server CU Knowledge Base Articles

Microsoft has made a small, but helpful improvement to the format for their Knowledge Base articles that accompany new Cumulative Updates for SQL Server 2012 and SQL Server 2014. As you hopefully know, there will not be any more cumulative updates for SQL Server 2008 or SQL Server 2008 R2, since those versions are now out of mainstream support.

There is now a new column in the hotfix table that lists the major functional area that the hotfix applies to (see Figure 1). Having this information readily available and visible can help you focus your efforts as you scan the overall hotfix list looking for relevant fixes for the SQL Server  components you are using, which is something you should be doing when each new Cumulative Update is released.

The fix area information is generated automatically (probably by VSTS), so it may not be 100% accurate, but it is certainly a good start.

clip image001 thumb Small Enhancement to Microsoft SQL Server CU Knowledge Base Articles

Figure 1: Recent SQL Server 2014 CU KB Article

SQL Server 2014 RTM Cumulative Update 3

Microsoft has released (about a week early by my calendar) SQL Server 2014 RTM Cumulative Update 3, which is build 12.0.2402. It has 32 fixes in the public fix list. As always, I think that you should strongly consider applying this update to your SQL Server 2014 instances, after you have done your normal round of application testing.

Based on some of the public hints that Microsoft has made, they may be moving away from using Service Packs to service SQL Server in the future, so I think it makes even more sense to get yourself and your organization ready to consider applying Cumulative Updates in order to maintain your SQL Server instances. This means having some sort of formal testing process for your applications and a plan for how you go about patching your SQL Server machines to minimize your downtime and reduce your risk.

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

Updated to include SP1 CU11: There have already been eleven Cumulative Updates (CU) for the Service Pack 1 branch of SQL Server 2012. There have been a fairly high number of hotfixes in every one of these Cumulative Updates, as more people are using SQL Server 2012 over the past two years. SQL Server 2012 SP1 has all of the fixes through SQL Server 2012 RTM CU2. If you are running SQL Server 2012, I really think you should be running the latest SQL Server 2012 Service Pack and Cumulative Update. Right now, that means Service Pack 1, CU11 (Build 11.0.3449). I am advising people to hold off on SQL Server 2012 Service Pack 2 until SQL Server 2012 SP2 CU1 is released (which finally happened on July 23, 2014).

This table shows the SP1 CU builds that have been released so far.

Build Description Release Date
11.0.3000 SP1 RTM November 7, 2012
11.0.3321 SP1 CU1 November 20, 2012
11.0.3339 SP1 CU2 January 21, 2013
11.0.3349 SP1 CU3 March 18, 2013
11.0.3368 SP1 CU4 May 30, 2013
11.0.3373 SP1 CU5 July 15, 2013
11.0.3381 SP1 CU6 September 16, 2013
11.0.3393 SP1 CU7 November 18, 2013
11.0.3401 SP1 CU8 January 20, 2014
11.0.3412 SP1 CU9 March 17, 2014
11.0.3431 SP1 CU10 May 19, 2014
11.0.3449 SP1 CU11 July 21, 2014
     

Table 1: SQL Server 2012 SP1 CU Builds

 

You can follow the KB article link below to see all of the CU builds for the SQL Server 2012 Service Pack 1 branch.

The SQL Server 2012 builds that were released after SQL Server 2012 Service Pack 1 was released

Like I did in my previous post, I decided to scan the hotfix list for all of the Cumulative Updates in the SP1 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 SQL Server 2012 features you are using.

Here are the fixes in the RTM branch, before Service Pack 1 was released (you will get these when you install SP1):

SQL Server 2012 RTM Cumulative Update 1  (Build 11.0.2316) , 65 total public hot fixes

FIX: Low CPU spikes at a set interval even without user activity in SQL Server 2008 R2 or in SQL Server 2012

FIX: Slow performance when an AFTER trigger runs on a partitioned table in SQL Server 2008 R2 or in SQL Server 2012

FIX: It takes a long time to restore a database in SQL Server 2008 R2 or in SQL Server 2008 or in SQL 2012

FIX: Poor performance when you run a query that contains correlated AND predicates in SQL Server 2008 or in SQL Server 2008 R2 or in SQL Server 2012

FIX: Slow performance occurs in SQL Server 2008 R2 or in SQL Server 2012 if high CPU usage is observed with contention over the QUERY_EXEC_STATS spinlock

FIX: Out-of-memory error when you run SQL Server 2012 on a computer that uses NUMA

SQL Server 2012 RTM Cumulative Update 2  (Build 11.0.2325) , 37 total public hot fixes

FIX: An access violation occurs intermittently when you run a query against a table that has a columnstore index in SQL Server 2012

Here are the fixes in the Service Pack 1 branch:

SQL Server 2012 SP1 Cumulative Update 1 (Build 11.0.3321), 44 total public hot fixes

FIX: “out of memory” error when you use ODBC with SQL Server 2012, SQL Server 2008 R2 or SQL Server 2008

FIX: Performance of a SELECT statement that contains a LIKE operator and an ESCAPE clause is low in SQL Server 2008 R2 or in SQL Server 2012

FIX: SQL Server 2012, SQL Server 2008 R2 or SQL Server 2008 stops responding and a “Non-yielding Scheduler” error is logged

FIX: Slow performance or deadlock when you restore a database and execute statements at the same time in SQL Server 2012

FIX: Worker threads do not wake up immediately when multiple I/O-intensive tasks are running at the same in SQL Server 2012

FIX: “Process appears to be non-yielding on Scheduler ” error message when you run a query in SQL Server 2012

SQL Server 2012 SP1 Cumulative Update 2 (Build 11.0.3339), 50 total public hot fixes

A database transaction log continues to grow after you upgrade to SQL Server 2012, SQL Server 2008 R2 SP1, SQL Server 2008 SP2 or SQL Server 2008 SP3

FIX: The size of a database file is not reduced when you use the DBCC SHRINKFILE command in SQL Server 2008 R2 or in SQL Server 2012

FIX: High CPU usage when you query a binary large object column by using the NOLOCK hint in SQL Server 2008 R2 or in SQL Server 2012

FIX: Large queries that modify data run slower than expected when many locks accumulate in a SQL Server 2008 R2 or in SQL Server 2012

FIX: A transaction log restore operation takes longer than expected and a 3402 error occurs if a database file is removed in SQL Server 2008 R2 or in SQL Server 2012

SQL Server 2012 experiences out-of-memory errors

FIX: Deadlocks occur when you execute a stored procedure to alter a temporary table if lock partitioning is enabled in SQL Server 2008 R2 or in SQL Server 2012

FIX: Memory leak if you enable the AUTO_UPDATE_STATISTICS_ASYNC statistics option in SQL Server 2008, in SQL Server 2012 or in SQL Server 2008 R2

FIX: A “non-yielding” error occurs on a server for Service Broker Message Forwarding in SQL Server 2008 R2 or in SQL Server 2012

FIX: Error 17883 when you run a query on a server that has many CPUs and a large amount of memory in SQL Server 2012

SQL Server 2012 SP1 Cumulative Update 3 (Build 11.0.3349), 38 total public hot fixes

FIX: Access violation when you run a query that contains many constant values in an IN clause in SQL Server 2008 or in SQL Server 2012

FIX: Memory leak when you run queries against a temporary table in a nested stored procedure in SQL Server 2012

FIX: Poor performance in SQL Server 2012 when you run a SQL Server trace

FIX: You experience poor performance when you run a query against an RCSI-enabled table in SQL Server 2012

FIX: CPU spike when there is no load on a server after you install SQL Server 2012 on the server

SQL Server 2012 experiences performance issues in NUMA environments

SQL Server 2012 SP1 Cumulative Update 4 (Build 11.0.3368), 38 total public hot fixes

FIX: Out-of-memory errors related to a memory clerk in SQL Server 2012

FIX: Out of memory error when you build a columnstore index on partitioned tables in SQL Server 2012

Update that improves the Service Broker when you send messages to remote servers in SQL Server 2012 is available

An update is available for SQL Server 2012 Memory Management

FIX: “Non-yielding Scheduler” error occurs when you insert a row in SQL Server 2012

FIX: You may experience performance issues in SQL Server 2012

SQL Server 2012 SP1 Cumulative Update 5 (Build 11.0.3373), 27 total public hot fixes

FIX: Poor performance when you run a query that uses the LIKE operator in the WHERE clause in SQL Server 2008 R2

FIX: Non-yielding scheduler error when you run a stored procedure that uses a TVP in SQL Server 2012

SQL Server 2012 SP1 Cumulative Update 6 (Build 11.0.3381), 24 total public hot fixes

FIX: Slow performance on SQL Server 2012 linked server when you update the data together with different collation on the remote server

FIX: A memory leak occurs when you enable AlwaysOn Availability Groups or SQL Server failover cluster in Microsoft SQL Server 2012

SQL Server 2012 SP1 Cumulative Update 7 (Build 11.0.3393), 47 total public hotfixes

FIX: Access violation when you trace an RPC event class by using SQL profiler or XEvents in SQL Server 2012

FIX: Nonclustered index corruption may occur when you run a complex UPDATE statement together with a NOLOCK hint against a table in SQL Server 2008, SQL Server 2008 R2 or SQL Server 2012

FIX: The threads are not scheduled evenly in SQL Server 2012 Standard Edition

A memory leak occurs when a SQL Server Native Client OLE DB provider application calls the Prepare method in SQL Server 2012

FIX: “Non-yielding Resource Monitor” when you run a workload that executes concurrent queries in SQL Server 2012

FIX: Function sys.fn_hadr_backup_is_preferred_replica costs almost all the CPU usage in Log Shipping in SQL Server 2012

FIX: Slow performance in SQL Server when you build an index on a spatial data type of a large table in a SQL Server 2012 instance

FIX: Slow performance in SQL Server 2012 when you build an index on a spatial data type of a large table

An access violation occurs when “sys.dm_db_index_physical_stats” is used in an IF EXISTS statement in an SQL query in SQL Server 2012

FIX: Suboptimal execution plan is generated when you run a query in SQL Server 2012

Slow SQL Server performance and a memory leak occurs after you apply Cumulative Update 3 for SQL Server 2012 Service Pack 1

FIX: Slow performance in SQL Server 2012 when you build an index on a spatial data type of a large table

Slow performance or error messages are logged when you have a transactional replication publisher server in SQL Server 2012

FIX: Insufficient system memory error occurs when you try to create an index in a char, varchar, or nvarchar type column in SQL Server 2012

SQL Server 2012 SP1 Cumulative Update 8 (Build 11.0.3401), 32 total public hotfixes

FIX: Data purity corruption in sys.sysbinobjs table in master database when you log on to SQL Server 2008 R2 or SQL Server 2012 by using the SA account and then run DBCC CHECKDB

FIX: Interrupted distributed query may return partial result set without any error in SQL Server 2008 R2 or in SQL Server 2012

FIX: Replication Log Reader Agent fails when you enable CDC and transactional replication for some columns in SQL Server 2008 R2 or SQL Server 2012

FIX: Query that you run against a partitioned table returns incorrect results in SQL Server 2008, SQL Server 2008 R2 or SQL Server 2012

FIX: A small number of rows are sampled in SQL Server 2008 R2 or in SQL Server 2012 when table statistics are updated automatically or together with specified sampling rate

FIX: Access violation may occur when you query sys.dm_server_memory_dumps in SQL Server 2008 R2 or SQL Server 2012

FIX: Replication Queue Reader Agent crashes when you run transactional replication in SQL Server 2008 R2 or SQL Server 2012

FIX: Validation of service accounts takes a long time when you set up SQL Server 2012

FIX: The system function sys.fn_hadr_backup_is_preferred_replica does not work correctly after you have CU7 for SQL Server 2012 SP1 installed

FIX: The query deadlocks when the ALLOW_SNAPSHOT_ISOLATION and READ_COMMITTED_SNAPSHOT are enabled in SQL Server 2012

FIX: The clustered index table may take longer than you expect to be rebuilt when you use the ALTER INDEX REBUILD statement in SQL Server 2008 or SQL Server 2012

SQL Server 2012 SP1 Cumulative Update 9 (Build 11.0.3412), 30 total public hotfixes

FIX: Access violation in SQL Server internal deadlock monitor when you run multiple applications together with Multiple Active Result Sets enabled in SQL Server 2008 R2 or in SQL Server 2012

FIX: Access violation in replication Distribution Agent in SQL Server 2008 R2 or SQL Server 2012 Transactional Replication

FIX: Poor performance when many table-valued parameters are used in SQL Server 2008 R2 or SQL Server 2012

FIX: Merge Agent fails or you experience non-convergence when you use a custom stored procedure conflict resolver in SQL Server 2008 or SQL Server 2008 R2 or SQL Server 2012

FIX: Performance problems occur when database lock activity increases in SQL Server 2012

FIX: Performance problems occur in NUMA environments during foreign page processing in SQL Server 2012

FIX: Operating system version information that is returned from SQL Server 2008 or SQL Server 2008 R2 or SQL Server 2012 is incorrect in Windows 8.1

FIX: Performance decreases after an ALTER INDEX…ONLINE operation is aborted in SQL Server 2012

FIX: Access violation may occur when trigger query joins large dataset in deleted/inserted table and runs in parallel in SQL server 2012

FIX: Database shows “recovery pending” state when you use TDE with EKM provider in SQL Server 2012

FIX: Incorrect usage of built-in FORMAT function brings down SQL Server 2012 Instance

FIX: SQL Server 2012 instance shuts down when you join database as secondary replica during AlwaysOn Availability Groups configuration

FIX: Merge agent fails when you apply snapshot in SQL Server 2012 instance if cross database dependencies are present

FIX: Distribution Agent skips applying sp_MSins_, sp_MSupd_, sp_MSdel_ stored procedures on a Subscriber that is initialized with Backup in SQL Server 2012

FIX: Access violation occurs when you update a table that has an XML calculated column in SQL Server 2012

FIX: SQL Server takes long time to open the databases after the recovery phase when the number of databases or database files or both is large in SQL Server 2012

FIX: Message is not removed from transmission queue even though the ACK is received successfully in an AlwaysOn availability group in SQL Server 2012

FIX: Non-yielding scheduler tries to close the listener for availability group in SQL Server 2012

SQL Server 2012 SP1 Cumulative Update 10 (Build 11.0.3431), 36 total public hotfixes

FIX: Intense query compilation workload does not scale with growing number of cores on NUMA hardware and results in CPU saturation in SQL Server 2012

FIX: Assertion failure when you execute a query specifying TOP N and ORDER BY in SQL Server 2008 R2 or SQL Server 2012

FIX: Access violation occurs when you run CHECKTABLE or CHECKDB against a table that has persisted computed columns in SQL Server 2008 R2 or SQL Server 2012

FIX: Incorrect results when you run queries that contain UNION operator by using parallel query plan in SQL Server 2012

FIX: An access violation occurs when you execute update query on a table that has a DML trigger in SQL Server 2008 R2 or SQL Server 2012

FIX: Access violation when the start offset is larger than the string length in function Substring in SQL Server 2012

FIX: Poor cardinality estimation when the ascending key column is branded as stationary in SQL Server 2012

FIX: Poor performance on I/O when you execute select into temporary table operation in SQL Server 2012

FIX: Parallel deadlock or self-deadlock occurs when you run a query that results in parallelism in SQL Server 2012

SQL Server 2012 SP1 Cumulative Update 11 (Build 11.0.3449), 32 total public hotfixes

FIX: “Cannot insert duplicate key” error occurs in Spool operation when you update a table in SQL Server 2012

FIX: Incorrect result when you execute a query that uses WITH RECOMPILE option in SQL Server 2012

FIX: Memory leak occurs when you start and stop an XEvent session repeatedly in SQL Server 2012

FIX: Cannot reclaim unused space by using shrink operation in the table that contains a LOB column in SQL Server

FIX: Fatal exception when you run a query that contains CLR functions against an indexed view in SQL Server 2012

FIX: Undetected deadlock occurs when you use a sequence object in SQL Server 2012

FIX: Data corruption occurs in clustered index when you run online index rebuild in SQL Server 2012 or SQL Server 2014

FIX: The database sticks in role configuration phase when you perform a manual failover of a mirrored database

FIX: Error when you execute a Transact-SQL query that contains a long case statement in SQL Server 2012

FIX: Error when you insert a record into a FileTable or FILESTREAM in SQL Server 2012

FIX: Log Reader Agent crashes during initialization when you use transactional replication in SQL Server 2012

FIX: Poor performance when you create spatial index on Point data in SQL Server 2012

FIX: Log Reader Agent fails when you upgrade from SQL Server 2008 to SQL Server 2012

FIX: A severe error occurs when you run a query that uses a hash join parallel execution plan in SQL Server 2012

  

Once again, the idea here is to give you a lot of concrete reasons to want to stay current with the latest SQL Server 2012 SP and CU, by pointing out some of the more valuable fixes in each CU in the Service Pack 1 branch.

SQL Server Diagnostic Information Queries for July 2014

I revised several of the queries this month in all five versions of the script. There are also two new queries for SQL Server 2014, and one new query in the SQL Server 2008 through SQL Server 2012 versions of the script.

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 query script, and the one below on the right is the matching blank results spreadsheet.  

SQL Server 2005 Diagnostic Information Queries

SQL Server 2005 Blank Results

SQL Server 2008 Diagnostic Information Queries

SQL Server 2008 Blank Results

SQL Server 2008 R2 Diagnostic Information Queries

SQL Server 2008 R2 Blank Results

SQL Server 2012 Diagnostic Information Queries

SQL Server 2012 Blank Results

SQL Server 2014 Diagnostic Information Queries

SQL Server 2014 Blank Results

The basic idea is that you should run each query in the set, one at a time (after reading the directions). You need to click on the top left square of the results grid in 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. There are also some comments on how to interpret the results after each query.

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.

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.

It is also 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 want to understand how to better run and interpret these queries, you should consider listening to my latest Pluralsight course, which is SQL Server 2014 DMV Diagnostic Queries – Part 1. This course is short and to the point (only 67 minutes), and I think you will enjoy it!

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