SP4 might slow down your single instance of sql server 2000
if Address Windowing Extensions(AWE) has been enabled on a single instance of sql server 2000, after applying SP4, you got to apply .2040 hotfix as well, otherwise your SQL server instance only uses 50% of the available physical memory that sucks:).
1) run the following script from SQL Query Analyzer to determine if AWE is enabled on the server :
sp_configure 'show advanced options', 1
go
reconfigure
go
sp_configure 'awe enabled'
go
If run_value is set to 1, AWE is enabled on the server.
2) BTW After applying the SP4, just make sure that it has been installed properly as I’ve heard that some broken installations of SP4 are not reported properly. Run the following query in Analyzer and you should get the following results.
Query in Query Analyzer:
SELECT SERVERPROPERTY('productversion')
SELECT SERVERPROPERTY('productlevel')
Result after applying SP4:
8.00.2039
SP4
Result after applying SP4 and its hotfix :
8.00.2040
SP4