Three New TPC-E Submissions

Posted on June 15, 2011 by Glenn Berry

I just noticed three very recent new TPC-E Benchmark Submissions that I thought were quite interesting. As I have written before, I think the TPC-E OLTP benchmark is a very useful tool for helping to make hardware selection and sizing decisions. It is especially relevant for SQL Server, since all of the submitted results are for SQL Server. For whatever reason, nobody has submitted any Oracle or DB2 results, even though the TPC-E benchmark has been out for about four years.

The first benchmark is for a Futjitsu Primergy RX900 S2 system that comes in with a score of 4555.54 tpsE. This is for an eight socket server, with the 10-core, 2.4GHz Intel Xeon E7-8870 processor. With hyper-threading enabled, this gives you 160 logical processors, along with 2TB of RAM. This is the highest TPC-E score that has been submitted so far. While this is an impressive score, it is not four times the score of the two socket IBM system listed next.

The second benchmark is for an IBM System x3690 X5 system that has a score 0f 1,560.70 tpsE. This is for a two-socket server, with the 10-core, 2.4GHz Intel Xeon E7-2870 processor. With hyper-threading enabled, this gives you 40 logical processors, along with 512GB of RAM. This is essentially the same processor used in the eight socket Futjitsu system above, except that Intel has different SKUs for different socket capacities, (E7-2800 series, E7-4800 series, and E7-8800 series).

The third benchmark is for a HP ProLiant DL380 G7 Server that has a score of 1,284.14 tpsE. This is for a two-socket server, with the six-core, 3.46GHz Intel Xeon X5690 processor. With hyper-threading enabled, this gives you 24 logical processors, along with 192GB of RAM. This system uses the 32nm Westmere-EP, which still gives the best single-threaded OLTP performance due its higher clock speed (on the same basic Westmere architecture).

