6 Little Known Things That Can Cause Big Performance Issues

As a system admin, DBA, or a developer it is our job to root out performance problems quickly. The trouble is that there are so many things that can cause performance issues it can be difficult to know every possible root cause. This is especially true when the root cause lies somewhere outside your area of expertise or control.

It is also true when the root cause is right under our noses the whole time.

Here are six things that I have seen cause performance issues for end users. None of them are complex by nature. Each is something that is simple and easily addressed when they are known.

So that’s why I wrote this post, in an effort to get these into the mainstream. If you are not taking steps to to mitigate these possible performance issues, start now. If you find yourself one day sitting at your screen and scratching your head wondering “what’s wrong”, then take a chance that one of these six things could be the culprit.

1. Configure your memory settings

I see this scenario all the time: Customer notices they are low on memory. They call me. I ask if they have configure the max memory setting to a non-default value. They say no.

Lather, rinse, repeat. I used to count the number of times this would happen to me but I stopped after the first hundred or so. It is one of the reasons I put together a talk on SQL Server memory management last year, because it just seemed to be the easiest thing for people to be doing and yet they weren’t doing it.

I wrote a post a while back to help explain this fact as well. And then there is this post by Glenn Berry (blog | @GlennAlanBerry) and one done by Jonathan Kehayias (blog | @SQLPoolboy), and yet still people don’t know to configure these settings. Take the few minutes you need to adjust your settings and you will reduce the chances of having performance problems due to memory settings.

2. Optimize tempdb

This one is often overlooked as most folks wait around for a problem with tempdb before thinking about how best to optimize it for performance right from the start. This SQLCAT link mentions this as well, and there is info over at MSDN that you should review before making changes. And yes, you should consider moving tempdb to it’s own dedicated set of drives.

In fact, I would tell you that the placing of data files, log files, backup files, and tempdb onto separate disks should be a part of any standard database server build. I emphasize the ‘should’ because for some odd reason I still see shops where this is not true. If that is the case for you, and you need to boost performance, then roll up your sleeves and get started on the work that should have been done from the onset.

3. Increase transaction log throughput

Another area that will affect your database performance has to do with the configuration of your transaction logs. Kimberly Tripp (blog | @KimberlyLTripp) has a great post on the 8 Steps to Better Transaction Log Throughput. Many folks don’t think about how something as simple as managing log file growth properly can help with overall performance.

One of the hidden parts of transaction logs has to do with virtual log files (VLFs), the “chunks” that are strung together to form the transaction log. I wrote about VLFs a while back when I witnessed firsthand the havoc they could cause. Until I knew about VLFs I had no idea why my log files were “misbehaving”. After I found out about them I decided to come up with a way to identify databases that had too many VLFs and were causing us issues (both for performance and administrative activities) and took the necessary corrective actions. Those actions also led to a review of overall transaction log throughput, as I wanted to make certain we were deploying servers to our end users that were configured for performance right from the start because it is a real pain to go back after the fact and say things like “Oh, sorry, I didn’t realize you wanted that server to perform well, so we just gave you all the default stuff which is usually crappy, we can go back and fix all of that for you and have things ready in about two months.”

Save yourself that headache. Take care of your transaction logs from the start. You can thank me later.

4. Build a proper reporting database

I see a lot of “mixed-use” databases in every type of industry. At some point someone gets the idea that they need a database in order to store information, usually customer information at first, then things like products, inventory, or sales. You know, standard business stuff. Most of those systems are built along guidelines for typical transactional based processing. After some time, perhaps months or years, other people in the office think to themselves “hey, I need some info, and don’t we have it already in that database”? So they go about building some reports.

And before you can say “writers block readers” you soon have yourself an OLTP system that is being used more often for OLAP purposes. I used to have a job that would alert me whenever a spid was blocked for 300 seconds. That’s five minutes. Of doing nothing but waiting for someone else. When I would call the person and tell them something like “hey, you stuff is blocked and just sitting there, and it is Joe doing the blocking” I would get responses that would range from “OK, his stuff needs to run, so I can wait” to “OMG WTF IS JOE DOING IN MY DATABASE RIGHT NOW WHILE I AM TRYING TO FINISH MY WORK!”

Building a proper reporting solution can help you improve performance for everyone, and it is often never talked about. Usually people focus on trying to coexist when they should really talk about signing the divorce papers.

5. Choosing the correct datatypes

More often than not, database performance (good and bad) can be traced all the way back to the design phase. During that phase there is (or, there should) be a discussion around the choice of proper data types for the underlying data. Unfortunately I see that such discussions typically don’t happen, thanks to tools like Visual Studio that will create tables with columns that are nvarchar(50) by default.

Most of the time I try to talk to customer about the selection of data types and the impact on performance I get back one of two responses: “I didn’t know that” or “It’s vendor code, we can’t touch it”.  Clearly we need to raise awareness in either case. Also, we need to stop blaming the database for the problems that have been created at design time. The database is only doing what you have asked, and you have likely asked it to work inefficiently and slowly. Check out this article by Greg Larsen regarding performance and data types. And here is a list of bad habits to kick by Aaron Bertrand (blog | @aaronbertrand).

6. Configure Your Power Settings

I know that “being green” is all in vogue these days. As a result we have things like power savings options for servers that allow for the CPU to be throttled when not in use. The net result of this is that you get the chance to explain to your end users that while database performance is not very good they can feel better knowing that they are helping to keep polar bears colder at night. Then again, you can only make such a comment to them if you are aware about these power savings options. Since most people have no idea about these power settings they are likely to spend many fruitless hours trying to track down the root cause of performance issues. In an incredible twist of irony, the extra power they consume while trying to research the issue brought about by power saving results in those polar bears needing more and more suntan lotion with each passing year.

The above six items are all ways that you could be suffering from poor database performance. The problem I see with many DBAs is that they try to fix the one thing that is on their plate at the moment, without seeing a bigger picture. Similar to saying “you can’t see the forest through the trees”, many DBAs are so focused on getting one query at a time tuned that they never bother to look up and see things like memory settings, or transaction log configurations, or optimizing tempdb for performance as ways to improve performance. Rarely will I ever see a discussion about changing data types or building a proper report solution and yet those are also ways your performance could be slowly draining away.

These are all items that can be addressed early on in any project, or even a rollout of a new instance of SQL Server. It doesn’t take much time and the benefits are worth every minute.