Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Wrong Execution Plan question Expand / Collapse
Author
Message
Posted Wednesday, January 16, 2013 1:33 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Sunday, December 21, 2014 11:34 PM
Points: 32, Visits: 317
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
Post #1407667
Posted Wednesday, January 16, 2013 2:09 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 8:04 PM
Points: 7,141, Visits: 12,768
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
Post #1407683
Posted Wednesday, January 16, 2013 3:46 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 3:36 AM
Points: 2,841, Visits: 3,988
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
Post #1407728
Posted Wednesday, January 16, 2013 11:08 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Sunday, December 21, 2014 11:34 PM
Points: 32, Visits: 317
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
Post #1408153
Posted Wednesday, January 16, 2013 11:50 PM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 3:36 AM
Points: 2,841, Visits: 3,988
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
Post #1408164
Posted Thursday, January 17, 2013 12:39 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Sunday, December 21, 2014 11:34 PM
Points: 32, Visits: 317
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...

Post #1408186
Posted Thursday, January 17, 2013 1:24 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 3:36 AM
Points: 2,841, Visits: 3,988
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
Post #1408206
Posted Thursday, January 17, 2013 2:37 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Sunday, December 21, 2014 11:34 PM
Points: 32, Visits: 317
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
Post #1408242
Posted Thursday, January 17, 2013 3:31 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 3:36 AM
Points: 2,841, Visits: 3,988
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
Post #1408283
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse