Ask the Expert

How to tune SQL Server query performance after 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?

    Requires Free Membership to View

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.

This was first published in August 2007

There are Comments. Add yours.

 
TIP: Want to include a code block in your comment? Use <pre> or <code> tags around the desired text. Ex: <code>insert code</code>

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
Sort by: OldestNewest

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to: