CPU High utilization -urgent help please

Question

Post reply

CPU High utilization -urgent help please

Davekahn

SSCommitted

Points: 1840
More actions
November 1, 2012 at 6:07 am

#264223

Hi All,
One of our Prod box running SQL2008R2 having CPU high utilization issue
I already checked Min/Max memory changed it but no effect(Min 20GB Max is 100GB)
SQL remain on 51GB
2. I used DBCC DROPCLEANBUFFERS, after couple of minutes cpu is back to 80%
3. DBCC MEMORYSTATUS is showing me locked allocated page which is taking 47GB memorym, I need to know do I need to disable this page locking, why it is suddenly locked the pages?, the server OS is W28
How to unlock this paging?
How to resolve it, kindly help me and tell me how to fix this issue ASAP
Thanks
Dave

Viewing 15 posts - 1 through 14 (of 14 total)

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

arnipetursson SSCertifiable Points: 6557 More actions · Answer 1

Have you checked for runaway queries with massive paralellism?

Davekahn SSCommitted Points: 1840 More actions · Answer 2

Below are the settings:

The Max degree of Parallelism is setup with "1"

Cost Threshold is "5"

Query Wait is "-1"

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

DROPCLEANBUFFERS is likely to worsen problems, not fix them, it throws the data cache out forcing SQL to read from the slower disk not memory until the cache has been repopulated. If it reduced CPU it's probably because SQL was instead waiting on IOs and generally not getting as much done as before.

High CPU - chapter 3 of http://www.simple-talk.com/books/sql-books/troubleshooting-sql-server-a-guide-for-the-accidental-dba/

p.s. Why is max degree of parallelism set to 1?

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Davekahn SSCommitted Points: 1840 More actions · Answer 4

Thanks Gail.

I don't know why it was setup as '1' because the person who build the server is gone

What exactly I suppose to do tonight ?

Just downloaded the book, Will be reading the chapter 3 right away

What else you suggest ?

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 5

Read chapter 3, work through it. If this is a 'Must Fix Now!' crisis, tell your boss to hire a consultant.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 6

qew420 (11/1/2012)
3. DBCC MEMORYSTATUS is showing me locked allocated page which is taking 47GB memorym, I need to know do I need to disable this page locking, why it is suddenly locked the pages?, the server OS is W28
How to unlock this paging?

Missed this first time I read.

Locked pages is when the service account has the locked pages in memory privilege. It prevents windows from paging SQL's buffer pool to disk (which would be a rather bad thing)

Hence you probably don't what to disable that locking, doing so could easily make matters far worse.

If SQL is using too much memory, reduce the max server memory setting (the book I previously recommended, chapter 4)

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Davekahn SSCommitted Points: 1840 More actions · Answer 7

======================================================

Locked pages is when the service account has the locked pages in memory privilege. It prevents windows from paging SQL's buffer pool to disk (which would be a rather bad thing)

Hence you probably don't what to disable that locking, doing so could easily make matters far worse.

If SQL is using too much memory, reduce the max server memory setting (the book I previously recommended, chapter 4)

====================================================

Gail: how to make this to work, how to fix the page locking as now 59GB is occupied by the lock

SQL Server is not eating any memory : it stays on 65GB but CPU consumption is high "75 to 88%"

Just to test: I stopped the reporting services and CPU was back to 2%

After turning on CPU went back to 85%

So I really need to know how to fix Paging issue, how to unlock the pages and release memory ?, Is this where I need Windows admin assistance or I login with service account and fix as a DBA, can you share the steps please

Also Why SQL is not CAChing Memory out of 100GB - it is only using 65 ? weather services are running or not the Memory count remains on 65GB

Which make me think that 59GB is locked and SQL is only using 5GB ?

Thanks

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 8

qew420 (11/1/2012)

======================================================

Locked pages is when the service account has the locked pages in memory privilege. It prevents windows from paging SQL's buffer pool to disk (which would be a rather bad thing)

Hence you probably don't what to disable that locking, doing so could easily make matters far worse.

If SQL is using too much memory, reduce the max server memory setting (the book I previously recommended, chapter 4)

====================================================

Gail: how to make this to work, how to fix the page locking as now 59GB is occupied by the lock /quote]

Read what I said. Locked pages is something you probably want, not something you need to fix. It is SQL locking its memory so that the OS can't page it out to disk. Paging SQL's memory out to disk slows things down a lot

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Davekahn SSCommitted Points: 1840 More actions · Answer 9

Still working on this issue

I changed the min and max but no effect

Thx for helping with Paging logic

Anything else you can recommend to resolve the issue

Thanks

Dave

Greg Edwards-268690 SSC-Insane Points: 20676 More actions · Answer 10

Is this a dedicated SQL Server?

Or are other applications running on it?

SQL 2008 or 2008 R2?

R2 has some improvements in how it handles memory.

Especially if you are running both SQL Server and SSAS on the same box.

As Gail points out, you might want to consider bringing someone in.

I assume this is production, which is usually not a good place to do too much experimentation.

Greg Edwards-268690 SSC-Insane Points: 20676 More actions · Answer 11

I see you are running R2.

The reason for asking about other applications - especially SSAS - is that SQL and SSAS will use all the memory you can give them.

Flushing to disk, as Gail pointed out, is I/O and CPU intensive.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 12

qew420 (11/2/2012)
Still working on this issue
I changed the min and max but no effect

Errr... please don't tell me you set min and max to the same value. Min isn't something that always needs changing.

Anything else you can recommend to resolve the issue

Did you work through the book chapter I recommended?

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Charles Hearn SSCrazy Points: 2680 More actions · Answer 13

qew420 (11/1/2012)
SQL Server is not eating any memory : it stays on 65GB but CPU consumption is high "75 to 88%"
Just to test: I stopped the reporting services and CPU was back to 2%
After turning on CPU went back to 85%

It sounds like SQL Server reporting services (SSRS) is causing high CPU utilization, not the SQL database engine. If this is the case then start looking at what reports are causing the high utilization (google "ssrs report utilization").

Davekahn SSCommitted Points: 1840 More actions · Answer 14

Thanks all and Gail issue fixed

I changed the Min and Max few times & finally I ended up restarting the server too

& no I didn't used same values for Min/Max

Dave