Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Wrong Execution Plan question


Wrong Execution Plan question

Author
Message
sqlnes
sqlnes
SSC Rookie
SSC Rookie (33 reputation)SSC Rookie (33 reputation)SSC Rookie (33 reputation)SSC Rookie (33 reputation)SSC Rookie (33 reputation)SSC Rookie (33 reputation)SSC Rookie (33 reputation)SSC Rookie (33 reputation)

Group: General Forum Members
Points: 33 Visits: 347
Hi,

Our developer send me a query that takes long time to run. it has a query structure like this,

select T1.* from
(select T0.a, T0.b, ROW_NUMBER() OVER (ORDER BY T0.A) as RN
FROM (
SELECT a, b FROM KEY_TABLE kt JOIN......
UNION
SELECT a, b FROM KEY_TABLE kt JOIN......
UNION
SELECT a, b FROM KEY_TABLE kt JOIN......
) T0
) T1
WHERE T1.RN BETWEEN 1 to 50

I checked the execution plan and it costs a lot on the Clustered index of the KEY_TABLE and took more than 2 minute to finish. However, if i put "SET FORCEPLAN ON" before it and it took only 1 second to finish.

Could anyone get idea of why SQL Server did not use the "correct" plan as the one when FORCEPLAN was set ON?

Thanks in advance
V
Orlando Colamatteo
Orlando Colamatteo
SSCrazy Eights
SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)

Group: General Forum Members
Points: 8235 Visits: 14368
Can you post the actual execution plans for both versions of the query? The one without the hint and the one with the hint?

__________________________________________________________________________________________________
There are no special teachers of virtue, because virtue is taught by the whole community. --Plato
Bhuvnesh
Bhuvnesh
SSCrazy
SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)

Group: General Forum Members
Points: 2924 Visits: 4076
sqlnes (1/16/2013)
Could anyone get idea of why SQL Server did not use the "correct" plan as the one when FORCEPLAN was set ON?
from BOL SQL Server query optimizer processes a join in the same order as the tables appear in the FROM clause of a query. So thats the reason the selected plan didnt provide optimal performance.

Also post the index definition along with opc.three request.

-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
sqlnes
sqlnes
SSC Rookie
SSC Rookie (33 reputation)SSC Rookie (33 reputation)SSC Rookie (33 reputation)SSC Rookie (33 reputation)SSC Rookie (33 reputation)SSC Rookie (33 reputation)SSC Rookie (33 reputation)SSC Rookie (33 reputation)

Group: General Forum Members
Points: 33 Visits: 347
Thanks guys!

It's weird, when I tried it today, it has back to the "correct" plan without set FORCEPLAN. it has been running fine now.

I just wondering now would it take some time after re-analyzing table/indexes then optimizer can do accurate calculation?

V
Bhuvnesh
Bhuvnesh
SSCrazy
SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)

Group: General Forum Members
Points: 2924 Visits: 4076
sqlnes (1/16/2013)
It's weird, when I tried it today, it has back to the "correct" plan without set FORCEPLAN. it has been running fine now. I just wondering now would it take some time after re-analyzing table/indexes then optimizer can do accurate calculation?


1) Have you/any job updated the statistics ?
2) have you recomplied the SP/script ?
3) did both execution have same parameter's values ?

-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
sqlnes
sqlnes
SSC Rookie
SSC Rookie (33 reputation)SSC Rookie (33 reputation)SSC Rookie (33 reputation)SSC Rookie (33 reputation)SSC Rookie (33 reputation)SSC Rookie (33 reputation)SSC Rookie (33 reputation)SSC Rookie (33 reputation)

Group: General Forum Members
Points: 33 Visits: 347
Bhuvnesh (1/16/2013)
sqlnes (1/16/2013)
It's weird, when I tried it today, it has back to the "correct" plan without set FORCEPLAN. it has been running fine now. I just wondering now would it take some time after re-analyzing table/indexes then optimizer can do accurate calculation?


1) Have you/any job updated the statistics ?
2) have you recomplied the SP/script ?
3) did both execution have same parameter's values ?


Yes, I did "update statistics" yesterday. but it did not help. Actually, I just retried and I was wrong, the plan is still go the "incorrect" one.

And I just found that in my query,

select T1.* from
(select T0.a, T0.b, ROW_NUMBER() OVER (ORDER BY T0.A) as RN
FROM (
SELECT a, b FROM KEY_TABLE kt JOIN......
UNION
SELECT a, b FROM KEY_TABLE kt JOIN......
UNION
SELECT a, b FROM KEY_TABLE kt JOIN......
) T0
) T1
WHERE T1.RN BETWEEN 1 to 50

If I commented out the last line (WHERE...), it went normal. And if I added the where clause back, it went bad again. strange...
Bhuvnesh
Bhuvnesh
SSCrazy
SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)

Group: General Forum Members
Points: 2924 Visits: 4076
Place your
(
SELECT a, b FROM KEY_TABLE kt JOIN......
UNION
SELECT a, b FROM KEY_TABLE kt JOIN......
UNION
SELECT a, b FROM KEY_TABLE kt JOIN......
) T0

code in temp table and add appropraite indexes (acc to outer query ) to it
then use that temp table in main query

PLUS
POST exec plan here (as OPC suggested above)

-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
sqlnes
sqlnes
SSC Rookie
SSC Rookie (33 reputation)SSC Rookie (33 reputation)SSC Rookie (33 reputation)SSC Rookie (33 reputation)SSC Rookie (33 reputation)SSC Rookie (33 reputation)SSC Rookie (33 reputation)SSC Rookie (33 reputation)

Group: General Forum Members
Points: 33 Visits: 347
Bhuvnesh (1/17/2013)
Place your
(
SELECT a, b FROM KEY_TABLE kt JOIN......
UNION
SELECT a, b FROM KEY_TABLE kt JOIN......
UNION
SELECT a, b FROM KEY_TABLE kt JOIN......
) T0

code in temp table and add appropraite indexes (acc to outer query ) to it
then use that temp table in main query

PLUS
POST exec plan here (as OPC suggested above)


Thank you for sharing.
I hope i can change the code but I could not. it's auto generated code based on criterion set to application. What i can do so far is explained the current situation to the application owner.

V
Bhuvnesh
Bhuvnesh
SSCrazy
SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)

Group: General Forum Members
Points: 2924 Visits: 4076
sqlnes (1/17/2013)
[quote][b]What i can do so far is explained the current situation to the application owner.
What you can before explanting to them, test the code with the suggestion and show them and what and why we need those changes ?

-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search