The boss is yelling. Users are grumpy. SQL Server’s performance is tanking and that’s making line-of-business applications slow. And it’s your problem because you’re the “Microsoft person” in your organization.
There are three straightforward things you can do to improve SQL Server performance. 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” database administrator to take care of these things.
None involves software changes, meaning you can do each 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 worth considering.
Upgrade SQL Server. 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. Then 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.
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 examine your query traffic and the existing indexes that are helping to execute those queries and suggest index modifications that can improve performance. Modifying indexes is generally safe, even if you can’t alter any other parts of an application or database, but 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; that way, you can re-create those same indexes without affecting the underlying data. That’s an important lesson: Indexes don’t actually do anything to the data in your database. Like a phonebook, they just point to information. Changing indexes will always have some impact on performance, but those changes won’t affect the underlying data in the database.
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.
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 applications, move 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 farther by offering long-term performance trend analysis, the ability to automatically implement fixes and other features.
A dawdling database is certainly a pain in the neck, but it’s not unsolvable. Use these tips and you’ll improve SQL Server performance in no time.
ABOUT THE AUTHOR
Don Jones is a co-founder of Concentrated Technology LLC and the author of more than 30 books on IT and a speaker at technical conferences worldwide.
Miss a tip? Check out the archive page for Jones’ series, "SQL Server for the Reluctant DBA.”
This was first published in January 2012