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 CU13: There have already been thirteen 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 2, CU3 (Build 11.0.5556), which was released on November 17, 2014.

If you are still on the SQL Server 2012 SP1 branch, then you want to be on SP1 CU13.

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
11.0.3470 SP1 CU12 September 15, 2014
11.0.3482 SP1 CU13 November 17, 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

SQL Server 2012 SP1 Cumulative Update 12 (Build 11.0.3470), 14 total public hotfixes

FIX: Error 7105 when you execute UPDATE on sparse column set while ONLINE INDEX REBUILD is running on the same table in SQL Server 2012

SQL Server 2012 SP1 Cumulative Update 13 (Build 11.0.3482), 10 total public hotfixes

FIX: High CPU consumption when you use spatial data type and associated methods in SQL Server 2012

FIX: SQL Server is in script upgrade mode for a long time after you apply a SQL Server hotfix or security update  

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.  If my opinion does not sway everyone, this relatively new Microsoft KB article might be more convincing:

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

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!

SQL Server 2014 RTM Cumulative Update 2

Microsoft has released SQL Server 2014 RTM Cumulative Update 2, which is Build 12.0.2370.0. It has 48 hotfixes in the public fix list.

This is one of the more interesting fixes:

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

As usual, I recommend that you install this CU if you are using SQL Server 2014, because of the relatively high number of significant fixes in this build.

Upcoming SQLskills Training Classes

As Paul announced last week, SQLskills has revamped the previous IE1 and IE2 classes to focus even more on performance tuning and optimization. If you are interested in how to select and configure database hardware for various workloads as well as how to configure your operating system and SQL Server for the best performance and scalability, you should consider attending my IEHW class in Chicago on October 9-10, 2014.

This is the schedule for the next round of SQLskills training classes:

2014 Immersion Events
Chicago, IL

  • October 6-8, 2014: IE0: Immersion Event for the Accidental/Junior DBA
  • October 9-10, 2014: IEHW: Immersion Event on SQL Server Hardware
  • October 6-10, 2014: IEPTO1: Immersion Event on Performance Tuning and Optimization – Part 1 (formerly IE1)
  • October 13-17, 2014: IEPTO2: Immersion Event on Performance Tuning and Optimization – Part 2 (formerly IE2)

See here for the main Immersion Event Calendar page that allows you to drill through to each class for more details and registration links.

Special Pricing Options and Referrals
  • Past attendee price: If you’ve attended an Immersion Event in the past, you can register any time for 75% of the full price ($1,099). Please contact us for instructions.
  • Refer someone: If you know someone who would benefit from this class, refer them to us and when they register, we’ll give you a $50 Amazon gift card. They or you just need to let us know you’re referring them, and when they register, we’ll match them to your referral and send you the gift card.

Fall SQLintersection
This year our Fall SQLintersection conference will be the week of November 10th in Las Vegas. See here for details. Don’t forget to use the discount code “SQLskills” (without the quotes and it isn’t case-sensitive) and you can save $50 off registration! I hope to see you there!

Learn About SQL Server Hardware This October in Chicago

On October 9-10, 2014, I will be teaching IEHW: Immersion Event on SQL Server Hardware in Chicago, IL. This is a great opportunity for you to learn how to properly select and configure your server hardware and storage subsystem to get the best performance and scalability for the lowest SQL Server 2012/2014 licensing costs. You will also learn how to properly configure your hardware, storage subsystem, operating system, and SQL Server itself for the best performance and scalability for your workload. Attendees of this class will learn how to analyze, select, and size their database server hardware and storage subsystems for different types of SQL Server workloads in order to get the best performance and scalability while minimizing their SQL Server 2012/2014 license costs.

Since SQL Server 2014 is available, and Windows Server 2012 R2 has been available for over nine months, and we have new server models with the 2nd generation Intel Xeon E5 and E7 processor families, I think that it is going to make a lot of sense for many organizations to do a complete data platform refresh sometime during 2014/2015. By making wise hardware selection choices, you can easily save so much money on your SQL Server 2012/2014 licensing costs, that your actual server hardware is free. This is not an exaggeration! 

This two-day SQL Server hardware training class explains the core fundamentals and deeper details of database server hardware and storage subsystems for SQL Server database professionals. Many database professionals are completely unfamiliar with the details and nuances of modern server hardware and storage subsystems, while many server and storage administrators are completely unfamiliar with the specific workload demands of a SQL Server database server. Taking this course will allow you to bridge that gap.

This class also covers how to properly configure and benchmark your database server hardware and storage subsystems, along with how to properly install and configure the operating system and SQL Server for the best performance and reliability. The class will show you how to diagnose and troubleshoot hardware and storage related performance issues, and will include coverage of how virtualization interacts with your database server hardware and storage subsystem. Note: the primary audience for this class is SQL Server database professionals, not general system/server admins who are already familiar with server/storage hardware.

I think it is very important for database administrators to know as much as possible about the critical details of their server hardware and storage subsystem, rather than trusting their fate to “Shon the server guy”, who may or may not know that much about modern server hardware (maybe he is a networking specialist). Even if Shon is very knowledgeable about hardware, he may not understand the different demands that SQL Server will create with different types of workloads. I want you to be able to successfully make the case for selecting the best hardware and storage subsystem components for your workload and budget.  You can read more about the registration details here.

Special Pricing Options and Referrals
  • Past attendee price: If you’ve attended an Immersion Event in the past, you can register any time for 75% of the full price ($1,099). Please contact us for instructions.
  • Refer someone: If you know someone who would benefit from this class, refer them to us and when they register, we’ll give you a $50 Amazon gift card. They or you just need to let us know you’re referring them, and when they register, we’ll match them to your referral and send you the gift card.