Balancing Your Available SQL Server Core Licenses Evenly Across NUMA Nodes

One issue I recently ran into with a customer was a case where they were using a new, two-socket server with two Intel Xeon E5-2697 v2 Ivy Bridge-EP processors. This particular 22nm processor has twelve physical cores, plus hyper-threading, so you can have 24 logical cores for one entire physical processor, and 48 logical cores for a two-socket server with both sockets populated.

The reason why we had an issue is because both SQL Server 2012 and 2014 Standard Edition have an artificial license limit of either 16 physical cores or 32 logical cores (along with a limit of four sockets, whichever is lower). This meant that SQL Server 2014 Standard Edition could only use 32 out of the 48 logical cores in this new server. To make matters worse, the available cores were spread unevenly across the two NUMA nodes in the server. There were 24 logical cores on one NUMA node and 8 logical cores on the other NUMA node in the server, which was not an ideal situation.

Fortunately, it is pretty easy to fix this issue by using the ALTER SERVER CONFIGURATION command that was introduced in SQL Server 2008 R2. On the negative side, fixing it was not completely intuitive, because of these dumb Standard Edition license limits.

You can use the queries in the code below to detect and correct this issue.

-- Balancing your available SQL Server core licenses evenly across two NUMA nodes
-- Glenn Berry
-- SQLskills.com


-- Get socket, physical core and logical core count from SQL Server Error Log
EXEC sys.xp_readerrorlog 0, 1, N'detected', N'socket';


-- SQL Server NUMA node information 
SELECT node_id, node_state_desc, memory_node_id, processor_group, online_scheduler_count, 
       active_worker_count, avg_load_balance, resource_monitor_state
FROM sys.dm_os_nodes WITH (NOLOCK) 
WHERE node_state_desc <> N'ONLINE DAC' OPTION (RECOMPILE);


-- SQL Server schedulers by NUMA node
SELECT parent_node_id, 
  SUM(current_tasks_count) AS [current_tasks_count], 
  SUM(runnable_tasks_count) AS [runnable_tasks_count], 
  SUM(active_workers_count) AS [active_workers_count], 
  AVG(load_factor) AS avg_load_factor
FROM sys.dm_os_schedulers WITH (NOLOCK) 
WHERE [status] = N'VISIBLE ONLINE'
GROUP BY parent_node_id;



-- SQL Server NUMA node and cpu_id information
SELECT parent_node_id, scheduler_id, cpu_id
FROM sys.dm_os_schedulers WITH (NOLOCK) 
WHERE [status] = N'VISIBLE ONLINE';


-- Fixing the problem

-- Unfortunately, this does not work, due to the license limits in SQL 2012/2014 Standard Edition
ALTER SERVER CONFIGURATION SET PROCESS AFFINITY NUMANODE = 0,1;

-- Msg 5833, Level 16, State 2, Line 7
-- The affinity mask specified is greater than the number of CPUs supported or licensed on this edition of SQL Server.


-- This command spreads your available 32 logical core licenses across two NUMA nodes 
-- This is valid for an Intel processor, with HT enabled
ALTER SERVER CONFIGURATION
SET PROCESS AFFINITY CPU = 0 TO 15, 25 TO 40;
 

 

A similar issue with “grandfathered” SQL Server 2012 Enterprise Server+CAL customers was discussed by my colleague, Jonathan Kehayias here.

SQL Server Diagnostic Information Queries for February 2015

I revised several of the queries this month in all five versions of the script.  The main enhancements are additional comments and documentation about the queries, including more information about how to interpret the results. 

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 for that query). It is not really a good idea to simply run the entire batch in one shot, especially the first time you run these queries on a particular server, since some of these queries can take some time to run, depending on your workload and hardware. I also think it is very helpful to run each query, look at the results and think about the emerging picture of what is happening on your server as you go through the complete set.

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 when they run these queries.

Note: These queries are stored on Dropbox. I occasionally get reports that the links to the queries and blank results spreadsheets do not work, which is most likely because Dropbox is blocked wherever people are trying to connect.

I also occasionally get reports that some of the queries simply don’t work. This usually turns out to be an issue where people have some of their user databases in 80 compatibility mode, which breaks many DMV queries.

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 are not using the correct version of these queries for your version of SQL Server, some of the queries are not going to work correctly.

If you want to understand how to better run and interpret these queries, you should consider listening to my three latest Pluralsight courses, which are SQL Server 2014 DMV Diagnostic Queries – Part 1SQL Server 2014 DMV Diagnostic Queries – Part 2 and SQL Server 2014 DMV Diagnostic Queries – Part 3. All three of these courses are pretty short and to the point, at 67, 77, and 68 minutes respectively. Listening to these three courses is best way to thank me for maintaining and improving theses scripts…

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

