A SQL Server Hardware Nugget A Day – Day 13

Posted on April 13, 2011 by Glenn Berry

For Day 13 of this series, we will cover Intel Turbo Boost Technology. This is not like the the old Turbo button that was on many computers back in the 1980s-1990s. Instead, Turbo Boost Technology and the newer Turbo Boost Technology 2.0 are essentially intelligent overclocking of individual cores of a physical processor. Intel has a short animated demo of how it works here.

When the operating system requests the highest processor performance state (P0), individual cores of the processor have their core speed increased temporarily by varying amounts, based on how many cores are busy, the total power usage of the processor, the ambient temperature of the system, etc.

This core speed increase is very helpful for OLTP workloads, especially if they are processor dependent. The amount of the possible speed increase varies according to the model of the processor, with the newer models with Turbo Boost 2.0 typically getting a 400MHz speed boost over the base clock speed.

I really don’t see any possible downside to this. The base clock speed on Intel processors is rated quite conservatively, with lots of thermal headroom. Having various algorithms in place to temporarily increase the clock speed of individual cores poses very little risk of causing any problems, such as overheating or shorter life for the processor.

One free tool you can use to monitor the effect of Turbo Boost in real-time is called TMonitor, which is available from cpuid.com. A screenshot of the tool in action is shown in Figure 1.

image

Figure 1: TMonitor running while the system is under a load

Figure 2 shows the Core Speed of Core #0 running at 2932MHz (which is the same as 2.93GHz), even though the rated base clock of my Core i7 930 is only 2.8GHz. The Core i7 930 only has the 1st generation Turbo Boost Technology, which is much less aggressive than the newer Turbo Boost Technology 2.0 in the Sandy Bridge processors.

image

Figure 2: CPU-Z showing Core #0 running above rated speed

Posted in Computer Hardware, Processors | Tagged , | 1 Comment

A SQL Server Hardware Nugget A Day – Day 12

Posted on April 12, 2011 by Glenn Berry

In Day 12 of this series, we will be talking about AMD Opteron Processor Numbering. Advanced Micro Devices  (AMD) has various versions of the Opteron family that are meant for server use. When assessing AMD processors, it is very helpful to understand what the model numbers mean.

Recent AMD Opteron processors are identified by a four digit model number in the format ZYXX, where the Z digit indicates the product series:

    •   1000 Series = 1-socket servers
    •   2000 Series = Up to 2-socket servers and workstations
    •   4000 Series = Up to 2-socket servers
    •   6000 Series = High performance 2 and 4-socket servers
    •   8000 Series = Up to 8-socket servers and workstations

The Y digit differentiates products within a series. For example:

    •   Z2XX = Dual-Core
    •   Z3XX = Quad-Core
    •   Z4XX = Six-Core
    •   First generation AMD Opteron 6000 series processors are denoted by 61XX

The XX digits indicate a change in product features within the series (for example, in the 8200 series of dual-core processors, we have models 8214, 8216, 8218, and so on), and are not a measure of performance. It is also possible to have a two digit product suffix after the XX model number, as follows:

    •  No suffix– indicates a standard power AMD Opteron processor.
    •  SE – Performance optimized, high-powered
    •  HE– Low-power
    •  EE– Lowest power AMD Opteron processor.

For example, an Opteron 6180 SE would be a 6000 series, twelve-core, performance optimized processor; an Opteron 8439 SE would be an 8000 series, six-core, performance optimized processor, while an Opteron 2419 EE would be a 2000 series, six-core, energy efficient processor. For mission critical database servers, I would recommend that you select SE suffix processor, if it is available for your server model. The reason that it is not always available in every server model is due to its higher electrical power requirements.

I talked about my current recommended AMD processors in the Day 9 post for this series.

Recent Opteron AMD releases, plus planned releases, are summarized in Figure 1. Since 2010, the Magny-Cours processor has been AMD’s best-performing model.

Year Process Model Families Code Name
2006 90nm 1200,2200,8200 Santa Ana, Santa Rosa
2007-8 65nm 1300,2300,8300 Budapest, Barcelona
2009 45nm 2400,8400 Shanghai, Istanbul
2010 45nm 4100, 6100 Lisbon, Magny-Cours
2011 32nm ?? Interlagos, Valencia

Figure 1: Recent and Upcoming AMD Opteron Releases

Posted in Computer Hardware, Processors | Tagged , | 1 Comment

April Version of SQL Server 2008 Diagnostic Queries

Posted on April 11, 2011 by Glenn Berry

I have added two new queries to this month’s version, to try to collect some more hardware information about the database server. I specifically wanted to find out more information about the processor type from T-SQL, since many DBAs don’t have direct access to their database server to run a tool like CPU-Z.

Here are the two new queries:

-- Get System Manufacturer and model number from 
-- SQL Server Error log. This query might take a few seconds 
-- if you have not recycled your error log recently
EXEC xp_readerrorlog 0,1,"Manufacturer"; 


-- Get processor description from Windows Registry
-- (Uncomment query to make it work)
--EXEC xp_instance_regread 
--'HKEY_LOCAL_MACHINE',
--'HARDWARE\DESCRIPTION\System\CentralProcessor',
--'ProcessorNameString';

If we had a processor description column in the sys.dm_os_sys_info DMV, it would be a lot easier to collect this type of information. I have filed a Connect item to get this added to SQL Server Denali. If you agree that this is important, please vote it up!

Just click on the link to get the full SQL Server 2008 Diagnostic Information Query (Apr 2011), and the matching version of the results spreadsheet.

As always, you will need VIEW SERVER STATE permission to run many of these queries. You should run each query one at a time (after reading my notes for each one), rather than running the entire batch. That way, you can study the results, and see how long each query takes to complete in your environment.

Posted in SQL Server 2008, SQL Server 2008 R2 | Tagged | 9 Comments

A SQL Server Hardware Nugget A Day – Day 11

Posted on April 10, 2011 by Glenn Berry

For Day 11, I want to talk about the new processor numbering system for Xeon processors that Intel introduced on April 5, 2011. This new system will only be used for the new processors that Intel released on April 5 (the E3 Series and the E7 Series) and the upcoming E5 Series. The model numbers for existing Xeon processors will remain unchanged.

 

Processor Name = Brand (Intel® Xeon® processor) + Number (E3- 1 2 35)

Figure 1: New Intel Xeon Processor Numbering System

The E3 Product Line is for single processor server 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 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. Later this year, Intel will release the E5 Product Line, which is the Sandy Bridge-EP.

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.

Posted in Computer Hardware, Processors | Tagged , , | 1 Comment

A SQL Server Hardware Nugget A Day – Day 10

Posted on April 10, 2011 by Glenn Berry

Wow, we are 1/3 of the way through this series! Today’s post is “Classic Intel Xeon Processor Numbering Explained”. By “classic”, I mean Intel Xeon processors produced from about 2006 until April 2011 (when Intel introduced a new processor numbering system for new and upcoming processors).

Knowing how to decode the processor model number is a very handy skill to have when you want to be able to understand the capabilities, relative age, and relative performance of a particular processor. An example of an Intel processor number is shown in Figure 1.

image

Figure 1: Classic Intel Xeon Processor Numbering

Intel Xeon processor numbers are categorized in four digit numerical sequences, plus an alpha prefix to indicate electrical power usage and performance. The alpha prefixes are as follows:

   •  X   meaning Performance
    •  E   meaning Mainstream
    •  L   meaning Power-Optimized

The model number will start with a 3, 5, or 7, depending on the server form factor that the processor is designed for. If the processor number starts with a 3, it is designed for a single socket server,if it starts with a 5, it is designed for a two socket server, and if it starts with a 7, it is designed for a four socket or more server.

The second digit of the model number designates the generation, or relative age of a processor. For example the Xeon 5100 series was launched in Q2 2006, while the Xeon 5300 series was launched in Q4 2006, and the Xeon 5400 series was launched in Q4 2007.

