Building a Workstation for SQL Server 2012 Development and Testing

With the current crop of high-performance and very affordable desktop computer components, it is not very difficult to assemble an extremely high performance workstation for SQL Server 2012 development and testing at a very reasonable cost. Depending on how much of a purist you are 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 series 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-3770K 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 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. Optical Drive (becoming less important)
  8. Discrete Video Card (optional)

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 obviously 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 Antec, Cooler Master, and Corsair. Mid-Tower cases give you plenty of room for common ATX motherboards, and they usually have 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 easier to work with, and they often have much better cable management features (so you can route most 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 Antec Three Hundred Two is a good example of an affordable, good quality case for about $70.00.

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 35-50 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.


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 2013 is the LGA 1155, which will work with both 2nd and 3rd generation Intel Core processors (Sandy Bridge and Ivy Bridge). 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 Z77 chipset is their best chipset for the LGA 1155 motherboard. As you are looking at motherboards, you should be looking at the low-to-mid range Z77-based motherboards instead of the high-end, gaming motherboards. The high-end gaming Z77 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 four 6Gbps SATA ports and four 6Gbps SATA ports. Some newer model motherboards also have an mSATA connector on the motherboard so you can use an mSATA SSD mounted directly on the motherboard itself. A good example is the Gigabyte GA-Z77X-D3H.


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-3770K 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. You can have a maximum of 32GB of DDR3 RAM with this processor.

Most 3rd generation Intel Core processors (ivy Bridge) have pretty decent integrated graphics built-in to the CPU package. The better models have HD4000 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 Sandy Bridge and Ivy Bridge 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.

You can see the CPU-Z figures for a lightly over-clocked Intel Core i7-3770K processor in Figure 1 below.


Figure 1: CPU-Z of a Lightly Over-Clocked Intel Core i7-3770K

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-3770K processor for $229.99, while NewEgg is selling the exact same Intel Core i7-3770K processor for $329.99 (although they currently have a $20.00 promotion going). 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.


If you select an LGA 1155, Z77 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 $85.00, so it would be about $170.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 Sandy 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.


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 WD2002FAEX for $179.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 256GB OCZ Vector SSD for $239.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 ten times as much as conventional magnetic storage.

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 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 300MB/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 256GB (or larger) 6Gbps SATA III SSD plugged into each SATA III port that you have available on your motherboard. This would give 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 eight 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.

Optical Drive

Even though they are becoming 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 2012. You can get bare, OEM optical drives for about $20-25.

Discrete Video Card

There are some situations where the Ivy Bridge HD4000 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.

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

  1. Case                   $75.00
  2. Power Supply     $125.00
  3. Motherboard      $150.00
  4. Processor          $230.00          (Intel Core i7-3770K from Micro Center)
  5. RAM                  $170.00          (32GB of DDR3 RAM)
  6. Storage             $420.00          (One 256GB SSD and one 2TB 7200rpm drive)
  7. Optical drive         $25.00

Total System     $1195.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 $500.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-3570K from Micro Center)
  5. RAM                   $ 85.00          (16GB of DDR3 RAM)
  6. Storage              $210.00         (One 128GB SSD and one 1TB 7200rpm drive)
  7. Optical drive         $ 25.00

Total System         $670.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-3770K), 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.

This type of system is going to give you the best price/performance ratio until the upcoming 22nm Intel Haswell processors are released in June 2013. The Haswell is slated to use a different LGA 1150 socket, so you will have to get a different motherboard along with your Haswell CPU.

More Courses from SQLskills on Pluralsight!

Late this afternoon, two more SQLskills authored courses went live on Pluralsight. The first one is from me, which is SQL Server 2012: Installation and Configuration, while the second one is from my colleague Joe Sack, who authored SQL Server: Troubleshooting Query Plan Quality Issues . With these two courses being available, that makes 13 out of 24 total SQL Server related courses from SQLskills on Pluralsight.  We plan on adding many more during 2013.

If you have not watched a Pluralsight course, I really urge you to give them a try. They pick top authors, and enforce strict quality standards, so you get a great product.

Here is a list of all of the latest releases from the Pluralsight catalog.

Important Hotfix for SQL Server 2008 and Newer

Microsoft recently released a hotfix for a memory leak leak issue that affects SQL Server 2008, SQL Server 2008 R2, and SQL Server 2012. This memory leak only occurs if the have the AUTO_UPDATE_STATISTICS_ASYNC database level setting enabled for any of your databases. Here is how Microsoft describes the issue in the hotfix KB article:

