sqlservr.exe uses 100% CPU - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

sqlservr.exe uses 100% CPU

Author

Topic

bjavaid
Starting Member

4 Posts

Posted - 2009-03-09 : 06:20:00

I have installed SQL server 2005 (standard edition) with SP2 on a Windows Server 2003 with SP2 machine. It has a 4 GB ram and 8 processors.

Sometimes for query execution the CPU utilization goes 100% and never comes back until I restart the SQL server.

If I execute the stored procedure it works well and returns result in 10-30 seconds (around 5 million records).

When I execute the stored procedure using SQLhelper and .net code the SQL server is unable to return the complete results and query timeouts.

Any prompt help will be appreciable.

mfemenel
Professor Frink

1421 Posts

Posted - 2009-03-09 : 09:36:33

My first red flag here is that your stored procedure is returnin 5million records. Why on earth would you want to do this? Also, why return 5 million records to an application? Who is going to actually scroll through that many. Help me understand what this procedure does first, I think there are some serious design issues to consider.

Mike
"oh, that monkey is going to pay"

bjavaid
Starting Member

4 Posts

Posted - 2009-03-09 : 11:36:29

I need to generate the report from the data fields that i am getting using SP.

I process all records in the dot net code and display the report results accordingly.

What other alternates i can use to calculate the report.

yosiasz
Master Smack Fu Yak Hacker

1635 Posts

Posted - 2009-03-09 : 12:25:57

show us the SP

bjavaid
Starting Member

4 Posts

Posted - 2009-03-11 : 04:27:36

CREATE PROCEDURE [dbo].[GetReport]
(
@whereClause varchar(2000),
@orderBy varchar(250) = NULL
)
AS
DECLARE @sqlWhere AS varchar(2000)
SET @sqlWhere = ''
IF @whereClause <> ''
SET @sqlWhere = ' WHERE ' + @whereClause
IF @orderBy IS NOT NULL AND LEN(@orderBy) > 0
SET @sqlWhere = @sqlWhere + ' ORDER BY ' + @orderBy

SET NOCOUNT ON

exec('WITH Report AS
(
SELECT field1, field2, field3, field4, field5, field6, field7, field8, field9, field10, field11,
CASE field12 WHEN 3 THEN CAST(1 AS BIT) ELSE CAST(0 AS BIT)
END, CASE field13 WHEN 3 THEN CAST(1 AS BIT)
ELSE CAST(0 AS BIT) END,
CASE field14 WHEN 3 THEN CAST(1 AS BIT) ELSE CAST(0 AS BIT)
END, CASE field15 WHEN 3 THEN CAST(1 AS BIT)
ELSE CAST(0 AS BIT) END, field16, field17,
field18, field19, 0, field18, CAST(0 AS BIT), field19 FROM Table1 INNER JOIN
Table2 ON Table1FK = Table2PK INNER JOIN
Table3 ON Table2FK = Table3PK LEFT OUTER JOIN
Table4 ON Table1PK = Table4FK LEFT OUTER JOIN
Table5 ON Table1PK = Table5PK) SELECT * FROM Report' + @sqlWhere)

RETURN @@Error

yosiasz
Master Smack Fu Yak Hacker

1635 Posts

Posted - 2009-03-11 : 14:06:48

1. Do you have indexes on all those PKs and FKs?
2. Why are you casting as BIT?

instead of exec('WITH Report....
do Print @sqlWhere and show us what it prints

bjavaid
Starting Member

4 Posts

Posted - 2009-03-12 : 07:19:19

What other ways that i can use to generate the reports for 5-10 million records.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources