Slow Queries After Updating to 2005

Question

Slow Queries After Updating to 2005

Luiz-458831

SSCommitted

Points: 1904
More actions
September 17, 2007 at 10:28 am

#153831

Hi Guys,

I just migrated from SQL2000 to SQL2005 and now some queries that used to run really fast are taking forever to run.
It seems that SQL2005 is choosing a different execution plan. I changed some of those queries to force SQL to choose another plan, but I can't do this everywhere.

Has somebody had the same problem, or know how to fix it ?
Thanks a lot,
Luiz.

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

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

alen teplitsky SSC-Dedicated Points: 30014 More actions · Answer 1

depends on the query

also look into completely dropping and recreating some indexes. we had one query where MS said they changed the optimizer because it caused a lot of problems in sql 2000

ianT SSCrazy Points: 2956 More actions · Answer 2

How correct are the statistics?

Microsoft recommends that you "Use the sp_updatestats stored procedure to update statistics in user-defined tables in SQL Server 2005 databases." (http://msdn2.microsoft.com/en-us/library/ms144267.aspx)

Bob Fazio SSChampion Points: 10794 More actions · Answer 3

I came across a FEW instances where performance was negatively impacted, but I also came across instances where it was improved. There are a lot of steps that are to be performed when you migrate. sp_updateusage, is one of them, statistics is the other that NEED TO BE UPDATED. Even if you are running it 80 compat mode.

Luiz-458831 SSCommitted Points: 1904 More actions · Answer 4

Thank you for all your answers.

I still have the same problem and I am trying to defrag some indexes, but for some reason they still are fragmented.

Here are some things that I have tried:

ALTER INDEX ALL ON healthc.tblSurveyQuestions REBUILD

ALTER INDEX ALL ON healthc.tblSurveyQuestions REORGANIZE

DBCC INDEXDEFRAG ('healthcomputingdb','healthc.tblSurveyQuestions')

DBCC DBREINDEX([healthc.tblSurveyQuestions], '', 100)

I even tried to drop an index and create it again.

I also run sp_updatestats, but the fragmentation levels haven't changed.

This is how I am checking the fragmentation:

SELECT Object_Name(a.object_id), a.index_id, name, avg_fragmentation_in_percent

FROM sys.dm_db_index_physical_stats (10, NULL, NULL, NULL, NULL) AS a

JOIN sys.indexes AS b ON a.object_id = b.object_id AND a.index_id = b.index_id

where Object_Name(a.object_id)='tblSurveyQuestions'

[10 is my DB_ID()]

Is it possible that I'm not getting an updated information? I mean, if I dropped a trigger and created again, shouldn't it be created with a fragmentation level iqual to zero, or close to zero ?

Any ideas of what may be happening?

Thanks a ton,

Luiz.

moosamca Right there with Babe Points: 773 More actions · Answer 5

Please run re-index and update statics after the migration. The will help to increase the performance.

Same thing is happend for me. But after 2 days it came to normal.

Luiz-458831 SSCommitted Points: 1904 More actions · Answer 6

What you mean "run reindex"?

About updating statics, I already run sp_updatestats. Am I supposed to do anything else?

Thanks.