Windows Power Plan Effects on Newer Intel Processors

I recently was working with a customer who had purchased a new Lenovo ThinkServer RD640 with two 22nm Intel Xeon E5-2697 v2 Ivy Bridge-EP processors. This processor was introduced in Q3 of 2013, and it is one generation behind the latest Intel Xeon E5-2600 v3 series processors.

This server had a new, default installation of Windows Server 2012 R2 Standard Edition, which meant that it was using the default Windows Balanced Power Plan. Running CPU-Z 1.71.1 showed the actual core speed of Core #0 while the system was at rest, with the Balanced Power Plan (Figure 1) and with the High Performance Power Plan (Figure 2).

clip_image002

Figure 1: CPU-Z Results with Balanced Power Plan

Changing the Power Plan to High Performance had an immediate effect on the processor core speed, as shown in Figure 2.

clip_image002[5]

Figure 2: CPU-Z Results with High Performance Power Plan

 

Here are the Geekbench results for the default Balanced Power Plan (Figure 3) and the High Performance Power Plan (Figure 4). The Single-Core score is more relevant here, since the 32-bit GUI version of Geekbench 3.3 only uses 32 total cores (and there are 48 logical cores in this server).

clip_image002[7]

Figure 3: Geekbench 3.3 Results with Balanced Power Plan

 

clip_image002[9]

Figure 4: Geekbench 3.3 Results with High Performance Power Plan

You need to keep in mind that your BIOS power management settings will override your Windows Power Plan settings, so it is very important to check what is going on with CPU-Z. For you virtualization users, you need to check what your hypervisor power management settings are doing, because they will override what is happening with the guest OS Windows Power Plan settings.

Not only does power management affect your processor core speed (and CPU/memory benchmarks like Geekbench), but it also affects things like the performance of your PCIe expansion slots. In case you want some official Microsoft guidance on this subject, the Microsoft KB article linked below discusses it in quite a bit of detail.

Slow Performance on Windows Server 2008 R2 when using the “Balanced” Power Plan

This issue has been around since Windows Server 2008, and still exists with Windows Server 2012 R2.

SQL Server 2014 Hardware Analysis Case Study

Imagine that you have been given the go-ahead to upgrade your entire data platform stack from SQL Server 2008 Enterprise Edition to SQL Server 2014 Enterprise Edition. You need to come up with a recommendation for your new database server hardware, looking to maximize performance while controlling your SQL Server 2014 license costs.

To help you with that effort, here is an example hardware analysis comparing an existing legacy four-socket server (a Dell PowerEdge R815) with four AMD Opteron 6168 processors to a new four-socket server (a Dell PowerEdge R920) with newer 22nm Intel Xeon E7 v2 Ivy Bridge-EX processors.

For a Dell PowerEdge R920, I would be looking at one of these three processors:

1. Xeon E7-8857 v2   (12 cores, 3.0 GHz base clock speed)

2. Xeon E7-8891 v2   (10 cores, 3.2 GHz base clock speed)

3. Xeon E7-8893 v2   (6 cores, 3.4 GHz base clock speed)

These three candidate processors all have higher base clock speeds and lower physical core counts than some other more common choices, such as the fifteen-core Xeon E7-4890 v2.

The closest equivalent AMD-based system I could find in the TPC-E benchmark results (to the legacy system) was an HP ProLiant BL685c G7 Blade Server with four, 2.2GHz AMD Opteron 6174 processors and 512GB of RAM, with an actual raw TPC-E score of 1464.12. The raw TPC-E score is a good way of measuring the overall CPU capacity of a system.

Dividing this score by the number of physical cores in the system gives us a score/core of 30.5, which is a good measure of single-threaded processor performance. Since the legacy system has slower 1.9GHz AMD Opteron 6168 processors (from the same generation and family), we simply need to adjust for the clock speed difference. Taking 1.9GHz divided by 2.2 GHz is 0.8636. Taking the actual 1464.12 score times 0.8636 gives us an estimated TPC-E score of 1264.46 for the legacy system. Dividing that by 48 physical cores gives an estimated score/core of 26.34 for the legacy system.

There is an actual TPC-E result for a four-socket IBM System x3850 X6 with four, 15-core 2.8GHz Intel Xeon E7-4890 v2 processors and 2TB of RAM, with a raw TPC-E score of 5576.27. Dividing this actual score by 60 physical cores gives us an actual score/core of 92.94.

We can adjust this actual result for the three candidate processors listed above to take into account the difference in core counts and base clock speeds to get estimated TPC-E scores for a four-socket system with each of those processors since they are from the same generation and family.

1. Xeon E7-8857 v2               5576.27 original score, times .80 (core count difference), times 1.0714 (clock speed difference), is 4779.53 divided by 48 total physical cores is 99.57 score/core

