Execution plan generated but query is hung?

Question

Execution plan generated but query is hung?

Viewing 5 posts - 16 through 19 (of 19 total)

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

Jeff Moden SSC Guru Points: 1004441 More actions · Answer 1

Just my 2 cents. I agree with Remi... the recompile will likely keep the hang from happening again.

--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)

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 2

sqldba_icon (6/15/2011)
I think i answered my own question after reading Gail's blog. With the query in question what would have probably happened is it generated an estimate plan and then estimate plan was good and used as actual execution plan and so i see the estimated plan( in this case which is also actual plan) in sys.dm_exec_query_plan

Aaah...

The ONLY differences between an estimated plan and an actual plan is that the actual plan contains run time information. It is not that an 'estimated plan' is less work, rough quick guess or anything like that. It's not.

When you ask SSMS for an estimated plan it submits the query to SQL, SQL parses, binds and optimises and hands back the plan. No run time info because the query wasn't run.

When you ask SSMS for an actual plan it submits the query to SQL, SQL parses, binds and optimises, executes and hands back the plan with run-time information. No difference in the optimise portion.

What gets put into cache is the plan that the query uses, just with the run-time information stripped out (it's cached and reused, which Execution's run-time info should be used?)

http://sqlinthewild.co.za/index.php/2009/02/19/estimated-and-actual-execution-plan-revisited/

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 3

Jeff Moden (6/15/2011)
Just my 2 cents. I agree with Remi... the recompile will likely keep the hang from happening again.

Not the first option, I'd still like to see what this query is doing... I'm 75% certain that we can rewrite or rework the indexes to fix this.

Of course simply updating the stats daily and then during the day might solve it also. But alas the OP is not cooperating on that end!

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 4

Ninja's_RGR'us (6/16/2011)
Jeff Moden (6/15/2011)
Just my 2 cents. I agree with Remi... the recompile will likely keep the hang from happening again.
Not the first option, I'd still like to see what this query is doing... I'm 75% certain that we can rewrite or rework the indexes to fix this.
Of course simply updating the stats daily and then during the day might solve it also. But alas the OP is not cooperating on that end!

In case you want to solve your problem...

http://sqlinthewild.co.za/index.php/2011/03/22/statistics-row-estimations-and-the-ascending-date-column/

curious_sqldba SSC-Dedicated Points: 36494 More actions · Answer 5

Update stats worked, i have changed the schedule to update the stats before the load also. Before the stats we about 12 hours old now it will be less than an hour old. I will have to monitor for few days and see if this fixed the issue. Thanks