Learn About SQL Server Hardware in Chicago

On May 5-6, 2014, I will be teaching IEHW: Immersion Event on SQL Server Hardware in Chicago, IL. This is a great opportunity for you to learn how to properly select and configure your server hardware and storage subsystem to get the best performance and scalability for the lowest SQL Server 2012/2014 licensing costs.

Now that SQL Server 2014 is generally available, and Windows Server 2012 R2 has been available for over six months, combined with the release of the 2nd generation Intel Xeon E5 and E7 processor families, I think that it is going to make a lot of sense for many organizations to do a complete data platform refresh sometime during 2014. By making wise hardware selection choices, you can easily save so much money on your SQL Server 2012/2014 licensing costs, that your actual server hardware is free.

This two-day SQL Server hardware training class explains the core fundamentals and deeper details of database server hardware and storage subsystems for SQL Server database professionals. Many database professionals are unfamiliar with the details and nuances of modern server hardware and storage subsystems, while many server and storage administrators are unfamiliar with the specific workload demands of a SQL Server database server.

Attendees of this class will learn how to analyze, select, and size their server hardware and storage subsystems for different types of SQL Server workloads in order to get the best performance and scalability while minimizing their SQL Server 2012/2014 license costs.

This class also covers how to properly configure and benchmark your database server hardware and storage subsystems, along with how to properly install and configure the operating system and SQL Server for the best performance and reliability. The class will show you how to diagnose and troubleshoot hardware and storage related performance issues, and will include coverage of how virtualization interacts with your database server hardware and storage subsystem. Note: the primary audience for this class is SQL Server database professionals, not general system/server admins who are already familiar with server/storage hardware.

I think it is very important for database administrators to know as much as possible about the critical details of their server hardware and storage subsystem, rather than trusting their fate to “Shon the server guy”, who may or may not know that much about modern server hardware (maybe he is a networking specialist). Even if Shon is very knowledgeable about hardware, he may not understand the different demands that SQL Server will create with different types of workloads. I want you to be able to successfully make the case for selecting the best hardware and storage subsystem components for your workload and budget.  You can read more about the registration details here.

Special Pricing Options and Referrals
  • Past attendee price: If you’ve attended an Immersion Event in the past, you can register any time for 75% of the full price ($1,099). Please contact us for instructions.
  • Refer someone: If you know someone who would benefit from this class, refer them to us and when they register, we’ll give you a $50 Amazon gift card. They or you just need to let us know you’re referring them, and when they register, we’ll match them to your referral and send you the gift card.

Recommended Intel Processors For SQL Server 2014 OLTP Workloads

If you are in the process of evaluating and selecting the components for a new database server to run an OLTP workload on SQL Server 2014 Enterprise Edition, you have several initial choices that you have to make as a part of the decision process. First you have to decide whether you want to go with an AMD-based server or an Intel-based server. Unfortunately, I cannot recommend that you use an AMD processor for SQL Server 2012/2014 OLTP workloads, due to the combination of low single-threaded performance and high SQL Server licensing costs (even with the 25% discount from the SQL Server 2012 Core Factor Table).

Next, you need to decide on the server socket count, which means choosing a single-socket, dual-socket, quad-socket, or eight-socket server (at least in the commodity server market). After you choose the socket count, you need to decide exactly which of the available processors you want to use in that model server. Looking at the choices for several current model servers from the major system vendors, you will discover that you will have to pick from around 15-20 different specific processors. All of this can be a little overwhelming to consider, but I urge you to do some research, and to choose carefully. Letting someone else pick your processors, who may not be familiar with SQL Server 2012/2014 licensing and the demands of different database workload types, could be a lasting, costly mistake.

With the core-based licensing in SQL Server 2012/2014 Enterprise Edition, you need to pay closer attention to your physical core counts, and think about whether you are more concerned with extra scalability (from having more physical cores), or whether you want the absolute best OLTP query performance (from having a processor with fewer cores but a higher base clock speed from the same processor generation). Unlike in the good old days of SQL Server 2008 R2 and older, having more physical cores will cost you more for your SQL Server 2012/2014 Enterprise Edition licensing costs. You really need to think about what you are trying to accomplish with your database hardware. For example, if you can partition your workload between multiple servers, then you could see much better OLTP performance from using two dual-socket servers instead of one quad-socket server.

So, here are the Intel processors that I recommend in mid-April 2014 for OLTP workloads, with their high-level specifications and some commentary.

One-Socket Server (High Capacity)

Intel Xeon E5-2470 v2 (22nm Ivy Bridge-EN)

  • 2.4 GHz, 25MB L3 cache, 8 GT/s Intel QPI 1.1
  • 10 cores, Turbo Boost 2.0 (3.2 GHz), hyper-threading
  • Three memory channels, six memory slots per processor, 96GB RAM with 16GB DIMMs

One-Socket Server (High Performance)

Intel Xeon E3-1280 v3 (22nm Haswell)

  • 3.6 GHz, 8MB L3 cache, 5 GT/s Intel QPI 1.1
  • 4 cores, Turbo Boost 2.0 (4.0 GHz), hyper-threading
  • Two memory channels, four memory slots per processor, 32GB RAM with 8GB DIMMs

At least one Tier One vendor (Dell) is offering a single-socket server with the new Ivy Bridge-EN processor family. This is the entry level, two-socket capable Ivy Bridge processor that has lower clock speeds and less memory bandwidth than the Ivy Bridge-EP processor family, so it is NOT a good choice for a two-socket server. Despite this, it does give you the ability to have ten physical cores and 96GB of RAM in a single-socket server. You would see much better single-threaded OLTP performance from a new 3rd generation E3-1280 v3 Haswell processor, but you would be limited to four physical cores and 32GB of RAM. Again, if you can partition your workload, two single-socket Xeon E3-1280 v3 based servers would give you much better OLTP performance than one Xeon E5-2470 v2 based server with a lower SQL Server 2012/2014 Enterprise Edition licensing cost.

Two-Socket Server (High Capacity)

Intel Xeon E5-2697 v2 (22nm Ivy Bridge-EP)

  • 2.7 GHz, 30MB L3 cache, 8 GT/s Intel QPI 1.1
  • 12 cores, Turbo Boost 2.0 (3.5 GHz), hyper-threading
  • Four memory channels, twelve memory slots per processor, 384GB RAM with 16GB DIMMs

Two-Socket Server (High Performance)

Intel Xeon E5-2643 v2 (22nm Ivy Bridge-EP)

  • 3.5 GHz, 25MB L3 cache, 8 GT/s Intel QPI 1.1
  • 6 cores, Turbo Boost 2.0 (3.8 GHz), hyper-threading
  • Four memory channels, twelve memory slots per processor, 384GB RAM with 16GB DIMMs

Choosing the top of the line, 12-core Xeon E5-2697 v2 would cost twice as much for the SQL Server license costs as the 6 core Xeon E5-2643 v2. Once again, if you can partition your workload, two dual-socket Xeon E5-2643 v2 based servers would give you better overall OLTP performance than one Xeon E5-2697 v2 based server for the same SQL Server 2012/2014 Enterprise Edition licensing cost. You would have more total memory between the two servers, and more potential I/O capacity, at the cost of buying two servers instead of one server.  In some situations, this strategy might not make sense, especially with the added management and maintenance overhead of two servers instead of one.

Four-Socket Server (High Capacity)

Intel Xeon E7-4890 v2 (22nm Ivy Bridge-EX)

  • 2.8 GHz, 37.5MB L3 cache, 8 GT/s Intel QPI 1.1
  • 15 cores, Turbo Boost 2.0 (3.4 GHz), hyper-threading
  • Four memory channels, twenty-four memory slots per processor, 1536GB RAM with 16GB DIMMs

Four-Socket Server (High Performance)

Intel Xeon E7-8893 v2 (22nm Ivy Bridge-EX)

  • 3.4 GHz, 37.5MB L3 cache, 8 GT/s Intel QPI 1.1
  • 6 cores, Turbo Boost 2.0 (3.7 GHz), hyper-threading
  • Four memory channels, twenty-four memory slots per processor, 1536GB RAM with 16GB DIMMs

The brand new Xeon E7-8893 v2 will give you significantly better single-threaded OLTP query performance in a four-socket server than the E7-4890 v2, at the cost of less total capacity because of the lower physical core count. The E7-8893 v2 is a “frequency-optimized” model that is actually meant for eight-socket servers, but is available in several new four-socket server models from the major server vendors.

It would save you enough on SQL Server 2012/2014 Enterprise Edition license costs (about $250K) to buy the server itself and still have lots of money left over. I even think it is a better choice in many situations than a two-socket server with the 12-core, Intel Xeon E5-2697 v2, since you will have much higher single-threaded performance and much higher memory capacity. The downside is a higher hardware cost, since you will be buying four, quite expensive processors.

Eight-Socket Server (High Capacity)

Intel Xeon E7-8890 v2 (22nm Ivy Bridge-EX)

  • 2.8 GHz, 37.5MB L3 cache, 8 GT/s Intel QPI 1.1
  • 15 cores, Turbo Boost 2.0 (3.4 GHz), hyper-threading
  • Four memory channels, twenty-four memory slots per processor, 3072GB RAM with 16GB DIMMs (eight sockets)

Eight-Socket Server (High Performance)

Intel Xeon E7-8891 v2 (22nm Ivy Bridge-EX)

  • 3.2 GHz, 37.5MB L3 cache, 8 GT/s Intel QPI 1.1
  • 10 cores, Turbo Boost 2.0 (3.7 GHz), hyper-threading
  • Four memory channels, twenty-four memory slots per processor, 3072GB RAM with 16GB DIMMs (eight sockets)

You can choose a lower core count, frequency-optimized model, that has a higher clock speed for better single-threaded performance. The lower core count will also save you a LOT of money on SQL Server 2012/2014 licensing costs, although you will give up that extra load capacity with few total processor cores available.

I always like to hear what you think about my posts, so be sure to let me know!

SQL Server Diagnostic Information Queries for April 2014

I made some small improvements to a few of the queries this month. I plan to add several more SQL Server 2014 specific queries over the next couple of months, along with a lot more comments on how to interpret the results of each query in the entire set.

Rather than having a separate blog post for each version, I have just put the links for all five major versions in this single post. There are two separate links for each version. The first one on the top left is the actual query script, and the one below on the right is the matching blank results spreadsheet.  

SQL Server 2005 Diagnostic Information Queries

SQL Server 2005 Blank Results

SQL Server 2008 Diagnostic Information Queries

SQL Server 2008 Blank Results

SQL Server 2008 R2 Diagnostic Information Queries

SQL Server 2008 R2 Blank Results

SQL Server 2012 Diagnostic Information Queries

SQL Server 2012 Blank Results

SQL Server 2014 Diagnostic Information Queries

SQL Server 2014 Blank Results

The basic idea is that you should run each query in the set, one at a time (after reading the directions). You need to click on the top left square of the results grid in SSMS to select all of the results, and then right-click and select “Copy with Headers” to copy all of the results, including the column headers to the Windows clipboard. Then you paste the results into the matching tab in the blank results spreadsheet. There are also some comments on how to interpret the results after each query.

About half of the queries are instance specific and about half are database specific, so you will want to make sure you are connected to a database that you are concerned about instead of the master system database.

Note: These queries are stored on Dropbox. I occasionally get reports that the links to the queries and blank results spreadsheets do not work, which is most likely because Dropbox is blocked wherever people are trying to connect.

I also occasionally get reports that some of the queries simply don’t work. This usually turns out to be an issue where people have some of their user databases in 80 compatibility mode, which breaks many DMV queries.

There is an initial query in each version that tries to confirm that you are using the correct version of the script for your version of SQL Server. Please let me know what you think of these queries, and whether you have any suggestions for improvements. Thanks!

Windows Server 2012 R2 Update

Microsoft has released an update for Windows 8.1 and Windows Server 2012 R2 that is somewhat of a cross between a Cumulative Update and a Service Pack. Microsoft is calling this update “Windows Server 2012 R2 Update” which is sort of a silly name. I would have preferred something like “Windows Server 2012 R2 Update 1”, since there are rumors of another update like this coming out later this year (at least according to Mary Jo Foley).

This update is primarily designed to make both of these operating systems easier to use for mouse and keyboard users (even though Windows Server 2012 R2 is already easier than Windows 8.1). The update does make it even easier to use Windows Server 2012 R2 if you are more used to working with Windows Server 2008 R2. It also includes a rollup of previous updates for Windows Server 2012 R2, so it will save you quite a bit of time compared to pulling those updates down from Microsoft Update. You can also use a slipstream version that Microsoft has made available if you are doing a new operating system installation.

You can read more about the Windows Server 2012 R2 Update here. Adin Ermie has a nice blog post with lots of before and after screenshots here.

The update is already available for MSDN Subscribers Downloads, and Microsoft is going to make it available on Windows Update on April 8. I have installed it on a few VMs already without any problems. One initial question I had was how to quickly and easily determine whether the update was installed or not, since it does not show up in System Properties like a Service Pack would. It turns out that there is a somewhat easy method to find out, by running Msinfo32.exe.

To confirm the exact version of Windows Server 2012 R2 that is installed on a computer, run Msinfo32.exe. If Windows Server 2012 R2 Update is installed, the value reported for Hardware Abstraction Layer will be 6.3.9600.17031. You can also look for KB2919355 under installed updates.

If you are or will be running SQL Server 2012 or SQL Server 2014 in the near future, you really should be running Windows Server 2012 R2 as your operating system. If you are already running Windows Server 2012 R2, you should make plans to install this update as soon as possible, since it will be required in the near future. According to Microsoft, “Future updates as of the patch Tuesday in May, including security fixes, will be based on Windows Server 2012 R2 Update as the baseline.”

How to Do Some Very Basic SQL Server Monitoring

One thing that I think is very important for a database professional to do as part of their regular work is to have some idea of how their most important SQL Server instances are running and how they have been performing over time. This helps you get a feel for what a normal workload is for your instance and what your workload extremes look like. It also helps you answer the inevitable questions about “What happened to the database server last Thursday at 2PM”?

There are many fine 3rd party products available for this purpose such as Performance Advisor for SQL Server from SQLSentry and SQL Monitor from Redgate. Regardless of whether you use any of those products, I think you should also consider using something that is very simple, lightweight and free, such as my ServerMonitor database and related SQL Server Agent job, which you can download from here. This version only works on SQL Server 2008 or newer.

The zip file includes two separate T-SQL scripts. The first script creates a database called ServerMonitor in the default database location on your instance, sets the recovery model to Simple, and then creates one table and two stored procedures in that database. It also uses PAGE data compression on the indexes for that table if you have SQL Server 2008 Enterprise Edition or newer. Finally, it creates a SQL Server Agent job called “Record Instance Level Metrics” that runs once every minute, to collect a few instance-level metrics and store them in the ServerMonitor database. You can easily modify the schedule for that job if you wish.

These basic metrics include: Average Task Count, Average Runnable Task Count, Average Pending IO Count, SQL Server process CPU utilization, and Average Page Life Expectancy across all NUMA nodes. This is just some very basic, easy to collect information that can be quite useful for getting some baseline and trending information about your instance over time.

The second script just has a few example queries for pulling some useful information out of the ServerMonitor database. The ServerMonitor database is just a simple example that anyone can understand and easily extend if they want to. Please let me know what you think. Thank you!

SQL Server 2012 Service Pack 1 CU9

On March 17, 2014, Microsoft released SQL Server 2012 Service Pack 1 Cumulative Update 9. This is Build 11.0.3412, and it has 30 hotfixes in the public fix list. This CU is only for SQL Server 2012 Service Pack 1. If you are still on the SQL Server 2012 RTM branch, you do not want this update (and it will not let you install it if you try).

These are two of the more interesting hotfixes:

FIX: Performance problems occur in NUMA environments during foreign page processing in SQL Server 2012

FIX: Performance problems occur when database lock activity increases in SQL Server 2012

If you have been paying attention, you may have noticed that Microsoft did not release a SQL Server 2012 RTM CU12 back in mid-February. This means that the RTM branch is close to being retired (although I have not seen an official announcement from Microsoft yet). It also likely means that we are getting closer to seeing SQL Server 2012 SP2 being released (although there is no public date for that yet either). As soon as SP2 is released, Microsoft will probably officially retire the RTM branch.

Building a Workstation for SQL Server 2014 Development and Testing

With the existing supply of high-performance and very affordable desktop computer components, it is not very difficult to assemble an extremely high performance workstation for SQL Server 2014 development and testing at a very reasonable cost. Depending on how much performance you want and what your available budget is, you can take several different routes to get this accomplished.

At the high end of the spectrum, you can get a dual-socket, Socket 2011 motherboard, with two Intel Xeon E5-2600 v2 product family processors and a rather large quantity of ECC DDR3 RAM, and several data center-class SSDs, and spend a pretty significant amount of money.  At the lower end of the spectrum, you can put together a system with a single Intel Core i7-4770K processor, 32GB of non-ECC DDR3 RAM, and a single high-performance, 6Gbps consumer-class SSD, and have a system with more processing power than many existing Production database servers. It is really not very difficult to to do!

If you are going to build a desktop system from scratch, you need eight basic components:

  1. Computer Case
  2. Power Supply
  3. Motherboard
  4. Processor
  5. Memory (RAM)
  6. Storage (Hard Drive or SSD)
  7. Discrete Video Card (optional, not really necessary in most cases)
  8. Optical Drive (optional, becoming less important)

This assumes that you have a keyboard, mouse, and one or more monitors. I’ll discuss each one of these components, with some tips for what you should consider as you are choosing them.

Computer Case

You will need some sort of case to hold your components (unless you want to leave them running on a test bench). Personally, I like mid-range, mid-tower cases from companies like Fractal Design, Antec, Cooler Master, and Corsair. Mid-Tower cases give you plenty of room for common ATX motherboards, and they usually have at least four to six internal 3.5” drive bays. Newer designs have special 2.5” drive bays for SSDs and front or top mounted USB 3.0 ports. Better cases are much easier to work with, and they often have much better cable management features (so you can route most of your cables in a separate space under the motherboard). This not only looks much nicer, but it gives you better airflow inside the case. You probably don’t really need a fancy, gaming-oriented case with LED lighting and a huge number of case fans. A decent case in the $50-100 range will usually have good quality components (such as quieter, larger diameter case fans), along with good thermal and noise management features. The Fractal Design Core 3000 is a good example of an affordable, good quality case for about $80.00. It has six internal drive trays that can hold either 3.5” or 2.5” drives.

Power Supply

