Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 


 
     
Recent Posts
   
Popular Topics
Home   
Search
    Members    Calendar    Who's On

Home » SQL Server 2008 » T-SQL (SS2K8) » Wrong Execution Plan question
Add to briefcase

Wrong Execution Plan question Expand / Collapse
Rate Topic
Display Mode
Topic Options
Author
Message
sqlnes
Posted Wednesday, January 16, 2013 1:33 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, June 10, 2013 3:29 AM
Points: 19, Visits: 233
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
opc.three
Posted Wednesday, January 16, 2013 2:09 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 1:24 PM
Points: 6,826, Visits: 11,950
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

Believe you can and you're halfway there. --Theodore Roosevelt

Everything Should Be Made as Simple as Possible, But Not Simpler --Albert Einstein

The significant problems we face cannot be solved at the same level of thinking we were at when we created them. --Albert Einstein

1 apple is not exactly 1/8 of 8 apples. Because there are no absolutely identical apples. --Giordy
Post #1407683
Bhuvnesh
Posted Wednesday, January 16, 2013 3:46 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Thursday, June 13, 2013 7:03 AM
Points: 2,562, Visits: 3,453
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----------
While 1 = 1 (Learning SQL....)
Click to get fast response of your post
Post #1407728
sqlnes
Posted Wednesday, January 16, 2013 11:08 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, June 10, 2013 3:29 AM
Points: 19, Visits: 233
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
Bhuvnesh
Posted Wednesday, January 16, 2013 11:50 PM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Thursday, June 13, 2013 7:03 AM
Points: 2,562, Visits: 3,453
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----------
While 1 = 1 (Learning SQL....)
Click to get fast response of your post
Post #1408164
sqlnes
Posted Thursday, January 17, 2013 12:39 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, June 10, 2013 3:29 AM
Points: 19, Visits: 233
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
Bhuvnesh
Posted Thursday, January 17, 2013 1:24 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Thursday, June 13, 2013 7:03 AM
Points: 2,562, Visits: 3,453
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----------
While 1 = 1 (Learning SQL....)
Click to get fast response of your post
Post #1408206
sqlnes
Posted Thursday, January 17, 2013 2:37 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, June 10, 2013 3:29 AM
Points: 19, Visits: 233
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
Bhuvnesh
Posted Thursday, January 17, 2013 3:31 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Thursday, June 13, 2013 7:03 AM
Points: 2,562, Visits: 3,453
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----------
While 1 = 1 (Learning SQL....)
Click to get fast response of your post
Post #1408283
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse

 
 
Copyright © 2002-2013 Simple Talk Publishing. All Rights Reserved. Privacy Policy. Terms of Use. Report Abuse.