Post SQL Server 2000 Service Pack 4 Performance Issues

By: Jeremy Kadlec   |   Updated: 2007-04-24   |   Comments   |   Related: > Upgrades and Migrations

Problem

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?

Solution

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:
    • NUMERIC to DECIMAL
    • NUMERIC to INTEGER
    • VARCHAR to INTEGER
  • 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
  • UPDATE STATISTICS
  • sp_recompile
  • DBCC UPDATEUSAGE

Next Steps

• Ensure that the data types are consistent and follow database design best practices (primary keys, referential integrity, data type selection, etc.) for your applications as this release with Service Pack 4 was considered a bug fix from Microsoft
• Conduct load testing to ensure the application will perform as expected following the upgrade
  • This would be a proactive approach to identify potential issues prior to the production release
• Review the release notes and service pack related information from Microsoft
  • A list of the bugs that are fixed in SQL Server 2000 Service Pack 4
  • Microsoft SQL Server 2000 Service Pack 4
  • Cumlative Hotfix for SQL Server 2000 Service Pack 4 - Build 2187 for x86 and x64 Based Systems
• Contact your vendors to ensure they have tested their application with the new release
• Build a rollback plan into the equation in case you have an issue
  • Be aware of rollback issues with SQL Server 2005
    • SQL Server 2005 - Service Pack 2
    • Service Pack 2 Uninstall Not Possible for SQL Server 2005

About the author

Jeremy Kadlec is a Co-Founder, Editor and Author at MSSQLTips.com with more than 300 contributions. He is also the CTO @ Edgewood Solutions and a six-time SQL Server MVP. Jeremy brings 20+ years of SQL Server DBA and Developer experience to the community after earning a bachelor's degree from SSU and master's from UMBC.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips