SQL Server 2008 Service Pack 2 Cumulative Update 7 Released

Posted on November 22, 2011 by Glenn Berry

Microsoft has released SQL Server 2008 Service Pack 2 Cumulative Update 7, which is Build 10.00.4323.00. I count 18 fixes in this CU, several of which are related to Full-Text indexes. If you are still on the SQL Server 2008 SP2 branch, I would be thinking about getting on the SP3 branch, because eventually, the SP2 branch will be “retired”, and you will be on an unsupported service pack. As I always do, I want to remind everyone that this CU is only for SQL Server 2008 SP2, not for SQL Server 2008 R2.

Posted in Microsoft, SQL Server 2008 | Tagged | Leave a comment

Tuesday Hardware Tidbits, Nov 15, 2011

Posted on November 14, 2011 by Glenn Berry

A couple of interesting developments this week. First, Intel has officially released (and lifted the embargo) on their new high-end, Sandy Bridge-E desktop processors that use the LGA 2011 platform and X79 chipset. This is relevant for SQL Server professionals because Sandy Bridge-E is a binned version of the upcoming Sandy Bridge-EP two-socket server processor (which is due to be released in Q1 2012). The initial Sandy Bridge-E desktop processors only have six cores (plus hyper-threading), and either 12MB or 15MB of L3 cache instead of eight cores (plus hyper-threading) and 20MB of L3 cache like you will see in the Sandy Bridge-EP, aka the Xeon E5-2600 series. They both support PCI-E 3.0 and have quad-channel memory controllers, which is far more important in the server space than for desktops. In the desktop space, you will see support for eight memory slots, which means you could have 64GB of RAM on your desktop if you have deep enough pockets.  AnandTech, Tom’s Hardware, HardOCP, and LegitReviews all have good coverage of these processors.  Personally, if I was looking to build a new desktop system, I would wait for the mainstream 22nm Ivy Bridge, which is due in Q1 2012.

Second, AMD has officially released the AMD Opteron 4200 and 6200 series processors (even though they have been shipping to OEMs like Cray since September). Until Microsoft changed their licensing policy for SQL Server 2012 a couple of weeks ago, I was pretty hopeful that the AMD Opteron 6200 (with sixteen physical cores) might be competitive in the SQL Server space, especially for data warehouse workloads. With core-based licensing for SQL Server 2012 Enterprise Edition, that will be a much harder challenge for AMD.  You can check out AMD’s presentation here (PDF warning).

I really think the Sandy Bridge-EP (Xeon E5-2600 series) is going to be a world beater in the two socket space. Keep in mind that there will not be a Sandy Bridge-EX processor for four socket and above systems, so the current Xeon E7-4800 and E7-8800 series (Westmere-EX) will be the best you can get in that form factor, staying around until the Ivy Bridge-EX is released in probably 12-18 months. If you look at the per core performance on the TPC-E OLTP benchmark, the current Xeon X5690 (Westmere-EP) smokes the current Xeon E7-4870 (Westmere-EX) processor. The Sandy Bridge-EP should only increase that performance gap, which will make a two socket system with Sandy Bridge-EP a very compelling choice for SQL Server 2012 Enterprise Edition.

Posted in Computer Hardware, Processors | Tagged | 1 Comment

OT: Amateur Astronomy

Posted on November 12, 2011 by Glenn Berry

Back in 1997, when the Mars Pathfinder bounced down on Mars on July 4, releasing the little Sojourner rover to move around the Martian surface, I became interested in Astronomy. I was living in an apartment in Foothill Ranch, California, stuck in an area with quite a bit of light pollution. After doing some research on how to get into the hobby on a proper basis, I bought a pair of pretty decent astronomical binoculars to get started. These were a pair of Celestron Ultima 8×56 binoculars, which have served me well for many years. Using these binoculars, along with my naked eyes, I was able to start learning the constellations, which is really the best way to get started in Astronomy.

After about six months, I was ready to buy my first telescope. I had bought a number of books, and I had subscribed to both Sky and Telescope and Astronomy magazines. I had also joined the Orange County Astronomers club, going to meetings and to a few local Star Parties. Because of all this preparation and support, I did not make the common mistake of buying a cheap, department store refractor (which is what most lay people think of when they visualize a telescope). Instead, I bought an 8” Celestron Starhopper Dobsonian telescope. Dobsonian telescopes are reflectors that use a simple, alt-azimuth mount of a design popularized by John Dobson.

8 inch Starhopper

The idea was that you could use very cheap materials, such as particle board for the mount, and a thick cardboard sonotube for the barrel of the telescope. This made it pretty easy to have a relatively large mirror, such as a 6”, 8”, 10”, etc. in a large aperture telescope, that could gather a lot of light for not very much money at all. This type of telescope is often called a light bucket. Dobsonian telescopes are very easy to learn how to use, provided you know your way around the sky. They are not suitable for photographic use, since they don’t have equatorial, motorized mounts (although some newer Dobsonian mounts do have Goto systems). I added a TelRad finder scope, a few eyepieces and filters, and I was having a lot of fun. Then I moved to Colorado in 1999, and I ended up using that big Dobsonian scope less and less. It was partly due to the weather, where it was colder at night, and also more frequently cloudy at night. I think is was also because I was more reluctant to pack up the Dobsonian and take it somewhere dark.

After a number of years of being idle in the hobby (and forgetting a lot of what I had learned), my interest in Astronomy has been reawakened. I discovered a very nice local telescope shop called S&S Optika, in Littleton, CO. After a little research, I bought a brand new Celestron NexStar 6SE Schmitt-Cassegrain telescope (SCT). This scope does not have the same light gathering capacity of my old 8” Dobsonian, but it is much more compact. Plus, it has a nice little computer GoTo system. This is definitely an entry level SCT, not really suitable for astrophotography. Still, I am having fun with it so far.

I have also discovered and joined the Denver Astronomical Society, which is an old and very active astronomy club that does a lot of outreach to the public. They operate the historic 20” Chamberlin Telescope that was built in 1894 a few blocks away from the University of Denver Campus, where they have Public Nights every Tuesday and Thursday, along with a monthly Open House. Now that I am getting back in the hobby, I plan to start working on some of the observing clubs from the Astronomical League, such as the Messier Club.

Posted in Astronomy, Hobbies | Tagged | Leave a comment

SQL Server 2012 Licensing and Hardware Considerations

Posted on November 10, 2011 by Glenn Berry

Last week, Microsoft announced some pretty fundamental changes in how SQL Server 2012 will be licensed compared to previous versions of SQL Server. With SQL Server 2012, there will be three main Editions of SQL Server. These are Enterprise Edition, Business Intelligence Edition, and Standard Edition. Data Center Edition and Workgroup Edition are gone (both of which are no big loss in my opinion). Developer and Express Editions will still be available, along with Web Edition for hosting providers.

Rather than the old familiar socket-based licensing used in SQL Server 2008 R2 and below, SQL Server 2012 will use a combination of core-based and Server + Client Access License (CAL) based licensing, depending on which Edition you buy (and which choice you make for Standard Edition). With Standard Edition, you can choose core-based licensing or Server + CAL-based licensing. With Business Intelligence Edition, you have to use Server + CAL-based licensing, while Enterprise Edition requires the use of core-based licensing. Standard Edition is the base edition, with a limit of 16 physical processor cores. Microsoft has not announced whether there will be a RAM limit for Standard Edition (like the 64GB RAM limit in SQL Server 2008 R2 Standard Edition). Business Intelligence Edition includes all of the functionality of Standard Edition, plus extra BI features and functionality. Enterprise Edition includes everything in BI Edition, plus all of the extra Enterprise Edition features and functionality. Enterprise Edition is the top of the line edition of SQL Server 2012 that will now get everything that was in SQL Server 2008 R2 Data Center Edition.

If you are using core-based licensing (like you must for Enterprise Edition), each physical socket in your server must use a minimum of four core licenses. That means if you have old hardware that uses dual-core processors, you will still have to buy four core licenses for each socket. That is yet another reason to not use ancient hardware for a new version of SQL Server! Any Intel Xeon processor that only has two physical cores will be at least four-five years old by the time SQL Server 2012 is released, so it really should be retired. Keep in mind that only physical cores count for licensing purposes (on non-virtualized servers), so hyper-threading comes for free.

Core licenses will be sold in two-core packs, again with a minimum of four-cores per physical socket. The full retail license cost per physical core is $6874.00 for SQL Server 2012 Enterprise Edition. This is pretty grim news for AMD, with their higher physical core counts and lower per socket performance compared to Intel.  The table below shows the cost differential in pretty graphic detail.

Processor Cores Per Socket Cost Total Sockets Total License Cost/Server
Intel Xeon X5690 6 $41,244 2 $82,488
Intel Xeon E5-2690 8 $54,992 2 $109,984
Intel Xeon X7560 8 $54,992 4 $219,968
Intel Xeon E7-4870 10 $68,740 4 $274,960
AMD Opteron 6180SE 12 $82,488 4 $329,952
AMD Opteron 6276 16 $109,984 4 $439,936

 

For most OLTP workloads, you would be far better off from a performance perspective with a two-socket Intel Xeon X5690 server than you would be with a four-socket AMD Opteron 6180SE server. The extremely large license cost difference between those two choices makes Intel an even more compelling choice.

One way to somewhat confirm this assessment is to look at TPC-E scores for different systems and divide them by the total physical core count for the system (not by the thread-count). Looking at the table below, it seems like a two-socket Intel system is a pretty good choice.

System Processor TPC-E Score Total Cores Score/Core
HP ProLiant DL380 G7 Server Intel Xeon X5690 1284.14 12 107.01
HP ProLiant DL580 G7 Server Intel Xeon E7-4870 2454.51 40 61.36
HP ProLiant DL585 G7 AMD Opteron 6176SE 1400.14 48 29.17

 

Here are the links to the specific TPC-E Submissions that I refer to in the table above:

HP ProLiant DL380 G7 Server

HP ProLiant DL580 G7 Server

HP ProLiant DL585 G7

Of course, a new two socket server will have a lower total RAM limit than a new four socket server. For example, a two socket Xeon X5690 would be limited to 288GB of RAM, which is probably enough for most workloads. A two socket server will also have less total I/O capacity than a new four-socket server because it will have fewer PCI-E expansion slots. Still, you can pretty easily get 5-6GB/sec of sequential throughput out of a modern two socket server, which should be plenty for most workloads. Once the Intel 32nm Sandy Bridge-EP Xeon E5-2690 is released in Q1 of 2012, the wisdom of choosing a two-socket Intel based server will be even more clear.

Posted in Microsoft, Processors, SQL Server 2012 | Tagged , | 37 Comments

Eighty Logical Cores of Goodness!

Posted on November 9, 2011 by Glenn Berry

I recently had the opportunity to specify the purchase of a couple of Dell PowerEdge R810 2U servers that will be used as database servers for SQL Server 2008 R2. These two servers have four Intel Xeon E7-4870 (32nm Westmere-EX) processors and 256GB of RAM each.  This model server can hold up to 512GB of RAM using relatively economical 16GB DIMMs, or up to 1TB of RAM with the still outrageously expensive 32GB DIMMs. That is quite a bit of power in a 2U server.

Since each Xeon E7-4870 processor has 10 physical cores, plus hyper-threading, this gives me a total of 80 logical cores visible to Windows Server 2008 R2. You can see this in Task Manager below. With machines like this, it will be very nice to get the improved Task Manager that will be in Windows 8.

image

 

In order to recognize more than 64 logical processors, you must have both Windows Server 2008 R2 and SQL Server 2008 R2 or SQL Server 2012 installed. With either of these combinations, you can have up to 256 logical processors. Microsoft has already announced that Windows 8 will support up to 640 logical processors. Once Windows Server 8 is released, it is extremely likely (although not officially announced) that SQL Server 2012 running on top of Windows Server 8 will also support up to 640 logical cores. Of course, with the new core-based licensing in SQL Server 2012 Enterprise Edition, that would be a pretty expensive proposition.

 

image

 

Looking at CPU-Z, you can see that the Core Speed of Core#0 on Processor #1 is running at 2794.6MHz, which shows that Intel Turbo Boost is enabled and running, since the rated clock speed is only 2.40GHz. In order to get this, you should make sure that you have the Windows Power Plan set to High Performance (instead of the default of Balanced), and that your main BIOS Power Management is set to OS Control. Otherwise, you will have some sort of power management in effect, either from Windows or from the hardware itself. This will cause your processors to throttle back to a slower speed (by reducing the Multiplier) when the processor load is relatively light. It can also cause issues for Fusion-io cards on your PCI-E expansion slots.

When the processors see a spike in load, they will increase the multiplier to increase the clock speed of the cores. The reaction time of this throttle up mechanism is slow enough that it can affect the performance of short-duration OLTP queries. I have seen this both on synthetic benchmarks (like GeekBench) and in the real-world.  The Intel Sandy Bridge processors that I have tested, such as the Core i7-2600K seem to be much less susceptible to this problem.

Depending on what combination of settings you have for your BIOS power management and Windows Power Plan, you may also lose out on the benefit of Turbo Boost, which will “overclock” individual cores based on the overall load, temperature and power usage of each processor. I think power management is a great thing for laptops, desktops, and web servers, but not for mission critical database servers.

Posted in Computer Hardware, Processors, SQL Server 2008 R2, SQL Server 2012 | Tagged , | 1 Comment

Tuesday Hardware Tidbits

Posted on October 24, 2011 by Glenn Berry

