September 2012 - Glenn Berry

Dell 12th Generation Rack Mounted Server Comparison

Posted on: September 27, 2012 10:05 pm

I think it is useful to examine the various 12th generation, rack-mounted server models available from Dell, comparing them from a SQL Server perspective. There are currently seven models in this line, ranging from an entry-level, single-socket server to a four-socket server. All of these servers use the Intel Xeon E5 processor (Sandy Bridge), but different models use different series of that processor family, which is a very important detail to pay attention to for SQL Server 2012 usage. You could easily compare the available server models from another vendor in the same way, and I will probably do that in a future post.

Dell PowerEdge R320
This model server has a 1U form factor, one processor socket, and uses the Intel Xeon E5-2400 series processor. It has six memory slots (96GB total RAM with 16GB DIMMs), has eight 2.5 inch drive bays, and has one PCI-E 3.0 x8 and one PCI-E 3.0 x16 expansion slots. It has a total of four, six, or eight physical cores for SQL Server 2012 core licensing purposes. It has a total of eight, twelve, or sixteen logical cores with Intel hyper-threading enabled. The R320 is an interesting option for some smaller workloads, since it uses the Xeon E5-2400 series Sandy Bridge-EN processor (that is usually used in two-socket systems) instead of the Xeon E3-1200 Sandy Bridge or Xeon E3-1200 v2 series Ivy Bridge processor that is used in most new single-socket servers. This lets you use up to 96GB of RAM instead of being limited to 32GB of RAM, and it lets you have up to eight physical processor cores instead of being limited to four physical processor cores. The downside of this is being limited to slower processor clock speeds with the E5-2400 series compared to the E3-1200 series processors, which means you will see slower single-threaded performance. The R320 might be a good choice for a DW type of workload, where the extra processor cores and higher memory capacity would be more useful. A single-socket server with an Intel E3-1200 v2 series processor would be better for a small OLTP workload. Keep in mind that SQL Server 2012 Standard Edition is limited to 64GB of RAM.

Dell PowerEdge R420
This model server has a 1U form factor, two processor sockets, and uses Intel Xeon E5-2400 series processors. It also has twelve memory slots (192GB total RAM with 16GB DIMMs), has eight 2.5 inch drive bays, and has two PCI-E 3.0 x16 expansion slots. It has a total of eight, twelve, or sixteen physical cores for SQL Server 2012 core licensing purposes. It has a total of sixteen, 24, or 32 logical cores with Intel hyper-threading enabled. This model is a bad choice for SQL Server 2012. The Xeon E5-2400 series Sandy Bridge-EN processor is very limited compared to the Xeon E5-2600 series Sandy Bridge-EP processor. It has slower clock speeds, less memory bandwidth, and less memory capacity. Since you pay the same amount for each SQL Server 2012 core license regardless of what type of physical core is in the processor, the E5-2400 series is a bad choice compared to the E5-2600 series. Another problem with this model server is the fact that it only has two PCI-E expansion slots and eight internal drive bays, which limits your overall I/O capacity and performance.

Dell PowerEdge R520
This model server has a 2U form factor, two processor sockets, and uses Intel Xeon E5-2400 series processors. It also has twelve memory slots (192GB total RAM with 16GB DIMMs), has eight 3.5 inch drive bays, and has three PCI-E 3.0 x8 and one PCI-E 3.0 x16 expansion slots. It has a total of eight, twelve, or sixteen physical cores for SQL Server 2012 core licensing purposes. It has a total of sixteen, 24, or 32 logical cores with Intel hyper-threading enabled. This model is also a bad choice for SQL Server 2012 since it uses the same Intel Xeon E5-2400 series processor as the R420 . It does have four PCI-E expansion slots, which is a little better for I/O capacity and performance. Still, I would steer clear of both the R420 and R520 models for SQL Server 2012 usage.

Dell PowerEdge R620
This model server has a 1U form factor, two processor sockets, and uses Intel Xeon E5-2600 series processors. It has 24 memory slots (384GB total RAM with 16GB DIMMs), has ten 2.5 inch drive bays, and has one PCI-E 3.0 x8 and two PCI-E 3.0 x16 expansion slots. It also has a total of eight, twelve, or sixteen physical cores for SQL Server 2012 core licensing purposes. It has a total of sixteen, 24, or 32 logical cores with Intel hyper-threading enabled. The R620 is a much better choice for SQL Server 2012 than either the R420 or R520 since it uses the Intel Xeon E5-2600 series Sandy Bridge-EP processor. That processor series gives you higher clock speeds, higher memory bandwidth, and higher memory capacity compared to the Xeon E5-2400 series Sandy Bridge-EN processor. The R620 is limited to three PCI-E expansion slots, but it does have ten internal drive bays. Overall, it is a good model for use as an entry level two-socket database server, especially if you want a 1U form factor.

Dell PowerEdge R720
This model server has a 2U form factor, two processor sockets, and uses Intel Xeon E5-2600 series processors. It has 24 memory slots (384GB total RAM with 16GB DIMMs), has sixteen 2.5 inch drive bays, and has six PCI-E 3.0 x8 and one PCI-E 3.0 x16 expansion slots. It has a total of eight, twelve, or sixteen physical cores for SQL Server 2012 core licensing purposes. Total of sixteen, 24, or 32 logical cores with Intel hyper-threading enabled. The R720 is one of my favorite models in the Dell 12th generation line. It uses the same Intel Xeon E5-2600 series processor as the R620, but it has seven PCI-E expansion slots and sixteen internal drive bays, which combine to give you a lot of potential I/O capacity and performance. It does cost a little bit more than the R620, and it is in a 2U vertical size, so there are some scenarios where I would prefer an R620. An example scenario would be an OLTP workload where I knew that I would have external SAN storage with very good random I/O performance, and I wanted to be able to use 1U database servers instead of 2U database servers.

Dell PowerEdge R720xd
This model server has a 2U form factor, two processor sockets, uses Intel Xeon E5-2600 series processors, has 24 memory slots (384GB total RAM with 16GB DIMMs), has 26 2.5 inch drive bays, and has four PCI-E 3.0 x8 and two PCI-E 3.0 x16 expansion slots. Has a total of eight, twelve, or sixteen physical cores for SQL Server 2012 core licensing purposes. Total of sixteen, 24, or 32 logical cores with Intel hyper-threading enabled. The R720xd is similar to the R720, except that it has 26 internal drive bays and only six PCI-E expansion slots. This model could be a good choice if you can run your I/O workload on 26 internal drive bays, some or all of which could be solid state drives (SSDs). This could let you avoid the expense of an external direct-attached storage (DAS) enclosure or a storage area network (SAN).

Dell PowerEdge R820
This model server has a 2U form factor, four processor sockets, and uses Intel Xeon E5-4600 series processors. It has 48 memory slots (768GB total RAM with 16GB DIMMs), has sixteen 2.5 inch drive bays, and has five PCI-E 3.0 x8 and two PCI-E 3.0 x16 expansion slots. It also has a total of sixteen, 24, or 32 physical cores for SQL Server 2012 core licensing purposes. Total of 32, 48, or 64 logical cores with Intel hyper-threading enabled. The R820 has four processor sockets in a 2U vertical size. It uses the Intel Xeon E5-4600 series processor, which has lower clock speeds than the Xeon E5-2600 series. There is also some non-uniform memory access (NUMA) scaling loss as you move from a two-socket to a four-socket server, i.e. a four socket server does not have twice the scalability as a two socket server with the exact same processor. The R820 does have sixteen internal drive bays and seven PCI-E expansion slots, so it has good I/O capacity and performance potential. It also has twice the total RAM capacity compared to an R620, R720, or R720xd. In spite of all these factors, I would tend to prefer two R720xd servers instead of one R820 server, assuming you can split your workload between two servers. You would have faster, less expensive processors, over three times as many internal drive bays, and nearly twice as many PCI-E expansion slots, while paying the same SQL Server 2012 license costs. I really like the R720xd, with its 26 internal drive bays. I suspect that a very high percentage of SQL Server workloads would run extremely well on an R720xd. If 26 internal drives did not give you enough I/O performance and capacity, you could always add some internal solid state storage cards or use some form of external storage.

As a database professional, I would be actively lobbying against using the R420 or R520 models, since they have the entry-level Intel Xeon E5-2400 series processors, which have lower clock speeds and less memory bandwidth compared to the Intel Xeon E5-2600 series processors that are used in the R620, R720, and R720xd. They also have half of the total memory capacity and far fewer PCI-E slots compared to the higher end models. They are a little less expensive, but the hardware cost delta is very small compared to the SQL Server 2012 license costs. Remember, you are paying for SQL Server 2012 core licenses based on physical core counts, so you want to get the best package you can as far as the rest of the server goes. One nice fact is that the Intel Xeon E5 processor family is available in four-core, six-core, and eight core models, with specific four-core models having higher base clock speeds than the “top-of-the line” eight-core model processor. If you wanted to minimize your SQL Server 2012 core-based licensing costs and were willing to give up some scalability and capacity, you could pick one of these faster base clock speed four-core model processors for your server and actually see very good single-threaded performance.

A good example of a fast, quad-core Intel Xeon E5 processor is the Intel Xeon E5-2643 that runs at a base clock speed of 3.3GHz, with a Turbo Boost speed of 3.5GHz. This processor would give very good OLTP performance at 50% the SQL Server 2012 Core license cost of an eight-core Intel Xeon E5-2690 that runs at a base clock speed of 2.9GHz, with a Turbo Boost speed of 3.8GHz.

Posted in: Dell, Hardware, Intel, Processors, Sandy Bridge-EP, SQL Server 2012

Be the first to Reply!

How to Create SQL Server Agent Alerts for Critical Errors

By: Glenn Berry

Posted on: September 27, 2012 3:33 pm

One necessary part of being a responsible 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 recently 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 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. SQLskills own Paul Randal previously wrote about this. The code below is an updated version of the script that I included in my article that 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.

-- Add important SQL Agent Alerts to your instance

-- Change the Alert names and @OperatorName as needed
-- Glenn Berry
-- SQLskills
-- 9-27-2012

USE [msdb];
GO

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

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






Posted in: SQL Server 2005, SQL Server 2008, SQL Server 2008 R2, SQL Server 2012, SQL Server Agent Alerts

4 Comments

Save Money on SQL Server 2012 Licensing Costs

By: Glenn Berry

Posted on: September 12, 2012 3:53 pm

I recently had a chance to record a 33 minute podcast interview on RunAs Radio #281 with Richard Campbell, where I talked about the licensing changes in SQL Server 2012 and how to select your new hardware to minimize your SQL Server 2012 licensing costs. I also talked about Intel versus AMD processors, and talked about which Intel processors were good and bad choices for different workloads on SQL Server 2012.

RunAs Radio has been putting out weekly podcasts since April of 2007, and they have a lot of good content available there! Paul Randal and Kimberly Tripp did RunAs Radio #221 back in July 2011. Paul also did RunAs Radio #74 and RunAs Radio #72.

Posted in: Hardware, Intel, Processors, Sandy Bridge-EP, Speaking, SQL Server 2012

Be the first to Reply!

Semi-random musings about SQL Server performance

Dell 12th Generation Rack Mounted Server Comparison

How to Create SQL Server Agent Alerts for Critical Errors

Save Money on SQL Server 2012 Licensing Costs