SQL 2005 execution plan problem

Question

Post reply

SQL 2005 execution plan problem

OomBoom

Hall of Fame

Points: 3481
More actions
November 16, 2010 at 1:52 am

#241241

Hi
I am looking at upgrading a system from 2000 to 2005. I have some views that is causing problems. I hope some one can help me with this.
On 2000 they run in 1 sec and in 2005 they run 8min. This is because 2005 chooses an execution path that is very slow. If you look at the following two queries you would think that both would run the same or maybe the first would run a bit faster. If you think so you would be wrong. The firts runs for about 8min and the second one for 35sec. I attached the execution plans and stats if someone wants to look at it.
My quetions then is :
1. Why would SQL 2005 choose such a slow execution plan. Am I doing something wrong.
2. Why would adding left join change the execution plan so dramticaly.
SELECT A.DEPTNAME_ABBRV, D.EMPLID, D.SUPERVISOR_ID, C.OPRID
FROM
dbo.PS_G5_BU_REPORTSTO AS A
join dbo.PSOPRDEFN AS C on A.OPRID = C.OPRID
join dbo.PS_PERS_SRCH_GBL AS B on B.ROWSECCLASS = C.ROWSECCLASS
join dbo.PS_EMPLOYEES AS D on D.EMPLID = B.EMPLID and D.DEPTNAME_ABBRV = A.DEPTNAME_ABBRV
SELECT A.DEPTNAME_ABBRV, D.EMPLID, D.SUPERVISOR_ID, C.OPRID
FROM
dbo.PS_G5_BU_REPORTSTO AS A
left join dbo.PSOPRDEFN AS C on A.OPRID = C.OPRID
left join dbo.PS_PERS_SRCH_GBL AS B on B.ROWSECCLASS = C.ROWSECCLASS
left join dbo.PS_EMPLOYEES AS D on D.EMPLID = B.EMPLID and D.DEPTNAME_ABBRV = A.DEPTNAME_ABBRV
where C.OPRID is not null and
C.ROWSECCLASS is not null and
A.DEPTNAME_ABBRV is not null and
B.EMPLID is not null
>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
Do not reinvent the wheel.
>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Dave Ballantyne SSC-Dedicated Points: 33667 More actions · Answer 1

Please post all DDL including table , view and index definition

Clear Sky SQL
My Blog[/url]

OomBoom Hall of Fame Points: 3481 More actions · Answer 2

Hi

here is the scripts requested. I included stats.

>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
Do not reinvent the wheel.
>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

Have you updated all stats after the upgrade? It is highly recommended.

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Dave Ballantyne SSC-Dedicated Points: 33667 More actions · Answer 4

We've been here before havent we ?

http://www.sqlservercentral.com/Forums/FindPost986414.aspx

Sorry but im going to repeat my advice in that post....

This is tough to diagnose like this , though i feel the answer is in rewriting you queries.
That will take more time , and more effort , that i have , to do that.
Maybe you should look at getting a consultant in for a few days.

Clear Sky SQL
My Blog[/url]

OomBoom Hall of Fame Points: 3481 More actions · Answer 5

Hi Gail

Yes I have rebuilt all indexes and updated stats with full scan. All normal tasks have been run through. I personaly think this is a 2005 bug but I do not have the knowledge to put my finger on the exact cause. The execution path matches 2 very big tables before it goes to the lookup tables that is small in comparison.

Thanks for the reply.

>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
Do not reinvent the wheel.
>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>

OomBoom Hall of Fame Points: 3481 More actions · Answer 6

Hi Dave

Yes sort off. The previous one was a comparison between the same query in 2000 and 2005 that ran totaly diff. This is same database diff TSQL. If you are unable to see a problem on execution plans do not worry we are working around the problem Scripts by rewriting, I am just trying to get to the bottom of the preformance diff. Why SQL chooses so differently? It must be some rule in SQL2005. If I know what it is I can avoid it in future and ask the development team to work around it.

>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
Do not reinvent the wheel.
>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 7

The optimiser changed. There will be occasional cases where a query plan gets worse, it comes with the territory.

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass