Tip: Check for Fragmentation

A few months ago, I described a performance troubleshooting scenario in which a reader was frustrated by getting different performance results from seemingly identical tests run on seemingly identical servers (read the SQL Server Magazine article "Merry-Go-Round Scans: a Culprit for Performance Variances," April 2006, InstantDoc ID 49285). I presented several ideas for the reader to use to track down the potential differences, but I assumed that because the servers were identical, the difference was related to SQL Server. Here's a reader's comment that provides a different approach.

"Another possible difference could be the fragmentation in database files on the disk array. If the production server has been running for a while (expanding regularly to meet increased space needs), this server could have more fragmented database files than the development server.The development server could have been a clean install with a restore of a database file that resulted in almost no fragmentation.We recently defragmented the drives on our production servers and experienced improved performance as well. It's easy to overlook the hardware when comparing similar systems."

This is a great point in many ways. It reminds us that identical hardware might not always be identical. It's always a good idea to test your assumptions when you investigate performance problems.