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

Different Execution Plan on different servers Expand / Collapse
Author
Message
Posted Monday, July 26, 2010 5:39 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, March 10, 2014 2:06 AM
Points: 39, Visits: 283
Hello all,

i have a query, on T environment it performs fine (20 seconds), but on Q environment not (6 minutes) due to different execution plans.

1. The query looks like..

Select * from VIEW
where DD_ID_SALES in (
Select DD_ID_SALES from T_AD_ACTUAL_DATE.DATE_FORMAT_USED = 'M'
)

2. Statistics have been updated

3. Inthe view there are some joins against a main fact table.In the execution plan i have seen, that the where statement is performed as last step in the execution plan, but this step should be first because it significantly reduces the number of rows which should be processed.

---> Anyone an idea how the execution plan can differ between 2 servers?


  Post Attachments 
ExecPlan_T.sqlplan (8 views, 52.17 KB)
ExecPlan_Q.sqlplan (5 views, 66.11 KB)
Post #958720
Posted Monday, July 26, 2010 5:44 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Yesterday @ 2:55 PM
Points: 41,570, Visits: 34,495
Same hardware?
Same load?
Same amount of data?
Same schema?

Can you post the actual execution plans rather than the estimated please. There's a lot of info that isn't in the estimated plans.



Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
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

Post #958723
Posted Monday, July 26, 2010 5:57 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, March 10, 2014 2:06 AM
Points: 39, Visits: 283
- quite the same hardware ~ 20gb ram
- same amount of data in the source tables
- no other loads during my tests on the server
- same schema, etc.

Attached you'll find the actual execution plans.
Thanks for your help!


  Post Attachments 
ExecPlan_Q_Actual.sqlplan (18 views, 72.53 KB)
ExecPlan_T_Actual.sqlplan (8 views, 61.49 KB)
Post #958729
Posted Monday, July 26, 2010 6:28 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Yesterday @ 2:55 PM
Points: 41,570, Visits: 34,495
Are you sure that the data is the same?

The actual plan for Environment Q has 3.4 million rows coming out of the parallelism operator (the last operator that shows the actual row count), the actual plan for Environment T has 750 000 rows coming out of the parallelism operator. (Actual row count, not estimate). That would make a huge difference in plan choice.
Do these return the same row counts?

You've also got a different degree of parallelism on the two servers - 16 on T, 8 on Q.



Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
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

Post #958741
Posted Monday, July 26, 2010 6:45 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, March 10, 2014 2:06 AM
Points: 39, Visits: 283
Hello,

yes i made a mistake which shows indeed that samething is wrong in the execution plan.

I have forgotten to limit the data in T_AD_ACTUAL_DATE (Where condition).
But as you can see in the new Execution Plan although the data is now filtered (750 000 rows ) the optimizer does this filtering as last step.

On T server are 8 (big) processors, on Q are 16 (smaller) processors, but i think that should not influence the execution plan in this query.

Thanks again for your help!


  Post Attachments 
ExecPlan_Q_Actual_Corrected.sqlplan (6 views, 72.53 KB)
Post #958758
Posted Tuesday, July 27, 2010 6:53 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, March 10, 2014 2:06 AM
Points: 39, Visits: 283
In the execution plan i have seen that for the good server the estimated number of rows is 1 and on the bad server the estimated number of rows is 15,5 for the table T_AD_ACTUAL_DATE.
I have already updated the statistics, how can i influence this number? Maybe this would be the solution.
Post #959349
Posted Tuesday, September 03, 2013 11:55 AM


SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: 2 days ago @ 10:47 AM
Points: 28, Visits: 304
I am having a very similar issue as I prepare to migrate to a new server. Trying to do some testing I do basically a select top 10000 * with an order by date. The point of this was to test tempdb. The performance on the new server is much slower despite it being much more powerful on san disk and server hardware. Not to mention the old server has a heavy production load on it. The query plan is very different with the Sort costing 68% on the new server. Disk latencies in tempdb hit 2000 ms as measured in the OS. As measured on the SAN all are less than 3 ms. The databases are identical as I restored production from backup. Not counting the different query plan it seems there is a bottleneck somewhere since the SAN reports no latency. Have looked at the HBA Que depth settings and made changes with little results. I would like to solve the execution plan issue to know i am comparing apples to apples. Tried moving the tempdb files from the tempdb luns to the DATA luns and had the same results. The data luns have 75 15K spindles under then.

Anyway Ill be watching this thread to see what you come up with.


I don't always test my SQL scripts, but when I do, I test in Production.
Post #1491057
Posted Tuesday, September 03, 2013 11:36 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, March 10, 2014 2:06 AM
Points: 39, Visits: 283
Hello KTD,

my post is already 3 years old .
The "estimated number of rows" was consequently wrong for one table and i could not improve it by updating statistics. I had decided just to drop and recreate the table and then it worked.

Post #1491139
Posted Wednesday, September 04, 2013 6:58 AM


SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: 2 days ago @ 10:47 AM
Points: 28, Visits: 304
Oh wow I didn't notice I think I was looking at your last login date as the time it was posted. I'm pretty sure that isn't my issue I'm pretty much at a loss on this one. thanks for letting me know

I don't always test my SQL scripts, but when I do, I test in Production.
Post #1491269
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse