Manage Learn to apply best practices and optimize your operations.

How to tune SQL Server query performance after upgrade

Still have slow queries after upgrading to SQL Server 2005? Learn how to performance tune your queries by rebuilding indexes and updating statistics.

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.

Next Steps

See how you can classify the complexity of your next SQL Server upgrade 

Dig Deeper on Microsoft SQL Server Performance Monitoring and Tuning

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.