The latest newsletter from the excellent SQL-Server-Performance.com had a tip about doing performance tuning on high-powered machines. If you need to test large queries running inside the server room (meaning, the app or web server runs a big query against the db server), you may not get the speed results you’re expecting if you try to run that same query on your desktop. SQL-Server-Performance attributes this to low resources on the desktop, but there can also be another reason: network bandwidth and latency.

Last week, I was doing performance tuning on a SQL Server that supports users across the United States. One of the applications exhibited very high Duration numbers in the profiles that I ran, and I couldn’t figure out why. Even when I tuned the tables by adding appropriate indexes, the load on the server didn’t improve the way I’d expected. Finally, I found out that this particular application was running on machines in another state – not application servers inside our data centers like we’d typically do. The query results were being piped over their slow VPN connection down to their workstations, and so the Duration statistics were artificially high. Sure, it looked like the queries were taking forever, but in reality, it was just a slow network connection, and there was nothing I could do on the database side to speed that up.

The morale of that first story: never performance tune by looking at a single statistic alone, like Duration, Reads, Writes, or CPU. Use all of them together to gauge what queries are really having an impact.

Technorati tags: PerformanceTuning, SQLServer