August 11, 2008 at 12:28 am
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
August 11, 2008 at 1:17 am
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
August 11, 2008 at 8:35 am
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
August 11, 2008 at 9:03 am
Yes, the compatible level set to 90. I have done,
EXEC sp_dbcmptlevel MyDatabase , 90;
But, still no luck
Thx
Chandi
August 11, 2008 at 9:23 am
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]
August 11, 2008 at 12:28 pm
Its working now.
We had to change one of the UDFs, there was redundant code. I can't believe it has been running in on SQL 2000 for years without any problems.
Thanks, everyone for your support
Chandi
PS:
These SP, Views and UDF are very massive code, did not want to share the mess here
August 11, 2008 at 3:30 pm
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply