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_updatestats procedure.
Dig Deeper on Microsoft SQL Server Performance Monitoring and Tuning
Troubleshoot long running stored procedures with SQL Server Profiler by adding events to find the command with high run time.continue reading
To avoid performance problems when running multiple instances of SQL Server 2005 with Exchange on Windows SBS, refer to these expert best practices.continue reading
Learn how to troubleshoot SQL Server 2005 temporary table performance problems when a stored procedure execution time is slower than usual.continue reading
Have a question for an expert?
Please add a title for your question
Get answers from a TechTarget expert on whatever's puzzling you.