Sunday, October 11, 2009

CPU usage reaching 100% with SP3 on SQL Server 2005

Symptom:
You install an instance of Microsoft SQL Server 2005 on a computer that has many processors. You upgrade to SQL Server 2005 Service Pack 3 (SP3) or to Microsoft SQL Server 2008. You run a query that uses the Like operator in the WHERE clause or that uses a string comparison function, such as the CharIndex() function. When you run the query many times on multiple connections at the same time, the CPU usage reaches 100 percent, and the server performance decreases significantly.

Then refer the Microsoft article and apply the cumulative update

No Surface Area Configuration (SAC) Tool in SQL Server 2008

In SQL Server 2008, Surface Area Configuration (SAC) Tool has been added to the deprecated feature list.

So how to do the functionality of SAC configuring Service, Connection and features?

1. For Instance and Database Engine Features:
Select Facets on Properties of Instance / Database on SQL Server Management Studio.

2.For Connections, Protocols and StartUp options:
Use SQL Server Configuration Manager

3.SSAS Features:
It can be done at Property Settings

4.SSRS Features
It can be done by editing the RSReportServer.config file or by using the facets feature