You should invest in a decent quality power supply as opposed to the cheapest one you can find. You don’t want to go overboard and get a 1200 watt behemoth gaming-oriented power supply (unless you are building an extreme gaming rig with multiple, high-end video cards that really need that much power). For the kind of system that I am recommending, you can use a high quality 400-500 watt 80 PLUS modular power supply and have plenty of reserve power. Modular power supplies have detachable cables for things like SATA power, MOLEX power, PCI-E power, etc., so you only need to plug in and use the cables you actually need.

Power supplies are much less efficient when they are only supplying a very small portion of their rated output. Getting a 1200 watt power supply because you think it must be “better” than a good 500 watt power supply is actually a waste of money, both for the initial cost of your power supply and the electrical power costs over the life of your machine. The components that I am recommending will end up drawing about 30-40 watts at idle.  I really like Seasonic power supplies, especially their fan-less, modular models such as the SS400FL and the newer SS-520FL. They are both completely silent and highly efficient power supplies. Another less expensive alternative that I like are Corsair power supplies, such as the Corsair CX500M modular power supply.

Motherboard

The motherboard is where all of your other components are plugged into, so it is a critical component. You need to consider which processor you are going to be using, since there are several different processor socket types available, which will dictate your motherboard choices. The most common type in early 2014 is the LGA 1150, which will work with the 4th generation 22nm Intel Core processors (Haswell). You also need to consider the form-factor of your motherboard. You can choose from ATX, micro-ATX, and mini-ITX, which refers to the size of the motherboard. You also need to think about the chipset used on your motherboard.

The Intel Z87 chipset is their best chipset for an LGA 1150 motherboard. As you are looking at motherboards, you should be looking at the low-to-mid range Z87-based motherboards instead of the high-end, gaming motherboards. The high-end gaming Z87 motherboards can be quite expensive, and they will have features (such as support for three discrete video cards), that you don’t need for a SQL Server workstation or test server. Instead, make sure you choose a model that has four DDR3 RAM slots, and a model that has at least six 6Gbps SATA ports. A good example is the Gigabyte GA-Z87M-D3H.

If you are going to run Windows Server 2012 or Windows Server 2012 R2 for your operating system, you should be aware that most Intel embedded NICs that you will find on many desktop motherboards will refuse let you install the NIC drivers with a Microsoft server operating system. In that case, you can buy an inexpensive, non-Intel ($15-20) PCI-E Gigabit Ethernet card that work just fine. If you are running Windows 8.1, you won’t have this issue.

Processor

You can choose a modern, Intel desktop processor that may well have much more raw processing power than many older two or four-socket production database servers. This is not an exaggeration, although it depends on the age of your production database server. You are far more likely to run into memory or I/O bottlenecks as you push a modern Intel desktop system than processor bottlenecks. For most people, an Intel Core i7-4770K processor will be your best choice (especially if you live near a Micro Center). It is a quad-core processor with hyper-threading (so you have eight logical cores) that runs at a base clock speed of 3.5GHz, with the ability to TurboBoost to 3.9GHz. It runs very cool, and is easy to overclock with the stock Intel processor cooler. It is not really necessary to overclock this processor, though. You can have a maximum of 32GB of DDR3 RAM with this processor.

Most 4th generation Intel Core processors (Haswell) have pretty good integrated graphics built-in to the CPU package. The better models have HD4600 graphics which give you more than enough performance for normal desktop usage and even some moderate gaming. There was a pretty big improvement in the integrated graphics performance between the Ivy Bridge and Haswell processors, so it is much more feasible to simply use the integrated graphics instead of buying a separate, discrete video card. This will save you money and reduce your electrical power usage.

One big variable in the cost of using this processor is whether you live near a Micro Center computer store or not. Micro Center has 23 locations in the Continental United States, and they sell a few specific models of Intel processors at prices that no other company seems willing to match. They have been doing this for years, and it is their regular practice (so it is not a special sale or promotion). The only catch is that those processors are only available for in-store pickup (so no mail-order).

For example, Micro Center is currently selling the Intel Core i7-4770K processor for $269.99, while NewEgg is selling the exact same Intel Core i7-4770K processor for $339.99. Micro Center quite often does promotions where they will reduce the price of a motherboard by $40-$50 if you buy the motherboard with a qualifying processor. Their prices on motherboards, cases, memory, hard drives and SSDs are also quite competitive.

Memory

If you select an LGA 1150, Z87 motherboard with four RAM slots, you can have up to 32GB of non-ECC DDR3 RAM in your system. You can get two 8GB sticks of 240-pin PC3 12800 DDR3 RAM for about $147.00, so it would be about $294.00 to get 32GB of RAM. This should be plenty for most development and testing workloads (including running multiple VMs), but if you really need more, you could make the jump to the LGA 2011 platform that uses the more expensive quad and six-core Intel Ivy Bridge-E processors where you can have 64GB of RAM. One thing you will want to do as you are configuring your system is to go into your BIOS setup and turn on Extreme Memory Profile (XMP), so that you will get better memory performance. This can occasionally cause stability problems, depending on the type of memory that you have, but if that happens, you can always turn it back off.

