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.
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.)
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.
use this at the beginning of the query window. it'll clear any plan caches.
dbcc freeproccache
go
dbcc dropcleanbuffers
go
so have that at the top, and then put your dreat and drop index code in a comment, and then your query. run it initially without an index and see what it looks like, then create the index and rerun it.
and yes, use the actual execution plan and you can also use client statistics.
The technique that ended up working for me was to use the WITH keyword in the FROM clause.
When the index is created:
SELECT * FROM table WHERE ...
SELECT * FROM table WITH (INDEX(0)) WHERE ...
The first query will (probably) use the index, the second will not. You could also use multiple indexes like:
SELECT * FROM table WITH (INDEX(IX_test1)) WHERE ...
SELECT * FROM table WITH (INDEX(IX_test2)) WHERE ...