link of exec plan's parts with running query

Question

link of exec plan's parts with running query

den_sidr

Old Hand

Points: 323
More actions
December 2, 2011 at 2:36 pm

#260854

Good Morning ,
is it possible to map estimated plan (its parts) to currently running query?
for example i have a simple query has been running for 5 minutes already. And i have its estimated plan with 3 main parts like clustered index seek for one of join tables , table scan for the second join table and nested loops join operation that works with two inputs and outputs matching rows.
can i figure out somehow what exact part of the plan SQL Server query executer is running currently - at this minute- for example, is reading data pages #n-#m through clustered index or/and matches key rows #x-#y.
or my questions do not make sense ?
Thank you for your help.

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

Devendrakumar SSC-Forever Points: 42493 More actions · Answer 1

And why do you want to do that? If you have the execution plans (estimated / actual), you know very well what are the bottlenecks? Its cost distribution & execution time can anyway guide you on individual component’s performance behaviour.

Jeff Moden SSC Guru Points: 1004415 More actions · Answer 2

den_sidr (12/2/2011)
Good Morning ,
is it possible to map estimated plan (its parts) to currently running query?
for example i have a simple query has been running for 5 minutes already. And i have its estimated plan with 3 main parts like clustered index seek for one of join tables , table scan for the second join table and nested loops join operation that works with two inputs and outputs matching rows.
can i figure out somehow what exact part of the plan SQL Server query executer is running currently - at this minute- for example, is reading data pages #n-#m through clustered index or/and matches key rows #x-#y.
or my questions do not make sense ?
Thank you for your help.

You can't tell which part of the execution plan is "currently" running but you can tell which tables are involved in the longest running parts (which are normally table scans and sorts). You can right click on the objects in the estimated execution plan and see what the objects are under "Properties". You can also get a pretty good idea as to "why".

If you really want to "go deep" on execution plans, I recommend you read the wonderful book on the subject written by Grant Fritchey. Here's the link to the free download. You can also buy the real book. It's a great addition to anyone's library.

http://www.sqlservercentral.com/articles/books/65831/

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Grant Fritchey SSC Guru Points: 398909 More actions · Answer 3

If you look at sys.dm_exec_requests you can see what the process is currently waiting on. You can also get a link to what locks are being held by the process. That can give you a clue as to what parts of the query are being executed, but as Jeff says, no way to say it's currently performing Operator 4 from the plan beyond that kind of deductive reasoning.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

den_sidr Old Hand Points: 323 More actions · Answer 4

Jeff Moden (12/3/2011)
den_sidr (12/2/2011)
Good Morning ,
is it possible to map estimated plan (its parts) to currently running query?
for example i have a simple query has been running for 5 minutes already. And i have its estimated plan with 3 main parts like clustered index seek for one of join tables , table scan for the second join table and nested loops join operation that works with two inputs and outputs matching rows.
can i figure out somehow what exact part of the plan SQL Server query executer is running currently - at this minute- for example, is reading data pages #n-#m through clustered index or/and matches key rows #x-#y.
or my questions do not make sense ?
Thank you for your help.
You can't tell which part of the execution plan is "currently" running but you can tell which tables are involved in the longest running parts (which are normally table scans and sorts). You can right click on the objects in the estimated execution plan and see what the objects are under "Properties". You can also get a pretty good idea as to "why".
If you really want to "go deep" on execution plans, I recommend you read the wonderful book on the subject written by Grant Fritchey. Here's the link to the free download. You can also buy the real book. It's a great addition to anyone's library.
http://www.sqlservercentral.com/articles/books/65831/

Hi Jeff Moden ,

Thank you very much for this book, i started read it.

and i know how analyze execution plans, but it is a pity there is no direct evidence of plan' parts in runtime.

Also i know that in ORACLE it is possible to observe such activity, using Toad tool for example, you can see the index scan or index seek or sort operation in runtime.

Maybe i will find something in that book:w00t:

den_sidr Old Hand Points: 323 More actions · Answer 5

Grant Fritchey (12/4/2011)
If you look at sys.dm_exec_requests you can see what the process is currently waiting on. You can also get a link to what locks are being held by the process. That can give you a clue as to what parts of the query are being executed, but as Jeff says, no way to say it's currently performing Operator 4 from the plan beyond that kind of deductive reasoning.

Hello Grant Fritchey , Thank you for your idea to check locks. Definitely it can point you to some objects(table's or index's pages) involved in current operation.and sometimes i analyze lock:-)

den_sidr Old Hand Points: 323 More actions · Answer 6

Dev (12/3/2011)
And why do you want to do that? If you have the execution plans (estimated / actual), you know very well what are the bottlenecks? Its cost distribution & execution time can anyway guide you on individual component’s performance behaviour.

Hi Dev,

i need this only to observe some activity - to see prcesses according to estimated exec. plan.

there is no bottleneck- we have tuned this query already.

Devendrakumar SSC-Forever Points: 42493 More actions · Answer 7

Also i know that in ORACLE it is possible to observe such activity, using Toad tool for example, you can see the index scan or index seek or sort operation in runtime.

Toad is third party tool for Oracle (now SQL Server as well). I am not sure if any third party tools provide this information for SQL Server.

Paul White SSC Guru Points: 150468 More actions · Answer 8

den_sidr (12/6/2011)
Also i know that in ORACLE it is possible to observe such activity, using Toad tool for example, you can see the index scan or index seek or sort operation in runtime.

It is possible, in principle, to write something similar for SQL Server using information exposed by Extended Events. I don't know if any third party vendor (e.g. Toad) has a product that does that though.

Paul White
All articles available on SQL.kiwi
@SQL_Kiwi