Like This Blog 0

Added by Don Jones May 3, 2013

The boss is yelling. Users are grumpy. SQL Server’s performance is tanking, and it’s making all of your organization’s line-of-business applications slow. And it’s your problem, just because you’re the “Microsoft person” in your environment.

What’s a “reluctant DBA” to do?

There are three straightforward things you can do to improve performance on almost any SQL Server. Some of these involve some compromises, so you’ll need to think about what’s more important. Others may involve some expenditure, but you can always remind the boss how much the company is saving by not hiring a “real” DBA to take care of these things. None of these things involve software changes, meaning you can do them without worrying about breaking your applications. For that reason, some of these approaches might be less-than-optimal, meaning they’re just hiding the real cause of the performance problem. But if you’re in a hurry, or have no ability to modify the software, then they’re all worth considering.

1. Upgrade. It’s really rare to run into a SQL Server performance problem that can’t be at least mitigated by bigger hardware. Spend some time looking at Windows’ and SQL Server’s performance counters as a starting point. You’ll need to determine where your most immediate problem is: Memory, disk throughput, or processor. Be aware that one can sometimes disguise another: A busy disk subsystem may be covering for insufficient memory by using the Windows page file, for example. Checking a few additional counters to examine swap file activity is a good way to eliminate or confirm that possibility.
    
2. Run the Database Engine Tuning Advisor, Index Tuning Wizard, or whatever it’s called in your version of SQL Server. Ideally, feed it some real-world query traffic that you’ve first captured using SQL Server Profiler. These tuning tools take a look at your query traffic, examine the existing indexes that are helping to execute those queries, and suggest index modifications that can improve performance. Modifying indexes is generally safe to do, even if you can’t modify any other parts of an application or database, but of course you should always take a full backup of SQL Server just in case you need to “roll back” your changes. You can also carefully document the existing indexes before making any changes, enabling you to re-create those same indexes without affecting the underlying data. That’s an important lesson: Indexes don’t impact the actual data in your database. Like a phone book, they just point to that data. Changing indexes will always have some impact on performance, but those changes won’t ever impact the underlying data in the database.
    
3. Perform some maintenance. Sometimes, performance can be significantly improved just by doing some basic maintenance on the database. Back it up first, of course, but then consider defragmenting the database, rebuilding the database’s indexes, and even temporarily shutting down SQL Server and letting Windows defragment the disk drives. All of these tasks get your data “lined up” so that SQL Server can read and write more effectively.
    
4. Here’s a bonus tip: Give SQL Server some breathing room. Specifically, look for other, unnecessary services on the server and shut them down (after checking to make sure they’re not used, of course). If SQL Server is sharing a machine with other critical services, migrate it to its own server.

I’m also a big fan of third-party SQL Server performance management tools. These often incorporate some of the functionality of the native tuning wizards, but usually go further by offering long-term performance trend analysis, the ability to automatically implement fixes, and other conveniences. Use your favorite search engine to look for “sql server performance tool” and you should turn up several useful possibilities.

Poor SQL Server performance is certainly a pain in the neck, but it’s not an unsolvable problem. Use these tips to start making a dent in your performance problems, and you’ll have SQL Server humming along in no time.

Don Jones
PowerShell and SQL Instructor – Interface Technical Training
Phoenix, AZ