SQL Server 2005 Diagnostic Information Queries (June 2012)

Posted on June 12, 2012 by Glenn Berry

Here is the latest version of my SQL Server 2005 Diagnostic Information Queries, with some minor tweaks and improvements, including query numbering and one new query at the end. To go with it is an updated, blank results spreadsheet, where the tabs in the spreadsheet are labeled and in the same order as the queries.

The idea is that you can run these queries one by one, click on the top left square of the results grid, and then right-click and select “Copy with Headers” to select and copy the results of the query and then paste them into the matching tab of the results spreadsheet. With the SQL Server 2005 version of SQL Server Management Studio (SSMS), you will want to enable the “Copy with Headers” option under Tools, Options,Query Results, SQL Server, Results to Grid in order to be able copy the results with headers

These queries are designed to work on SQL Server 2005, although many of them will also work on newer versions of SQL Server. If you have a newer version of SQL Server you are really better off getting the proper version of these queries. The latest SQL Server 2008 version is here, and the latest SQL Server 2012 version is here.

As always, let me know what you think of these queries.

Posted in SQL Server 2005 | Tagged , | 9 Comments

SQL Server 2012 Diagnostic Information Queries (June 2012)

Posted on June 11, 2012 by Glenn Berry

Here is the latest version of my SQL Server 2012 Diagnostic Information Queries, with some minor tweaks and improvements, including query numbering and one new query at the end. To go with it is an updated, blank results spreadsheet, where the tabs in the spreadsheet are labeled and in the same order as the queries. The idea is that you can run these queries one by one, click on the top left square of the results grid, and then right-click and select “Copy with Headers” to select and copy the results of the query and then paste them into the matching tab of the results spreadsheet.

These queries will work on SQL Server 2012, although some of them will also work on older versions of SQL Server. As always, let me know what you think of these queries.

Posted in SQL Server 2012, SQL Server Denali | Tagged , | 5 Comments

SQL Server 2008 Diagnostic Information Queries (June 2012)

Posted on June 11, 2012 by Glenn Berry

Here is the latest version of my SQL Server 2008 Diagnostic Information Queries, with some minor tweaks and improvements, including query numbering and one new query at the end. To go with it is an updated, blank results spreadsheet, where the tabs in the spreadsheet are labeled and in the same order as the queries. The idea is that you can run these queries one by one, click on the top left square of the results grid, and then right-click and select “Copy with Headers” to select and copy the results of the query and then paste them into the matching tab of the results spreadsheet.

These queries will work on both SQL Server 2008 and SQL Server 2008 R2 (although some of them will only work on SQL Server 2008 R2 SP1 or later). This is indicated in the instructions for each query. Updated versions for SQL Server 2005 and SQL Server 2012 will be available in the next day or so.  As always, let me know what you think of these queries.

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

Analyzing TPC-E Benchmark Performance by Physical Processor Core

Posted on May 23, 2012 by Glenn Berry

The TPC Benchmark E (TPC-E) is an OLTP performance benchmark that was introduced in May, 2007. The TPC-E benchmark is a not a replacement for the old TPC-C benchmark, but rather is a completely new OLTP benchmark. Even though this new benchmark has been available for about five years, there are still no posted results for any other RDBMS besides SQL Server. Whatever the reasons why other database vendors have not allowed the hardware vendors post TPC-E results with their RDBMS, there are certainly many results posted for SQL Server, which makes it a very useful benchmark when assessing SQL Server hardware. At the time of this writing, we are up to 54 published TPC-E results, using SQL Server 2005, 2008, 2008 R2, and SQL Server 2012. This gives you many different systems and configurations to choose from, as you look for a system that is similar to a new system that you want to evaluate.

