Using SQL Server Query Analyzer
Sometimes, it is valuable to compare two more queries at the same time, checking to see which one is the better performer. This is especially true if you have a query that you have rewritten two or more different ways, and you want to find out which variation of the query is the most efficient.
While you can analyze each query, one at a time in Query Analyzer, one trick you should consider trying is to place all of the queries you want to compare in Query Analyzer, turn on “Show Execution Plan,” then run all the queries at once. When you do this, the “Execution Plan” window will divide into a separate window for each query ran, allowing you to compare their execution plans much easier. In addition, in each execution plan window, you will see a “Query Cost (relative to the batch)” number. What this number is telling you is how long each query took as a percentage of the total amount of time it took all of the queries to run. The query with the lowest number is the query that performed the quickest, and so on.
As you can imagine, this information can make it much easier to compare different queries. [7.0, 2000] Updated 4-17-2006
Using Query Analyzer inappropriately when testing query performance can lead to poor performance tuning decisions. When you run a query manually using Query Analyzer, the results generated by the query are stored on the computer where Query Analyzer is running. When the results set is small, this is not an issue. But if the results set is large, say 10,000 or more rows returned, then the available resources on the computer running Query Analyzer can distort results.
Here is a real example that happened to me. I wanted to analyze the performance of a particular query that returned over 100,000 rows of data. When I ran the query from my desktop computer running Query Analyzer, it took over 30 minutes for the query to complete. This desktop had 512MB of RAM total, but much of it was already allocated to other concurrently running applications. The database was on its own dedicated SQL Server.
Next, I then ran the same query, but this time, instead of running it on my desktop, I ran it from a quad CPU server with 2GB of RAM, using Query Analyzer. This time, the exact same query completed in less than 10 seconds.
So why did the exact same query, running from Query Analyzer against the exact same database return such different results? The answer to this comes from a statement I made earlier. And that is, when you run a query from Query Analyzer, the results set is stored on the computer that is running Query Analyzer.
In my example, my desktop did not have enough available RAM to store over 100,000 rows of data, and it had to begin performing a lot of paging, which slowed down the ability of Query Analyzer to display the returned data. So, in other words, the slowdown in performance between the same queries was due not to the query or SQL Server, but to the desktop that was running Query Analyzer.
The moral of this story is to ensure that whatever computer you are running Query Analyzer on must have enough resources in order to display easily all of the query’s returned rows. As you might imagine, if you do not heed this advice, trying to performance tune a query might be made a little more difficult than it needs to be. [7.0, 2000] Updated 4-17-2006