Upgrade to SQL Server 2000 SP1 for Improved Performance

All service packs includes numerous fixes to resolve various problems that Microsoft has found since the initial release of the product, and the recent release of SP1 for SQL Server 2000 is no different.

What many people may not realize is that many of the fixes included with service packs affect performance. So not only should you upgrade to SP1 for the benefit of the included bug fixes, but additionally in order to gain the performance benefits it provides.

No, you probably won't see drastic performance boosts, but like much of SQL Server performance tuning, most performance boosts are incremental. But fortunately, all of the small boost are additive, and in the long run, can add up to a speedier SQL Server.

Below is a summary of the key fixes made in SP1 that affect performance.

Fixes for SQL Server 2000 in SP1

• ServerNet II Library Does Not Display if Giganet Driver is Not Available
• How to Use sp_scriptdynamicupdproc to Improve the Performance of Update Custom Procedures in SQL Server 2000 Transactional Replication
• Affinity Mask Option in sp_configure Does Not Support 32 Processors
• Running Stack Dumper Causes Memory Leak of Virtual Bytes
• Optimizer Slow to Generate Query Plan for Complex Queries that have Many Joins and Semi-Joins
• Reading Perflib Registry Values Terminates SQL Server Startup with Fatal Exception
• Text Pasted in Query Analyzer Result Window Is Not Visible
• Suboptimal Execution Plan on an Indirect UPDATE to an Indexed View
• Linked Server Query with Hyphen in LIKE Clause May Run Slowly
• Update Query Returns Error 1903 if Temporary Composite Index Over 900 Bytes
• Complex Distinct or Group By Query Can Return Unexpected Results with Parallel Execution Plan
• Creating Indexes in Parallel May Fail with Deadlock on Sysindexes
• SQL Server 2000 Takes Much Longer to Compile the Query Against Local Partitioned View Than SQL Server 7.0
• Interleaving Prepare And Adhoc Execute of Query May Cause Recompile Loop
• Parallel Index Creation on Large SMP Hardware Can Cause Handled Access Violation
• Opening the Database Folder in SQL Server Enterprise Manager 2000 Takes a Long Time
• DBCC DBREINDEX Does Not Keep Original Fillfactor
• Suboptimal Performance Using a Cursor to Update a Heap Involved in an Indexed View
• Instead of Triggers that Use Work Tables Cause a Memory Leak
• Parallel Plan for Index Creation of Non-Clustered Index May Result in Use of Pages That are not Allocated
• Mssearch.exe Memory Leak Occurs when Results are not Completely Processed
• Microsoft Search Service May Cause 100% CPU Usage if BUILTIN\Administrators Login Is Removed
• Modification Queries May Take Too Long to Compile if Halloween Protection is Required for Multiple Tables

Fixes in SQL Server Analysis Services in SP1

• Analysis Services Cubes Restored From Archive May Show #ERR When Browsed
• Extreme Memory Usage When Adding Many Security Roles
• Query Performance Slower When Client Locale Differs from Server Locale
• Analysis Server HTTPS Connections Slower Than HTTP and TCP/IP Connections

How to Find Out More About SP1

To find out more about the various bug fixes in SP1.

To read the ReadMe file that comes with SP1.

To learn how to download SP1.