Change of Execution plan

Hi,

We migrated our database from SQL Server 2000 to Yukon last week. Now, when we run our application it has slowed down. We analyzed some stored procedure and they seems to have degarded. The execution plan has changed. Now, this looks like there's lot of work if we have to tune each query w.r.t the new execution plan. Our application has around 4000 stored procs. Is anyone aware of some generic pattern or solution such that these exection plans problem can resolved? Also, does the new execution plan ensure that the once we tune stored procs will perform better than SQL Server 2000.

Need help on this, otherwise it seems we might have to move back to 2000.

Thanks in Advance
Ritesh

Did you update statistics after you upgraded? It seems unlikely to me that SQL 2005 would provide a less efficient execution plan than SQL 2000, given accurate statistics data. I'd update statistics and then recompile all the stored procedures first. I'd also be interested to see what you had to tune in the stored procedure you changed to accommodate SQL 2005.|||

Yes we do updated the statistics and recomplied the SP. In few queries we changed Not Exists to Left Outer Join and this works well. I don't say that SQL Server 2005 comes up with less efficient query plan, may be it comes up with much better execution however it looks like to make queries work w.r.t to those plans we might need to do some query tuning.

Change of Execution plan

Hi,

We migrated our database from SQL Server 2000 to Yukon last week. Now, when we run our application it has slowed down. We analyzed some stored procedure and they seems to have degarded. The execution plan has changed. Now, this looks like there's lot of work if we have to tune each query w.r.t the new execution plan. Our application has around 4000 stored procs. Is anyone aware of some generic pattern or solution such that these exection plans problem can resolved? Also, does the new execution plan ensure that the once we tune stored procs will perform better than SQL Server 2000.

Need help on this, otherwise it seems we might have to move back to 2000.

Thanks in Advance
Ritesh

Did you update statistics after you upgraded? It seems unlikely to me that SQL 2005 would provide a less efficient execution plan than SQL 2000, given accurate statistics data. I'd update statistics and then recompile all the stored procedures first. I'd also be interested to see what you had to tune in the stored procedure you changed to accommodate SQL 2005.|||

Yes we do updated the statistics and recomplied the SP. In few queries we changed Not Exists to Left Outer Join and this works well. I don't say that SQL Server 2005 comes up with less efficient query plan, may be it comes up with much better execution however it looks like to make queries work w.r.t to those plans we might need to do some query tuning.