Intel Xeon E5-2600 v2 Series Processors (Ivy Bridge-EP) in Q3 2013

More details about the upcoming 22nm Intel Xeon E5-2600 series processors (aka Ivy Bridge-EP) are starting to leak out. Initially, we will see the single-socket Xeon E5-1600 v2 series and the two-socket Xeon E5-2600 v2 series processors, that share the 22nm Ivy Bridge microarchitecture. They use the same LGA2011 socket as the current 32nm E5-1600 and E5-2600 series (Sandy Bridge-EP), so that should mean that they will show up quickly in existing server models, such as the Dell PowerEdge R720 and the HP ProLiant DL380p Gen 8.

The chart that TechPowerUp found does not show the physical core count for each processor model, but we can infer that based on the total L3 cache size, since Intel has been using 2.5MB of shared L3 cache for each physical core in the Sandy Bridge and Ivy Bridge microarchitectures. That means you can divide the L3 cache size (in MB) by 2.5 to get the physical core count. Based on this calculation, it looks like the Intel Xeon E5-2600 v2 processors will have from eight to twelve physical cores. The Xeon E5-2697 v2 and E5-2695 v2 have twelve physical cores and a 30MB shared L3 cache, which means that you give up quick a bit of base clock speed to get twelve cores. If you want to minimize your physical core counts for SQL Server 2012 licensing purposes, the eight-core, 2.6GHz Xeon E5-2650 v2 would be a good choice. I am a little surprised that there is not a four or six-core model in the lineup, but we may not have all of the available models yet. Table 1 shows the known specifications for the Xeon E5-2600 v2 series processors.

Model Physical Cores Base Clock Speed L3 Cache Size
Xeon E5-2640 v2 8 2.0GHz 20MB
Xeon E5-2643 v2 10 (??) 3.5GHz 25MB
Xeon E5-2650 v2 8 2.6GHz 20MB
Xeon E5-2650L v2 10 1.7GHz 25MB
Xeon E5-2660 v2 10 2.2GHz 25MB
Xeon E5-2667 v2 10 3.3GHz 25MB
Xeon E5-2670 v2 10 2.5GHz 25MB
Xeon E5-2680 v2 10 2.8GHz 25MB
Xeon E5-2687W v2 10 3.4GHz 25MB
Xeon E5-2690 v2 10 3.0GHz 25MB
Xeon E5-2695 v2 12 2.4GHz 30MB
Xeon E5-2697 v2 12 2.7GHz 30MB

Table 1: Intel E5-2600 v2 Series Model Information

I am a little suspicious of the core count and L3 cache size for the Xeon E5-2643 v2. I think it is more likely to have eight physical cores and a 20MB L3 cache based on it’s base clock speed. Even though the Ivy Bridge-EP core counts are up to 50% higher than the 32nm Xeon E5-2600 series, the TDP ratings are about the same, which shows the benefits of moving to a smaller 22nm manufacturing process. The only low-power SKU in the lineup is Xeon E5-2650L v2. It has ten CPU cores, a 1.7 GHz base clock speed, and a 70 Watt TDP rating. It would not be a good choice for SQL Server, since you give up a significant amount of performance to save perhaps 20-30 watts of electrical usage per processor. The Xeon E5-2600 v2 processors are supposed to have two QPI 1.1 links, up to 40 lanes of PCI Express 3.0 interface, and a 4-channel DDR3 memory controller that supports DDR3-1866 memory.

You should be able to get an existing model server with these processors sometime in July. Getting a two-socket system with two Intel Xeon E5-2690 v2 processors will be a pretty compelling choice for a lot of SQL Server 2012/2014 workloads. I predict we will see TPC-E scores in the 2500-2600 range for that type of system, which compares quite well to a four-socket, Intel E7-4870 system that would cost twice as much for SQL Server 2012 Enterprise core licenses.

Hardware 201: Selecting and Sizing Database Hardware

I had the opportunity to present Hardware 201 in Boulder on June 18 and then do it again in Denver on June 20. It was fun presenting to both user’s groups nearly back to back. Here is the abstract:

Hardware 201: Selecting and Sizing Database Hardware for OLTP Performance

The foundation of database performance is the underlying server hardware and storage subsystem. Even the best designed and optimized database application can be crippled by an inadequate hardware and storage infrastructure. Recent advances in new processors and chipsets, along with improvements in magnetic and SSD storage have dramatically changed the evaluation and selection process compared to the past. Many database professionals struggle to keep up with new technology and often simply let someone else make their hardware selection and sizing decisions. Unfortunately, the DBA usually gets the blame for any performance issues that crop up later. Don’t let this happen to you! This session covers current and upcoming hardware from both Intel and AMD and gives you the tools and resources to make better hardware selection decisions to support SQL Server OLTP workloads.

New AMD-based TPC-E Benchmark Result

HP recently submitted a TPC-E benchmark result for a two-socket HP ProLiant DL385p Gen 8 system that is using two 2.8GHz AMD Opteron 6386SE, 16-core processors. This system had a score of 1416.37 TpsE, which does not sound too bad until you compare it to a very similar two-socket HP ProLiant DL380p Gen 8 system that is using two 2.9GHz Intel Xeon E5-2690, eight-core processors. The Intel system had a score of 1881.76 TpsE, which is 32.8% higher than the AMD-based system. That is pretty significant just from a pure OLTP performance perspective, but the story gets even worse when you look at the per-physical core performance and the SQL Server 2012 Enterprise Edition licensing cost differences for the two systems, as shown in Figure 1.

Processor TPC-E Score Score/Physical Core SQL 2012 License Cost
Intel Xeon E5-2690 1881.76 117.61 $109,952
AMD Opteron 6386SE 1416.37 44.26 $164,928

Figure 1: Comparative TPC-E Metrics and SQL Server 2012 License Costs

The DL385p Gen 8 system has 32 physical cores (that all must be licensed with $6872.00 SQL Server 2012 Enterprise Edition core licenses). The SQL Server 2012 Core Factor Table gives a 25% discount for modern AMD processors that have more than six physical cores. You can download it here (PDF warning), although Microsoft has not updated it to include the newer Opteron 6300 series of processors, which is probably just an oversight by Microsoft.

The DL 380p Gen 8 system only has 16 physical cores that must be licensed (hyper-threading is not taken into account for licensing on physical servers), so the SQL Server 2012 Enterprise Edition licensing cost is significantly less. Paying about 50% more to get about 33% less performance does not seem like a compelling value proposition!

I really wish that AMD was able to present some viable competition in this area for Intel, but I just don’t see that happening based on the currently available roadmaps from both companies. Lacking any real competition on the performance front, there is less incentive for Intel to meet their release schedules for new processor microarchitectures.

Speaking at Boulder SQL Server User’s Group on June 18

I will be speaking at the Boulder SQL Server User’s Group on June 18, 2013. I will be presenting an updated version of  Hardware 201. Here is the abstract:

Hardware 201: Selecting and Sizing Database Hardware for OLTP Performance

The foundation of database performance is the underlying server hardware and storage subsystem. Even the best designed and optimized database application can be crippled by an inadequate hardware and storage infrastructure. Recent advances in new processors and chipsets, along with improvements in magnetic and SSD storage have dramatically changed the evaluation and selection process compared to the past. Many database professionals struggle to keep up with new technology and often simply let someone else make their hardware selection and sizing decisions. Unfortunately, the DBA usually gets the blame for any performance issues that crop up later. Don’t let this happen to you! This session covers current and upcoming hardware from both Intel and AMD and gives you the tools and resources to make better hardware selection decisions to support SQL Server OLTP workloads.

Just in case you are curious, I will be driving my red Tesla Model S P85 to both meetings, so if you are interested in seeing and possibly riding in a Tesla, you might want to show up…  Speaking at Boulder SQL Server User’s Group on June 18

Regardless of that, the meeting should be a lot of fun, and I hope to see a lot of people there!

The Accidental DBA (Day 17 of 30): Configuring Alerts for High Severity Problems

This month the SQLskills team is presenting a series of blog posts aimed at helping Accidental/Junior DBAs ‘keep the SQL Server lights on’. It’s a little taster to let you know what we cover in our Immersion Event for The Accidental/Junior DBA, which we present several times each year. If you know someone who would benefit from this class, refer them and earn a $50 Amazon gift card – see class pages for details. You can find all the other posts in this series at http://www.SQLskills.com/help/AccidentalDBA. Enjoy!

One necessary part of being a responsible, pro-active DBA is to try to configure your SQL Server instances in such a manner so they will be as reliable as possible. This begins with how you initially install and configure the operating system and SQL Server, and what you do over time to maintain and monitor your SQL Server instances. I previously wrote a three part series about how to provision a new SQL Server instance that is available on the SimpleTalk web site. Part One is here, Part Two is here, and Part Three is here. If you read and follow my recommendations in those three articles, you will have a very good installation and configuration foundation to build on going forward.

One subject that I touched on in Part Three of that series was how to use some T-SQL to quickly create SQL Server Agent Alerts for a number of critical error conditions. SQL Server Agent Alerts are often confused with SQL Server Agent job notifications, but they are actually quite different. SQL Server Agent job notifications allow you to send an e-mail or page to a SQL Server Operator when an Agent job fails, succeeds or completes. The most common usage is to send an e-mail to your SQL Server Operator (which can be an e-mail distribution list) whenever a SQL Server Agent job fails. You really should make sure each of your SQL Server Agent Jobs has a proper SQL Server Agent job notification so that someone in your organization knows whenever a SQL Server Agent job fails, so that the problem can be investigated and corrected. The Notification tab for a SQL Server Agent Job is shown in Figure 1.

image thumb4 The Accidental DBA (Day 17 of 30): Configuring Alerts for High Severity Problems

Figure 1: SQL Server Agent Job Notification Tab

While these Agent Job notifications are important, they are not the same thing as a SQL Server Agent Alert. A SQL Server Agent Alert will trigger an action (such as notifying an Operator by sending an e-mail) whenever certain errors occur or certain other performance conditions exist (that are detected from Windows Performance Monitor objects values or Windows Management Instrumentation (WMI) queries). You can also execute a SQL Server Agent Job in response to a SQL Server Agent Alert, in addition to simply notifying an Operator. This is much better than finding out about a problem by stumbling across it in the SQL Server Error Log or finding about it when it has led to some problem that is affecting your availability or data integrity. Figure 2 shows the General tab for a SQL Server Agent Alert. Figure 3 shows the Response tab for for a SQL Server Agent Alert.

image thumb5 The Accidental DBA (Day 17 of 30): Configuring Alerts for High Severity Problems

Figure 2: SQL Server Agent Alert General Tab

 

image thumb6 The Accidental DBA (Day 17 of 30): Configuring Alerts for High Severity Problems

Figure 3: SQL Server Agent Alert Response Tab

Paul Randal previously wrote about the importance of creating SQL Server Agent Alerts. I wanted to make it even easier to create a set of very important SQL Server Agent Alerts by just running a relatively simple T-SQL script. The code below automatically picks up the server name and uses that as part of the alert name. It also uses variables for the operator name and for the alert category name. In this script, the delay between responses is set to 900 seconds, which is 15 minutes. That might be a little on the long side, but I did not want the operator to get spammed with e-mail alerts every couple of minutes. You can always adjust that value to whatever you see fit.

You need to make sure that you have a SQL Operator defined for your instance (and that you change the name of the operator in the script below to match your Operator name). You also need to make sure that you have Database Mail properly configured and enabled, so that you will actually get an e-mail when one of your SQL Server Agent Alerts is triggered.

-- Add important SQL Agent Alerts to your instance
-- Change the @OperatorName as needed

USE [msdb];
GO

-- Make sure you have an Agent Operator defined
-- Change @OperatorName as needed
DECLARE @OperatorName sysname = N'SQLDBAGroup';

-- Change @CategoryName as needed
DECLARE @CategoryName sysname = N'SQL Server Agent Alerts';

-- Add Alert Category if it does not exist
IF NOT EXISTS (SELECT *
               FROM msdb.dbo.syscategories
               WHERE category_class = 2  -- ALERT
               AND category_type = 3
               AND name = @CategoryName)
    BEGIN
        EXEC dbo.sp_add_category @class = N'ALERT', @type = N'NONE', @name = @CategoryName;
    END

-- Get the server name
DECLARE @ServerName sysname = (SELECT @@SERVERNAME);

-- Alert Names start with the name of the server 
DECLARE @Sev19AlertName sysname = @ServerName + N' Alert - Sev 19 Error: Fatal Error in Resource';
DECLARE @Sev20AlertName sysname = @ServerName + N' Alert - Sev 20 Error: Fatal Error in Current Process';
DECLARE @Sev21AlertName sysname = @ServerName + N' Alert - Sev 21 Error: Fatal Error in Database Process';
DECLARE @Sev22AlertName sysname = @ServerName + N' Alert - Sev 22 Error Fatal Error: Table Integrity Suspect';
DECLARE @Sev23AlertName sysname = @ServerName + N' Alert - Sev 23 Error: Fatal Error Database Integrity Suspect';
DECLARE @Sev24AlertName sysname = @ServerName + N' Alert - Sev 24 Error: Fatal Hardware Error';
DECLARE @Sev25AlertName sysname = @ServerName + N' Alert - Sev 25 Error: Fatal Error';
DECLARE @Error825AlertName sysname = @ServerName + N' Alert - Error 825: Read-Retry Required';

-- Sev 19 Error: Fatal Error in Resource
IF NOT EXISTS (SELECT name FROM msdb.dbo.sysalerts WHERE name = @Sev19AlertName)
    EXEC msdb.dbo.sp_add_alert @name = @Sev19AlertName, 
                  @message_id=0, @severity=19, @enabled=1, 
                  @delay_between_responses=900, @include_event_description_in=1,
                  @category_name = @CategoryName, 
                  @job_id=N'00000000-0000-0000-0000-000000000000';

-- Add a notification if it does not exist
IF NOT EXISTS(SELECT *
          FROM dbo.sysalerts AS sa
          INNER JOIN dbo.sysnotifications AS sn
          ON sa.id = sn.alert_id
          WHERE sa.name = @Sev19AlertName)
    BEGIN
        EXEC msdb.dbo.sp_add_notification @alert_name = @Sev19AlertName, @operator_name=@OperatorName, @notification_method = 1;
    END

-- Sev 20 Error: Fatal Error in Current Process
IF NOT EXISTS (SELECT name FROM msdb.dbo.sysalerts WHERE name = @Sev20AlertName)
    EXEC msdb.dbo.sp_add_alert @name = @Sev20AlertName, 
                  @message_id=0, @severity=20, @enabled=1, 
                  @delay_between_responses=900, @include_event_description_in=1,
                  @category_name = @CategoryName, 
                  @job_id=N'00000000-0000-0000-0000-000000000000'

-- Add a notification if it does not exist
IF NOT EXISTS(SELECT *
          FROM dbo.sysalerts AS sa
          INNER JOIN dbo.sysnotifications AS sn
          ON sa.id = sn.alert_id
          WHERE sa.name = @Sev20AlertName)
    BEGIN
        EXEC msdb.dbo.sp_add_notification @alert_name = @Sev20AlertName, @operator_name=@OperatorName, @notification_method = 1;
    END

-- Sev 21 Error: Fatal Error in Database Process
IF NOT EXISTS (SELECT name FROM msdb.dbo.sysalerts WHERE name = @Sev21AlertName)
    EXEC msdb.dbo.sp_add_alert @name = @Sev21AlertName, 
                  @message_id=0, @severity=21, @enabled=1, 
                  @delay_between_responses=900, @include_event_description_in=1,
                  @category_name = @CategoryName, 
                  @job_id=N'00000000-0000-0000-0000-000000000000';

-- Add a notification if it does not exist
IF NOT EXISTS(SELECT *
          FROM dbo.sysalerts AS sa
          INNER JOIN dbo.sysnotifications AS sn
          ON sa.id = sn.alert_id
          WHERE sa.name = @Sev21AlertName)
    BEGIN
        EXEC msdb.dbo.sp_add_notification @alert_name = @Sev21AlertName, @operator_name=@OperatorName, @notification_method = 1;
    END

-- Sev 22 Error: Fatal Error Table Integrity Suspect
IF NOT EXISTS (SELECT name FROM msdb.dbo.sysalerts WHERE name = @Sev22AlertName)
    EXEC msdb.dbo.sp_add_alert @name = @Sev22AlertName, 
                  @message_id=0, @severity=22, @enabled=1, 
                  @delay_between_responses=900, @include_event_description_in=1,
                  @category_name = @CategoryName, 
                  @job_id=N'00000000-0000-0000-0000-000000000000';

-- Add a notification if it does not exist
IF NOT EXISTS(SELECT *
          FROM dbo.sysalerts AS sa
          INNER JOIN dbo.sysnotifications AS sn
          ON sa.id = sn.alert_id
          WHERE sa.name = @Sev22AlertName)
    BEGIN
        EXEC msdb.dbo.sp_add_notification @alert_name = @Sev22AlertName, @operator_name=@OperatorName, @notification_method = 1;
    END

-- Sev 23 Error: Fatal Error Database Integrity Suspect
IF NOT EXISTS (SELECT name FROM msdb.dbo.sysalerts WHERE name = @Sev23AlertName)
    EXEC msdb.dbo.sp_add_alert @name = @Sev23AlertName, 
                  @message_id=0, @severity=23, @enabled=1, 
                  @delay_between_responses=900, @include_event_description_in=1,
                  @category_name = @CategoryName, 
                  @job_id=N'00000000-0000-0000-0000-000000000000';

-- Add a notification if it does not exist
IF NOT EXISTS(SELECT *
          FROM dbo.sysalerts AS sa
          INNER JOIN dbo.sysnotifications AS sn
          ON sa.id = sn.alert_id
          WHERE sa.name = @Sev23AlertName)
    BEGIN
        EXEC msdb.dbo.sp_add_notification @alert_name = @Sev23AlertName, @operator_name = @OperatorName, @notification_method = 1;
    END

-- Sev 24 Error: Fatal Hardware Error
IF NOT EXISTS (SELECT name FROM msdb.dbo.sysalerts WHERE name = @Sev24AlertName)
    EXEC msdb.dbo.sp_add_alert @name = @Sev24AlertName, 
                  @message_id=0, @severity=24, @enabled=1, 
                  @delay_between_responses=900, @include_event_description_in=1,
                  @category_name = @CategoryName, 
                  @job_id=N'00000000-0000-0000-0000-000000000000';

-- Add a notification if it does not exist
IF NOT EXISTS(SELECT *
          FROM dbo.sysalerts AS sa
          INNER JOIN dbo.sysnotifications AS sn
          ON sa.id = sn.alert_id
          WHERE sa.name = @Sev24AlertName)
    BEGIN
        EXEC msdb.dbo.sp_add_notification @alert_name = @Sev24AlertName, @operator_name = @OperatorName, @notification_method = 1;
    END

-- Sev 25 Error: Fatal Error
IF NOT EXISTS (SELECT name FROM msdb.dbo.sysalerts WHERE name = @Sev25AlertName)
    EXEC msdb.dbo.sp_add_alert @name = @Sev25AlertName, 
                  @message_id=0, @severity=25, @enabled=1, 
                  @delay_between_responses=900, @include_event_description_in=1, 
                  @category_name = @CategoryName,
                  @job_id=N'00000000-0000-0000-0000-000000000000';

-- Add a notification if it does not exist
IF NOT EXISTS(SELECT *
          FROM dbo.sysalerts AS sa
          INNER JOIN dbo.sysnotifications AS sn
          ON sa.id = sn.alert_id
          WHERE sa.name = @Sev25AlertName)
    BEGIN
        EXEC msdb.dbo.sp_add_notification @alert_name = @Sev25AlertName, @operator_name = @OperatorName, @notification_method = 1;
    END

-- Error 825: Read-Retry Required
IF NOT EXISTS (SELECT name FROM msdb.dbo.sysalerts WHERE name = @Error825AlertName)
    EXEC msdb.dbo.sp_add_alert @name = @Error825AlertName, 
                  @message_id=825, @severity=0, @enabled=1, 
                  @delay_between_responses=900, @include_event_description_in=1, 
                  @category_name = @CategoryName, 
                  @job_id=N'00000000-0000-0000-0000-000000000000';

-- Add a notification if it does not exist
IF NOT EXISTS(SELECT *
          FROM dbo.sysalerts AS sa
          INNER JOIN dbo.sysnotifications AS sn
          ON sa.id = sn.alert_id
          WHERE sa.name = @Error825AlertName)
    BEGIN
        EXEC msdb.dbo.sp_add_notification @alert_name = @Error825AlertName, @operator_name = @OperatorName, @notification_method = 1;
    END
GO



First Intel Haswell Desktop Build

Last Sunday, I was in line at 11AM at the Denver Micro Center (along with a number of other computer geeks) to buy the parts for a new Intel 4th generation Core (aka Haswell) desktop system. This system was destined for my fiancé, Veronica Thomas, as a replacement for a system I built for her back in January of 2011.

Since most of her computer time is spent on Facebook and Gmail, I figured that a Haswell Core i5 processor would be more than sufficient. I also wanted the system to use the the least amount of electricity possible and to be as quiet as possible (without going to any extreme measures). Here are the exact components that I selected:

LG 24X  SATA DVD-RW (OEM)                               $15.99

Samsung 256GB 840 Pro SSD                              $229.99

Crucial 16GB (2×8) DDR3 1600MHz RAM                 $79.99

Corsair 430W CX-430M modular power supply        $59.99

Gigabyte GA-Z87-D3HP LGA 1150 motherboard    $119.99

Intel Core i5-4670K boxed processor                    $199.99

Fractal Design Define R4 case                               $109.99

The Fractal Design Define R4 case is very easy to work with, with lots of room and openings to route the cables in a separate compartment under the motherboard. It also has sound deadening material lining the inside of the case. It also has eight 3.5/2.5 internal drive bays with drive carriers that easily accommodate 2.5” SSDs, with screw holes in the right positions.

The Gigabyte GA-Z87-D3HP motherboard is a mid-range Z87 motherboard that has six SATA III ports and four memory slots, along with lots of USB 3.0 ports. I did not see any reason to get a high-end gaming-oriented motherboard. I did enable Extreme Memory Profile (XMP) in the BIOS for a mild overclock.

The 22nm, 3.4GHz Intel Core i5-4670K is the top-of-the-line, unlocked Core i5 desktop processor that has the improved HD4600 integrated graphics, which are more than capable of handling normal desktop and non-extreme gaming usage. Using the integrated graphics would save money and electrical usage compared to discrete graphics. I decided to just use the included, stock air cooler for now. Since it is a K-series, unlocked processor, it does not have VT-d virtualization support for directed I/O, but that was not a factor for this system. You can see the CPU tab from CPU-Z in Figure 1.

image thumb First Intel Haswell Desktop Build

Figure 1: CPU tab of CPU-Z for Intel Core i5-4670K

I would have like to have gotten a Seasonic modular, fan-less power supply, but Micro Center no longer carries them. I ended up with a pretty nice 430 watt, Corsair 430M modular power supply that is 80PLUS Bronze rated. The reason for getting a low wattage power supply is that power supplies are less efficient when they are only putting out a small fraction of their rated output, so if you know that you are building a system that will only use a small amount of electricity, you are better off to get a good quality, low wattage power supply rather than a 750-1000 watt power supply. Don’t make the mistake of getting a $19.99. no-name 400 watt power supply though.

The 256GB Samsung 840 Pro SSD is still one of the best desktop SSDs available right now, and there was an $20.00 discount if I bought it along with a processor or motherboard. It is actually available for $10.00 less on Amazon right now, but I did not want to wait…  You should make sure to download the Samsung Magician software, so you can easily download and install the latest firmware and optimize your OS for an SSD. This is more important with Windows 7 than for Windows 8. Figure 2 shows the CrystalDiskMark results for the Samsung 840 Pro SSD.

image thumb1 First Intel Haswell Desktop Build

Figure 2: CrystalDiskMark Results for 256GB Samsung 840 Pro SSD

As you can see in Figure 3, this relatively modest system has a Windows Experience Index (WEI) score of 6.7, with the lowest scores being for the integrated graphics. A 6.7 score is not bad at all, and the other scores are 7.7-7.9. Not too bad for a system that cost slightly over $800.00 and only draws 25 watts at idle.

image thumb2 First Intel Haswell Desktop Build

Figure 3: WEI Results for Core i5-4670K System

As you can see in Figure 4, this system has a 32-bit Geekbench score of 10090, which is quite good for a Core i5 system.

image thumb3 First Intel Haswell Desktop Build

Figure 4: Geekbench 2.4.3 Results

SQL Server 2012 Service Pack 1 Cumulative Update 4

On May 31, Microsoft finally posted SQL Server 2012 Service Pack 1 Cumulative Update 4, which is Build 11.0.3368. This release was about two weeks later than expected, which is a little unusual for Cumulative Updates. I count 38 fixes in the public fix list.

These are probably the two most interesting fixes for the Database Engine:

An update is available for SQL Server 2012 Memory Management

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

Remember, this CU is only for SQL Server 2012 Service Pack 1.

The Accidental DBA (Day 4 of 30): SQL Server Installation and Configuration Best Practices

This month the SQLskills team is presenting a series of blog posts aimed at helping Accidental/Junior DBAs ‘keep the SQL Server lights on’. It’s a little taster to let you know what we cover in our Immersion Event for The Accidental/Junior DBA, which we present several times each year. If you know someone who would benefit from this class, refer them and earn a $50 Amazon gift card – see class pages for details. You can find all the other posts in this series at http://www.SQLskills.com/help/AccidentalDBA. Enjoy!

SQL Server 2012 is deceptively easy to install. You can pretty much click on the “Next” button a number of times, and you can get SQL Server 2012 installed without really having any idea what you are doing. SQL Server 2012 will run with all of the installation and configuration defaults, and depending on your workload, you may be perfectly fine for some time. On the other hand, this is somewhat like a ticking time bomb, waiting to blow up in the face of the unfortunate accidental DBA at the worst possible time.

If you have a more substantial workload or you just want to make sure that you have the best installation and configuration possible, you need to do some preparation work and planning before you install SQL Server 2012, and you need to make the correct choices during the installation process. You also need to make some key instance-level configuration changes after you are done installing and updating SQL Server 2012.

This begins with having an appropriate server and storage subsystem available. The exact details of your server and processor selection matter a great deal because of the core-based licensing in SQL Server 2012. Once you have the server and storage subsystem in place, and you have Windows Server 2012 installed, you want to make sure that the main system BIOS, all of the firmware, and all of the device drivers have been updated to the latest versions. You also want to make sure that Windows Server 2012 has been fully patched, using Microsoft Update (which is a superset of Microsoft Update).

In most environments, you are going to want to have a dedicated Windows domain account for each SQL Server service that you will be using, such as the Database Engine, SQL Server Agent, etc. These should be regular domain accounts with no special rights, since the SQL Server installation program will grant them the local rights that they need during the installation. Depending on your organization, getting these accounts created could take some time, but you will need them (along with their passwords) during the installation.

You also want to make sure that your hardware power management (in your BIOS) is either disabled or set to OS control. Along with this, you need to ensure that the Windows Power Plan is changed from the default of “Balanced” to the “High Performance” setting. Forgetting to make these changes is one of the most common mistakes that I see in the field, even among experienced database administrators. It is also very important to make sure that Intel Turbo Boost is enabled in the BIOS (which it usually is by default). Intel hyper-threading (HT) is somewhat more controversial. If you have an OLTP workload, I would always enable HT on newer processors (Nehalem and newer) unless you have done testing with your workload that shows that you are better off without HT (which will be very rare). With DW workloads, you may want to disable HT, but you really should do some testing with your workload before you decide.

You want to think about how you are going to configure your storage subsystem in terms of RAID levels, and the number and composition of logical drives that will be presented to Windows. This will be determined by how many drives you have available, your anticipated workload, your desired performance characteristics and your necessary drive space. After your logical drives have been configured and presented to Windows, you should test their performance, first with CrystalDiskMark, and then with SQLIO, to make sure you are getting the performance you expect. If you are using RAID controllers with dedicated hardware caches, you want to make sure that the cache policy is enabled so that the cache is actually being used.

You should also (in almost all  cases) enable Windows Instant File Initialization (IFI) and Lock Pages in Memory (LPIM) by granting these rights to the SQL Server Service account. Once you have installed SQL Server 2012 you will want to get it fully patched with the latest Service Pack and Cumulative Update (CU). Installing SQL Server Cumulative Updates is also somewhat controversial, but I am a proponent. Especially with SQL Server 2012, many important issues have been corrected with CUs and Microsoft has even added new features though the CU process.

You should also set the instance-level Maximum Server memory setting to an appropriate value (especially if you have enabled LPIM), and you should enable the “Optimize for ad hoc workloads” instance-level setting. I also generally will start out with at least four tempdb data files, making sure they are the same size, with the same autogrowth increment. Finally, I will add Trace Flag (TF) 3226 as a startup parameter to disable the logging of successful database backup messages to the SQL Server Error log.

I wrote a three-part article series that goes into more detail on this subject last year:

Provisioning a New SQL Server Instance – Part One

Provisioning a New SQL Server Instance – Part Two

Provisioning a New SQL Server Instance – Part Three

I also recorded a Pluralsight course called SQL Server 2012: Installation and Configuration, and I covered how to install, patch and configure SQL Server 2008 R2 in my book, SQL Server Hardware. Erin and Jonathan will be covering this topic in more depth in Module 2 of their IE0: Immersion Event for the Accidental DBA class in September.

The Accidental DBA (Day 3 of 30): Hardware Selection: Solid State Drives and Usage

This month the SQLskills team is presenting a series of blog posts aimed at helping Accidental/Junior DBAs ‘keep the SQL Server lights on’. It’s a little taster to let you know what we cover in our Immersion Event for The Accidental/Junior DBA, which we present several times each year. If you know someone who would benefit from this class, refer them and earn a $50 Amazon gift card – see class pages for details. You can find all the other posts in this series at http://www.SQLskills.com/help/AccidentalDBA. Enjoy!

I usually get two questions whenever I talk about hardware at a SQL Server event. The first one is always about virtualization, while the second is usually about Solid State Drives (SSDs) and how they should be used with SQL Server. I am often asked which components of a SQL Server database should be moved to flash-based storage as it becomes more affordable. Unfortunately, the answer is that it depends on your workload, and on where (if anywhere) you are experiencing I/O bottlenecks in your system, whether it is on your SQL Server data files, log files, or tempdb files.

Traditional magnetic spinning storage (a hard drive) does relatively well with sequential read and write operations. A single, 15K rpm Serial-Attached Storage (SAS) drive can do about 150-200MB/sec of sequential throughput. Where traditional hard drives have more issues is with random input/output operations, which is measured as Input Output Operations per Second (IOPS). Since a traditional hard drive is an electro-mechanical device, with a moving actuator arm that has to move the drive heads over a spinning disk platter to find and then access the data you need, you are dealing with much higher latency than you see with solid-state storage that has no moving parts. Because of this, a single, 15K rpm Serial-Attached Storage (SAS) drive can only do about 150-200 IOPS.

In contrast, a single 6Gbps SATA or SAS solid-state drive can do about 550MB/sec for sequential throughput and about 100,000 IOPS for random read/write operations. If that is not impressive enough, there are flash-based, high-end PCI-E storage devices that can do up to 6GB/sec for sequential throughput and about 1,000,000 IOPS for random read/write operations. There are also more affordable flash-based, PCI-E storage devices that can do up to 2GB/sec for sequential throughput and about 200,000 IOPS for random read/write operations.

Flash-based storage has become much more affordable and much more reliable over the past couple of years. There are some entry-level, Enterprise-class flash-based storage devices from Intel, such as the Intel DC S3700 line of SATA SSDs and the Intel 910 series PCI-E storage card line, that make it much more feasible to start moving more of your database infrastructure to solid-state storage.

Depending on your database size and your budget, it may make a lot of sense to move an entire user database (data files and log file) to solid-state storage, especially with a heavy OLTP workload. If you have multiple user databases running on a single instance of SQL Server, your I/O workload will become more randomized (with lots of random reads and writes), which means that you will see even more of a benefit from solid-state storage, which excels at random I/O operations.

If you don’t have enough space available to move all of your user database files to solid-state storage, you will need to be more selective about what types of files you move to solid-state storage. You will want to think about what type of I/O workload you have (which is related to your overall workload type), and which logical drives and which specific database files are seeing I/O bottlenecks.

For example, if you have multiple OLTP databases on the same instance of SQL Server, and they all have their log file on the same logical drive, that drive will be dealing with a highly random I/O workload. Moving those log files to solid-state storage could be an excellent solution to improve your I/O performance. Another example might be where you have very heavy tempdb usage, and you are seeing very high read and write latency for your tempdb data files (as opposed to allocation contention from a single tempdb data file). This would be another case where moving your tempdb data files to solid-state storage could be very beneficial.

Erin and Jonathan will be covering this topic in more detail in their IE0: Immersion Event for the Accidental DBA class in September. I will also be discussing it in great detail in the IEHW: Immersion Event on SQL Server Hardware class in September. You can get a head start on all of this by watching my Pluralsight course SQL Server 2012: Evaluating and Sizing Hardware.

SQL Server 2014 Announced

Microsoft has finally released some information during the TechEd North America 2013 Day 1 Keynote about the next major version of SQL Server, which will be called SQL Server 2014. According to Mary Jo Foley, it is due to be released in early 2014, shortly after the release of Windows Server 2012 R2 (aka Windows Blue Server).

Here are a few quick links to some early information and thoughts about SQL Server 2014:

SQL Server 2014: Unlocking Real-Time Insights

TechEd North America 2013 Editor’s Choice: SQL Server

(Almost) Everything You Need to Know About the Next Version of SQL Server

SQL Server 2014 due out in late 2013, in-memory OLTP a big feature

There are definitely some interesting new features in SQL Server 2014 that I will be talking about in some more detail pretty soon.