Storage

You will need some type of storage for your system. Traditional magnetic hard drive prices have finally gone down after the tragic flooding in Thailand back in late 2011, so that you can get a high-performance 2TB, 7200rpm SATA III drive with 64MB of cache, such as a 2TB Western Digital WD Black WD2003FZEX for $154.99. For just a little more money, you can also get a much smaller, but much much higher performance 6Gbps SATA III consumer-grade SSD, such as a 250GB Samsung 840 EVO SSD for $144.99. Solid State Drive prices have come down a lot (as performance has increased dramatically) over the past couple of years, but they still cost about eight times as much as conventional magnetic storage, per gigabyte.

I really encourage you to use a modern, fast 6Gbps SATA III SSD for your boot drive since it will have an extremely dramatic, positive effect on how fast your system performs and “feels” in everyday use. It will boot faster, shut down faster, programs will load nearly instantly, and it will take much less time to install new software and Windows Updates. It is similar to the difference between a dial-up modem and a fast broadband connection. Once you start using a fast SSD, you will never want to go back to a conventional magnetic hard drive.

You want to make sure your fast 6Gbps SSD is plugged into a 6Gbps SATA III port (not one of the 3Gbps SATA II ports). Otherwise, your fast SSD will be limited to about 275MB/sec for sequential reads and writes (which is still about twice as fast as a very fast traditional 7200rpm SATA hard drive). You also want to avoid the smallest capacity 64GB and 128GB SSD models, since their performance is much usually much lower than the larger capacity models from the same manufacturer and product line. This is because the smaller capacity models have fewer NAND chips and fewer data channels. Ideally, you would want a 250GB (or larger) 6Gbps SATA III SSD plugged into each SATA III port that you have available on your motherboard. This would give you lots of options for how to lay out your SQL Server data files, log files, tempdb files and SQL Server backup files.

Of course, you may not want to spend that much money, so it is still common to have one or two SSDs, along with one or more conventional magnetic drives in a desktop system. One of the luxuries with a desktop system compared to any laptop is that you have a very high number of internal drive bays and up to ten or twelve SATA ports on the motherboard. You can also buy inexpensive PCI-E SATA III cards to add even more SATA III ports to a desktop system.

Discrete Video Card

There are some situations where the Intel Haswell HD4600 integrated graphics might not be enough for your needs. An example would be if you were doing things such as AutoCad that really place a lot of stress on your graphics performance. Another example is if you wanted to run multiple, large monitors on your system. Most motherboards that support the Intel integrated graphics only have two or three video connectors (such as a VGA connector, DVI connector and an HDMI connector), so that would limit how many monitors you could connect to the system. If you do decide to go with one or more discrete video cards, you can get quite decent performance for about $100-150.00 each (but you can spend much more). You may also need a power supply with multiple, supplemental PCI-E power connectors, and you might even need a higher capacity power supply.

Optical Drive

Even though they are becoming much less useful over time, I still like to have a DVD-Recorder, optical drive in a desktop system. It just makes it easier to install the operating system and other software (although you can certainly install from a USB drive). It is also becoming much more common to simply mount an .iso file for doing something like installing SQL Server 2014. You can get bare, OEM optical DVD drives for about $15-20.

So, after all of this, how much money am I trying to convince you to spend?  Well, here is one example:

  1. Case                   $80.00
  2. Power Supply       $50.00
  3. Motherboard      $130.00
  4. Processor          $270.00          (Intel Core i7-4770K from Micro Center)
  5. RAM                  $294.00          (32GB of DDR3 RAM)
  6. Storage             $300.00          (One 250GB Samsung 840 EVO SSD and one 2TB 7200rpm drive)
  7. Optical drive         $20.00

Total System         $1144.00

This system would have much better performance than a laptop that would cost several times as much. It would also have better performance than many production SQL Server database servers. It would be pretty easy to slice over $400.00 off of this system cost by choosing some different components, and still have a very capable system.

Here is a second, lower cost example:

  1. Case                   $50.00
  2. Power Supply       $50.00
  3. Motherboard        $60.00          (Lower cost model, plus $40.00 Micro Center Processor Bundle discount)
  4. Processor           $190.00         (Intel Core i7-4670K from Micro Center)
  5. RAM                   $147.00         (16GB of DDR3 RAM)
  6. Storage              $200.00         (One 120GB Samsung 840 EVO SSD and one 1TB 7200rpm drive)
  7. Optical drive         $ 20.00

Total System            $717.00

This second system would still be quite powerful, although it would only be a quad-core processor (with no hyper-threading, so it would have about 70% of the processor capacity of the Core i7-4770K), have half the RAM, and half the storage space.

