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.

SQL Server Diagnostic Information Queries for February 2014

I improved the file-level latency query this month and made some other small improvements to a few other 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!

If you want to see me demonstrate and explain how to interpret these queries, you should consider attending the SQLintersection Conference in April.

SQL SP14 300x250 v2 SQL Server Diagnostic Information Queries for February 2014

Spring SQLintersection Conference in Orlando

This year the Spring 2014 SQLintersection Conference will be in Orlando, FL at the JW Marriott Grand Lakes from April 12th to April 17, (including the pre-con and post-con workshops). There will be an informal “Ask the Experts” area with many of the speakers, including two Microsoft Premier Field Engineers, – David Pless and Tim Chapman. The idea is that you can bring your own problems and network with other attendees and speakers in smaller, more relaxed setting, where you can actually spend some quality time with the experts.

There are a number of separate, practical tracks on each day for the conference, including things like Performance, Query Tuning, SQL Server 2014, BI Development, Platform Development, Statistics, Workload Analysis, Database Development, Programming, Troubleshooting, Hadoop & Riak, Indexing, and Core Administration. There will also be a number of sessions from Microsoft on In-Memory OLTP in SQL Server 2014, aka “Hekaton”, which I think will be very interesting for a lot of people.

Who are the speakers?

Honestly, there is really a great list of speakers for this conference, with nothing but top-notch content. You can see the complete list of sessions here. Here is the complete speaker list:

  • Aaron Bertrand, Sr. Consultant, SQL Sentry, Inc. (blog | twitter)
  • Andrew J. Kelly, Mentor, SolidQ (blog | twitter)
  • Bob Beauchemin, Development Partner, SQLskills.com (blog | twitter)
  • Bob Ward, Principal Architect Escalation Engineer, Microsoft (blog | twitter)
  • Brent Ozar, Brent Ozar Unlimited (blog | twitter)
  • David Pless, Senior Premier Field Engineer, Microsoft (blog | twitter)
  • Erin Stellato, Principal Consultant, SQLskills.com (blog | twitter)
  • Glenn Berry, Principal Consultant, SQLskills.com (blog | twitter)
  • Grant Fritchey, Product Evangelist, Red Gate Software (blog | twitter)
  • Jeremiah Peschka, Brent Ozar Unlimited (blog | twitter)
  • Jonathan Kehayias, Principal Consultant, SQLskills.com (blog | twitter)
  • Jos de Bruin, Senior Program Manager, Microsoft
  • Kendra Little, Managing Director, Brent Ozar Unlimited (blog | twitter)
  • Kevin Kline, Director of Engineering Services, SQL Sentry (blog | twitter)
  • Kimberly L. Tripp, President/Founder, SQLskills.com (blog | twitter)
  • Mike Zwilling, Principal Architect, Microsoft
  • Paul S. Randal, CEO / Owner, SQLskills.com (blog | twitter)
  • Steve Jones, Editor, SQLServerCentral.com (blog | twitter)
  • Tim Chapman, Premier Field Engineer, Microsoft (blog | twitter)

There are also five pre-con workshops and two post-con workshops:

  • What’s New in SQL Server 2014 with Bob Beauchemin on Saturday, April 12
  • 50 Things All SQL Server Developers Need To Know! with Kevin Kline and Aaron Bertrand on Saturday, April 12
  • Practical Disaster Recovery Techniques with Paul S. Randal on Sunday, April 13
  • Queries Gone Wild: Real-world Solutions  with Kimberly L. Tripp on Sunday, April 13
  • Developer’s Guide to SQL Server Operations with Jeremiah Peschka and Kendra Little on Sunday, April 13
  • Make SQL Server Apps Go Faster with Brent Ozar, Jeremiah Peschka, and Kendra Little on Thursday, April 17
  • Windows Azure SQL Database from A to Z with Bob Beauchemin on Thursday, April 17

You also get to pick and choose from any sessions from the five co-located conferences on SharePoint, ASP.NET, Azure, Visual Studio, and AngleBrackets while you are at this conference.  Early Bird Bonus: If you register for a Show Package (which is the full conference plus one pre or post-conference session), by February 24, you will receive your choice of a Microsoft Surface 2, Microsoft XBox One, or a $300.00 gift card.

Finally, if you use the discount code of SQLskills when you register, you will get $50.00 off of your registration.

SQL SP14 300x250 v2 Spring SQLintersection Conference in Orlando

Final Service Packs for SQL Server 2008 and SQL Server 2008 R2

As we get ever closer to the end of mainstream support for both SQL Server 2008 and SQL Server 2008 R2 on July 8, 2014, I am very curious whether Microsoft is planning on releasing a SQL Server 2008 R2 Service Pack 3 or a SQL Server 2008 Service Pack 4 ?

SQL Server 2008 Service Pack 3 was released on October 25, 2011 , and we are now up to SQL Server 2008 SP3 CU15. SQL Server 2008 R2 Service Pack 2 was released on July 26, 2012, and we are now up to SQL Server 2008 R2 SP2 CU10.

I am not looking for release dates, just some word on whether there will be new Service Packs for SQL Server 2008 or SQL Server 2008 R2 before they both fall out of mainstream support this July.  Since many organizations still refuse to install Cumulative Updates, we have the very strong possibility of many customers running some very old builds of SQL Server 2008 and 2008 R2 going forward unless we get a final set of Service Packs for both versions.

As Paul Randal discovered in his recent survey, a very large percentage of SQL Server 2008 and 2008 R2 instances in the wild are already running on “unsupported service packs”, and this situation only got worse when SQL Server 2008 R2 SP1 fell out of support on October 8, 2013. These links show the builds that have been released since the most recent Service Packs for SQL Server 2008 and SQL Server 2008 R2.

The SQL Server 2008 Builds that were released after SQL Server 2008 Service Pack 3 was released

The SQL Server 2008 R2 Builds that were released after SQL Server 2008 R2 Service Pack 2 was released

If you want to try to influence Microsoft in some small way, please take a moment to up vote these Connect items. It only will take a few seconds to click the green arrow!

This is mine, meant to be a generic item:

https://connect.microsoft.com/SQLServer/feedback/details/814658/release-final-service-packs-for-sql-server-2008-and-2008-r2

This one is from Christoph Muthmann, for SQL Server 2008 SP3:

https://connect.microsoft.com/SQLServer/feedback/details/814600/release-service-pack-3-for-sql-server-2008-r2

This one is from Erland Sommarskog, regarding SQL Server 2012 SP2:

https://connect.microsoft.com/SQLServer/feedback/details/814656/release-service-pack-2-for-sql-server-2012

If you have a bit more time, write a comment in any of the Connect items.