Article ID: 297864
Article Last Modified on 1/12/2005
APPLIES TO
- Microsoft SQL Server 2000 Standard Edition
- Microsoft SQL Server 6.5 Standard Edition
- Microsoft SQL Server 7.0 Standard Edition
This article was previously published under Q297864
SUMMARY
SQL Server was completely rewritten between version 6.5 and version 7.0. The way data and statistics are stored and retrieved is vastly different in the newer versions than it was in SQL Server 6.5. Therefore, the "best practices" for coding and schema design are not the same for newer versions of SQL Server.
This article provides you with a quick overview of some of the issues to consider for coding and schema design between the two versions. This article does not cover every potential performance issue, but does point out some of the more common issues. For more information, refer to SQL Server Books Online, "Inside Microsoft SQL Server 7.0", or "Inside Microsoft SQL Server 2000" by MSPress, or to the list of Microsoft Knowledge Base articles shown in the "References" section of this article.
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:
311826 INF: Index Tuning Wizard Best Practices
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:
166967 INF: Proper SQL Server 6.5 Configuration Settings
319942 HOW TO: Determine Proper SQL Server Configuration Settings
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:
315512 INF: Considerations for Autogrow and Autoshrink Configuration
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:
243589 INF: Troubleshooting Slow-Running Queries on SQL Server 7.0 or Later
243586 INF: Troubleshooting Stored Procedure Recompilation
224587 HOW TO: Troubleshoot Application Performance with SQL Server
243588 HOW TO: Troubleshoot the Performance of Ad-Hoc Queries
224453 INF: Resolving SQL Server 7.0 or 2000 Blocking Problems
251004 INF: How to Monitor SQL Server 7.0 Blocking
271509 INF: How to Monitor SQL Server 2000 Blocking
Additional query words: performance slow fast speed response time
Keywords: kbinfo KB297864
