CPU "sleeping" when executing queries

Question

CPU "sleeping" when executing queries

Viewing 6 posts - 16 through 20 (of 20 total)

You must be logged in to reply to this topic. Login to reply

steve ledek SSC Enthusiast Points: 164 More actions · Answer 1

The "Logical Disk: Avg. Disk sec/Transfer" never exceeds 0.004 and everything seems to be ok on this side, but i will probably check the disk with specific diagnostic tools.

by the way, we also tried it with and without hyper thread >> no difference. the CPU never shows more than 1% in this case. When we use for example disk intensive operations (disk defragmenting), the overall performance is absolutely ok. So i really don't know if it's really connected to the disks.

pottsgf Grasshopper Points: 10 More actions · Answer 2

These queries don't look either complex or CPU intensive.

Focus on configuration, CPU, memory, disk i/o. What about the network?

Are queries executed in QA on server console or across a network?

If across a network could queries execute OK but results not return due to bandwidth or protocol problem?

steve ledek SSC Enthusiast Points: 164 More actions · Answer 3

the queries are executed on a server console, so it should not be a network problem...

Mark Coleman-274922 Newbie Points: 3 More actions · Answer 4

Hi

we have exactly the same problem, just upgraded from a Dell single processor P3 Server to a Dell 2800 with twin xenon processors and 2 gb ram. The complex procedures execute within 2-3 seconds and can return over 30,000 records on the old server but with the new one they take anything upto 30 seconds to complete.

Processor activity never gets above 1% !!

Any help would be appreciated

Mark

Jhon-270712 Grasshopper Points: 24 More actions · Answer 5

Yep, they are indexed. and statistics also updated. The DBA already did all that.. Any more idea?

peterhe SSChampion Points: 11469 More actions · Answer 6

You are right to set the boost to default. But for paralisam, you can try to turn it off (by default SQL server uses the actual number of available in paralell plans). You can add the MAXDOP hint in that query or run the following code to disable paralell plan for all statements:

USE master

GO

EXEC sp_configure 'show advanced option', '1'

RECONFIGURE

EXEC sp_configure 'max degree of parallelism',1

RECONFIGURE WITH OVERRIDE

EXEC sp_configure 'show advanced option', '0'

RECONFIGURE

GO

Did you check the server pack of the SQL server?

Did you try to restart SQL server service?