What is the best way to compare 2 variants of a SQL query for performance?

Question

I've got a SQL 2005 DB running under a virtual environment.

To simplify things, let's say I have two SQL SELECT Queries. They both do the exact same thing. But I'm trying to analyze them for performance purposes.

Generally, I'd fire up a local DB, load up some data and using timing to compare one variant to other variants.

But in this case, since the DB is large and it's a testbox, the client has placed it on a host that's serving other VM's as well.

The DB is too large to pull down locally, so that's out (at least for now).

But my main issue is that when I run queries against the server, the timing is all over the place. I can run the +exact+ same query 4 times and get timings of 7secs, 8 minutes, 3:45min and 15min.

My first thought was use SET STATISTICS IO ON.

But, that yields basically read and write stats on the tables being queries, which, depending on the variations in the queries (temp tables, vs views, vs joins, etc) can't really be accurately compared, except in aggregate.

I then though of SET STATISTICS TIME ON, and just using the CPU time, but that seems to discount all the IO, which also doesn't make for a good baseline.

My question is is there any other statistic or performance analysis technique that could be useful in a situation like this?

Answer 1

The STATISTICS IO information will still be useful. You may see significantly different numbers of reads, writes and scans that will make it obvious which query is better.

You can also view Execution Plan information for each query. You can select Query -> Display Estimated Execution Plan to see a graphical presentation of the SQL Server estimate to run the query. You can also use the Query -> Include Actual Execution Plan to show the actual plan used.

And, you can also use SET SHOWPLAN_TEXT, SET SHOWPLAN_ALL or SET SHOWPLAN_XML to include the execution plan to view a textual display of the plan.

When viewing the results of the execution plan, you can look at the estimated cost value and compare the values for each query. The estimated cost is a relative value that can be used to compare the cost of each option.