Saturday, February 5, 2011

Multiple Query Plans

As my last post, this is again something I got from MongoDB.

Execution plans are vital to measure performance of your queries. How many times, you are puzzled with why that index is not using? why it is doing a scan?

In MongoDB, there is a option to view all the execution plans with query engine considered and what was used eventually.

syntax for that is db.users.find({UserID:14049}).explain(true)

explain() will tell you what is the query plan used and true parameter will give you all the query plans considered for the execution.

However, though this is a great feature to have, in case of SQL Server implementation may not be easy. In MongoDB anyway queries have one collection only and query plan is not complex


  1. Not entirely sure why you regard this as difficult in SQL Server.

    SQL Server has a great many dmvs that reveal a great deal about what is going on in the system, not least of which is which indexes are being used and in what manner.

    Posted by Dave Poole

  2. I agree that it is complex than querying against a single table. (In Mongo it is a single collection of documents) But it is not entirely impossible, as SQL Server does take "all" or some) plans before getting into the final plan