The TPC-E benchmark is an OLTP, database-centric workload that is meant to reduce the cost and complexity of running the benchmark compared to the older TPC-C benchmark. Unlike TPC-C, the storage media for TPC-E must be fault tolerant (which means no RAID 0 arrays). Overall, the TPC-E benchmark is designed to have reduced I/O requirements compared to the old TPC-C benchmark, which makes it both less expensive and more realistic since the sponsoring hardware vendors will not feel as much pressure to equip their test systems with disproportionately large, expensive disk subsystems in order to get the best test results. The TPC-E benchmark is also more CPU intensive than the old TPC-C benchmark, which means that the results tend to correlate pretty well to CPU performance, as long as the I/O subsystem can drive the workload effectively. My assumption here is that any hardware vendor that takes the time and expense required to submit a TPC-E Benchmark result is going to make sure they have enough I/O capacity to properly drive the workload for the benchmark.

When I am assessing the relative OLTP performance of different processors, I like to take the raw TPC-E tpsE score for a system using that particular processor and divide it by the number of physical cores in the system to get an idea of the relative “per physical core performance”.  When you do this, you should also be aware of how many physical sockets are in the system, since you will not see 1:1 scaling as you add additional sockets, even with NUMA (which is a huge improvement over the old SMP architecture). This means that a four socket system would not have double the performance of a two socket system that was using the exact same processors and components. This is further complicated by the fact that Intel has a different release cycle for their processors that are primarily intended for two socket systems, where newer models become available in the two socket space about 12-18 months before the roughly equivalent four socket processor. I say roughly equivalent, since the two socket models usually have higher base and turbo clock speeds than the four socket models from the same processor generation. A good example is the two socket Intel Xeon X5690 (Westmere-EP) compared to the four socket Intel Xeon E7-4870 (Westmere-EX).  The Intel Xeon X5690 has a significantly higher base clock speed (3.46GHz vs. 2.4GHz) and a higher turbo clock speed (3.73GHz vs. 2.8GHz) compared to the Intel Xeon E7-4870. There are more than clock speed differences to consider here, since the X5690 only has six physical cores, and a 12MB L3 cache, while the E7-4870 has ten physical cores and a 30MB L3 cache.

Keeping all of this in mind, I found some recent TPC-E results for several of the latest processors from both AMD and Intel, and divided the actual raw score by the number of physical cores in the system. I decided to divide by physical cores, since SQL Server 2012 Enterprise Edition is licensed by physical processor core instead of the old familiar per socket licensing that was used in previous versions of SQL Server. This means that people should be focused on getting the most performance and scalability possible from each physical processor core from a hardware selection and licensing perspective. As you can see by looking at the Score/Core column of Table 1, the new Intel Xeon E5-2690 (Sandy Bridge-EP) is the current champion, followed pretty closely by the Intel Xeon X5690 (Westmere-EP). We see a pretty big drop going to the Intel Xeon E7 processors, in a four and eight socket configuration. Finally, the latest AMD processors are bringing up the rear, with quite a gap between them and the Intel offerings. This does not look very good if you are thinking about using AMD processors for an OLTP workload, especially since you will have to pay for more physical cores (up to 16) in an Opteron 6200 series processor.  One factor that helps mitigate these scores and license costs somewhat for AMD is the new SQL Server 2012 Core Factor Table that Microsoft released on April 1, 2012. This table reduces the licensing cost for most new AMD processors that have six or more cores by allowing you to multiply the actual physical core count by a factor of 0.75 for licensing purposes.

 

System Processor tpsE Sockets Total Cores Score/Core
IBM System x360 M4 Intel Xeon E5-2690 1863.23 2 16 116.45
HP Proliant DL380 G7 Intel Xeon X5690 1284.14 2 12 107.01
IBM System x3850 X5 Intel Xeon E7-4870 2862.61 4 40 71.57
NEC Express 5800/A1080a Intel Xeon E7-8870 4614.22 8 80 57.68
HP Proliant DL385 G7 AMD Opteron 6282SE 1232.84 2 32 38.53
HP Proliant DL585 G7 AMD Opteron 6176SE 1400.14 4 48 29.17

Table 1: TPC-E Performance by Physical Core

Posted in AMD, Computer Hardware, Intel, Processors, SQL Server 2008 R2, SQL Server 2012 | Tagged , | 3 Comments

SQL Server 2008 SP3 Cumulative Update 5 Released

Posted on May 21, 2012 by Glenn Berry

Microsoft has released two new Cumulative Updates for SQL Server 2008 today. The first one is SQL Server 2008 SP3 CU5, which is Build 10.00.5785.00. There are only four fixes listed in the public fix list. The second Cumulative Update is SQL Server 2008 SP2 CU10, which is Build 10.00.4332.00. There is only one fix listed in the public fix list for CU10. Remember, both of these are for SQL Server 2008, not for SQL Server 2008 R2.

In order for you to install SQL Server 2008 SP2 Cumulative Update 10, you must have SQL Server 2008 SP2 installed first.  In order for you to install SQL Server 2008 SP3 Cumulative Update 5, you must have SQL Server 2008 SP3 installed first.  When you download a SQL Server Service Pack, you must get the appropriate version of the Service Pack to match what is installed on the machine you want to upgrade. This could be the x86, x64, or ia64 version. If you don’t know what version of SQL Server is running, you can run SELECT @@VERSION; to find out.

As the name implies, a Cumulative Update is actually cumulative, which means that it includes all of the fixes for all of the previous Cumulative Updates for the version and Service Pack of SQL Server.

Posted in Microsoft, SQL Server 2008 | Tagged | 1 Comment

Dell 12th Generation Server Cheat Sheet

Posted on May 15, 2012 by Glenn Berry

Over the past several months, Dell has been rolling out a number of new 12th generation servers that all use the new 32nm Intel Xeon E5 series processor (aka Sandy Bridge-EP). These new servers all have much higher memory density and more PCI-E slots (which are also PCI-E 3.0) compared to the older 11th generation Intel based servers that used the 32nm Intel Xeon 5600 series processor (aka Westmere-EP). These new servers are a huge improvement over the previous models.

Several of these new server models were just announced this week, and are not yet available for sale, while several others have been available for a couple of months now. The new models include three entry level servers (R320, R420, and R520) that use the new 32nm Intel Xeon E5-2400 series processor, and the new quad-socket PowerEdge R820 that uses the new 32nm Intel Xeon E5-4600 series processor. In case you are not fluent in how to decode Dell server model numbers, the R means rack-mounted, the first numerical digit is an indication of where the model fits in the overall lineup (with entry level models having lower numbers), the second numerical digit is the generation ( 1 means 11th Gen, 2 means 12th Gen), and the final numerical digit tells you whether it is an Intel based model or an AMD based model (0 means Intel, 5 means AMD).

So, if you are not a complete hardware geek like me, here is a handy little cheat sheet that lays out the major differences between these seven models. For memory capacity, I am assuming the use of 16GB DIMMs, since 32GB DIMMs are still extremely expensive, and frankly don’t make economic sense. From a processor choice perspective, each of these model servers are available with Sandy Bridge-EP processors that have either 4, 6, or 8 physical cores. In some cases (E5-2600 and E5-4600 series), you can choose a processor model that has fewer physical cores, but a higher base clock speed. This could make sense if you had an OLTP workload and are worried about the core-based licensing in SQL Server 2012 Enterprise Edition.

Personally, I really like the R720xd model, with up to (26) 2.5” internal drive bays. I suspect that a very high percentage of SQL Server workloads would run extremely well on one of those. If twenty-six internal drives did not give you enough I/O performance and disk space, you could always add some Fusion-io cards and/or use some external DAS enclosures or a SAN. If you can partition your workload across multiple servers, two R720xd servers would be much better than one R820 server, since you would have faster, less expensive processors, over three times as many internal drive bays, and nearly twice as many PCI-E expansion slots for things like RAID controllers or HBAs.

As a DBA, I would be actively lobbying against using the R420 or R520 models, since they use the lower-end E5-2400 series processors, which have lower clock speeds and less memory bandwidth compared to the E5-2600 series used in the R620, R720, and R720xd. They also have have half the memory capacity and fewer PCI-E slots. They are less expensive, but the hardware cost delta is pretty small compared to the SQL Server license costs, especially for SQL Server 2012. Remember, you are paying based on physical core counts, so you want to get the best package you can as far as the rest of the server goes. I can see where the R320 could be a good choice for a smaller workload, where you can still get 96GB of RAM in a one socket server. Don’t forget that SQL Server 2008 R2 and 2012 Standard Edition are limited to using 64GB of RAM.

 

Dell PowerEdge R320

1U form factor, one CPU socket, uses Intel Xeon E5-2400 series, 6 memory slots (96GB RAM),  (8) 2.5” drive bays, (1) x8 and (1) x16 PCI-E 3.0 expansion slots

Total of 4, 6, or 8 physical cores for SQL Server 2012 Enterprise Edition licensing purposes.  Total of 8, 12, or 16 logical cores with HT enabled.

 

Dell PowerEdge R420

1U form factor, two CPU sockets, uses Intel Xeon E5-2400 series, 12 memory slots (192GB RAM), (8) 2.5” drive bays,  (2) x16 PCI-E 3.0 expansion slots

Total of 8, 12, or 16 physical cores for SQL Server 2012 Enterprise Edition licensing purposes. Total of 16, 24, or 32 logical cores with HT enabled.

 

Dell PowerEdge R520

2U form factor, two CPU sockets, uses Intel Xeon E5-2400 series, 12 memory slots (192GB RAM), (8) 3.5” drive bays, (3) x8 and (1) x16 PCI-E 3.0 expansion slots

Total of 8, 12, or 16 physical cores for SQL Server 2012 Enterprise Edition licensing purposes. Total of 16, 24, or 32 logical cores with HT enabled.

 

Dell PowerEdge R620

1U form factor, two CPU sockets, uses Intel Xeon E5-2600 series, 24 memory slots (384GB RAM), (10) 2.5” drive bays, (1) x8 and (2) x16 PCI-E 3.0 expansion slots

Total of 8, 12, or 16 physical cores for SQL Server 2012 Enterprise Edition licensing purposes. Total of 16, 24, or 32 logical cores with HT enabled

 

Dell PowerEdge R720

2U form factor, two CPU sockets, uses Intel Xeon E5-2600 series, 24 memory slots (384GB RAM), (16) 2.5” drive bays, (6) x8 and (1) x16 PCI-E 3.0 expansion slots

Total of 8, 12, or 16 physical cores for SQL Server 2012 Enterprise Edition licensing purposes. Total of 16, 24, or 32 logical cores with HT enabled

 

Dell PowerEdge R720xd

2U form factor, two CPU sockets, uses Intel Xeon E5-2600 series, 24 memory slots (384GB RAM), (26) 2.5” drive bays, (4) x8 and (2) x16 PCI-E 3.0 expansion slots

Total of 8, 12, or 16 physical cores for SQL Server 2012 Enterprise Edition licensing purposes. Total of 16, 24, or 32 logical cores with HT enabled

 

Dell PowerEdge R820

2U form factor, four CPU sockets, uses Intel Xeon E5-4600 series, 48 memory slots (768GB RAM), (16) 2.5” drive bays, (5) x8 and (2) x16 PCI-E 3.0 expansion slots

Total of 16, 24, or 32 physical cores for SQL Server 2012 Enterprise Edition licensing purposes. Total of 32, 48, or 64 logical cores with HT enabled

Posted in Computer Hardware, Dell, Processors, SQL Server 2012 | Tagged | 10 Comments

Speaking at Rocky Mountain Tech Trifecta 2012 on May 19

Posted on May 10, 2012 by Glenn Berry

I will be presenting two sessions at the Rocky Mountain Tech Trifecta in Denver on May 19, 2012. This is an all-day, free event that is very similar to a SQLSaturday or a CodeCamp. There are a large number of great speakers at the event (which is not SQL Server only), which is being held on the Auraria Campus in downtown Denver. I will be doing DMV Emergency Room! and Hardware 301: Diving Deeper into Database Hardware. Here are the abstracts:

 

DMV Emergency Room!

If you have ever been responsible for a mission critical database, you have probably been faced with a high stress, emergency situation where a database issue is causing unacceptable application performance, resulting in angry users and hovering managers and executives. If this hasn’t happened to you yet, thank your lucky stars, but start getting prepared for your time in the hot seat. This session will show you how to use DMV queries to quickly detect and diagnose the problem, starting at the server and instance level, and then progressing down to the database and object level. Based on the initial assessment of the problem, different types of DMV queries will help you narrow down and identify the problem. This session will show you how to assemble and use an emergency DMV toolkit that you can use to save the day the next time a sick database shows up on your watch in the Database ER!

 

Hardware 301: Diving Deeper into Database Hardware

Making the right hardware selection decisions is extremely important for database scalability. Having properly sized and configured hardware can both increase application performance and reduce capital expenses dramatically. Unfortunately, there are so many different choices and options available when it comes to selecting hardware and storage subsystems, it is very easy to make bad choices based on outmoded conventional wisdom. This session will give you a framework for how to pick the right hardware and storage subsystem for your workload type. You will learn how to evaluate and compare key hardware components, such as processors, chipsets, and memory. You will also learn how to evaluate and compare different types of storage subsystems for different database workload types. This session will give you the knowledge you need to make sure you get the best performance and scalability possible from your hardware budget!

I have spoken at the Tech Trifecta several times in the past, and it has always been a good time!

Posted in Computer Hardware, Microsoft, Teaching | Tagged | Leave a comment

The Fantastic 12 of SQL Server 2012

Posted on May 7, 2012 by Glenn Berry

The SQL Server Team Blog is in the midst of posting twelve, short weekly videos that let someone from the SQL Server Product team talk about their favorite features and improvements in SQL Server 2012. These videos go up every Thursday, continuing through the end of June. The first eight episodes are linked below:

The Fantastic 12 of 2012: Behind the Scenes Look into Required 9s and Data Protection

The Fantastic 12 of 2012: A Behind the Scenes View of Blazing Fast Performance

Fantastic 12 of 2012: Behind the Scenes of Organizational Security and Compliance

The Fantastic 12 of 2012: Behind the Scenes – Ensuring Peace of Mind

The Fantastic 12 of 2012: Behind the Scenes of Managed Self-Service BI

The Fantastic 12 of 2012: Behind the Scenes of Credible, Consistent Data

The Fantastic 12 of 2012: Behind the Scenes of Big Data Analytics and Data Warehousing

The Fantastic 12 of 2012: Behind the Scenes of Developing Scale on Demand Capabilities

 

 

You can also enter their Twitter contest, for a chance to win a SQL Server 2012 T-Shirt.

Posted in Microsoft, SQL Server, SQL Server 2012, SQL Server Denali | Tagged | 1 Comment

Updated SQL Rockstar Blogger Rankings

Posted on May 7, 2012 by Glenn Berry

This morning, Thomas LaRock (blog |@SQLRockstar) released an updated version of his Blogger Rankings. I was very happy and humbled to see that I had been promoted to the ResourceDB group, which I really appreciate!  My co-worker, Joe Sack (blog | @josephsack) has been promoted from msdb to model, while Paul White (blog | @sql_kiwi) has been promoted from tempdb to model, and Ted Krueger (blog | @onpnt) has been promoted from tempdb to msdb.

Especially if you are new to the SQL Server Community, Tom’s Blogger Rankings list is a good place to find quality blogs to subscribe to and read, and SQL Server people to follow on Twitter. There is a very active and vibrant online community of people in the SQL Server world, that are a very valuable resource if you are working with SQL Server. You are not alone in your cubicle, with only Bing and Google to help you!

Posted in Blogging, SQL Server, Teaching | Tagged | Leave a comment

SQL Server 2005 Diagnostic Information Queries (May 2012)

Posted on May 3, 2012 by Glenn Berry

Even though SQL Server 2005 fell out of Mainstream Support back in April of 2011, I know many people are still using it and will be for some time to come. Because of this, I decided to release an updated version of my diagnostic queries for SQL Server 2005. The last time I released a version for SQL Server 2005 was back in March of 2011. You can get the new version from the following link: SQL Server 2005 Diagnostic Information Queries (May 2012).

I also wanted to thank everyone on Twitter who beta tested these scripts for me. Any remaining issues are totally my responsibility!  Please tell me what you think of this version.

Posted in SQL Server 2005 | Tagged , | 4 Comments