CPU 100%

I have written an application that needs to constantly communicate with SQL Server . The application works fine but I am having issues with the SQL server spiking to 100% CPU utilisation constanly.

I have no triggers or cursors in my database. All my calls to the database are via stored procedures and almost all the code in the stored procedures are Clustered Index Seeks retuning maximum 1 or 2 rows.

example :

Code Snippet

IF NOT EXISTS(Select PrimaryKey from TableName where (PrimaryKey = 'Value'))

BEGIN

INSERT INTO TableName (PrimaryKey,Column1,Column2) Values (@.PrimaryKey,@.Column1,@.Column2)

END

ELSE
BEGIN

UPDATE TableName SET Column1 = @.Column1, Column2 = @.Column2 WHERE PrimaryKey = @.PrimaryKey

END

I ran through the profiler and there were no errors reported and all the stats show that the stored procedures take maximum 1 second to execute. However the CPU on the box is constanly running at 100%. I am not sure what I need to do.

I use full text indexing but I at the moment that is not turned on so it cannot contribute to the CPU usage.

Some tables have 100,000 rows and other have around a million rows. I noticed the queres to the tables that have millions of rows are the main tables causing the performance to go to 100%.

The SQL Server box that I am testing on has around 2 GB RAM ,Pentium(4) 3.2GHz. I will appreciate any advise!!

Start the Activity Monitor and see which process (in SQL Server) hold the processor time (column CPU). Maybe this is not a SQL Server problem.

Jens K. Suessmeyer

http://www.sqlserver2005.de