SQL2005SP2 Performance Can Degrade with OCS2007RTM

Today i was reading the Readme of OCS2007RTM. There was an interesting part in that overview.

SQL Server 2005 SP2 Performance Can Degrade Over Time
Issue: When running SQL 2005 SP2 on the back-end server of an Enterprise pool, over time performance can degrade and the total CPU usage increases. This is a known issue with SQL Server 2005 SP2. For more details, see http://support.microsoft.com/default.aspx/kb/927396

Workaround: Use one of the following two options:

Option 1

Use the following steps to add a SQL trace flag for T4618

1. Open SQL Server Configuration Manager: Point to Start, point to All Programs, point to Microsoft SQL Server 2005, point to Configuration Tools, and then click SQL Server Configuration Manager.

2. Select RTC SQL server instance in SQL Server 2005 Services, right click on the instance and then click Properties.

3. Click the Advanced Properties tab and Edit Startup Parameters.

4. Append ;-T4618 to the end of the Startup Parameters list (note that startup parameters are separated by semicolons).

Option 2

Or manually add the following registry value:

Name | Value

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\{0}\MSSQLServer\Parameters\SQLArgN | -T4618

(where N is the next SQL parameter number and {0} is the SQL instance name found under the following key below that maps to the RTC instance)

Name | Value

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\Instance Names\SQL

I did implement option 1 and the overall performance was significally better.