July 26, 2010 at 5:39 am
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?
July 26, 2010 at 5:44 am
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, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
July 26, 2010 at 5:57 am
- 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!
July 26, 2010 at 6:28 am
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, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
July 26, 2010 at 6:45 am
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!
July 27, 2010 at 6:53 am
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.
September 3, 2013 at 11:55 am
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.
September 3, 2013 at 11:36 pm
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.
September 4, 2013 at 6:58 am
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.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply