INF: Performance Considerations for an Upgrade from SQL Server 6.5 (297864)

MORE INFORMATION

Baseline

As with any test you perform, make sure you have a valid baseline for comparison. For example:

• Verify that the hardware, operating system, disk layout, RAID level, network, and other factors are identical. You cannot assume that minor differences can be ignored because they may have unexpected side effects.
  
  
• Consider the potential impact on response times from other applications that run on the server, client, or network or from services that are started on the server or client computers.
  
  
• Check the computer that is running SQL Server and the Microsoft Windows Event Viewer logs (application, system, and security) for any error messages or warnings that you might need to address.
  
  
• Use the SQL Server Profiler to find particular queries that seem troublesome and concentrate on tuning those queries.

Indexes

Often a different set of indexes is needed for optimal performance after an upgrade from SQL Server 6.5. Sometimes, the indexes that were present in SQL Server 6.5 provide acceptable performance in SQL Server 7.0 or SQL Server 2000; however; even in those cases it is likely that you can further improve performance if you alter the index strategy to take advantage of the way the newer versions of SQL Server work.

• The Index Tuning Wizard can give you a very good start about which indexes to add, modify, or remove. For more information about the Index Tuning Wizard, refer to the following:
  
  SQL Server Books Online
  
  Index Tuning Wizard for Microsoft SQL Server 7.0
  
  Index Tuning Wizard for Microsoft SQL Server 2000
  
  Troubleshooting the Index Tuning Wizard
  
  Designing an Index
  
• In addition to what the Index Tuning Wizard recommends, in most cases it is best to start with a clustered index on every table. There are occasional instances where this is not optimal, but it is extremely rare that the existence of a clustered index hurts performance and it usually helps. For additional information, click the article number below to view the article in the Microsoft Knowledge Base:

  297861 INF: Poor performance on a Heap

• As a side effect of the change in the way indexes are stored you may see an increase in non-clustered index size in the newer versions. If this causes more pages to be scanned in index seeks this could, under some circumstances, impair performance. This is not something you should be overly concerned about, but you may want to check it if you have already ruled out other causes.

For additional information, click the following article number to view the article in the Microsoft Knowledge Base:

Statistics

It is a good idea to run an UPDATE STATISTICS statement immediately after the version upgrade. There are situations where you may need to manually schedule UPDATE STATISTICS periodically. How often you need to execute the UPDATE STATISTICS statement is dependent upon the amount of data, data distribution, frequency, and type of modification activity, and so forth in your specific environment. Some things to keep in mind are:

• Even if auto-update statistics is on, it is only triggered at certain thresholds. Any time you make significant changes to the amount or distribution of your data, Microsoft recommends that you manually execute an UPDATE STATISTICS statement. For additional information, click the article number below to view the article in the Microsoft Knowledge Base:

  195565 INF: How SQL Server 7.0 and SQL Server 2000 Autostats Work

• Auto-update statistics always uses sampling; it never uses the WITH FULLSCAN option. The use of the WITH FULLSCAN option does require you to allocate additional time to execute the UPDATE STATISTICS statement; however, it may result in statistics that are more accurate if your data is not evenly distributed.

Configuration

Most configuration options will self-tune and when you change them it is often counter-productive. There are environments where rigorous testing has proven that some setting changes will improve performance, but that is not the case in most situations. Therefore, you should only change settings from their defaults after rigorous testing of how the change will affect your environment.

• In almost all environments, the priority boost option should be set OFF and the lightweight pooling option (also known as Fiber Mode) should be set OFF.
  
  
• Setting the max worker threads option to a value higher than the default of 255 can also be extremely detrimental to system performance and stability.
  
  

For more information about these settings, refer to the "Setting Configuration Options" and "sp_dboption" topics in SQL Server Books Online. You can also refer to the following article in the Microsoft Knowledge Base:

Additional Tips

• Remove all query hints (index, join, union, and so forth) that were added to code that was used in SQL Server 6.5. Due to the extensive optimizer changes in SQL Server 7.0, hints that improved performance in SQL Server 6.5 are not likely to help in SQL Server 7.0 or SQL Server 2000. As stated in the "OPTION Clause" topic in SQL Server Books Online:

  Because the query optimizer usually selects the best execution plan for a query, it is recommended that <join_hint>, <query_hint>, and <table_hint> be used only as a last resort by experienced database administrators.

• Owner qualify all object names in all queries and stored procedures. For additional information, click the article number below to view the article in the Microsoft Knowledge Base:

  243586 Troubleshooting Stored Procedure Recompilation

• Use the latest SQL Server service pack. For additional information, click the article numbers below to view the articles in the Microsoft Knowledge Base:

  290211 INF: How to Obtain the Latest SQL Server 2000 Service Pack

  274799 INF: How to Obtain Service Pack 3 for Microsoft SQL Server 7.0

• Avoid dynamic cursors (use the "least" possible cursor). For additional information, click the article number below to view the article in the Microsoft Knowledge Base:

  280406 PRB: Dynamic Cursor Infinite Loop When a Non-Unique Clustered Index Key Is Updated to an Equal or Larger Value

• Ensure that your disk drives are not compressed. Storing data or log files on compressed drives is not supported as documented in the "Physical Database Files and Filegroups" topic in SQL Server Books Online. For additional information about compressed drive support, click the article number below to view the article in the Microsoft Knowledge Base:

  231347 INF: SQL Server Databases Not Supported on Compressed Volumes

• Avoid use of the autoshrink option because it can lead to fragmentation as well as performance overhead.
  
  
• If you configure your databases to grow automatically (by using the autogrow option), set the growth increment to a value large enough so that it expands infrequently.
• Use the latest MDAC drivers on client computers. Newer drivers may have features or optimizations that were not present in prior versions. Refer to the "DLL Help Database" to determine which drivers you need to upgrade:
  
  DLL Help Database
  
  
• Set the sp_dbcmptlevel option to the appropriate version. For additional information, click the article number below to view the article in the Microsoft Knowledge Base:

  285165 INF: Attaching or Restoring a SQL Server 7.0 Database To SQL Server 2000 Does Not Change the Compatibility Mode

  
  

REFERENCES

For additional information, click the following article number to view the article in the Microsoft Knowledge Base: For additional information, visit the following Microsoft Web sites:How to Upgrade SQL Server 6.5 and 7.0 to SQL Server 2000
TechNet: How to Upgrade SQL Server 6.5 and 7.0 to SQL Server 2000
For additional information, click the article numbers below to view the articles in the Microsoft Knowledge Base: