Glenn Berry - Page 2 of 8 - Semi-random musings about SQL Server performance

A SQL Server Hardware Tidbit a Day – Day 22

Posted on: April 22, 2013 3:21 pm

I have written previously about Building a Workstation for SQL Server 2012 Development and Testing and Building a Larger Capacity SQL Server 2012 Workstation. In those posts, I talked about the fact that it becomes increasingly expensive to build a system from parts as you move beyond mainstream desktop systems based on an Intel Core i7-3770 or i7-3770K processor.

If you actually need more than 32GB of RAM or more than four physical processor cores, you will have to move up to a single-socket, Sandy Bridge-E, Socket 2011 system, which maxes out at 64GB of RAM and six physical processor cores, at a considerably higher cost. The next step up is moving to a single-socket, Sandy Bridge-EP system with one Intel Xeon E5-1600 series or E5-2600 series processor with up to eight physical processor cores and 128GB of RAM.

Finally, you can buy the components for a dual-socket, Socket 2011 system with two Intel Xeon E5-2600 series Sandy Bridge-EP processors to build a much more powerful and expensive system with up to 16 physical processor cores and 256GB of RAM. By the time you do this, you may end up spending so much money that you would be much better off buying a refurbished, actual server from someplace like the Dell Outlet.

Depending on what you are planning on doing with your server, you can choose a Tower server, a Rack server, or even a Blade server. Depending on your infrastructure, you are probably more likely to be looking at a tower server or a rack server.

Dell Outlet PowerEdge Tower Servers

Dell Outlet PowerEdge Rack Servers

One tactic I like to use when I am searching for a server on the Dell Outlet is to focus on the installed processors in the server. You are very unlikely to ever upgrade the processors in a server after you buy it since processor upgrade kits are usually quite expensive. That means you should focus on the processors and not worry as much about the amount of RAM. Getting more RAM later is easy and inexpensive.

Storage is a little trickier. Sometimes you can find outlet servers that have a lot of big, fast internal drives that are bargains. You may also be better off to think about buying a number Intel DC S3700 SSDs along with compatible 2.5” drive carriers from someplace like Amazon (since Dell does not seem to like selling empty drive carriers very much). You should be on the lookout for systems that have decent RAID controllers in them, since they are relatively expensive to buy later.

As you get ready to buy a server from the Dell Outlet, you want to make sure you have the necessary means of payment ready to go as soon as you find the system you want, since their available inventory is constantly changing. If your organization has a lot of bureaucratic overhead, it may take too long for you to get the necessary approvals before that system you were interested in is no longer available.

Posted in: Dell, Hardware, Processors

1 Comment

A SQL Server Hardware Tidbit a Day – Day 21

By: Glenn Berry

Posted on: April 22, 2013 2:40 pm

For Day 21 of this series, I want to talk a little bit about the TPC-E OLTP benchmark.

The TPC Benchmark E (TPC-E) is an OLTP performance benchmark that was introduced in February 2007. TPC-E is a not a replacement for the older TPC-C benchmark, but rather is a completely new OLTP benchmark. It is an OLTP, database-centric workload that is meant to reduce the cost and complexity of running the benchmark compared to the older TPC-C benchmark. It simulates the OLTP workload of a brokerage firm that interacts with customers using synchronous transactions and with a financial market using asynchronous transactions.

The business model of the brokerage firm is organized by Customers, Accounts, and Securities. The data model for TPC-E is significantly more complex, but more realistic than TPC-C, with 33 tables and many different data types. The data model for the TPC-E database does enforce referential integrity, unlike the older TPC-C data model.

The TPC-E database is populated with pseudo-real data, including customer names from the year 2000 U.S. Census, and company listings from the NYSE and NASDAQ. Having realistic data introduces data skew, and makes the data compressible. Unlike TPC-C, the storage media for TPC-E must be fault tolerant (which means no RAID 0 arrays). Overall, the TPC-E benchmark is designed to have reduced I/O requirements compared to the old TPC-C benchmark, which makes it both less expensive and more realistic since the sponsoring hardware vendors will not feel as much pressure to equip their test systems with disproportionately large disk subsystems in order to get the best test results. The TPC-E benchmark is also more CPU intensive than the old TPC-C benchmark. It is essentially CPU-bound, as long as you have adequate I/O capacity to drive the workload.

The TPC-E implementation is broken down into a Driver and a System Under Test (SUT), separated by a mandatory network. The Driver represents the various client devices that would use an N-tier client-server system, abstracted into a load generation system. The SUT has multiple Application servers (Tier A) that communicate with the database server and its associated storage subsystem (Tier B). TPC provides a transaction harness component that runs in Tier A, while the test sponsor provides the other components in the SUT.

The performance metric for TPC-E is transactions per second, tpsE. The actual tpsE score represents the average number of Trade Result transactions executed within one second. To be fully compliant with the TPC-E standard, all references to tpsE results must include the tpsE rate, the associated price per tpsE, and the availability date of the priced configuration.

It seems interesting that, as of early 2013, Microsoft is the only database vendor that has any submitted TPC-E results, even though the TPC-E benchmark has been available for over six years. Whatever the reasons why the other database vendors haven’t allowed any TPC-E results to be submitted by the hardware vendors, there are certainly many results posted for SQL Server, which makes it a very useful benchmark when assessing SQL Server hardware.

The most recent posted TPC-E result is for an IBM System x3850 X5 Server with a 5457.20 tpsE score for an eight-socket system. This system has eight, ten-core Intel Xeon E7-8870 processors that have a total of 160 logical cores for the system. It also has 4TB of RAM and 236 SSDs in its I/O subsystem, using a 22TB initial database size for the test. Looking at the Executive Summary, you can see that it is running SQL Server 2012 Enterprise Edition on top of Windows Server 2012 Standard Edition. This is basically the biggest system (in terms of RAM) that you can build under the current Windows Server 2012 license limits for memory.

It is using RAID 5 for the data files, RAID 10 for the log file and RAID 1 for tempdb, with (236) 200GB 3Gbps SAS SSDs (model # 81Y9956), and (2) 600GB 6Gbps 10K SAS drives. The data files are spread across eleven, twenty-drive RAID 5 arrays (using 200GB 3Gbps SSDs), while the log file is on one, sixteen-drive RAID 10 array (using 200GB 3Gbps SSDs), and tempdb is on one, two-drive RAID 1 array, using 600GB 10K SAS drives). That shows you that tempdb is probably not hit very hard during the TPC-E benchmark!

Posted in: Benchmarks, TPC-E

Be the first to Reply!

SQLSaturday #201 Slide Decks

By: Glenn Berry

Posted on: April 22, 2013 1:43 pm

I had the chance to give two presentations at SQLSaturday #201 in Huntington Beach, CA on April 20, 2013. The first one was Dr. DMV and the second one was Hardware 201: Selecting Database Hardware. Just in case you have any problem getting the decks from the SQLSaturday web site, I have links to the downloads below:

Dr DMV: How to Use Dynamic Management Views

SQL Server 2005 introduced Dynamic Management Views (DMVs) that allow you to see exactly what is happening inside your SQL Server instances and databases with much more detail than ever before. SQL Server 2008 R2 adds even more capability in this area. You can discover your top wait types, most CPU intensive stored procedures, find missing indexes, and identify unused indexes, to name just a few examples. This session (which is applicable to SQL Server 2005 through 2012), presents and explains over fifty DMV queries that you can quickly and easily use to detect and diagnose performance issues in your environment.

Hardware 201: Selecting Database Hardware

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. Don’t let this happen to you! This session covers current and upcoming hardware from both Intel and AMD.

I also want to thank the organizers, volunteers, and other speakers for putting on a great event!

Posted in: Speaking, SQLSaturday, Teaching

2 Comments

A SQL Server Hardware Tidbit a Day – Day 20

By: Glenn Berry

Posted on: April 20, 2013 7:16 am

For Day 20 of this series, I want to talk about the new processor numbering system for Xeon processors that Intel introduced on April 5, 2011. This new system will be used for the Xeon E3, E5 and E7 family processors. The model numbers for older Xeon processors were unchanged when this new numbering system was introduced.

Figure 1:Model Number Example for the Xeon E5-2690

xeon E3 1235 A SQL Server Hardware Tidbit a Day – Day 20

Figure 2: New Intel Xeon Processor Numbering System

The first two digits in the model number are the Product Line (E3, E5, or E7). After the Product Line designation, you have a four digit number that tells you more details about the particular processor. The first digit is the “wayness”, which is the number of physical CPUs that are allowed in a “node” (which is a physical server). This first digit can be 1, 2, 4, or 8. The second digit is the socket type, in terms of its physical and electrical characteristics. The last two digits are the processor SKU, with higher numbers generally being higher performance. Finally, you may have an L at the end, which is for energy efficient, low electrical power processors.

The E3 Product Line is for single-processor servers or workstations. The first generation of this line (E3-1200 series) is essentially the same as the desktop Sandy Bridge processors that were released in January 2011. The E5 Product Line is primarily for two-socket servers, with some overlap into the four-socket server space with the E5-4600 Series.

The E7 Product Line (the Westmere-EX) has different models that are meant for two-socket servers, four-socket servers, and eight-socket and above servers. The E7-2800 Series is for two-socket servers, the E7-4800 Series is for four-socket servers, while the E7-8800 Series is for eight-socket and above servers.

Going forward, you will see new releases appending a v2, v3, etc. to the model number to indicate a newer generation processor that uses the same basic model number as an earlier processor.

Posted in: Intel, Processors

Be the first to Reply!

A SQL Server Hardware Tidbit a Day – Day 19

By: Glenn Berry

Posted on: April 18, 2013 6:57 am

For Day 19 of this series, I am going to talk a little about RAID, which stands for Redundant array of independent disks or Redundant array of inexpensive disks, depending on who you believe.

RAID is a technology that allows the use of multiple hard drives, combined in various ways, to improve redundancy, availability and performance, depending on the RAID level used. When a RAID array is presented to a host in Windows, it is called a logical drive. Using RAID, the data is distributed across multiple disks in order to:

    • Overcome the I/O bottleneck of a single disk
    • Get protection from data loss through the redundant storage of data on multiple disks
    • Avoid any one hard drive being a single point of failure
    • Manage multiple drives more effectively

Regardless of whether you are using traditional magnetic hard drive storage or newer solid state storage technology, most database servers will employ some sort of RAID technology. RAID improves redundancy, improves performance, and makes it possible to have larger logical drives. RAID is used for both OLTP and DW workloads. Having more spindles in a RAID array helps both IOPS and throughput, although ultimately throughput can be limited by a RAID controller, HBA, NIC, or the PCI-E slot that is being used.

Keep in mind that while RAID does provide redundancy in your data storage, it is not a substitute for an effective backup strategy or a high availability/disaster recovery (HA/DR) strategy. Regardless of what level of RAID you use in your storage subsystem, you still need to run SQL Server full, differential, and log backups as necessary to meet your recovery point objective (RPO) and recovery time objective (RTO) goals.

There are a number of commercially-available RAID configurations, which I’ll review over the coming sections, and each has associated costs and benefits. When considering which level of RAID to use for different SQL Server components, you have to carefully consider your workload characteristics, keeping in mind your hardware budget. If cost is no object, I am going to want RAID 10 for everything, i.e. data files, log file, and tempdb. If my data is relatively static, I may be able to use RAID 5 for my data files. It is also fairly common to use RAID 5 for SQL Server backup files.

During the discussion, I will assume that you have a basic knowledge of how RAID works, and what the basic concepts of striping, mirroring, and parity mean.

RAID 0 (disk striping with no parity)

RAID 0 simply stripes data across multiple physical disks. This allows reads and writes to happen simultaneously, across all of the striped disks, so offering improved read and write performance, compared to a single disk. However, it actually provides no redundancy whatsoever. If any disk in a RAID 0 array fails, the array is off-line and all of the data in the array is lost. This is actually more likely to happen than if you only have a single disk, since the probability of failure for any single disk goes up as you add more disks. There is no disk space loss for storing parity data (since there is no parity data with RAID 0), but I don’t recommend that you use RAID 0 for database use, unless you enjoy updating your resume! RAID 0 is often used by serious computer gaming enthusiasts in order to reduce the time it takes to load portions of their favorite games. They do not keep any important data on their “gaming rigs”, so they are not that concerned about losing one of their drives. Even this usage is declining over time as SSDs become more affordable.

RAID 1 (disk mirroring or duplexing)

You need at least two physical disks for RAID 1. Your data is mirrored between the two disks, i.e. the data on one disk is an exact mirror of that on the other disk. This provides redundancy, since you can lose one side of the mirror without the array going off-line and without any data loss, but at the cost of losing 50% of your space to the mirroring overhead. RAID 1 can improve read performance, but can hurt write performance in some cases, since the data has to be written twice.

On a database server, it is very common to install the Windows Server operating system on two of the internal drives, configured in a RAID 1 array, and using an embedded internal RAID controller on the motherboard. In the case of a non-clustered database server, it is also common to install the SQL Server binaries on the same two drive RAID 1 array as the operating system. This provides basic redundancy for both the operating system and the SQL Server binaries. If one of the drives in the RAID 1 array fails, you will not have any data loss or down-time. You will need to replace the failed drive and rebuild the mirror, but this is a pretty painless operation, especially compared to reinstalling the operating system and SQL Server!

RAID 5 (striping with parity)

RAID 5 is probably the most commonly-used RAID level, for both general file server systems and for SQL Server. RAID 5 requires at least three physical disks. The data, and calculated parity information, is striped across the physical disks by the RAID controller. This provides redundancy because if one of the disks goes down, then the missing data from that disk can be reconstructed from the parity information on the other disks. Also, rather than losing 50% of your storage, in order to achieve redundancy, as for disk mirroring, you only lose 1/N of your disk space (where N equals the number of disks in the RAID 5 array) for storing the parity information. For example, if you had six disks in a RAID 5 array, you would lose 1/6th of your space for the parity information. As you add more disks to a RAID 5 array, the chances of losing any one of the disks goes up (due to simple statistics), so that is a reliability consideration for larger arrays.

However, you will notice a very significant decrease in performance while you are missing a disk in a RAID 5 array, since the RAID controller has to work pretty hard to reconstruct the missing data. Furthermore, if you lose a second drive in your RAID 5 array, the array will go offline, and all of the data will be lost. As such, if you lose one drive, you need to make sure to replace the failed drive as soon as possible. RAID 6 stores more parity information than RAID 5, at the cost of an additional disk devoted to parity information, so you can survive losing a second disk in a RAID 6 array.

Finally, there is a write performance penalty with RAID 5, since there is overhead to write the data, and then to calculate and write the parity information. As such, RAID 5 is usually not a good choice for transaction log drives, where we need very high write performance. I would also not want to use RAID 5 for data files where I am changing more than 10% of the data each day. One good candidate for RAID 5 is your SQL Server backup files. You can still get pretty good backup performance with RAID 5 volumes, especially if you use backup compression and striped backups.

RAID 10 and RAID 0+1

When you need the best possible write performance, you should consider either RAID 0+1 or, preferably, RAID 10. These two RAID levels both involve mirroring (so there is a 50% mirroring overhead) and striping but differ in the details in how it is done in each case.

In RAID 10 (striped set of mirrors), the data is first mirrored and then striped. In this configuration, it is possible to survive the loss of multiple drives in the array (one from each side of the mirror), while still leaving the system operational. Since RAID 10 is more fault tolerant than RAID 0+1, it is preferred for database usage.

In RAID 0+1 (mirrored pair of stripes) the data is first striped, and then mirrored. This configuration cannot handle the loss of more than one drive in each side of the array.

RAID 10 and RAID 0+1 offer the highest read/write performance, but incur a roughly 100% storage cost penalty, which is why they are sometimes called “rich man’s RAID”. These RAID levels are most often used for OLTP workloads, for both data files and transaction log files. As a SQL Server database professional, you should always try to use RAID 10 if you have the hardware and budget to support it. On the other hand, if your data is less volatile, you may be able to get perfectly acceptable performance using RAID 5 for your data files. By “less volatile”, I mean if less than 10% of your data changes per day, then you may still get acceptable performance from RAID 5 for your data files(s).

Posted in: High Availability, SSD, Storage

1 Comment

A SQL Server Hardware Tidbit a Day – Day 18

By: Glenn Berry

Posted on: April 17, 2013 4:27 pm

For Day 18 of this series, I am going to talk about the Intel 910 series PCI-E device that was released back in May of 2012. It is available in 400GB and 800GB capacities. The 400GB model has a suggested retail price about $2000.00 while the 800GB model has a suggested retail price of about $4000.00. As is typical with flash-based storage devices, the larger capacity model offers significantly better performance than the smaller capacity model, both for random I/O and for sequential reads and writes.

The 800GB model can do about 2000MB/sec for sequential reads and 1500MB/sec for sequential writes (in max performance mode), while the 400GB model can do about 1000MB/sec for sequential reads and about 750MB/sec for sequential writes. The 800GB model can do about 180,000 IOPs for random 4K reads and about 75,000 IOPS for random 4K writes, while the 400GB model can do about 90,000 IOPs for 4K random reads and about 38,000 IOPs for 4K random writes.

These cards require at least a PCI-E 2.0 x8 slot, which means they will not give their full performance in an older server with a lower bandwidth PCI-E slot. It is also not a bootable device. Both of these come in at about $5.00/GB which is quite affordable for PCI-E flash devices. NewEgg is selling the 400GB model for $1999.99 and the 800GB model for $3999.99. These are not the fastest PCI-E flash storage devices, but they do offer good performance at a relatively affordable price.

Intel SSD 910 Exploded 3 layers copy A SQL Server Hardware Tidbit a Day – Day 18

Figure 1: Exploded View of Intel 910

The Intel 910 has gotten quite a number of very good reviews, and I have a couple of customers using them for SQL Server workloads already. Here are links to some relevant reviews:

The Intel SSD 910 Review (AnandTech)

Intel SSD 910 Series Enterprise PCIe Review (Storage Review)

Intel SSD 910 Review: PCI Express-Based Enterprise Storage (Tom’s Hardware)

Intel 910 PCIe SSD Review – Amazing Performance Results In Both 400GB and 800GB Configurations (SSD Review)

Intel 910 800GB and 400GB PCI Express Solid State Drive Review (TweakTown)

One issue to keep in mind for the Intel 910 is that the 800GB model will show up as four 200GB devices (somewhat less than that after formatting) in Windows, while the 400GB model will show up as two 200GB devices. This is similar to what you see with the Fusion-io Duo product line.

Another issue is these Intel 910 cards have been so popular, they are currently a little hard to find, especially if you want the 800GB model. Unless you are using a high availability technology like database mirroring or SQL Server 2012 Availability Groups, you will want to use Windows Software RAID 1 at the OS level across two of these cards in a database server to avoid having the card or PCI-E slot being a single point of failure. These cards do have four onboard capacitors to allow time to write to the NAND in the event of a power failure.

Posted in: Intel, SSD, Storage

Be the first to Reply!

A SQL Server Hardware Tidbit a Day – Day 17

By: Glenn Berry

Posted on: April 17, 2013 3:35 pm

For Day 17 of this series, I am going to talk about the Intel DC S3700 series of 6Gbps MLC SATA SSDs. The DC S3700 series is available in 100GB, 200GB, 400GB, and 800GB capacities. The DC S3700 uses 25nm HET-MLC NAND technology, which means that you get higher write endurance than standard MLC consumer drives. This drive is rated at 500MB/sec for sequential reads and 460MB/sec for sequential writes. It is also rated at 75,000 IOPs for 4K random reads and 36,000 IOPS for 4K random writes.

StorageReview Intel SSD DC S3700 A SQL Server Hardware Tidbit a Day – Day 17

The DC S3700 series is a 6Gbps MLC SATA device, so it is limited to less than 600MB/sec of sequential throughput due to the SATA III interface. SATA devices do not perform as well as SAS devices under extremely high queue depth workloads, and they do not have the dual-port support that you get with SAS. Intel specifically engineered this line for more reliable, consistent performance than you get with normal, consumer MLC SSDs, so they are well-suited to many data center database server workloads. There are a number of detailed reviews of this series listed below.

The Intel SSD DC S3700 (200GB) Review (AnandTech)

Intel SSD DC S3700 Series Enterprise SSD Review (Storage Review)

Intel DC S3700 Data Center SSD Review (200/800GB) (TheSSDReview)

Intel SSD DC S3700 Review: Benchmarking Consistency (Tom’s Hardware)

The suggested retail pricing for this line is very aggressive, with the 100GB model going for $235.00, the 200GB model going for $470.00, the 400GB model going for $940.00, and the 800GB model going for $1880.00. That is $2.35/GB, which is very affordable for data center-class SSDs. Since they are still in short supply, the actual street prices are currently a little higher.

NewEgg is selling the 100GB model for $249.99 , the 200GB model for $499.99, and the 400GB model for $1099.99, (but they are all currently out of stock). Those street prices prices are pretty competitive with conventional 2.5” 15K enterprise SAS drives.

I think these Intel DC S3700 drives could be very useful as boot devices in a database server. Two of them in a RAID 1 array would give you a faster boot time than 15K magnetic drives and better reliability, along with lower power usage. According to Intel, they don’t need TRIM support to avoid having deteriorating write performance over time, so they are suitable for usage with a standard hardware RAID controller. I also think they are very feasible for use for other SQL Server usage, for things like data files, log files, and tempdb files, depending on your workload.

These drives use capacitors to maintain data integrity during a power failure. Two 35V, 47 uF capacitors store enough charge to commit all data in the write cache to NAND in the event of a power failure. Intel also did a lot of work with these drives to make sure they have consistent write performance over time, without the wide variations (usually caused by garbage collection) that you see with consumer-level SSDs. From all of the benchmarks I have seen, they work as advertised.

Posted in: Intel, SSD, Storage

Be the first to Reply!

A SQL Server Hardware Tidbit a Day – Day 16

By: Glenn Berry

Posted on: April 16, 2013 12:14 pm

For Day 16 of this series, I am going to talk about a few useful tools you can use to identify some hardware details about a database server. These tools all require that you have access to login to that server, which might be a problem for some people, depending on the policies of their organization. If you are barred from directly accessing your server, my Day 15 post from this series gives you another option to get some information from T-SQL.

The first tool is msinfo32.exe, which is built into all recent versions of Windows. You can simply type msinfo32 in a Run window, and you will see the System Information dialog shown in Figure 1.

Figure1: System Information Dialog

The System Information dialog shows that we have a Dell PowerEdge R720. This dialog also shows that I have two Intel Xeon E5-2670 processors, with 64GB of RAM, running Windows Server 2012 Datacenter Edition.

The second tool is the Computer Properties dialog shown in Figure 2. You can get there by bringing up the Windows Charm with Windows + C, then typing Computer then choosing Computer, right-clicking, and choosing Properties. This shows the version and edition of Windows, the computer name, the processor model, and the amount of installed RAM.

Figure 2: Computer Properties Dialog

The third tool is Windows Task Manager, which is shown in Figure 3. You can get there by right-clicking on the Task Bar, and choosing Start Task Manager. The Performance tab now has different pages for CPU, Memory, and some other items, depending on your hardware.

The CPU page tells you how many logical processors are visible to Windows (the number of sections you see in CPU Usage History) if you right-click and change the display to show logical processors. You can also see overall CPU usage and CPU usage by NUMA node by selecting the appropriate display type. What is even more useful in Windows Server 2012 is the fact that you see the processor model number and base clock speed, along with the total number of sockets, physical cores, and logical processors. You can also see whether hardware virtualization is enabled, and the cumulative size of your L1, L2, and L3 caches across all of your processors.

Figure 3: Windows Server 2012 Task Manager, Performance Tab, CPU Page

Figure 4 gives you some pretty detailed information about your memory, including the amount of RAM that you have installed, the type and speed of the RAM, and the number of memory slots you have used out of the total number of memory slots in the system. This is all very useful information to know about the system.

Figure 4: Windows Server 2012 Task Manager, Performance Tab, Memory Page

Posted in: Hardware, Microsoft, Windows Server 2012

Be the first to Reply!

A SQL Server Hardware Tidbit a Day – Day 15

By: Glenn Berry

Posted on: April 15, 2013 3:08 pm

For Day 15 of this series, I will be covering a few tools that can be used for hardware identification. Since quite a few database professionals do not have direct access to their database servers (i.e. they cannot login to their database server via RDP), I will talk about what you can learn about your hardware from T-SQL.

If you have VIEW SERVER STATE permission on your instance, you can query sys.dm_os_sys_info and find out your physical CPU Socket count, your hyperthread ratio, your logical CPU count, and the amount of physical memory in the machine. Depending on what version of SQL Server you are using, you can also get a few more items of information. Each new major version of SQL Server has added some additional columns to sys.dm_os_sys_info, which makes this query a little more useful. That is why I have three different versions of the query shown in Listing 1.

One frustrating fact is that you cannot tell the difference between hyper-threaded cores and physical cores when you see the hyperthread_ratio result. For example, if you had a quad-core processor with hyper-threading enabled, the hyperthread_ratio would be 8 (4×2), while a quad-core core processor (with no hyper-threading) would have a hyperthread_ratio of 4 (4×1).

-- Hardware Information for SQL Server 2005
SELECT cpu_count AS [Logical CPU Count], hyperthread_ratio AS [Hyperthread Ratio],
cpu_count/hyperthread_ratio AS [Physical CPU Count], 
physical_memory_in_bytes/1048576 AS [Physical Memory (MB)]
FROM sys.dm_os_sys_info WITH (NOLOCK) OPTION (RECOMPILE);

-- Hardware information from SQL Server 2008
SELECT cpu_count AS [Logical CPU Count], hyperthread_ratio AS [Hyperthread Ratio],
cpu_count/hyperthread_ratio AS [Physical CPU Count], 
physical_memory_in_bytes/1048576 AS [Physical Memory (MB)], sqlserver_start_time
FROM sys.dm_os_sys_info WITH (NOLOCK) OPTION (RECOMPILE);

-- Hardware information from SQL Server 2008 R2  
SELECT cpu_count AS [Logical CPU Count], hyperthread_ratio AS [Hyperthread Ratio],
cpu_count/hyperthread_ratio AS [Physical CPU Count], 
physical_memory_in_bytes/1048576 AS [Physical Memory (MB)], sqlserver_start_time, affinity_type_desc 
FROM sys.dm_os_sys_info WITH (NOLOCK) OPTION (RECOMPILE);

-- Hardware information from SQL Server 2012
SELECT cpu_count AS [Logical CPU Count], hyperthread_ratio AS [Hyperthread Ratio],
cpu_count/hyperthread_ratio AS [Physical CPU Count], 
physical_memory_kb/1024 AS [Physical Memory (MB)], affinity_type_desc, 
virtual_machine_type_desc, sqlserver_start_time
FROM sys.dm_os_sys_info WITH (NOLOCK) OPTION (RECOMPILE);

Listing 1: Hardware Information From Different SQL Server Versions

If you do have access to logon directly to your SQL Server machine, there are several great, free tools that you can use to determine a wealth of useful information about your hardware, which I will talk about tomorrow.

Posted in: Hardware, SQL Server 2005, SQL Server 2008, SQL Server 2008 R2, SQL Server 2012

1 Comment

A SQL Server Hardware Tidbit a Day – Day 14

By: Glenn Berry

Posted on: April 14, 2013 3:18 pm

For Day 14 of this series, I want to give my current recommended Intel Xeon server processors for different sizes of database servers and different workload types.

My basic premise is that for a database server running SQL Server 2008 R2 or earlier, you want the very best processor available for each physical socket in the server (since SQL Server 2008 R2 Processor licenses are relatively expensive). With SQL Server 2012 Enterprise Edition, you need to worry about the physical core counts in your processors, so there are some situations where you might want to choose a “frequency-optimized” model processor that has fewer physical cores but a higher base clock speed than the top-tier processor that has a higher number of physical cores. An example would be choosing a four-core Intel Xeon E5-2643 instead of an eight-core Xeon E5-2690 processor.

Unlike a laptop or web server, you usually don’t want to pick a processor for a database server that is one or two models down from the most expensive, “top of the line” model. With SQL Server 2012 Enterprise Edition, you certainly don’t want to select a slower speed, less expensive processor that has the same number of physical cores as a slightly more expensive processor from that same processor family and generation.

You will most likely be stuck with whatever processor you choose for the life of the server, since it rarely makes economic sense to upgrade the processors in an existing server. You can also use any “excess” processor capacity for things like data compression or backup compression, to reduce the pressure on your I/O subsystem. Trading CPU utilization for I/O utilization is usually a net win, especially if you have a modern, multi-core processor that can shrug off the extra work.

These recommendations will change when the Xeon E3-1200 v3 series is released in June 2013, and again when the E5-2600 v2 series is released in Q3 of 2013 and the E7-2800, 4800 and 8800 v2 series are released in Q4 of 2013.

So here is my recommended Intel Xeon server processor list:

One-socket server (OLTP workloads)
Xeon E3-1290 v2 (22nm Ivy Bridge)
•    3.7GHz, 8MB L3 Cache, 5.0 GT/s Intel QPI 1.1
•    Four-cores plus hyper-threading, Turbo Boost 2.0 (4.1GHz)
•    Two memory channels, 32GB max memory capacity

One-socket server (DW/DSS workloads)
Xeon E5-2470 (32nm Sandy Bridge-EN)
•    2.3GHz, 20MB L3 Cache, 8.0 GT/s Intel QPI 1.1
•    Eight-cores plus hyper-threading, Turbo Boost 2.0 (3.1GHz)
•    Three memory channels, 96GB max memory capacity

Two-socket server (OLTP workloads)
Xeon E5-2690 (32nm Sandy Bridge-EP)
•    2.9GHz, 20MB L3 Cache, 8.0 GT/s Intel QPI 1.1
•    Eight-cores plus hyper-threading, Turbo Boost 2.0 (3.8GHz)
•    Four memory channels, 384GB max memory capacity (16GB DIMMs)

Two-socket server (DW/DSS workloads)
Xeon E7-2870 (32nm Westmere-EX)
•    2.40GHz, 30MB L3 Cache, 6.40 GT/s Intel QPI 1.0
•    Ten-cores plus hyper-threading, Turbo Boost 2.0 (2.8GHz)
•    Four memory channels, 512GB max memory capacity (16GB DIMMs)

Four-socket server (OLTP workloads)
Xeon E5-4650 (32nm Sandy Bridge-EP)
•    2.7GHz, 20MB L3 Cache, 8.0 GT/s Intel QPI 1.1
•    Eight-cores plus hyper-threading, Turbo Boost 2.0 (3.3GHz)
•    Four memory channels, 768GB max memory capacity (16GB DIMMs)

Four-socket server (DW/DSS workloads)
Xeon E7-4870 (32nm Westmere-EX)
•    2.40GHz, 30MB L3 Cache, 6.40 GT/s Intel QPI 1.0
•    Ten-cores plus hyper-threading, Turbo Boost 2.0 (2.8GHz)
•    Four memory channels, 1TB max memory capacity (16GB DIMMs)

Eight-socket server (Any workload type)
Xeon E7-8870 (32nm Westmere-EX)
•    2.40GHz, 30MB L3 Cache, 6.40 GT/s Intel QPI 1.0
•    Ten-cores plus hyper-threading, Turbo Boost 2.0 (2.8GHz)
•    Four memory channels, 2TB max memory capacity

Posted in: Hardware, Intel, Processors

Be the first to Reply!