There have been a few interesting developments on the hardware front over the past week that I want to cover today. First, Intel has released the new Core i7-2700K processor, which is their new top of the line desktop CPU. It is basically identical to the older Core i7-2600K, except that it has a 100MHz higher base clock speed (3.5GHz), and a 100MHz higher Turbo Boost clock speed (4.0GHz). 

Looking at the table below, you can see how the i7-2700K compares to the upcoming (next month?) Sandy Bridge-E processors. The sweet spot for the Sandy Bridge-E will be the Core i7-3930K, which will have six cores, plus hyper-threading. It has nearly the same clock speed as the i7-3960X, with a slightly smaller L3 cache, at a 40% lower cost.

 

Processor Socket Cores Clock Turbo Cache Price
Core i7-3960X LGA2011 6 3.3GHz 3.9GHz 15MB $999
Core i7-3930K LGA2011 6 3.2GHz 3.8GHz 12MB $583
Core i7-3820 LGA2011 4 3.6GHz 3.9GHz 10MB $294
Core i7-2700K LGA1155 4 3.5GHz 3.9GHz 8MB $332

 

Last week, AMD finally released their long anticipated Bulldozer desktop processor line, led by the FX-8150. Most of the reviews that I have seen for Bulldozer have shown pretty disappointing single-threaded performance for this processor. It has done better on multi-threaded tests, which could be a hopeful sign for database performance, especially for reporting or data warehouse workloads. I cannot wait until someone does some benchmarks against the server equivalent of this processor, which will be the Opteron 4200 and 6200 series.

Finally, there have been a lot of stories about the effects of the recent flooding in Thailand on the hard drive market, with a large percentage of the production capacity of the major hard drive manufacturer’s being under water. This has already had a significant effect on prices, and it will affect availability for quite some time. This might increase the feasibility of SSDs, which is at least a small silver lining.

Posted in Computer Hardware, Processors, Storage Subsystems | Tagged , | 5 Comments

Denver SQL Server User’s Group Meeting on October 20, 2011

Posted on October 20, 2011 by Glenn Berry

The Denver SQL Server User’s Group will be having their monthly meeting tonight at the Microsoft office in the Denver Tech Center. It will be a PASS Summit 2011 Debrief, where a number of members will give their impressions and experiences from the PASS Summit last week in Seattle. This should be a good time, especially since Jason Horner was actively recruiting people who attended the PASS Summit, who were flying home on the 7:05PM Frontier flight last Friday!

Attending your local user’s group meeting is a good thing to get in the habit of doing, since you will get to know other people in the local SQL Server community, learn new things, and get free pizza and swag.

Posted in PASS, Teaching | Tagged | Leave a comment

October 2011 SQL Server 2012 Diagnostic Information Queries

Posted on October 19, 2011 by Glenn Berry

Since Microsoft released the official name for “SQL Server Code Named Denali” last week at the PASS 2011 Summit, it seems like a good time for me to release an updated version of my SQL Server 2012 Diagnostic Information Queries.  This set includes most of the relevant new DMVs that are included in SQL Server 2012. Some of these new DMVs are also in SQL Server 2008 R2 SP1.

Even though SQL Server 2012 is still in a Community Technology Preview (CTP) status, with an announced final release date of “the first half of 2012”, it is not too early to start trying it out.

Posted in Microsoft, SQL Server 2012, SQL Server Denali | Tagged | Leave a comment

October 2011 SQL Server 2008 Diagnostic Information Queries

Posted on October 18, 2011 by Glenn Berry

Since Microsoft has been so busy releasing Cumulative Updates for both SQL Server 2008 and SQL Server 2008 R2 over the past few days, I thought it would be a good time to release a new version of my SQL Server 2008 Diagnostic Information Queries. Most of these queries will work on either SQL Server 2008 or SQL Server 2008 R2, but a few (which are noted in the comments) only work on SQL Server 2008 R2 SP1 or later.

That is yet another reason to read the instructions, and run each query one at a time, rather than running them all in one batch. Most of the queries also require VIEW SERVER STATE permission.

The top half of the queries are instance level, so it does not matter what database you are pointing to when you run them, but the second half of the queries are database specific, which means you don’t want to be pointing at the master database when you run them.

Please let me know what you think of these queries. Thanks!

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

SQL Server 2008 SP3 CU1

Posted on October 18, 2011 by Glenn Berry

Microsoft has been busy getting SQL Server Cumulative Updates released over the past couple of days. The latest release is SQL Server 2008 SP3 CU1, which is Build 10.00.5766.00. This update has 19 listed fixes.

Remember, this Cumulative Update is only for SQL Server 2008 SP3, not for SQL Server 2008 R2.

Posted in Microsoft, SQL Server 2008 | Tagged | Leave a comment