SQL Server Diagnostic Information Queries for June 2014

I added several new queries this month to all five versions of the script. I also spent quite a bit of time getting all five versions back in synch as much as possible, as far as my comments and other minor differences that had cropped up over the last few months.

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 2008 Service Pack 3 Cumulative Update 17

On May 19, 2014, Microsoft released SQL Server 2008 Service Pack 3 Cumulative Update 17, which is Build 10.00.5861.0. This Cumulative Update has nine hotfixes in the public fix list, most of them for the Database Engine.

The most interesting one to me is this one:

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

This issue is going to affect systems with 16 or more logical processors, running on SQL Server 2008 or newer. The fix has been released for SQL Server 2008, SQL Server 2012 and SQL Server 2014, but not for SQL Server 2008 R2 (since SQL Server 2008 R2 SP2 CU13 has not been released yet). This hotfix introduces DATABASE lock partitioning when trace flag T1236 is enabled at startup.

One thing that you may not be aware of is that this is the final Cumulative Update for SQL Server 2008. As Microsoft states:

Note This is the last cumulative update for SQL Server 2008. Mainstream support for SQL Server 2008 ends on July 8, 2014. For more information about the lifecycle for SQL Server, check out the Microsoft product support life cycle information

So far, Microsoft has not announced their plans about whether there will be a SQL Server 2008 Service Pack 4. While you might assume that it is now too late to affect Microsoft’s decision process, this is actually not the case. First, you can comment on the SQL Server Release Services Blog post that I linked above.

Second, you can up vote my Microsoft Connect item on this subject. Even more effective is if you write a polite, well-reasoned comment on that Connect Item, explaining why you care whether or not Microsoft releases a final set of Service Packs for SQL Server 2008 and SQL Server 2008 R2. Try to explain how this decision will affect you and your customers.

Taking just a few minutes to do these items is very, very helpful to this effort!  You should also feel free to blog and tweet about this to help spread the word and gather more public support.

Microsoft DreamSpark for Students and Educators

Back in 2008, Microsoft rolled out the DreamSpark program as a means to get Microsoft software design and development tools in the hands of students at no charge. It was initially only for university or college students, but it has been expanded to also cover high school students. Basically, this means that any high school, college student, or faculty member (in most major countries) can download copies of many Microsoft operating systems, servers, and development tools for free.  If you fall into one of those categories, it is pretty easy to get signed up and verified into the program, so you can get your hands on the software for free.

Of course Microsoft is not doing this simply for altruistic reasons. They want more people to be familiar with Microsoft products and technologies, so that they are more likely to use them and recommend them in the future. There is nothing wrong with that, and if you are a student or educator, you might as well take advantage of the program!

Some of the most valuable software-related items are Visual Studio 2013 Professional, Windows Server 2012 R2 Standard, and SQL Server 2014 Developer Edition. You can also get a free 90-day subscription to Pluralsight On-Demand, a number of free eBooks from Microsoft Press, along with discount vouchers for Microsoft Certification exams.

The Microsoft DreamSpark site has more details about the program.

Performance Related Fixes in Post-SQL Server 2008 Service Pack 3 Builds

As you may be aware, Microsoft released SQL Server 2008 Service Pack 3 back around October of 2011. Even though there has not been an official announcement yet from Microsoft, it seems likely that there will not be a SQL Server 2008 Service Pack 4 release before SQL Server 2008 (and SQL Server 2008 R2) go out of Mainstream support on July 8, 2014.

If you have moved to SQL Server 2008 Service Pack 3 (which is the only currently supported Service Pack for SQL Server 2008) and are patiently waiting for SQL Server 2008 Service Pack 4, you might want to know that Microsoft has announced that there will be a SQL Server 2008 Service Pack 4, that will be available sometime after July 8, 2014. This final Service Pack will include all of the fixes through SQL Server 2008 Service Pack 3 Cumulative Update 17 (and nothing else in addition).

Personally, I think most organizations would be much better off by moving to the latest Service Pack 3 Cumulative Update now, or going to Service Pack 4 when it becomes available. In order to try to convince more people that this is the right thing to do, I have put together this list of what I consider to be the more important hotfixes that have been included in each post-SQL Server 2008 Service Pack 3 Cumulative Update. This list is very Database Engine focused, and completely arbitrary on my part. If you are running SQL Server 2008, you should look at the KB articles for each Cumulative Update (which are linked below), and look for hotfixes that are most relevant to your situation and what SQL Server components that you are using.

