T-SQL Tuesday #152 – Database Rants

Introduction

This month’s T-SQL Tuesday is being hosted by Deborah Melkin (blog|twitter). The subject is Database Rants. As Deborah describes it, “I came to a realization lately that I have a few opinions about databases. And I’m pretty sure that you do too“. This post will be my contribution for T-SQL Tuesday #152 – Database Rants.

This is a very wide subject area, that gives everyone plenty of possibilities for blog topics. Being more focused on one narrow topic can be bad, since it may discourage some people from writing a post. Just writing a useful blog post and getting quality links back and forth from other good blogs in the SQL Server community is good for everyone who participates!

BTW, reading and then commenting on the T-SQL Tuesday blog posts of other participants helps both you and the other blogger. They get views and comments on their blog, and you get backlinks to your blog. T-SQL Tuesday also helps encourage people to blog more often, which is good.

In my case, I am going to talk about some of my thoughts about Database Rants.

T-SQL Tuesday #152 – Database Rants

This will be two rants for the price of one! The first rant is when DBAs (and other SysAdmins) don’t know the basic hardware details about their most mission critical database server(s). My second rant is when everyone else in your organization automatically blames the database (or database server) for any and all performance problems. Remember, DBA actually stands for “Default Blame Acceptor”.

Hardware, What Is That?

This is rant #1. Even though I have an unusual fascination with computer hardware, I am still somewhat taken aback when I encounter DBAs who have absolutely no idea what type of hardware they are using. I’ll sometimes ask a DBA “What processor does your most important database server have?”, and I often get a “deer in headlights” look in response. Then a mumbled response, “I’m not sure, maybe a Xeon?”.

In my opinion, that is a less than satisfactory answer from a DBA. As a data professional, you really should at least know the make and model number of the processor(s) in your most important database server. Maybe you don’t have it memorized, but at least written down or readily available.

The reason why this is important is because your processor has a huge effect on the performance and scalability of your database server. It controls the speed and bandwidth of your memory and the PCIe generation of your storage. Your exact processor also is directly related to your SQL Server core licensing costs.

Absent other performance bottlenecks, the single-threaded CPU performance of your processor(s) has a direct effect on your query performance. Knowing whether you have old and slow (or new and slow) processors is very important for a DBA.

This information will be even more important with SQL Server 2022, which will leverage even more processor-specific instructions when they are available with your processor.

Luckily, there are many easy ways to get the exact make and model of the processor in your database server. Here are a few:

• Windows Task Manager
• msinfo32.exe (System Information)
• CPU-Z
• A query from my SQL Server Diagnostic Information Queries

Once you know the make and model number of your processor, you can use your favorite search engine to get its age and complete specifications. Then you can start doing further research to determine whether it is a good processor for SQL Server or not.

What’s Wrong with the Database?

This is rant #2. It will probably be familiar to most DBAs. Whenever there is any sort of performance issue with a web site or application, the immediate assumption is that it is a database problem. The database is guilty until proven innocent.

Often, application performance problems are caused by database issues, but this is not always the case. Constantly being on the defensive, at least initially, is tiresome and annoying. That is why I eventually developed my SQL Server Diagnostic Information Queries. I wanted to have a way to quickly determine whether a database server (and the databases on the server) were having performance issues or not.

The Birth of the Diagnostic Information Queries

When I was working at NewsGator Technologies in early 2006, I had an experience that led directly to my later creating my SQL Server Diagnostic Information Queries. It was late on a Friday afternoon, and I was making a quick final check of our main database server before leaving the office to go home. To my surprise, I noticed that CPU utilization was 100% for a sustained period, which was very unusual.

A few minutes later, a number of people from Support (and other parts of the company) were next to my desk, wanting to know “what was wrong with the database” because the NewsGator Online RSS Reader was timing out. That was no fun, but that happens when you are a DBA.

We had recently migrated from 32-bit SQL Server 2000 to 64-bit SQL Server 2005, so we had DMVs and DMFs available in the product. Unfortunately, I did not have much of a collection of DMV queries yet, so I did not have the tools to let me easily find out what was happening. There had been no database or application changes made recently.

I was the only DBA at NewsGator, so it fell mainly to me (and a great developer named Jeff Tingley) to try to resolve the issue. Newsgator Online (and all of our RSS applications) was completely down. Many other people in the company wanted to help, but they unfortunately did not have the proper skill set. Our founder, Greg Reinacker actually rolled a whiteboard next to my desk where he wrote “Glenn and Jeff are working on the problem, please leave them alone”.

We had opened a priority support ticket with Microsoft, and we worked with them until we stabilized the situation at about 3AM the next morning. As it turned out, we were a victim of parameter sniffing in a couple of stored procedures. This was pegging the CPU at 100%.

I went home exhausted, feeling terrible, convinced that I had failed and might even be fired for not instantly diagnosing and correcting the issue. Luckily, my bosses at NewsGator felt otherwise. Jeff and I were actually hailed as hero’s who worked all night to solve the issue. We got a round of applause at a company meeting and a cash bonus.

This painful experience made me vow “never again”. I was determined to start putting together my diagnostic queries so that I would never be in that situation again. That is the origin of the Diagnostic Information Queries.

Final Words

I hope that this post has been interesting and useful. I also want to thank Deborah for hosting this month!

If you have any questions about this post, please ask me here in the comments or on Twitter. I am pretty active on Twitter as GlennAlanBerry. Thanks for reading!