Of course, neither one of these systems has redundant, server-class components or ECC RAM, so you would not want to use them in a production situation. They would probably be much better (in terms of performance) than some ancient, out of warranty, retired server for development and testing.

SQL Server Diagnostic Information Queries for March 2014

I made a couple of changes in the order of the queries this month and made some other small improvements to a few queries. Rather than having a separate blog post for each version, I’ll just put the links for all five major versions in this single post. There are two separate links for each version. The first one on the top left is the actual query script, and the one below on the right is the matching blank results spreadsheet.  

SQL Server 2005 Diagnostic Information Queries

SQL Server 2005 Blank Results

SQL Server 2008 Diagnostic Information Queries

SQL Server 2008 Blank Results

SQL Server 2008 R2 Diagnostic Information Queries

SQL Server 2008 R2 Blank Results

SQL Server 2012 Diagnostic Information Queries

SQL Server 2012 Blank Results

SQL Server 2014 Diagnostic Information Queries

SQL Server 2014 Blank Results

The basic idea is that you should run each query in the set, one at a time (after reading the directions). You need to click on the top left square of the results grid in SSMS to select all of the results, and then right-click and select “Copy with Headers” to copy all of the results, including the column headers to the Windows clipboard. Then you paste the results into the matching tab in the blank results spreadsheet. There are also some comments on how to interpret the results after each query.

About half of the queries are instance specific and about half are database specific, so you will want to make sure you are connected to a database that you are concerned about instead of the master system database.

Note: These queries are stored on Dropbox. I occasionally get reports that the links to the queries and blank results spreadsheets do not work, which is most likely because Dropbox is blocked wherever people are trying to connect.

I also occasionally get reports that some of the queries simply don’t work. This usually turns out to be an issue where people have some of their user databases in 80 compatibility mode, which breaks many DMV queries.

There is an initial query in each version that tries to confirm that you are using the correct version of the script for your version of SQL Server. Please let me know what you think of these queries, and whether you have any suggestions for improvements. Thanks!

Two New TPC-E Benchmark Results for Intel Xeon Ivy Bridge-EX Processors

A couple of weeks ago, two new TPC-E benchmark submissions showed up for four-socket servers with the new 22nm, 15-core Intel Xeon E7-4890 v2 processor that I discussed a few days ago. NEC submitted a result for a four-socket NEC Express5800/A2040b system with a raw TPC-E score of 5,087.17, while IBM submitted a result for a four-socket IBM System x3850 X6 system with a raw TPC-E score of 5,576.27.

These are both incredible scores for a four-socket system, both for the actual raw score and from a score per physical core perspective. Both of these tested systems have actual TPC-E scores that rival an eight-socket system with the previous generation 32nm Intel Xeon E7-4870 Westmere-EX processor, while their single-threaded performance (as measured by the TPC-E score divided by the number of physical cores) is also relatively close to what we see in the latest 22nm Intel Xeon E5-2697 v2 Ivy Bridge-EP processors. This gives you the possibility of eight-socket capacity, with close to modern two-socket single-threaded performance in a four-socket server.

System Processor TPC-E Score Sockets Total Cores Score/Core
IBM System x3650 M4 E5-2697 v2 2590.93 2 24 107.96
IBM System x3850 X6 E7-4890 v2 5576.27 4 60 92.94
IBM System x3850 X5 E7-4870 3218.46 4 40 80.46
IBM System X3850 X5 E7-8870 5457.20 8 80 68.22

Table 1: Recent IBM TPC-E Benchmark scores

As you can see from Table 1, the Intel Xeon E7-4890 v2 processor is a huge improvement over the previous Intel Xeon E7-4870 processor, with much higher overall capacity and higher single-threaded performance. You also get much higher memory capacity and PCI-E 3.0 support with the new processor.

On the negative side, your SQL Server 2012/2014 core license costs will be 50% higher if you go with the high-end 15-core E7-4890 v2 processor. One alternative would be to use the 12-core, Xeon E7-4860 v2 processor or even the ten-core, Xeon E7-4830 v2 processor to minimize your SQL Server 2012/2014 license costs. One slight problem with that strategy is that the base and turbo clock speeds are lower in the lower core-count processors in the Xeon E7-48xx v2 product family, since they don’t have lower core count, “frequency-optimized” models like the Xeon E5-26xx v2 product family does.

Four-socket systems with these new processors are going to be much faster and have much more total load capacity than previous four-socket systems with the older Westmere-Ex processor.

Bigger Database Servers Get Faster

For quite some time, I have been talking about how current Intel-based four-socket database servers have had significantly lower single-threaded processor performance than current Intel-based two-socket database servers. This is because the first generation Intel Xeon E7 processors were using the relatively old 32nm Westmere microarchitecture that was introduced in early 2011 for the initial Xeon E7 (Westmere-EX) product line.

These E7 processors also use much lower base and turbo clock speeds than current Xeon E5 v2 processors, which also hurts their single-threaded processor performance. They do have higher overall concurrent load capacity due to higher total memory capacity and more total processor cores, but the individual processor cores in most four-socket servers have been much slower than what you find in a modern two-socket server. Simply put, bigger servers are not faster servers. It is like comparing an eighteen wheeler truck to a Tesla Model S.

Now, that old assessment is going to change somewhat, with the release of the 22nm Intel Xeon E7 Processor v2 Family (Ivy Bridge-EX), and new model servers from the major server vendors that have even higher memory capacity, PCI-E 3.0 support, and 12Gbps SAS/SATA support, along with much faster RAID controllers. These processors are a substantial improvement over the previous generation 32nm Intel Xeon E7 processors (Westmere-EX) that have been available since early 2011.

It will still be possible to configure a new two-socket server, such as a Dell PowerEdge R720, with an appropriate 22nm Intel Xeon E5-2600 Processor v2 Family (Ivy Bridge-EP) processor that will have better single-threaded performance than a new four-socket server such as a Dell PowerEdge R920, but the gap will not be nearly as large as it once was.

The actual good news here for a database professional is the fact that you will be able to have a four-socket server that has as much load capacity as a previous generation, eight-socket server, that also performs nearly as well as a current two-socket server, while paying 25% less for your SQL Server 2012/2014 license costs (compared to a previous generation eight-socket server). This is a pretty big gift from Intel!

A more pessimistic view is that your SQL Server 2012/2014 license costs could rise by 50% as you move from an existing server equipped with four, ten-core Xeon E7-4870 processors (with a total of forty physical cores) to a new server with four, fifteen-core Xeon E7-4890 v2 processors (with a total of of sixty physical cores). For reasons known only to Intel, the lower core count SKUs in the Xeon E7-48xx v2 product family are not “frequency optimized”, meaning they do not have higher clock speeds than the high-end, E7-4890 v2 processor. The base and turbo clock speeds of the best lower core-count SKUs in the E7- 48xx v2 family actually drop off pretty quickly as the core counts go down. The shared-L3 cache sizes also drop off very quickly, as does the processor price, as you can see in Table 1.

Processor Physical Cores L3 Cache Base Clock Turbo Clock Price
E7-4890 v2 15 37.5 MB 2.8GHz 3.4GHz $6,619.00
E7-4860 v2 12 30 MB 2.6GHz 3.2GHz $3,838.00
E7-4830 v2 10 20 MB 2.2GHz 2.7GHz $2,059.00
E7-4820 v2 8 16 MB 2.0GHz 2.5GHz $1,446.00
E7-4809 v2 6 12 MB 1.9GHz N/A $1,223.00

Table 1: Selected Intel E7-48xx v2 Processors

 

With the Xeon E4-48xx v2 product family, you are going to want to choose either the E7-4890 v2 or the E7-4860 v2 model processors in most situations, since the lower core count processors are giving up a substantial amount of performance due to their lower clock speeds and smaller L3 cache sizes. If you really want to reduce your core counts to reduce your SQL Server 2012/2014 license costs, you would be better off with the Intel Xeon E5-26xx v2 product family processors that are used in two socket servers. Another alternative is the upcoming Intel Xeon E5-46xx v2 product family processors that are used in four-socket servers.

Either of those choices would be better than one of the lower core count processors in the E7-48xx v2 product family, at least from a pure processor performance perspective.

Intel also has refreshed the E7-88xx v2 product family that is meant for eight-socket and larger servers. For some reason (probably for HPC use), Intel does have “frequency-optimized”, lower core-count models in this product family, as you can see in Table 2.

Processor Physical Cores L3 Cache Base Clock Turbo Clock Price
E7-8890 v2 15 37.5 MB 2.8GHz 3.4GHz $6,841.00
E7-8857 v2 12 30 MB 3.0GHz 3.6GHz $3,838.00
E7-8891 v2 10 37.5 MB 3.2GHz 3.7GHz $6,841.00
E7-8893 v2 6 37.5 MB 3.4GHz 3.7GHz $6,841.00

Table 2: Selected Intel E7-88xx v2 Processors

 

I could see some scenarios where you might want to get an eight-socket server with the six-core E7-8893 v2, so that you could have the same physical core count, while having double the memory capacity and much better single-threaded processor performance than a four-socket server with the twelve-core E7-4860 v2. The hardware cost would be significantly higher, since you would be buying eight processors for $6,841.00 each instead of four processors at $3,838.00 each, but for many organizations, that would not be a major issue.

Some server vendors may offer the Xeon E7-88xx v2 processors in their four-socket server models, since they are pin-compatible, which would give us a lot more flexibility as far as processor selection goes. I really wish Intel had “frequency-optimized” models in their Xeon E7-48xx v2 product family, to make this even easier.