How do I tune SQL Server query performance after an upgrade?
We migrated our SQL Server 2000 10 GB database to SQL Server 2005. Few queries run slower than when in SQL Server 2000. Earlier we were getting results in 10 secs now it’s taking about 30 minutes. The execution query plans in SQL Server 2000 and SQL 2005 are different. SQL 2000 is using indexes whereas SQL 2005 is using table scans of the table. Also the sequence of tables Nested Loop/Inner Join has changed. Since this is a live application, amending the query is difficult. What suggestions can you offer to tune query performance in SQL Server 2005? You need to troubleshoot the issue as a new problem, unrelated to the upgrade. Indexes need to be rebuilt and statistics updated. Sometimes after a SQL Server upgrade (from SQL 7 to SQL 2000, or SQL 2000 to SQL 2005) the indexes need to be rebuilt and the statistics updated. I always recommend using the UPDATE STATISTICS command instead of the sp_updatestats, as the UPDATE STATISTICS command gives you access to more options than the sp_upda
