x

Is there any tool to explain how a query executes in SQL Server

Hi Is there any tool which explains how a query executes in SQL Server.

For example,

SELECT NAME, (SELECT [DESC] FROM PRODUCT LEFT OUTER JOIN PRODUCTDESC ON PRODUCT.PDID = PRODUCTDESC.PDID) FROM PRODUCT enter code here

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 :)
more ▼

asked Aug 10, 2011 at 11:04 PM in Default

pvsrinivasrao gravatar image

pvsrinivasrao
174 26 27 27

Pick up any tsql book or read any number of thousands of blogs about tuning sql.
Aug 11, 2011 at 06:20 AM mikelanders
(comments are locked)
10|1200 characters needed characters left

4 answers: sort voted first

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:

SELECT * FROM sys.[dm_exec_query_stats] AS DEQS
CROSS APPLY sys.[dm_exec_query_plan]([DEQS].[plan_handle]) AS DEQP
ORDER BY [DEQS].[last_execution_time]
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.
more ▼

answered Aug 10, 2011 at 11:06 PM

Håkan Winther gravatar image

Håkan Winther
15.6k 34 37 48

(comments are locked)
10|1200 characters needed characters left
In sql server management studio, instead of executing the query; hit Ctrl+L and you'll ser the estimated query plan.
more ▼

answered Aug 10, 2011 at 11:07 PM

Magnus Ahlkvist gravatar image

Magnus Ahlkvist
16.4k 16 19 33

+1 actual and estimated may differ, but you should always show estimated before you execute anything to see if you will get any result within a couple of seconds and not a couple of hours
Aug 10, 2011 at 11:21 PM Håkan Winther
any guide to understand the ouput of query analyzer :)
Aug 10, 2011 at 11:31 PM pvsrinivasrao
(comments are locked)
10|1200 characters needed characters left

To start understand the query plans, you can read article [Understanding More Complex Query Plans][1] by @Grant Fritchey and even There are also links to his excelent book Dissecting SQL Server Execution Plans.

[1]: http://www.simple-talk.com/sql/performance/understanding-more-complex-query-plans/
more ▼

answered Aug 10, 2011 at 11:45 PM

Pavel Pawlowski gravatar image

Pavel Pawlowski
22.3k 9 11 21

Of course you can also refer the MSDN: [Logical and Physical Operators Reference][1]

[1]: http://msdn.microsoft.com/en-us/library/ms191158(v=SQL.105).aspx
Aug 11, 2011 at 12:57 AM Pavel Pawlowski
(comments are locked)
10|1200 characters needed characters left
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
more ▼

answered Aug 11, 2011 at 12:15 AM

Fatherjack gravatar image

Fatherjack ♦♦
42.3k 74 78 108

That series takes a lot of time to get through, but it is well worth reading if you are trying to understand what the plan means.
Aug 11, 2011 at 09:37 AM TimothyAWiseman
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x1833
x713
x6

asked: Aug 10, 2011 at 11:04 PM

Seen: 807 times

Last Updated: Aug 10, 2011 at 11:04 PM