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

2 comments:

  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

    ReplyDelete
  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

    ReplyDelete