More Tips to Optimize SQL Server Performance


Jim certainly knows how to get himself into a predicament at the beach, but at least he’s not wasting his summer away optimizing his SQL Server performance.

Jim’s sitting there waist-deep in sand, working on his suntan and wondering what the poor people are doing, instead of fretting away his precious summertime hours worried about bad indexes, SQL Server performance tuning and rogue queries in the plan cache. Even the fellow about to step on Jim’s lit cigar has it better than the poor DBAs back at the office, hunched over their SQL performance tuning tools and determined to eliminate the last I/O and CPU bottleneck from their environment.

Some guys just know how to spend a summer.

Sure, it’s gratifying when you find the problems and eliminate them, but the work itself is a grind most DBAs would rather avoid. A lot of DBAs would rather build a water weenie, as I described in my previous post.

My colleague Patrick O’Keefe has put together Ten Tips for Optimizing SQL Server Performance, a white paper focused on SQL Server 2008 and 2012. He’s been in the trenches himself and spent plenty of time with DBAs like you, hunting for the things that slow databases down and get users riled up. The paper summarizes the ten things he looks for first when he works on performance tuning in SQL Server. I’ll highlight a couple of his tips in this post.

Tip #5: Configure your SAN

Would you rather spend the summer buried in your SAN, or buried in the sand like Jim? The latter, of course. Then don’t forget to examine your storage area network (SAN) as you’re tuning SQL Server performance. When your databases are running poorly, the settings in your SAN may be part of the problem.

Most organizations think about SANs simply as a way to consolidate storage and simplify management, but SANs can also be a good place to start your SQL Server tuning, if you know what to look for.

To optimize SQL Server on a SAN, don’t store all your files on a single volume there. Consider these guidelines for spreading files around:

  • Store log files on their own volume.
  • Store tempdb on its own volume.
  • Create multiple data files and filegroups in very large databases (VLDBs).
  • Store backups on their own drives.

What’s really going on in your SAN? How can you measure it? Windows disk counters tell you what the OS is seeing, and you can run SQL Server queries for file-level I/O metrics like throughput, wait times and average time per I/O. Once you’ve collected enough data for a solid profile, you can see which files in the SAN consume the most bandwidth. You’ll know whether you’re missing an index or can move an index or table to another file on another volume to improve I/O.

And don’t forget the hardware itself. You can configure allocation unit size and partition alignment to make the disks in your SAN work better with SQL Server.

Tip #2: Mind your buffer cache and minimize your cache thrashing

SQL Server looks for pages first in the buffer cache, a large area of memory. If the page it seeks is not there, it queues a physical I/O request to access the page on disk. The buffer cache lets SQL Server read and write multiple pages in a single operation and read ahead to adjacent pages.

To evaluate the health of your buffer cache, keep your eye on two indicators: the buffer cache hit ratio and page life expectancy. They tell you how serene or frantic things are in the cache. If things are frantic, it could be a sign of cache thrashing.

Cache thrashing is as awful as it sounds. In fact, it’s worse. It usually happens when SQL Server has to evict small but repeatedly accessed pages from the buffer cache to make room for a one-time scan of large tables or indexes. After the scan, it evicts the large files and has to reload the repeatedly accessed pages from disk, generating more I/O and thrashing the cache.

SQL Server includes a Dynamic Management View (DMV) called sys.dm_os_ buffer_descriptors. You can query it to find out which tables and indexes are taking up the most space in the buffer cache and troubleshoot from there.

Next steps

Have a look at Ten Tips for Optimizing SQL Server Performance, the complete white paper we’ve put together. You’ll find details and SQL samples for these tips, plus more ways to get the best performance and greatest value from your SQL Server optimization efforts.

That way, if you too find yourself waist-deep in sand this summer, at least you won’t have to worry about SQL performance tuning.