2. Xeon E7-8891 v2               5576.27 original score, times .66 (core count difference), times 1.1428 (clock speed difference), is 4233.73 divided by 40 total physical cores is 105.84 score/core

3. Xeon E7-8893 v2               5576.27 original score, times .40 (core count difference), times 1.2142 (clock speed difference), is 2708.28 divided by 24 total physical cores is 112.84 score/core

Comparing the legacy system to the actual new four-socket TPC-E result and my estimates for the other three processors, gives us this summary:

Processor                        TPC-E Score        Score/Core         Total Physical Cores     SQL 2014 License Cost (EE)

Opteron 6168                    1264.46                 26.34                     48                             $329,952.00     ($274,464.00 with AMD Core Factor discount)

Opteron 6174                    1464.12                 30.50                     48                             $329,952.00     ($274,464.00 with AMD Core Factor discount)                        

Xeon E7-4890 v2               5576.27                 92.94                     60                             $395,942.00

Xeon E7-8857 v2               4779.53                 99.57                     48                             $329,952.00       

Xeon E7-8891 v2               4233.73                 105.84                   40                             $274,960.00

Xeon E7-8893 v2               2708.28                 112.84                   24                             $164,976.00

This means that we could choose from having from roughly four times better single-threaded processor performance using the Xeon E7-8893 v2 processor or from having roughly four times more processor capacity using the Xeon E7-8857 v2 processor in a new system compared to the legacy system, depending on which processor we choose. The difference in SQL Server 2014 Enterprise Edition license costs between the different processor choices is quite dramatic. For example, going from the twelve-core processor to the faster ten-core processor lowers your SQL Server license costs by about as much as the actual server would cost.

New Intel Data Center SSDs

Intel has announced two new series of their 2.5-inch and 1.8-inch SATA data center solid-state drives. These are the upper-tier DC S3710 Series and mid-level DC S3610 Series. The DC S3710 replaces the previous DC S3700 Series, which was introduced back in late 2012. The high-level specifications for the entire Intel Solid-State Drive Data Center Family are listed here.

The Intel SSD DC S3710 Series is a 2.5” form factor and comes in 200GB, 400GB, 800GB, and 1.2TB capacities. The Intel SSD DC S3610 Series comes in both 2.5” and 1.8” form factors with the 2.5” coming in 200GB, 400GB, 480GB, 800GB, 1.2TB, and 1.6TB capacities and the 1.8” coming in 200GB, 400GB, and 800GB capacities. Both of the new SSD Series will use a high-endurance version of Intel’s 20nm MLC NAND, with a SATA interface and will have greater write performance compared to the previous models. The endurance rating for the DC S3710 is 10 drive writes per day for the length of the five-year warranty, while the DC S3610 is rated at 3 drive writes per day for five years.

Intel quotes these performance figures for the DC S3710 Series:

  • Sustained sequential read/write
    • 200GB: Up to 550/300MB/s
    • 400GB: Up to 550/470MB/s
    • 800GB: Up to 550/460MB/s
    • 1.2TB: Up to 550/520MB/s
  • Random 4k read/write
    • 200GB: Up to 85,000/43,000 IOPS
    • 400GB: Up to 85,000/43,000 IOPS
    • 800GB: Up to 85,000/39,000 IOPS
    • 1.2TB: Up to 85,000/45,000 IOPS

Intel also quotes these performance figures for the 2.5-inch version of the DC S3610 Series:

  • Sustained sequential read/write
    • 200GB: Up to 550/230MB/s
    • 400GB: Up to 550/400MB/s
    • 480GB: Up to 550/440MB/s
    • 800GB: Up to 540/520MB/s
    • 1.2TB: Up to 500/500MB/s
    • 1.6TB: Up to 540/500MB/s
  • Random 4k read/write
    • 200GB: Up to 84,000/12,000 IOPS
    • 400GB: Up to 84,000/25,000 IOPS
    • 480GB: Up to 84,000/28,000 IOPS
    • 800GB: Up to 84,000/28,000 IOPS
    • 1.2TB: Up to 84,000/28,000 IOPS
    • 1.6TB: Up to 84,000/27,000 IOPS

The S3710 Series has better write performance, and higher write endurance compared to the S3610 Series. As always, the larger capacity models typically have better performance than the lower capacity models from the same series. These drives are supposedly available now, although I have not found them listed for sale anywhere just yet. Here is the suggested retail pricing from Intel:

  • 1.2TB  DC S3710            $1909.00
  • 800GB DC S3710            $1249.00
  • 400GB DC S3710            $  619.00
  • 200GB DC S3710            $  309.00

 

  • 1.6TB  DC S3610            $1719.00
  • 1.2TB  DC S3610            $1289.00
  • 800GB DC S3610            $  839.00
  • 480GB DC S3610            $  509.00
  • 400GB DC S3610            $  419.00
  • 200GB DC S3610            $  200.00