Updated to include SQL Server 2008 SP3 CU17. There will not be a SQL Server 2008 SP3 CU18 release, CU17 is the final CU for SQL Server 2008.

 

SQL Server 2008 SP3 CU1 (10.0.5766)  October 17, 2011 (19 public fixes)

FIX: Certain change tracking functions and DMVs do not work correctly after an automatic database mirroring failover occurs in SQL Server 2008 R2 or in SQL Server 2008

 FIX: Recovery takes longer than expected for a database in a SQL Server 2008 or in a SQL Server 2008 R2 environment

FIX: Errors when client application sends an attention signal to SQL Server 2008 or SQL Server 2008 R2

FIX: Size of a data file that is committed by online page compression is larger than that is committed by offline page compression in SQL Server 2008 or in SQL Server 2008 R2 

 

SQL Server 2008 SP3 CU2 (10.0.5768)  November 21, 2011 (2 public fixes)

FIX: Access violation when you insert a record into a new empty partition in SQL Server 2008

 

SQL Server 2008 SP3 CU3 (10.0.5770)  January 16, 2012 (14 public fixes)

FIX: Access violation when you run a DBCC CHECKDB command against a database that contains a table that has a spatial index in SQL Server 2008 or in SQL Server 2008 R2

FIX: Access violation when a query is compiled or recompiled in SQL Server 2008 or in SQL Server 2008 R2

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

 

SQL Server 2008 SP3 CU4 (10.0.5775)  March 19, 2012 (10 public fixes)

FIX: “A time-out occurred while waiting for buffer latch” error when many transactions concurrently update a database in SQL Server 2008 R2 or in SQL Server 2008 if the database uses the snapshot isolation level

FIX: Transaction log backup is created even though the full backup is canceled in SQL Server 2008 or in SQL Server 2008 R2

FIX: ghost_record_count values keep increasing in SQL Server 2008 R2 or in SQL Server 2008

Improvements for the DBCC CHECKDB command may result in faster performance when you use the PHYSICAL_ONLY option

FIX: Backup operation fails on a SQL Server 2008 database after you enable change tracking

 

SQL Server 2008 SP3 CU5 (10.0.5785) May 21, 2012 (4 public hotfixes)

FIX: Slow performance when you restore a database in SQL Server 2008 R2 or in SQL Server 2008 if CDC is enabled

FIX: SQL Server Agent job randomly stops when you schedule the job to run past midnight on specific days in SQL Server 2005, in SQL Server 2008 or in SQL Server 2008 R2

 

SQL Server 2008 SP3 CU6 (10.0.5788)  July 16, 2012 (14 public hotfixes)

FIX: Errors when a client application sends an attention signal to SQL Server 2008 or SQL Server 2008 R2

FIX: A database page is copied into a database snapshot even though the page is not updated when you perform a read operation in SQL Server 2008 or in SQL Server 2008 R2

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

FIX: Incorrect results are returned when you run a query that uses parallelism in the query execution plan in SQL Server 2008

 

SQL Server 2008 SP3 CU7 (10.0.5794)  September 17, 2012 (11 public hotfixes)

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

FIX: Error is logged when you start SQL Server 2008 or when a client sends a request to the program

FIX: High CPU usage when you execute an UPDATE statement that includes a “WHERE CURRENT OF <cursor>” clause in SQL Server 2008

FIX: Access violation or incorrect result when you insert data into or update a new partition of a partitioned table in SQL Server 2008 R2 or SQL Server 2008

FIX: SQL Server Agent job fails if you set the job schedule type as “Start automatically when SQL Server Agent starts” in SQL Server 2008 SP2, SQL Server 2008 SP3 or SQL Server 2008 R2 SP2

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 

 

SQL Server 2008 SP3 CU8 (10.0.5828)  November 19, 2012 (9 public hotfixes)

FIX: Incorrect result is returned when you query an indexed view by using the NOEXPAND hint in SQL Server 2008

Support for the varchar(max), nvarchar(max), and varbinary(max) data types is added in most OLE DB Providers in SQL Server 2008

FIX: “Non-yielding Scheduler” error message when you insert a record that contains large object (LOB) data into a snapshot database in SQL Server 2008

FIX: Memory leak if you enable the AUTO_UPDATE_STATISTICS_ASYNC statistics option in SQL Server 2008

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

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

 

SQL Server 2008 SP3 CU9 (10.0.5829)  January 21, 2013 (3 public hotfixes)

