SPs, UDFs and views are slower in SQL server 2005 than SQL server 2000

Question

Post reply

SPs, UDFs and views are slower in SQL server 2005 than SQL server 2000

chandima_panditha

Newbie

Points: 3
More actions
August 11, 2008 at 12:28 am

#195637

Hi there,

We have migrated our DB from SQL 2000 to SQL server 2005, the SPs, UDFs and views are very slower than they were in 2000.

For example, there's a stored procedure (which calls some views and UDFs) which takes only few seconds to run in SQL server 2000, but it takes more than 3 minutes in SQL Server 2005.

After restoring the DB as a backup from 2000 to 2005, I have done these routines.

1. DBCC UPDATEUSAGE

2. sp_updatestats

3. I have refresed all the views (sp_refreshview)

4. I have recompiled all the Stored Procedures (sp_recompile)

But, none of these have helped us so far, we running the SQL Server 2005 SP@ with latest patches installed.

I think this particular database may have been originally upsized from MS Access to 2000 (I'm not too sure about that). I’m not sure if this plays any role here though…

Please help

Regards,

Chandi

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

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

Gogula G. Aryalingam SSCrazy Points: 2688 More actions · Answer 1

Gogula G. Aryalingam

SSCrazy

Points: 2688

August 11, 2008 at 1:17 am

#855911

Hi Chandi,

Is the compatibility level set to 90? Or is it left as 80? This won't necessarily give you the problems that you are having, but some features of 2005 may not be used, which may sometimes be causing the problem since the query optimizers work differently in 2005.

Regards

GSquared SSC Guru Points: 260824 More actions · Answer 2

Do you have access to the execution plans from 2000? If so, comparing the two plans would be the best place to start.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

chandima_panditha Newbie Points: 3 More actions · Answer 3

chandima_panditha

Newbie

Points: 3

August 11, 2008 at 9:03 am

#856141

Yes, the compatible level set to 90. I have done,

EXEC sp_dbcmptlevel MyDatabase , 90;

But, still no luck

Thx

Chandi

RBarryYoung SSC Guru Points: 143329 More actions · Answer 4

chandima_panditha (8/11/2008)

For example, there's a stored procedure (which calls some views and UDFs) which takes only few seconds to run in SQL server 2000, but it takes more than 3 minutes in SQL Server 2005.

Can you show us this stored procedure and the UDF's and Views that it calls?

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]