November 1, 2012 at 6:07 am
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
November 1, 2012 at 10:12 am
Have you checked for runaway queries with massive paralellism?
November 1, 2012 at 11:14 am
Below are the settings:
The Max degree of Parallelism is setup with "1"
Cost Threshold is "5"
Query Wait is "-1"
November 1, 2012 at 11:31 am
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
November 1, 2012 at 11:52 am
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 ?
November 1, 2012 at 12:03 pm
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
November 1, 2012 at 12:07 pm
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 W28How 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
November 1, 2012 at 1:42 pm
======================================================
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
November 1, 2012 at 1:55 pm
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
November 2, 2012 at 5:08 am
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
November 2, 2012 at 6:36 am
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.
November 2, 2012 at 6:41 am
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.
November 2, 2012 at 7:11 am
qew420 (11/2/2012)
Still working on this issueI 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
November 2, 2012 at 11:22 am
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").
November 3, 2012 at 11:30 pm
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
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply