July 11, 2011 at 9:48 am
We are upgrading a server from SQL Server 2000 to SQL Server 2008 R2.
The database is like legacy databases that most of you have seen, old, neglected, and with numerous design problems. It is used by an application that is based on an old version of PeopleSoft software. For that reason, we have to run the database in compatibility mode 80.
So far, in testing, everything works. However, we are having a problem with some query plans being radically different under 2K8 than they are under 2000, and not in a good way. I have one sample query, the one where the problem was first identified, that runs in 25 milliseconds on 2000, but that takes more than 20 seconds on the (unloaded) new server.
The new server was prepared by restoring a backup of the production database. Stats were updated.
The problem query is not overly complex - it pulls data from 9 tables. However, due to some constraint with the application it talks to, it is written in the old syntax against views that are written in the old syntax. The query on SQL Server 2000 has about 20 logical reads. On SQL Server 2008, there are more than 4 million.
Things I have tried:
Recalculating stats.
Rewriting the query in new syntax.
Rewriting problem portions of the views in new syntax.
Dropping all auto-created stats on tables touched by the query. Autostats is on, so stats are recreated when needed.
Rebuilding indexes on all tables touched by the query, both on and off line.
Writing similar test queries of increasing complexity to see if I could see where the plan started to go bad.
Simple queries against these views produce identical plans on 2000 and 2008.
I have used Google till it screamed, and haven't found a solution yet. Is there anybody that has any 1. experience like this or 2. brilliant ideas?
July 12, 2011 at 1:55 am
Change the compatibility mode to 100 and rebuild the indexes and update the stats also,did you ?
Regards,
Syed Jahanzaib Bin Hassan
BSCS | MCTS | MCITP | OCA | OCP | OCE | SCJP | IBMCDBA
My Blog
www.aureus-salah.com
July 12, 2011 at 7:04 am
Syed Jahanzaib Bin hassan (7/12/2011)
Change the compatibility mode to 100 and rebuild the indexes and update the stats also,did you ?
I haven't tried that - change compatibility to 100, indexes/runstats, then change back to 80 you mean? That might be worth a try.
July 12, 2011 at 9:11 am
Dean Cochrane (7/12/2011)
Syed Jahanzaib Bin hassan (7/12/2011)
Change the compatibility mode to 100 and rebuild the indexes and update the stats also,did you ?I haven't tried that - change compatibility to 100, indexes/runstats, then change back to 80 you mean? That might be worth a try.
If you're planning to stay on 80 mode then don't bother changing to 100 and then changing back. The point is that when you upgrade, even if you stay in the lower compat mode, the structure of the database files will have changed as part of the upgrade process. It is a good idea to rebuild your indexes after the upgrade, or at the very least update all your statistics with FULLSCAN, to make sure they are healthy in the new system and any plans.
If you're still having trouble after updating your indexes/stats please post the actual execution plans from the queries (before and after from both systems if possible).
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply