SQL Server: Compare Index Performance

Question

How can I compare the performance of an Index on a table using the Query Analyzer?

I would like to run the same query through the Estimated Execution Plan twice, once using the index and once without, and have a good comparison of the table scans/index scans that are generated.

I have tried CREATE INDEX..., SELECT..., DROP INDEX..., SELECT. I expect the first query to use the index and the second to have no index, but both execution plans will use the index.

Answer 1

If there is no index, then it can't be used. However, the index still exists for the estimated execution plan for the 2nd select, so it is evaluated. The DROP INDEX plan is also just an estimate too

I'd use the actual execution plan anyway, because personally I don't like the estimated one.

You can use SET SHOWPLAN TEXT to capture what is actually used in the query window (or use the graphical one). I'd also use SET STATISTICS IO and often SET STATISTICS TIME too.

Answer 2

Use the Sql Server Profiler under configuration tools. Run your queries with and without indexing and record the data reads / writes timing etc of your function calls. This should give you a clear idea if your indexes have improved performance (less reads / latency etc.)