Hi Is there any tool which explains how a query executes in SQL Server.
SELECT NAME, (SELECT [DESC] FROM PRODUCT LEFT OUTER JOIN PRODUCTDESC ON PRODUCT.PDID = PRODUCTDESC.PDID) FROM PRODUCT
in the above query, how data is fetched, how data is filtered, when join is executed such kind of things can any tool explain? i heard about query analyzer but its complex is there any simple tool :)
asked Aug 10, 2011 at 11:04 PM in Default
Display actual execution plan in the toolbar is a good start (or use: SET STATISTICS XML ON )
I also use Set statistics io on; Set statistics time on;
You can also get the execution plans from the cache by the DMV:
This will give you a lot of information about the queries executed. You can sort and find slow or resource intense queries by this query. When you find the "worst" queries you can have a look at the execution plan to see whats wrong with it, and whats wrong always depends, but you can start to look for SCANS or KEY lookups, estimated cost, and the size of the arrows (the wider, the more data from each step). But to fully understand you should read the books from @grant fritchey.
In sql server management studio, instead of executing the query; hit Ctrl+L and you'll ser the estimated query plan.
answered Aug 10, 2011 at 11:07 PM
In addition to the other answers, if you want a detailed explanation of each of the showplan operators then the series of article that Simple Talk has here http://www.simple-talk.com/search/default.aspx?search=showplan+operator+of+the+week is a great reference
answered Aug 11, 2011 at 12:15 AM