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

As of March 27, 2015, there have been five Cumulative Updates (CU) for the Service Pack 2 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. 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, CU5 (Build 11.0.5582), which was released on March 16, 2015.

If you are still on the SQL Server 2012 SP1 branch, then you want to be on SP1 CU15. But really, you should be on the SP2 branch as soon as possible. Table 1 shows the SQL Server 2012 SP2 CU builds that have been released so far.

Build Description Release Date
11.0.5058 SP2 RTM June 10, 2014
11.0.5532 SP2 CU1 July 23, 2014
11.0.5548 SP2 CU2 September 15, 2014
11.0.5556 SP2 CU3 November 17, 2014
11.0.5569 SP2 CU4 January 19, 2015
11.0.5582 SP2 CU5 March 16, 2015

Table 1: SQL Server 2012 SP2 CU Builds

 

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

SQL Server 2012 SP2 Build Versions

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

Here are the fixes in the Service Pack 1 branch:

SQL Server 2012 SP2 Cumulative Update 1 (Build 11.0.5532), 43 total public hot 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

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

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

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

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

 

SQL Server 2012 SP2 Cumulative Update 2 (Build 11.0.5548), 43 total public hot fixes

FIX: SQL Cache Memory decreases and CPU usage increases when you rebuild an index for a table in SQL Server

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: Undetected deadlock occurs when you use a sequence object in SQL Server 2012

FIX: Performance improvement for SQL Server Spatial data access in SQL Server 2012

FIX: A severe error occurs when you run a query that uses CTEs against the partitioned tables in SQL Server 2012

FIX: UPDATE STATISTICS performs incorrect sampling and processing for a table with columnstore index in SQL Server 2012

 

SQL Server 2012 SP2 Cumulative Update 3 (Build 11.0.5556), 32 total public hot fixes

FIX: Poor performance for cdc.fn_cdc_get_net_changes_<capture_instance> in SQL Server 2012 SP2

FIX: “Non-yielding Scheduler” condition occurs when you run a complex query in SQL Server 2012

FIX: INSERT performance decreases in merge replication that uses precomputed partitions in SQL Server 2012

FIX: Error when you execute statistics update on a table that has sql_variant data type in SQL Server 2012

FIX: Large chain of blocking occurs when you use merge replication in SQL Server 2012

 

SQL Server 2012 SP2 Cumulative Update 4 (Build 11.0.5569), 36 total public hot fixes

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

FIX: Sequence object generates duplicate sequence values when SQL Server 2012 is under memory pressure

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

FIX: DBCC CHECKDB and DBCC CHECKTABLE take longer to run when SQL CLR UDTs are involved in SQL Server 2012

 

SQL Server 2012 SP2 Cumulative Update 5 (Build 11.0.5582), 27 total public hot fixes

FIX: SOS_CACHESTORE spinlock contention on system table rowset cache causes high CPU usage in SQL Server 2012 or 2014

FIX: SOS_CACHESTORE spinlock contention on ad hoc SQL Server plan cache causes high CPU usage in SQL Server 2012 or 2014

FIX: Memory leak in USERSTORE_SCHEMAMGR and CPU spikes occur when you use temp table in SQL Server 2012

FIX: AlwaysOn availability groups are reported as NOT SYNCHRONIZING

FIX: Complex parallel query does not respond in SQL Server 2012

FIX: Performance issue occurs when you use sqlncli11.dll ODBC driver to access SQL Server in an application

FIX: SQL Server 2012 shuts down unexpectedly when you run DBCC CHECKDB and the rollback recovery on the snapshot fails

 

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 2 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

Happily, Microsoft has been updating this KB article with new information, so you might want to read it again, if you have not done so already.

SQL Server Diagnostic Information Queries for March 2015

I revised several of the queries this month in all five versions of the script.  Once again, 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!

SQL Server 2012 SP2 CU5 Available

Microsoft has released SQL Server 2012 Service Pack 2 Cumulative Update 5, which is Build 11.0.5582. This Cumulative Update has 27 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. There are a number of pretty significant fixes in this update.

Microsoft has also released SQL Server 2012 Service Pack 1 Cumulative Update 15, which is Build 11.0.3487. This Cumulative Update has only one hotfix in the public fix list. As I said above, if you are running SQL Server 2012, you really should be on the SP2 branch, even though SP1 is still supported.

There is no corresponding CU for SQL Server 2012 RTM, since SQL Server 2012 RTM is not a supported Service Pack. You really should not be on the SQL Server 2012 RTM branch anymore.

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 thumb Windows Power Plan Effects on Newer Intel Processors

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 image0025 thumb Windows Power Plan Effects on Newer Intel Processors

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 image0027 thumb Windows Power Plan Effects on Newer Intel Processors

Figure 3: Geekbench 3.3 Results with Balanced Power Plan

 

clip image0029 thumb Windows Power Plan Effects on Newer Intel Processors

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.