FIX: Installation or upgrade process fails when you install or upgrade a SQL Server 2008 clustered instance on mount points

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

 

SQL Server 2008 SP3 CU10 (10.0.5835)  March 19, 2013 (8 public hotfixes)

FIX: Access violation or incorrect result when you insert data into or update a new partition of a partitioned table in SQL Server 2008 R2 or SQL Server 2008

FIX: Poor performance when table-valued functions use many table variables in SQL Server 2008 R2 or SQL Server 2008

FIX: Access violation when you run DML statements against a table that has partitioned indexes in SQL Server 2008 R2, in SQL Server 2008 or in SQL Server 2012

FIX: Data corruption occurs when LOB data is loaded into a Transact-SQL variable in SQL Server 2008 R2 or in SQL Server 2008

FIX: Index size increases significantly after you rebuild the index online and RCSI is enabled in SQL Server 2008

 

SQL Server 2008 SP3 CU11 (10.0.5841)  May 20, 2013 (6 public hotfixes)

FIX: “Access is denied. [0x80070005]” error message occurs in SSCM when you try to change the password of an account of SQL Server 2008 or SQL Server 2008 R2 in Windows Vista or in a later version of Windows

FIX: SQL Server may freeze when an instance of SQL Server 2012, SQL Server 2008 or SQL Server 2008 R2 is shut down

 

SQL Server 2008 SP3 CU12 (10.0.5844)  July 15, 2013 (6 public hotfixes)

FIX: A query that runs against a DMV enters an infinite loop in SQL Server 2008 R2, SQL Server 2008 or SQL Server 2012

FIX: SQL Server performance issues in NUMA environments

 

SQL Server 2008 SP3 CU13 (10.0.5846)  September 16, 2013 (2 public hotfixes)

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 SP3 CU14 (10.0.5848)  November 18, 2013 (6 public hotfixes)

FIX: You receive an incorrect result when you run a query that uses some ranking functions in SQL Server 2008

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

 

SQL Server 2008 SP3 CU15 (10.0.5850)  January 20, 2014 (3 public hotfixes)

FIX: “Installer terminated prematurely” error when you install SQL Server 2008 SP3

 

SQL Server 2008 SP3 CU16 (10.0.5852)  March 17, 2014 (2 public hotfixes)

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

 

SQL Server 2008 SP3 CU17 (10.0.5861) May 19, 2014 (9 public hotfixes)

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

FIX: Incorrect results when you execute a full-text search with a phrase that contains a hyphen in SQL Server 2008

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

New Azure SQL Database Service Tiers

Microsoft’s Eron Kelly has recently blogged about Azure SQL Database introduces new service tiers, where the existing Web and Business service tiers will be replaced in twelve months, as they are gradually replaced by six new service tiers, including Basic, Standard 1, Standard 2, Premium 1, Premium 2, and Premium 3. There will be differences in the “self-recovery” level and geo-replication levels across the SKUs.

Here are a couple of new acronyms for you to learn:

Database Throughput Unit (DTU): The resources powering each performance level are represented in DTUs. It combines CPU, memory, physical reads, and transaction log writes into a single unit. A performance level with 5 DTUs has five times more power than a performance level with 1 DTU. The “Database Throughput Unit” (DTU) represents database power and is meant to replace hardware specifications in the context of Azure SQL Database. 

Azure SQL Database Benchmark (ASDB): ASDB measures the actual throughput of a performance level by using a mix of database operations which occur most frequently in online transaction processing (OLTP) workloads.

Table 1 shows some relevant information about these new Azure SQL Database service tiers.

Service Tier Monthly Cost/DB Database Size Limit DTU/Database ASDB Transactions/Minute
Basic $4.99 2GB 1 DTU 58
Standard 1 $40.00 250GB 5 DTU 283
Standard 2 $200.00 250GB 25 DTU 1,470
Premium 1 $930.00 500GB 100 DTU 5,880
Premium 2 $1,860.00 500GB 200 DTU 11,520
Premium 3 $7,440.00 500GB 800 DTU 43,800

Table 1: Azure SQL Database Service Tiers

While the new service tiers are in a “preview” status, pricing will be 50% lower than what is shown in Table 1. You can read more about pricing details here. I am most interested in exploring the information disclosed in the new Azure SQL Database Service Tiers and Performance Levels, blog post, since it give us some more concrete information about what a DTU is. There is also some more detailed information about the new ASDB in the Azure SQL Database Benchmark Overview.