For a more complete example, a Xeon X7560 is a high-end Performance processor for multi-processor systems, an Intel Xeon E5540 is a Mainstream processor for dual-processor systems, while an Intel Xeon L5530 is a Power-Optimized processor for dual-processor systems. The final three digits denote the generation and performance of the processor; for example, a Xeon X7560 processor would be newer and probably more capable than a Xeon X7460 processor. Higher numbers for the last three digits of the model number mean a newer generation in the family, i.e. 560 is a newer generation than 460, in this example.

In my opinion, you should always choose the Performance models, with the X model prefix, for SQL Server usage. The additional cost of an X series Xeon processor, compared to an E series, is minimal compared to the overall hardware and SQL Server license cost of a database server system.

You should also avoid the power-optimized L series, since they can reduce processor performance by 20-30% while only saving 20-30 watts of power per processor, which is pretty insignificant compared to the overall electrical power usage of a typical database server (with its cooling fans, internal drives, power supplies, etc.). Of course, it would be a different story if you needed dozens or hundreds of web servers instead of a small number of mission critical database servers, since the overall power savings would be pretty significant.

Posted in Computer Hardware, Processors | Tagged , | 4 Comments

A SQL Server Hardware Nugget A Day – Day 9

Posted on April 9, 2011 by Glenn Berry

For Day 9 of my SQL Server hardware series, I want to give my current recommended AMD Processor List for different sizes of database servers and different workload types. There is not as much change on the AMD front as there has been on the Intel side recently, so that makes this list a little easier. Recommending AMD processors is made even simpler by the fact that AMD lets you use the exact same processor in two socket servers and in four socket and above servers, and they have had that capability for several years. Until the very recent release of the Intel Xeon E7 family of processors, Intel had completely separate processor lines for one socket, two socket and four socket and above servers.

Until the AMD Bulldozer family of processors is released sometime later in 2011 (presumably in Q3) the processor you want is either an Opteron 4184 or an Opteron 6180 SE. These are they best processors they have in their respective server form factors. Generally speaking, these two processors (especially the Opteron 6180 SE) do better with DW/DSS workloads than with OLTP workloads, because of their high physical core counts. The Opteron 6180 SE “Magny Cours” has twelve physical cores per processor, without using anything like hyper-threading to produce logical cores. Having lots of physical cores is helpful for DW/DSS type workloads, where you often have long-running, complex queries that are likely to be parallelized by the query optimizer.

In all honesty, I believe you are better off with an Intel processor for SQL Server OLTP workloads. The Intel Xeon X5690 will simply smoke the Opteron 6180 SE in single-threaded performance. As I have said before, I really hope that the Bulldozer lives up to its advance billing, so that AMD can close the performance gap with Intel.

So, here is my current list of recommended AMD processors:

One socket or budget two socket server
•    Opteron 4184 (45nm Lisbon), six cores
•    2.8GHz, 6MB L3 cache, 6.4GT/s

Two socket server
•    Opteron 6180 SE (45nm Magny-Cours), twelve cores
•    2.5GHz, 12MB L3 Cache, 6.4GT/s

Four socket server
•    Opteron 6180 SE (45nm Magny-Cours), twelve cores
•    2.5GHz, 12MB L3 Cache, 6.4GT/s

Posted in Computer Hardware, Processors | Tagged , | 6 Comments

A SQL Server Hardware Nugget A Day – Day 8

Posted on April 8, 2011 by Glenn Berry

For Day 8 of my SQL Server hardware series, I want to give my current recommended Intel Processor List for different sizes of database servers and different workload types. This list has a lot of changes because of all of the recently released Intel processors over the past couple of weeks. Intel has also rolled out a new naming scheme for the Xeon processors, which makes it even more interesting!

My basic premise is that for a database server, you want the very best processor available for each physical socket in the server (since SQL Server Processor licenses are relatively expensive). Unlike a laptop or web server, you 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”.

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

Intel® Xeon® Logo Logo   Intel® Xeon® Brand Logo

So here is my recommended Intel processor list:

One socket server (OLTP)
Xeon E3-1280 (32nm Sandy Bridge)
•    3.50GHz, 8MB L3 Cache, 5.0 GT/s Intel QPI
•    Four-cores, Turbo Boost 2.0 (3.90GHz), hyper-threading
•    Two memory channels

One socket server (DW/DSS)
Xeon W3690 (32nm Westmere)
•    3.46GHz, 12MB L3 Cache, 6.40 GT/s Intel QPI
•    Six-cores, Turbo Boost (3.73GHz), hyper-threading
•    Three memory channels

Two socket server (OLTP)
Xeon X5690 (32nm Westmere-EP)
•    3.46GHz, 12MB L3 Cache, 6.40 GT/s Intel QPI
•    Six-cores, Turbo Boost (3.73GHz), hyper-threading     
•    Three memory channels

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

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

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

Posted in Computer Hardware, Processors | Tagged , | 2 Comments

Presenting Dr. DMV by Live Meeting on April 19 at 12PM CDT

Posted on April 7, 2011 by Glenn Berry

I will be presenting my Dr. DMV presentation for the St. Louis SQL Server User’s Group on April 19. Here are the meeting details:

Join the STLSSUG for a Live Meeting Lecture Tuesday on April 19th from 12-1:00pm CDT

Dr. DMV: How to Use Dynamic Management Views to Monitor and Diagnose Performance Issues With High Volume OLTP Workloads

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 both 2005, 2008 and 2008 R2), presents and explains over thirty DMV queries that you can quickly and easily use to detect and diagnose performance issues in your environment.

Biography

Glenn works as a Database Architect at NewsGator Technologies in Denver, CO. He is a SQL Server MVP, and he has a whole collection of Microsoft certifications, including MCITP, MCDBA, MCSE, MCSD, MCAD, and MCTS, which proves that he likes to take tests. His expertise includes DMVs, high availability, hardware selection, full text search, and SQL Azure.  He is also an Adjunct Faculty member at University College – University of Denver, where has been teaching since 2000. He recently completed the Master Teacher Program at Denver University – University College. He is the author of two chapters in the book SQL Server MVP Deep Dives, and blogs regularly at https://sqlserverperformance.wordpress.com.

STLSSUG Live Meeting Lecture Tuesday, April 19, 2011  12:00 PM-1:00 PM. CDT

Join the meeting.
Audio Information
Computer Audio
To use computer audio, you need speakers and microphone, or a headset.
First Time Users:
To save time before the meeting, check your system to make sure it is ready to use Microsoft Office Live Meeting.
Troubleshooting
Unable to join the meeting? Follow these steps:

1. Copy this address and paste it into your web browser:
https://www.livemeeting.com/cc/usergroups/join

2. Copy and paste the required information:
Meeting ID: 62HN2B
Entry Code: 4&rQKHtCF
Location: https://www.livemeeting.com/cc/usergroups

If you still cannot enter the meeting, contact support

Notice
Microsoft Office Live Meeting can be used to record meetings. By participating in this meeting, you agree that your communications may be monitored or recorded at any time during the meeting.

Posted in PASS, SQL Server 2008, SQL Server 2008 R2, Teaching | Tagged , | Leave a comment

A SQL Server Hardware Nugget A Day – Day 7

Posted on April 7, 2011 by Glenn Berry

For Day 7 of this series, I will talk about the incredibly useful CPU-Z utility, which is available for free from cpuid.com. The latest release of the tool is version 1.57, which came out on February 18, 2011. I always download and use the 64-bit, English, no install zip version of the tool.

This tool will give you a great amount of detail about your processor(s), caches, motherboard, and memory, among other things. The CPU tab is shown in Figure 1.

image

Figure 1: CPU tab of CPU-Z 1.57

For example, this shows that I have a 45nm, Intel Core i7 930, that has a rated speed of 2.80GHz, but it is actually running at 2.93GHz. This shows that the processor is running at full speed, with Turbo Boost increasing the speed to 2.93Ghz (at least on Core 0).

It also shows that this processor is x64 compatible, since we see EM64T as one of the supported instructions. We can also see the size and types of the L1, L2, and L3 caches. Finally, I can see that this processor has four cores and eight threads, which means that it has hyper-threading, and hyper-threading is enabled.

The mainboard tab shown in Figure 2 gives you a lot of useful information about the motherboard, chipset, and main BIOS version.

image

Figure 2: Mainboard tab of CPU-Z 1.57

Figures 3 and 4 show the Memory and Serial Presence Detect (SPD) tabs of CPU-Z, which give you very useful information about the type and amount of memory that you have in your machine. Note, on some machines, the SPD tab will be blank. This seems to depend on the type and age of your machine, and the version of CPU-Z that you are using.

image

Figure 3: Memory tab of CPU-Z 1.57

 

image

Figure 4: SPD tab of CPU-Z 1.57

Over the several times I have talked about using CPU-Z, I have gotten numerous questions about whether it is safe to run on a production SQL Server. All I can say is that I have been using it myself for years, with absolutely no problems. Many other well-known people in the SQL Server community have been doing the same thing. I think it is an extremely valuable tool.

If you don’t feel comfortable using this tool, then don’t use it…

Posted in Computer Hardware, Processors | Tagged , , | 1 Comment

Getting More Hardware Information from SQL Server Denali DMV Queries

Posted on April 6, 2011 by Glenn Berry

As I recently discussed, I often hear from database professionals who are not allowed to access their database servers directly. Instead, they can only use SQL Server Management Studio (SSMS) to remotely manage the database servers that they are responsible for.

Unfortunately, this makes it much more difficult for the DBA to find out some very basic hardware information about all of the database servers that they have to manage. In SQL Server 2008 R2, you can use a T-SQL DMV query against sys.dm_os_sys_info (as shown in Listing 1)

-- Hardware information from SQL Server 2008 and 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)],
affinity_type_desc, sqlserver_start_time
FROM sys.dm_os_sys_info OPTION (RECOMPILE);

Listing 1: Getting Hardware Information from SQL Server 2008 and 2008 R2

This gives us the logical CPU count, the hyper-thread ratio, the physical CPU count, the Physical Memory, the CPU affinity type, and the SQL Server start time. Unfortunately, we have no idea what type of processor we are dealing with. Is it a top of the line, fire-breathing, 3.46GHz Intel Xeon X5690, or is it a much more humble, older processor?

This is very important information for the DBA to be aware of. For example, you might be trying to decide whether you should implement Data Compression on a few more indexes in your database. Knowing what type of processor(s) you have in your server is one data point that should go into your decision making process. For Data Compression, you would also want to consider how volatile the index is, what your estimated compression ratio is, and what your average CPU utilization rate is (all of which you can determine from my DMV diagnostic queries), but you would still be in the dark about the CPU type.

The CTP1 Build of SQL Server Denali has added at least one very useful column to sys.dm_os_sys_info, which is the virtual_machine_type_desc column, (which does not show up in the BOL entry yet). This will give you some idea about your hardware virtualization environment. The SQL Server Denali CTP1 version of this query is shown in Listing 2.

-- Hardware information from SQL Server Denali
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 OPTION (RECOMPILE);

Listing 2: Getting Hardware Information from SQL Server Denali

Another scenario where having a Processor Description column exposed in sys.dm_os_sys_info would be useful is if you are managing a large number of servers, perhaps by using a Central Management Server (CMS) in SQL Server Management Studio. Getting the processor description back from each server would be very helpful as you try to decide how to allocate and balance your workload across your available servers.

I also think it is very important to be aware of the age and relative performance of the processors in your servers. The latest processors are so much better than processors from even two to three years ago, that you have the opportunity to to upgrade or consolidate your hardware according to your priorities, in order to reduce your management overhead or increase your performance and scalability.

If you are unable to run a tool like msinfo32 or CPU-Z (since you are not allowed to access your database servers), how are you supposed to discover that you have a bunch of four to five year old processors in your servers, that have less CPU performance than my laptop?

If you agree with me about this issue, you can help convince Microsoft to take care of it in the SQL Server Denali release cycle, by going to Microsoft Connect and voting up this item. If you have a moment to leave a comment in the Connect Item, that would also be very helpful.

Posted in Computer Hardware, SQL Server Denali | Tagged , | 1 Comment