Learn more about SQL Server tools

mssqltips logo

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Scripts          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories


Post SQL Server 2000 Service Pack 4 Performance Issues

By:   |   Last Updated: 2007-04-24   |   Comments   |   Related Tips: More > Upgrades and Migrations

My company has recently upgraded to SQL Server 2000 Service Pack 4 and we have experienced significant performance issues.  Overall our performance baseline has dropped significantly (more than 50%) immediately after the service pack installation.  We need to correct our performance issue, determine the problematic symptoms and the root cause in the service pack.  How should we do so?

In researching this issue further take the following steps to address the item:

  • Captured performance metrics to determine the worst performing queries
  • Researched the individual queries to determine the root cause
  • Determined that table scanning was occurring frequently in queries post SQL Server 2000 Service Pack 4
  • Determined the cause of the table scanning as implicit conversions of mismatched data types
  • Data types do not match between the embedded T-SQL in the front end code\stored procedures to the views and to the base tables
    • In some examples the implicit conversion and data type mismatch problem was related to the following:
    • The implicit conversion performed a table scan as opposed to using an index as was the case with SQL Server 2000 Service Pack 3

To resolve the immediate issue, take the following steps:

  • Identify the data types mismatches and correct them in logical groupings in order to properly test the application
  • Correct the data type mismatches starting from the tables, then the views, next move on to the stored procedures and embedded T-SQL code in the front end to ensure the data types are consistent
  • Review the query plans to ensure the expected indexes are being used following the code changes
  • Perform both functional and load testing on the new versions of the code
  • Perform some basic maintenance tasks

Next Steps

Last Updated: 2007-04-24

get scripts

next tip button

About the author
MSSQLTips author Jeremy Kadlec Since 2002, Jeremy Kadlec has delivered value to the global SQL Server community as an MSSQLTips.com co-founder and Edgewood Solutions SQL Server Consultant.

View all my tips
Related Resources

Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Email me updates 

Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.


Learn more about SQL Server tools