Recently I was talking with a group of DBAs about what we thought were the top tuning techniques for optimizing SQL server performance. If you only had time to do a few things, where would you start?
TempDb optimization was the first thing on everybody’s list. SQL Server does a vast amount of work using TempDb simply performing normal queries. It uses TempDb to sort, group, filter and join data. It is essential for building indexes, and staging data. Since all databases on an instance of SQL Server share a single TempDb it must be optimized as the database server use increases.
Second to TempDb was indexing and statistics. Creating and maintaining appropriate indexes stands out as an activity that has ever increasing performance results. Missing indexes can cause queries to perform poorly. Second to missing indexes are fragmented indexes where the data is spread across the disk requiring wait states for the disk head to be re-located to the next segment of data. Out of data statistics for indexes result in indexes not being used that may be a good fit, or the use of indexes that are not the best fit.
The more quickly your data changes the better performance enhancements you will experience by maintaining your indexes. The biggest question was if the maintenance of indexes and/or statistics should be done real time, or performed as a batch process during hours of reduced load. Generally updates were preferend during reduced load whenever possible, and persons with skill to maintain them were present.
What’s on the top of your database optimization list? Do you think we got it right that these are the top two? What would you recommend instead? Share your optimization preferences here; join the conversation. Or feel free to drop me an Email at btaylor@sswug.org.
Cheers,
Ben