The table below shows the tpsE score per socket, and tpsE score per logical core for these three systems. It indicates that we don’t see linear scaling as we move from a two-socket system to an eight-socket system, using the new Westmere-EX, Xeon E7 series. It also shows better single-threaded performance for the Westmere-EP Xeon 5600 series (with a higher tpsE per logical core.

System tpsE Score tpsE/Socket tpsE/Logical Core
Fujitsu 4555.54 569.44 28.47
IBM 1560.70 780.35 39.01
HP 1284.14 642.07 53.50
Posted in Computer Hardware, Microsoft, SQL Server 2008 R2, Windows Server 2008 R2 | Tagged | Leave a comment

Moving On To New Challenges

Posted on June 12, 2011 by Glenn Berry

After nearly five and half years, I left NewsGator Technologies for a new position as a Database Architect at Avalara, in Bainbridge Island, Washington. My last day at NewsGator was June 3, 2011, and I started at Avalara on June 6, 2011.

NewsGator was a great experience for me over the past five plus years. I made some good friends, and I learned a lot about SQL Server. I am sure I would never have become a Microsoft SQL Server MVP if not for the experience I learned at NewsGator. As I told the founder of NewsGator, Greg Reinacker at my going away party, the fact that he usually said no when I wanted more and bigger database servers, forced me to learn other ways to improve scalability and performance besides just throwing hardware at the problem.

At some point, I will probably do a series of blog posts about the technical challenges we faced at NewsGator as we went through a period of very rapid growth in our database workload. It was an interesting journey, as we moved from 32-bit SQL Server 2000, running on a Dell PowerEdge 1850, all the way to SQL Server 2008 R2, running on slightly better hardware.

I am going to be staying in Parker, and working remotely, at least initially. I am staying on Bainbridge Island, at the Island Country Inn, for an initial three week period (which is a long business trip). Two more weeks, and I will be back in Parker. The crew at Avalara has been very helpful and welcoming. Steven Dunston wrote a very nice blog post about me here.

Posted in NewsGator | Tagged | 12 Comments

June 2011 Version of SQL Server 2008 Diagnostic Queries

Posted on June 2, 2011 by Glenn Berry

Since it is June, I thought it was time to update my SQL Server 2008 (and 2008 R2) Diagnostic Information Queries. This month has one new query, which rolls up total CPU usage by database for the current SQL Server instance. This can be useful if you are seeing signs of CPU pressure at the instance level, but are not sure which database is causing it. It is adapted from a query originally written by fellow SQL Server MVP Robert Pearl (web| Twitter).

As always, I strongly suggest that you run each query one at a time, after reading the comments and explanations that go with it. I also recommend that you copy and paste the results from the query results grid into the matching results spreadsheet. If you do that, it makes it easier to save the results over time, so you can better identify issues and trends.

Finally, whenever I update the SQL Server 2008 queries, I inevitably get a number of questions about a version for SQL Server 2005. Since SQL Server 2005 fell out of Mainstream support from Microsoft in April of 2011, I am not spending as much time keeping those queries updated. The latest version for SQL Server 2005 is here.

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

PASS Announces Pre-conference and Spotlight Sessions for PASS 2011 Summit

Posted on June 1, 2011 by Glenn Berry

Today, PASS announced the Pre-conference and Spotlight Sessions for the PASS Summit 2011 in Seattle, which will run from October 11-14, 2011. I actually found out last week that I was lucky enough to be selected for a 90 minute Spotlight Session.

I am still amazed that I am on that list of well-known speakers for the Spotlight Sessions for the PASS Summit 2011. Every person on that list is someone that I deeply respect and look up to. This year’s PASS Summit is going to be another great event, with lots of incredible content!

Here is my Spotlight Session:

Hardware 301: Diving Deeper into Database Hardware
Enterprise Database Administration and Deployment
Glenn Berry (NewsGator Technologies)

Here is my abstract:

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!

PASS_2011_SpeakingButton_Grey 250x250

Posted in PASS, SQL Server 2008 R2, SQL Server Denali, Teaching | 1 Comment

Presentation Deck For Hardware 201 at Denver SQL Server User’s Group on May 19, 2011

Posted on May 19, 2011 by Glenn Berry

I got the chance to give an updated version of Hardware 201: Selecting and Sizing Database Hardware for OLTP Performance to an audience of about forty people at the Denver SQL Server User’s Group on May 19, 2011, which was a lot of fun. You can download the presentation here.

If you want even more information on this subject, you might consider buying a copy of my upcoming book from Amazon!

 

Posted in Computer Hardware, PASS, Teaching | Tagged | 7 Comments

SQL Server 2008 SP1 CU14 and SQL Server 2008 SP2 CU4 Released

Posted on May 17, 2011 by Glenn Berry

Microsoft has released two new Cumulative Updates for SQL Server 2008 SP1 and SQL Server 2008 SP2. The first one is SQL Server 2008 SP1 Cumulative Update 14, which is Build 2821. This CU has only ten fixes.

The second one is SQL Server 2008 SP2 Cumulative Update 4, which is Build 4285. This CU has 21 fixes. Remember, neither one of these has anything to do with SQL Server 2008 R2, which is a completely different major release (at least according to Microsoft).

Personally, I try to stay current with Cumulative Updates as much as possible, rather than waiting for SQL Server Service Packs, which are released too infrequently for my taste. I like to have a routine where I regularly service my SQL Server instances in terms of Windows Updates, SQL Server Cumulative Updates or Service Packs, and hardware updates (such as firmware updates, BIOS updates, and driver updates).

With some careful planning, you can combine all of this activity into a regular maintenance window. If you have a high availability solution in place, such as fail-over clustering, database mirroring, or AlwaysOn technology (in SQL Server Denali), you can do “rolling updates” to minimize your downtime when doing this type of maintenance. This also lets you practice and validate your failover procedures. Its better to find out you have problems with failovers during a maintenance window than to find out in an emergency.

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

Presenting at Denver SQL Server User’s Group on May 19

Posted on May 17, 2011 by Glenn Berry

I will be giving the main presentation at this month’s meeting of the Denver SQL Server User’s Group on May 19, 2011. Here is the abstract:

“Hardware 201: Selecting and Sizing Database Hardware for OLTP Performance”

The foundation of database performance is the underlying server hardware and storage subsystem. Even the best designed and optimized database application can be crippled by an inadequate hardware and storage infrastructure. Recent advances in new processors and chipsets, along with improvements in magnetic and SSD storage have dramatically changed the evaluation and selection process compared to the past. Many database professionals struggle to keep up with new technology and often simply let someone else make their hardware selection and sizing decisions. Unfortunately, the DBA usually gets the blame for any performance issues that crop up later. Don’t let this happen to you! This session covers current and upcoming hardware from both Intel and AMD and gives you the tools and resources to make better hardware selection decisions to support SQL Server OLTP workloads.

The warm-up presentation will be from Jason Horner (Blog | Twitter). Jason’s abstract is below:

“Isn’t That Spatial?”

Isn’t that Spatial” is a 30 minute whirlwind tour of the spatial features introduced in SQL Server 2008. We will cover the basics of working with spatial data including: importing, querying, and visualizing. As time permits, we will dive into some best practices for working with spatial data and ways to improve spatial query performance.

This meeting should be a lot of fun, and I hope to see you there!

Posted in Computer Hardware, PASS, Teaching | Leave a comment

May 2011 SQL Server 2008 Diagnostic Information Queries

Posted on May 16, 2011 by Glenn Berry

Since we are in the middle of May 2011, I thought was was time to update my SQL Server 2008 Diagnostic Information Queries. These are very similar to the April 2011 version except for one new query at the end of the set.

You can click on the link above to download the queries from Dropbox. What I always recommend is that you run each query separately, after you read the instructions and comments on how to interpret the results. The first half of the queries focus on your SQL Server instance, while the second half focuses on a specific SQL Server database on that instance. That means you want to make sure you are in the context of the right database when you run the database specific queries (not master).

This set of queries will work on both SQL Server 2008 and SQL Server 2008 R2. There is a separate set of queries available for SQL Server 2005, and a separate set available for SQL Server Denali.

I also recommend that you download the matching query results spreadsheet, so that you can copy and paste the results for each query into it, for later analysis. This makes the results much easier to interpret, and lets you keep a record of changes over time.

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

PASS Summit 2011 Session Preference Survey

Posted on May 11, 2011 by Glenn Berry

PASS has added a new feature to the session selection process for the PASS Summit 2011, which they are calling the PASS Summit 2011 Session Preference Survey. This allows you to mark multiple proposed sessions for the PASS Summit 2011 as “Preferred”.  This gives you the opportunity to have some input on which sessions are selected for the Summit. You have until midnight Pacific time on May 20th to make your selections.

Now, I have to make my shameless plug for my proposed sessions! Below, I have listed my four session abstracts. If you are interested in seeing any of these sessions, I would really appreciate it if you would take the time to mark them as preferred sessions.

DMV Emergency Room! [300]
Session Category: Regular Session (75 minutes)
Session Track: Enterprise Database Administration and Deployment
Speaker(s): Glenn Berry
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 [300]
Session Category: Spotlight Session (90 minutes, Invitation only)
Session Track: Enterprise Database Administration and Deployment
Speaker(s): Glenn Berry
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!

Scaling SQL Server [300]
Session Category: 1/2 Day Sessions (3.5 hours)
Session Track: Enterprise Database Administration and Deployment
Speaker(s): Glenn Berry
How can you scale SQL Server? Scaling up is relatively easy (but can be expensive), while scaling out requires significant engineering time and effort. If you suggest hardware upgrades you may be accused of simply “throwing hardware at the problem”, and if you try to scale out, you may be thwarted by a lack of development resources or 3rd party software restrictions. As your database server nears its load capacity, what can you do? This session gives you concrete, practical advice on how to deal with this situation. Starting with your present workload, configuration and hardware, we will explore how to find and alleviate bottlenecks, whether they are workload related, configuration related, or hardware related. Next, we will cover how you can decide whether you should scale up or scale out your data tier. Once that decision is made, you will learn how to scale up properly, with nearly zero down-time. If you decide to scale out, you will learn about practical, production-ready techniques such as vertical partitioning, horizontal partitioning, and data dependent routing. We will also cover how to use middle-tier caching and other application techniques to increase your overall scalability.

Using Data Compression and Backup Compression to Improve Performance [200]
Session Category: Regular Session (75 minutes)
Session Track: Enterprise Database Administration and Deployment
Speaker(s): Glenn Berry
Native Data Compression and Native Backup Compression are two very useful features that were added with SQL Server 2008 Enterprise Edition. Both of these features were improved in SQL Server 2008 R2, yet many database professionals are not familiar with how to use them effectively to reduce I/O requirements and improve overall performance. Modern multi-core processors make various forms of compression a much easier choice in many situations. Knowing how to determine which indexes are good candidates for data compression is extremely important if you want to get the best results. This session will give you real-world, practical examples and guidelines for how to take advantage of both data compression and backup compression.

Posted in PASS, SQL Server 2008 R2, Teaching | Tagged | Leave a comment

Speaking at Colorado Springs SQL Server User’s Group on June 15

Posted on May 9, 2011 by Glenn Berry

I will be speaking at the Colorado Springs SQL Server User’s Group, live and in person, on Wednesday, June 15, 2011.  I will be giving an updated version of my Hardware 201: Selecting and Sizing Database Hardware for OLTP Performance presentation. The meeting will be at the Mr. Biggs Fun Center, which is located here.

Posted in PASS, Teaching | Tagged | Leave a comment