Most Recent Cumulative Updates for SQL Server 2008-2014

Here are the most recent cumulative updates for SQL Server 2014, 2012, 2008 R2 and 2008 (as of June 27, 2014)

SQL Server 2014 RTM CU2 (12.0.2370)           released on June 27, 2014

Microsoft has now released the second cumulative update for SQL Server 2014 RTM, which has 48 hotfixes by my count from the KB article. These include most of the hotfixes from SQL Server 2012 through SP1 CU10. If you are getting ready to deploy a new SQL Server 2014 instance, I really think you want to be on SQL Server 2014 RTM CU2.

SQL Server 2012 RTM CU11 (11.0.2424)          released on December 16, 2013  (this is the last one for the RTM branch)

SQL Server 2012 SP1 CU10 (11.0.3431)           released on May 19, 2014 (On SQL Server 2012, this is where you want to be, in my opinion)

SQL Server 2012 SP2 RTM (11.0.5058)            released on  June 19, 2014 (I would wait for SQL Server 2012 SP2 CU1 before you move to this branch)

If you are on SQL Server 2012, only Service Pack 1 and Service Pack 2 are still officially a “supported service pack”. The RTM branch is no longer a “supported service pack”.

SQL Server 2008 R2 SP2 CU12 (10.50.4305)     released on April 21, 2014

If you are on SQL Server 2008 R2, only Service Pack 2 is still a “supported service pack”. All other branches are retired.

SQL Server 2008 SP3 CU17 (10.0.5861)           released on May 19, 2014  (this is the final CU for SQL Server 2008 SP3)

If you are on SQL Server 2008, only Service Pack 3 is still a “supported service pack”. All other branches are retired.

For a number of months, I was very curious about whether there would be a SQL Server 2008 SP4 and/or a SQL Server 2008 R2 SP3 before both SQL Server 2008 and 2008 R2 fall out of mainstream support on July 8, 2014?  Especially for people who don’t believe in deploying cumulative updates, the SQL Server 2008 SP3 RTM build and the SQL Server 2008 R2 SP2 RTM build are going to be quite ancient (and missing a lot of very important fixes) by July 8, 2014.

June 2, 2014 Update: On May 29, Microsoft announced that there will be final Service Packs for both SQL Server 2008 and SQL Server 2008 R2, that will be released some time after July 8, 2014. This is very welcome news that I really appreciate!

So, to recap, if you are getting ready to deploy SQL Server 2014, you should install SQL Server 2014 RTM CU2.  If you are on SQL Server 2012, you should be on the SP1 branch by now (even though SP2 is now available). If you are on SQL Server 2008 R2, you should be on the SP2 branch, since SP1 and RTM are retired. If you are on SQL Server 2008, you should be on the SP3 branch, since all previous branches are retired. All of SQL Server 2005 is out of mainstream support, but if you are still on SQL Server 2005, you should be on SP4.

Learn About SQL Server Hardware in Chicago

On May 5-6, 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.

Now that SQL Server 2014 is generally available, and Windows Server 2012 R2 has been available for over six months, combined with the release of 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. 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 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 unfamiliar with the details and nuances of modern server hardware and storage subsystems, while many server and storage administrators are unfamiliar with the specific workload demands of a SQL Server database server.

Attendees of this class will learn how to analyze, select, and size their 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.

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.

Recommended Intel Processors For SQL Server 2014 OLTP Workloads

If you are in the process of evaluating and selecting the components for a new database server to run an OLTP workload on SQL Server 2014 Enterprise Edition, you have several initial choices that you have to make as a part of the decision process. First you have to decide whether you want to go with an AMD-based server or an Intel-based server. Unfortunately, I cannot recommend that you use an AMD processor for SQL Server 2012/2014 OLTP workloads, due to the combination of low single-threaded performance and high SQL Server licensing costs (even with the 25% discount from the SQL Server 2012 Core Factor Table).

Next, you need to decide on the server socket count, which means choosing a single-socket, dual-socket, quad-socket, or eight-socket server (at least in the commodity server market). After you choose the socket count, you need to decide exactly which of the available processors you want to use in that model server. Looking at the choices for several current model servers from the major system vendors, you will discover that you will have to pick from around 15-20 different specific processors. All of this can be a little overwhelming to consider, but I urge you to do some research, and to choose carefully. Letting someone else pick your processors, who may not be familiar with SQL Server 2012/2014 licensing and the demands of different database workload types, could be a lasting, costly mistake.

With the core-based licensing in SQL Server 2012/2014 Enterprise Edition, you need to pay closer attention to your physical core counts, and think about whether you are more concerned with extra scalability (from having more physical cores), or whether you want the absolute best OLTP query performance (from having a processor with fewer cores but a higher base clock speed from the same processor generation). Unlike in the good old days of SQL Server 2008 R2 and older, having more physical cores will cost you more for your SQL Server 2012/2014 Enterprise Edition licensing costs. You really need to think about what you are trying to accomplish with your database hardware. For example, if you can partition your workload between multiple servers, then you could see much better OLTP performance from using two dual-socket servers instead of one quad-socket server.

So, here are the Intel processors that I recommend in mid-April 2014 for OLTP workloads, with their high-level specifications and some commentary.

One-Socket Server (High Capacity)

Intel Xeon E5-2470 v2 (22nm Ivy Bridge-EN)

  • 2.4 GHz, 25MB L3 cache, 8 GT/s Intel QPI 1.1
  • 10 cores, Turbo Boost 2.0 (3.2 GHz), hyper-threading
  • Three memory channels, six memory slots per processor, 96GB RAM with 16GB DIMMs

One-Socket Server (High Performance)

Intel Xeon E3-1280 v3 (22nm Haswell)

  • 3.6 GHz, 8MB L3 cache, 5 GT/s Intel QPI 1.1
  • 4 cores, Turbo Boost 2.0 (4.0 GHz), hyper-threading
  • Two memory channels, four memory slots per processor, 32GB RAM with 8GB DIMMs

At least one Tier One vendor (Dell) is offering a single-socket server with the new Ivy Bridge-EN processor family. This is the entry level, two-socket capable Ivy Bridge processor that has lower clock speeds and less memory bandwidth than the Ivy Bridge-EP processor family, so it is NOT a good choice for a two-socket server. Despite this, it does give you the ability to have ten physical cores and 96GB of RAM in a single-socket server. You would see much better single-threaded OLTP performance from a new 3rd generation E3-1280 v3 Haswell processor, but you would be limited to four physical cores and 32GB of RAM. Again, if you can partition your workload, two single-socket Xeon E3-1280 v3 based servers would give you much better OLTP performance than one Xeon E5-2470 v2 based server with a lower SQL Server 2012/2014 Enterprise Edition licensing cost.

Two-Socket Server (High Capacity)

Intel Xeon E5-2697 v2 (22nm Ivy Bridge-EP)

  • 2.7 GHz, 30MB L3 cache, 8 GT/s Intel QPI 1.1
  • 12 cores, Turbo Boost 2.0 (3.5 GHz), hyper-threading
  • Four memory channels, twelve memory slots per processor, 384GB RAM with 16GB DIMMs

Two-Socket Server (High Performance)

Intel Xeon E5-2643 v2 (22nm Ivy Bridge-EP)

  • 3.5 GHz, 25MB L3 cache, 8 GT/s Intel QPI 1.1
  • 6 cores, Turbo Boost 2.0 (3.8 GHz), hyper-threading
  • Four memory channels, twelve memory slots per processor, 384GB RAM with 16GB DIMMs

Choosing the top of the line, 12-core Xeon E5-2697 v2 would cost twice as much for the SQL Server license costs as the 6 core Xeon E5-2643 v2. Once again, if you can partition your workload, two dual-socket Xeon E5-2643 v2 based servers would give you better overall OLTP performance than one Xeon E5-2697 v2 based server for the same SQL Server 2012/2014 Enterprise Edition licensing cost. You would have more total memory between the two servers, and more potential I/O capacity, at the cost of buying two servers instead of one server.  In some situations, this strategy might not make sense, especially with the added management and maintenance overhead of two servers instead of one.

Four-Socket Server (High Capacity)

Intel Xeon E7-4890 v2 (22nm Ivy Bridge-EX)

  • 2.8 GHz, 37.5MB L3 cache, 8 GT/s Intel QPI 1.1
  • 15 cores, Turbo Boost 2.0 (3.4 GHz), hyper-threading
  • Four memory channels, twenty-four memory slots per processor, 1536GB RAM with 16GB DIMMs

Four-Socket Server (High Performance)

Intel Xeon E7-8893 v2 (22nm Ivy Bridge-EX)

  • 3.4 GHz, 37.5MB L3 cache, 8 GT/s Intel QPI 1.1
  • 6 cores, Turbo Boost 2.0 (3.7 GHz), hyper-threading
  • Four memory channels, twenty-four memory slots per processor, 1536GB RAM with 16GB DIMMs