These drives are a very attractive alternative to being price-gouged for internal flash-storage by your server vendor. I have had a number of customers use the older DC S3700 drives in new servers they have purchased, all with good results.

Remote Presentation of Analyzing I/O Subsystem Performance for Israel PASS Chapter

I did a remote presentation of Analyzing I/O Subsystem Performance for the Israel PASS Chapter on Monday, February 2. Here is the abstract for this presentation:

SQL Server is often I/O bound – but why? Do you feel lost when talking to your storage administrator? Are your storage subsystems like a mysterious black box where your databases live but you can’t go visit? This session will get you up to speed with the fundamentals of storage subsystems for SQL Server. You will learn about the different types of storage that are available, and how to decide what type of storage to use for different workload types. You will also learn useful tips and techniques for configuring your storage for the best performance and reliability. We’ll cover methods to effectively measure and monitor your storage performance so that you will have valuable information and evidence available the next time you have to discuss IO performance with your storage administrator. Come to this session to learn how to analyze I/Os as well as options to reduce the bottlenecks.

The PDF version of my deck and the queries I used in the demos are available here.

SQL Server 2012 SP2 CU4 Available

Microsoft has released SQL Server 2012 Service Pack 2 Cumulative Update 4, which is Build 11.0.5569. This Cumulative Update has 36 fixes in the public fix list. This is the build that you want to be on if you are running SQL Server 2012, since you should be on the SQL Server 2012 SP2 branch by now, at least in my opinion.

Microsoft has also released SQL Server 2012 Service Pack 1 Cumulative Update 14, which is Build 11.0.3486. This Cumulative Update has only eight hotfixes in the public fix list. There is no corresponding CU for SQL Server 2012 RTM, since SQL Server 2012 RTM is not a supported Service Pack.

SQL Server Diagnostic Information Queries for January 2015

I revised a number of the queries this month in all five versions of the script.  The main enhancements are additional comments and documentation about the queries.

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. I also think it is very helpful to run each query, look at the results and think about the emerging picture of what is happening on your server as you go through the complete set.

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 when they run these queries.

Note: These queries are stored on Dropbox. I occasionally get reports that the links to the queries and blank results spreadsheets do not work, which is most likely because Dropbox is blocked wherever people are trying to connect.

I also occasionally get reports that some of the queries simply don’t work. This usually turns out to be an issue where people have some of their user databases in 80 compatibility mode, which breaks many DMV queries.

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 are not using the correct version of these queries for your version of SQL Server, some of the queries are not going to work correctly.

If you want to understand how to better run and interpret these queries, you should consider listening to my two latest Pluralsight courses, which are SQL Server 2014 DMV Diagnostic Queries – Part 1 and SQL Server 2014 DMV Diagnostic Queries – Part 2. Both of these courses are pretty short and to the point, at 67 and 77 minutes respectively. Part 3 of the series has been recorded, and will probably be published in February 2015.

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

SQL Server Diagnostic Information Queries for December 2014

I revised a number of the queries this month in all five 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. I also think it is very helpful to run each query, look at the results and think about the emerging picture of what is happening on your server as you go through the complete set.

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 when they run these queries.

Note: These queries are stored on Dropbox. I occasionally get reports that the links to the queries and blank results spreadsheets do not work, which is most likely because Dropbox is blocked wherever people are trying to connect.

I also occasionally get reports that some of the queries simply don’t work. This usually turns out to be an issue where people have some of their user databases in 80 compatibility mode, which breaks many DMV queries.

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 are not using the correct version of these queries for your version of SQL Server, some of the queries are not going to work correctly.

If you want to understand how to better run and interpret these queries, you should consider listening to my two latest Pluralsight courses, which are SQL Server 2014 DMV Diagnostic Queries – Part 1 and SQL Server 2014 DMV Diagnostic Queries – Part 2. Both of these courses are pretty short and to the point, at 67 and 77 minutes respectively. Part 3 of the series has been recorded, and will probably be published in February 2015.

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 5

Microsoft has released SQL Server 2014 RTM Cumulative Update 5, which is Build 12.0.2456. This cumulative update has 47 fixes in the Public hotfix list.

This handy Microsoft KB article “SQL Server 2014 build versions”, lists all of the cumulative updates that have been released for SQL Server 2014, including this one. If you have not already done so, I strongly recommend that you take a look at this KB article “Recommended updates and configuration options for SQL Server 2012 and SQL Server 2014 used with high-performance workloads”, which is full of very good information about how to configure SQL Server 2012/2014, along with quite a few reasons that you should try to stay current with your SQL Server cumulative updates.