Restored from SQL 2000 to 2005, now much slower! | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

Restored from SQL 2000 to 2005, now much slower!

Currently my company has a SQL Server 2000 database hosted at an external location, and we just purchased a hosting plan elsewhere that has SQL Server 2005. I backed up the SQL Server 2000 database and restored it on the 2005 host. However, the problem is that now everything seems to be much slower. A query which on the 2000 server took 0.4 seconds to complete takes 5-6 seconds on the 2005 server! I am a programmer, not a DB Admin. I know how to write queries but I’m not sure whats going on here. I emailed the host and their support says that when doing a restore that indexes (and clustered indexes?) aren’t usually rebuilt, and also there can be fragmentation. They say they defragmented the queries but the query is still running very slowly. What can I do / check to see whats going on? Thanks!
After upgrade run the update statistics against all user tables…
If possible run the dbcc dbreindex… Did you guys changed the compatability to 90?
Run the sql trace and see what is cuasing the slowness?
Woah woah woah, take it easy on me <img src=’/community/emoticons/emotion-1.gif’ alt=’:)‘ /><br /><br />I’m not a DB admin so most of what you’re saying is like gibberish to me. I am *very* computer literate though, so if you could just give me some pointers on how I can find and run these tests / instructions you are giving, that would be great. <br /><br />Also keep in mind that this is a hosted database so I may not have complete admin privileges. Thanks!
OK to update the stats run the following command…
Open the SQL SERVER MANAGEMENT STUDION (SSMS) then open the query window and connect to your server then run the following command… exec master..sp_msforeachdb ‘?..sp_updatestats ‘ If you can efford the downtime… depends on the size of the db…
run the following script …
declare @dbid int
select @dbid = max(database_id) from sys.databases
select @dbid while @dbid >= 1
DECLARE @TableName varchar(255) DECLARE TableCursor CURSOR FOR
SELECT Table_schema+’.’+table_name FROM information_schema.tables
WHERE table_type = ‘base table’ OPEN TableCursor FETCH NEXT FROM TableCursor INTO @TableName
DBCC DBREINDEX(@TableName,’ ‘,90)
FETCH NEXT FROM TableCursor INTO @TableName
END CLOSE TableCursor DEALLOCATE TableCursor
select @dbid = @dbid -1
end MohammedU.
Mohammed, First, thanks so much for helping me with this! Here is what I’ve done so far: I figured out how to run these DBCC queries. I ran DBCC SHOWCONTIG WITH TABLERESULTS, ALL_INDEXES and I saw that a lot of the tables / indexes are fragmented (Some LogicalFragmentation / ExtentFragmentation over 60. I’m not sure what difference between the two is). I ran DBCC INDEXDEFRAG and REINDEX on a few of these indexes, which seems to have reduced their fragmentation. I tried to run sp_updatestats but I am getting a "you do not have appropriate permissions" error. It seems like they will not allow me to run this query, unless I’m supposed to use this in a different way. I tried your syntax as well as a simple EXEC sp_updatestats, both with same result. Right now I am running that big query you wrote out for me. Currently its at 8 minutes execution time, and running. I’ll keep you updated.
In your case fragmentation is not the first issue…
When you restore all indexes and statiscs will be copies but in your case you upgraded your dbs from 200 to 2005…
so to get the better perfomance it is advisable to update the statistics so the 2005 query optimizer can utilize in better way… To run the update stats you have to be sysadmin or db owner… In 2005 EE, you can rebuild your indexes online but it takes time and have to be EE..
So if you can efford downtime run the reindex script irrespective of fragmentation so that indexes will be rebuild and statistics updated…
I believe when you rebuild indexes statistics updated with FULLSCAN… To run reindex you have to sysadmin or db owner or ddl admin… May be you have ddl admin rights.. MohammedU.
OK, I ran that big reindex query. It took about 22 minutes but it finished. I reran my query but it is still taking about same amount of time <img src=’/community/emoticons/emotion-6.gif’ alt=’:(‘ /><br /><br />Unfortunately, as I mentioned this is a hosted database so I cannot get any kind of admin privileges, so I can’t run sp_updatestats. <br /><br />What else can I do? There has to be some reason why this query is running so much slower on this 2005 server than on the 2000 server. Any ideas?
Did you change the compatability to 90? If not change it…if you guys tested your application for 2005.
Recomplile all procedures…. Check the query plan and i/o stats in 2000 and 2005…by executing in QA.
Is this remote server? If yes are you using remote desktop? Is HW and memory same or better than 2000?
You nee sql trace/profiler and perfmon to troubleshoot…
Just a small update. I ran DBCC SHOWCONTIG WITH TABLERESULTS, ALL_INDEXES on the 2000 server and on the 2005 server and looked for differences in the two. There are a few major differences i noticed. This is for a sample table, which is a very big part of the query I am trying to run: 2000
ScanDensity: 13.381
AveragePageDensity: 50.3828
AverageFreeBytes: 7011
BestCount: 249
ActualCount: 1850 2005
ScanDensity: 100
AveragePageDensity: 90.295
AverageFreeBytes: 785
BestCount: 138
ActualCount: 138
Does this tell you anything?
It is telling you that fragmentation gone in 2005…
Because you ran dbcc dbreindex against 2005 db, so it removed fragementation…
Unfortunately I can not connect to the hosted database using Profiler because I do not have enough permissions. I can use the query analyzer however I’m not sure how that helps me, to be honest. I get a diagram of the flow of the query but I have absolutely no idea how to read this. I can save it as a .sqlplan file, if that helps.<br /><br />Please let me know <img src=’/community/emoticons/emotion-6.gif’ alt=’:(‘ />
i suggest following the steps outlined
instead of blindly stabbing in the dark at the usual suspects