asked
Upgrading database from 2000 to 2005 performance degradation
I have restored a SQL Server 2000 database to SQL Server 2005 and the performance of a core set of queries is performing orders of magnitude slower. I have spent several days trying to optimize the queries, but they are very complicated with stored procedures calling stored procedures calling nested views seven to eight levels deep. It's a mess, but I'm trying to optimize it without changing much code - changing the code may take several weeks (which I don't have). If I restore the database and don't change any database settings or perform any maintenance, the query performance is fine. If I rebuild the indexes fully or with an online index rebuild, query performance then degrades badly (even after multiple runs to ensure the data and index pages are in the buffer pool). Any suggestions? I'm pulling my hair out. Instance settings are the same in terms of memory allocated, maxdop, and the server hardware is identical - both instances are on the same machine. I have tried: - Setting MaxDop to 1 - Reducing processor affinity - Changing table variables to temp tables and moving them to the top of the procs - Looked at the query plans, but they are so large my client machine runs out of memory Help! **EDIT** I have a test machine with three named instances on it, SQL 2000, 2005, and 2008 R2. I backed up SQL 2000 and restored the database onto the 2005 named instance. At this point no maintenance was performed. I only have one instance running at a time so that results are not influenced by other running instances. When I run my queries on SQL 2005 the performance is fine. However, when I run our maintenance jobs, both the ONLINE INDEX REBUILD and DBREINDEX jobs cause massive degradation in performance to the point that the application is unusable. I also tried DBCC UPDATEUSAGE and UPDATE STATS with FULL SCAN but performance remained terrible until I restored the database from 2000 again. I am currently working through sections of the code to try and find badly performing areas, by isolating views and flattening some views into direct calls to base tables, but this task is enormous and I'm not sure I will meet the expectations of the people calling on me to do this task. I'm hoping to find something simple that is causing the optimizer to use a differentr plan in SQL 2005 than SQL 2000.
The points you made at the end of the question are worth considering here. **The query plans are so large that you run out of memory before they are fully viewable** This sounds like an awful state to be in. Multiple SPs and multi-level views are another flashing warning sign for me. I know you wrote that a redesign is not on the cards, but you will find it hard to improve a system if it is built that way. 1. The index rebuilds may make performance bad due to a number of reasons: 1. A different execution plan is probably being chosen. This could cause a plan which suddenly runs different to before. 2. The index rebuild could have caused the database and log files to grow dramatically. If you have the database set to auto-shrink (would then happen when the rebuild has finished), this may lead to some *severe* internal file fragmentation. **Changing temp variables to temp tables and moving them to the top of the query** This can change the behaviour of a query dramatically. A temp variable does not have any statistics where a temp table does. This means that the query optimiser has a better chance of making assumptions about data spread in a temp table than a temp variable. If you are working with a lot of data in this temporary construct, you could gain a lot through using a temp table. A good idea would be to try and measure the performance of each section of the worst performing procedure. You can then narrow down the problem a little further. It takes a bit of digging, but removes false positives. The dymanic management views can be useful here, you can query them like a table and get a lot of interesting information about queries that have been run. Take a look at the information on [SQLServerPedia - DM Objects][1], you can find some examples of finding the worst queries on a system. You never really mentioned how this upgrade was made. Are you restoring onto a completely new machine, or is the instance on the same machine as before and the upgrade was done in-place? If you are on a new machine, you are not comparing apples to apples - so it will be difficult to draw comparisons. I don't think you will find a silver bullet - provide us with a few more details and we shall see where we get to. [1]:
http://sqlserverpedia.com/wiki/DM_Objects_-_Sys.dm_exec_query_stats
@Keymoo - the part that I think you should look at is the database internal fragmentation after the index rebuilds. Take a look at the file sizes and filegrowth settings. I will guess they are not optimal. Try presizing your database files so they have space to breathe - then do the index maintenance. Remember an index rebuild will need space to copy the entire index while rebuilding, so an index that is 1GB will need an additional 1GB to store the index again for the rebuild. As this is a migration and the database is not in use, consider putting the DB in simple recovery (if it isn't already) whilst doing the maintenance. You have the chance now to do some spring cleaning, so rebuild all indexes so fragmentation is completely blitzed. Check if foreign keys are untrusted (`SELECT * FROM sys.foreign_keys FK WHERE is_not_trusted=1`) and get those checked. Check index fillfactors too to see if those still make sense. All these things can greatly impact performance. Also check that the 2005 Instance is setup properly in terms of memory and cpu allocation.
Check the index fragmentation with select * from sys.dm_db_index_physical_stats(yourdbid,null,null,null,'detailed'); If you try to do an online rebuild of indexes with allow\_page\_locks = OFF (this setting didn't exist in SQL 2000) and are using parallellism (MAXDOP <>1 ), you will get even more fragmented indexes. Read more at [
http://www.sqlservice.se/sql-server-performance/rebuild-this-time-it-fragmented-even-more/][1] [1]:
http://www.sqlservice.se/sql-server-performance/rebuild-this-time-it-fragmented-even-more/