When you enable the Auto Update Statistics Asynchronously statistics option in a database of Microsoft SQL Server 2012, Microsoft SQL Server 2008 or Microsoft SQL Server 2008 R2, and then you run queries on the database, a memory leak occurs. The memory structure that is leaked is about 80bytes and it occurs every time you submit a task to update stats async which is per object. The symptom of this leak can be seen in DBCC MEMORYSTATUS from the Resource Global Memory Object.

I have cleaned up the T-SQL for the two queries that Microsoft supplies in the KB article to detect this issue, as shown below:

   1: -- Check for memory leak described in KB2778088 (SQL Server 2012)

   2: --

   3: SELECT pages_in_bytes /1024 AS [Memory Used KB]

   4: FROM sys.dm_os_memory_objects

   5: WHERE [type] = N'MEMOBJ_RESOURCE';


   7: -- Check for memory leak described in KB2778088 (SQL Server 2008 and 2008 R2)

   8: SELECT (pages_allocated_count * page_size_in_bytes)/1024 AS [Memory Used KB]

   9: FROM sys.dm_os_memory_objects

  10: WHERE [type] = N'MEMOBJ_RESOURCE';

Code Sample 1: Checking for this memory leak

According to my friends at Microsoft, this issue has been present since the RTM builds of all three of these major versions of SQL Server. Depending on how many databases you have with this database-level setting, how many objects (indexes and statistics objects) they have, and how often they are automatically updated asynchronously, this memory leak could add up at quite different rates.

The latest Cumulative Updates for all of the active branches (except for SQL Server 2012 SP1) of these three major versions of SQL Server all have this hotfix, as shown below:

Cumulative Update 5 for SQL Server 2012 RTM

Cumulative Update 4 for SQL Server 2008 R2 SP2

Cumulative Update 10 for SQL Server 2008 R2 SP1

Cumulative Update 8 for SQL Server 2008 SP3

SQL Server 2012 SP1 CU2 is due to be released sometime in January, and it should also have this hotfix.  SQL Server 2008 RTM, SQL Server 2008 SP1, SQL Server 2008 SP2 , and SQL Server 2008 RTM are all considered “retired service packs”, so they will not get this hotfix (or any other non-security hotfixes).

If you are on an older build of any of these three major versions of SQL Server, you can either disable this database level setting or you can get one of these Cumulative Updates installed. Personally, I would much rather be up-to-date on my SQL Server Build rather than disable this setting (which I believe is quite beneficial for most workloads).

This setting reduces unpredictable query performance by allowing statistics to be automatically updated asynchronously while your currently running queries continue to use the old statistics until the updated statistics are ready to use. The alternative to this (which is the default) is for query execution to halt (only for queries that use statistics for that object) while the statistics are automatically updated synchronously for that object. Depending on the size of the object, and on your hardware and I/O subsystem, this could take anywhere from a few seconds to many minutes.

Of course, you are also better off if you try to manage your statistics updates on your more important objects yourself, using scheduled SQL Server Agent jobs, while leaving Auto Update Statistics enabled “just in case”. That way, you are less likely to ever have any type of automatic statistics updating kick in at an inopportune time and you can tailor your statistics updating more appropriately for your workload.

You can check which of your databases have this setting enabled with this query:

   1: -- Check the status of auto_update_stats_async for all databases

   2: SELECT [name] AS [Database Name], is_auto_update_stats_async_on

   3: FROM sys.databases;

Code Sample 2: Checking Database Properties

You can also look in the SQL Server Management Studio (SSMS) Database Properties – Options page to see this setting.


Figure 1: Database Properties – Options


You can use a query like this to change this database-level setting

   1: -- Disable auto_update_stats_async for a database

   2: USE [master];

   3: GO

   4: -- Change to use the name of your database

   5: ALTER DATABASE [AdventureWorks2012]


   7: GO

Code Sample 3: Changing a Database Property


I have used this database-level setting for years, on many different databases, in many different environments, with very good results, and I plan on continuing to use it in the future, because I think it is very helpful, especially for OLTP workloads. If you are concerned about this memory leak, I think the best solution is to get a Cumulative Update that includes the hotfix installed on your instance as soon as you can do your planning and testing.