The brand new Xeon E7-8893 v2 will give you significantly better single-threaded OLTP query performance in a four-socket server than the E7-4890 v2, at the cost of less total capacity because of the lower physical core count. The E7-8893 v2 is a “frequency-optimized” model that is actually meant for eight-socket servers, but is available in several new four-socket server models from the major server vendors.

It would save you enough on SQL Server 2012/2014 Enterprise Edition license costs (about $250K) to buy the server itself and still have lots of money left over. I even think it is a better choice in many situations than a two-socket server with the 12-core, Intel Xeon E5-2697 v2, since you will have much higher single-threaded performance and much higher memory capacity. The downside is a higher hardware cost, since you will be buying four, quite expensive processors.

Eight-Socket Server (High Capacity)

Intel Xeon E7-8890 v2 (22nm Ivy Bridge-EX)

  • 2.8 GHz, 37.5MB L3 cache, 8 GT/s Intel QPI 1.1
  • 15 cores, Turbo Boost 2.0 (3.4 GHz), hyper-threading
  • Four memory channels, twenty-four memory slots per processor, 3072GB RAM with 16GB DIMMs (eight sockets)

Eight-Socket Server (High Performance)

Intel Xeon E7-8891 v2 (22nm Ivy Bridge-EX)

  • 3.2 GHz, 37.5MB L3 cache, 8 GT/s Intel QPI 1.1
  • 10 cores, Turbo Boost 2.0 (3.7 GHz), hyper-threading
  • Four memory channels, twenty-four memory slots per processor, 3072GB RAM with 16GB DIMMs (eight sockets)

You can choose a lower core count, frequency-optimized model, that has a higher clock speed for better single-threaded performance. The lower core count will also save you a LOT of money on SQL Server 2012/2014 licensing costs, although you will give up that extra load capacity with few total processor cores available.

I always like to hear what you think about my posts, so be sure to let me know!

SQL Server Diagnostic Information Queries for April 2014

I made some small improvements to a few of the queries this month. I plan to add several more SQL Server 2014 specific queries over the next couple of months, along with a lot more comments on how to interpret the results of each query in the entire set.

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.

There is an initial query in each version that tries to confirm that you are using the correct version of the script for your version of SQL Server. Please let me know what you think of these queries, and whether you have any suggestions for improvements. Thanks!

Windows Server 2012 R2 Update

Microsoft has released an update for Windows 8.1 and Windows Server 2012 R2 that is somewhat of a cross between a Cumulative Update and a Service Pack. Microsoft is calling this update “Windows Server 2012 R2 Update” which is sort of a silly name. I would have preferred something like “Windows Server 2012 R2 Update 1”, since there are rumors of another update like this coming out later this year (at least according to Mary Jo Foley).

This update is primarily designed to make both of these operating systems easier to use for mouse and keyboard users (even though Windows Server 2012 R2 is already easier than Windows 8.1). The update does make it even easier to use Windows Server 2012 R2 if you are more used to working with Windows Server 2008 R2. It also includes a rollup of previous updates for Windows Server 2012 R2, so it will save you quite a bit of time compared to pulling those updates down from Microsoft Update. You can also use a slipstream version that Microsoft has made available if you are doing a new operating system installation.

You can read more about the Windows Server 2012 R2 Update here. Adin Ermie has a nice blog post with lots of before and after screenshots here.

The update is already available for MSDN Subscribers Downloads, and Microsoft is going to make it available on Windows Update on April 8. I have installed it on a few VMs already without any problems. One initial question I had was how to quickly and easily determine whether the update was installed or not, since it does not show up in System Properties like a Service Pack would. It turns out that there is a somewhat easy method to find out, by running Msinfo32.exe.

To confirm the exact version of Windows Server 2012 R2 that is installed on a computer, run Msinfo32.exe. If Windows Server 2012 R2 Update is installed, the value reported for Hardware Abstraction Layer will be 6.3.9600.17031. You can also look for KB2919355 under installed updates.

If you are or will be running SQL Server 2012 or SQL Server 2014 in the near future, you really should be running Windows Server 2012 R2 as your operating system. If you are already running Windows Server 2012 R2, you should make plans to install this update as soon as possible, since it will be required in the near future. According to Microsoft, “Future updates as of the patch Tuesday in May, including security fixes, will be based on Windows Server 2012 R2 Update as the baseline.”