April 20, 2012 at 2:29 pm
I'm in the process of upgrading a sql 2000 environment to 2008 R2. I now have 2 servers, 1 with each version of sql. When I run a sproc on the 2000 version it creates a query plan that is different than the query plan in 2008. Sql 2000 runs the query in about 45 minutes while the 2008 R2 version will eventually fail after eating up over 150gb of tempdb usually around 6 or 7 hours. I have rebuilt indexes and made sure all indexes are correct. The code that is running is a select statement that queries 4 different views. Looking at the plan, the difference is that the 2008 code does a non-clustered index scan on what it claims is a table with 99,443,300 rows. It then takes this and does a hash match inner join which constitutes 70% of the query plan cost. The sql 2000 version doesn't do this step at all. In fact it scans a completely different table. How can this be? Are the differences in the query engine that different? I would assume that 2008 would be faster not slower. Any help would be appreciated.
April 20, 2012 at 2:43 pm
There were massive changes in the optimiser between 2000 and 2005.
First thing. Update your statistics. SQL 2005+ doesn't effectively use the SQL 2000 format statistics.
Run an UPDATE STATISTICS <table name> WITH FULLSCAN on every table in the DB if you haven't already done so.
If that doesn't help, post query and exec plan from SQL 2008. Regressions are uncommon but they do happen and it's usually as a result of questionable SQL.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
April 20, 2012 at 2:44 pm
What steps did you take when you moved the databases from 2000 to 2008 R2?
Set the compatibility to 10?
DBCC CHECKDB?
DBCC UPDATEUSAGE?
Rebuild Indexes/statistics?
April 20, 2012 at 2:45 pm
And what Gail said 🙂
April 23, 2012 at 9:13 am
the database was restored and upgraded to 2008 compatibility level. Indexes were rebuilt so my understanding is that the statistics are updated as well.
April 23, 2012 at 9:26 am
That'll take care of the index stats, but not the column stats.
Update statistics on all tables with fullscan. If the problem persists, post query and execution plan.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply