We help IT Professionals succeed at work.

We've partnered with Certified Experts, Carl Webster and Richard Faulkner, to bring you a podcast all about Citrix Workspace, moving to the cloud, and analytics & intelligence. Episode 2 coming soon!Listen Now

x

dbcc take ups all may buffer cache

david_32
david_32 asked
on
Medium Priority
309 Views
Last Modified: 2012-05-06
Hi when I run a dbcc it seems to take up all my buffer cache. That is to say after dbcc my ring buffers report a %100 Utilization, target and total memory are the same (before hand the total is lower). How can I stop the dbcc using all the buffer cache?
Comment
Watch Question

Mark WillsTopic Advisor, Page Editor
CERTIFIED EXPERT
Distinguished Expert 2018

Commented:
What dbcc commands are you running and how frequently ? Some of them can use a lot of resource, some require exclusive access...
Raja Jegan RSQL Server DBA & Architect, EE Solution Guide
CERTIFIED EXPERT
Awarded 2009
Distinguished Expert 2019

Commented:
Kindly provide me the command which you exactly used.
Mark WillsTopic Advisor, Page Editor
CERTIFIED EXPERT
Distinguished Expert 2018

Commented:
@rrjegan : it should be "us", not just "me", we are all trying to help :)
Raja Jegan RSQL Server DBA & Architect, EE Solution Guide
CERTIFIED EXPERT
Awarded 2009
Distinguished Expert 2019

Commented:
If you check the time gap between our posts, you may get why I meant me over there..
When I started typing, there was no posts, that's why..

We are Experts and we have something in common...
Cheers..
Mark WillsTopic Advisor, Page Editor
CERTIFIED EXPERT
Distinguished Expert 2018

Commented:
@rrjegan17: Didn't notice - sorry about that - yeah, my lack of refreshes covers hours, at least yours was a minute - seconds maybe... BTW, how are you enjoying EE - see that you are still tracking very well...
Raja Jegan RSQL Server DBA & Architect, EE Solution Guide
CERTIFIED EXPERT
Awarded 2009
Distinguished Expert 2019

Commented:
Superb..
Enjoying it..
And by helping others I feel I am replenishing my knowledge gathered till now..
By the way, just one and half months old in EE, I am not aware whether any private chats between experts is available or not.. Can you guide me on that if you are aware...

david_32,
    Kindly tolerate for asking these things in your question..

Author

Commented:
dbcc checkdb('databasename')  Is what I am running. The <instance>.memorymanager.total_memory  is much less than <instance>.memorymanager.target_mem before I run and after running they are equal which would indicate to me a stress in buffer cache. It does not seem to relieve itself afterwards.
Mark WillsTopic Advisor, Page Editor
CERTIFIED EXPERT
Distinguished Expert 2018

Commented:
david_32

Not neccessarily. Target memory is the computed amount of memory that SQL would like to be able to use. think of "target" as being the ideal goal.
It simply means of the target memory, the amount currently in use is less than it could be before you begin...

More accurately, Target is the number of 8-KB pages it can commit without causing paging. Need to check other metrics such as Private Bytes, or Buffers to work out if there are real memory pressures, and dbcc checkdb is not a good gauge - it should not be run all that often, and not during active time unless you have good reason to do so. Memory does not automatically release - it does get reused, and as time goes by with no additional memory pressures, those tasks do eventually release memory.
Mark WillsTopic Advisor, Page Editor
CERTIFIED EXPERT
Distinguished Expert 2018

Commented:
rrjegan17

yeah they are trialing a exports chat in the beta edition at the moment. In the meantime, my e-mail is on my bio, and you can certainly use it - can point out or maybe answer a few questions for you.

Author

Commented:
Hi Mark,

yes.. I think understand that - but isn't total memory the amount of memory the instance is actually using. If this increases to the same amoount as the target and stays there.. don't you have paging and hence memory stress?
Topic Advisor, Page Editor
CERTIFIED EXPERT
Distinguished Expert 2018
Commented:
well the only correct answer is "maybe". and by itself is insufficient information to do anything other than "I might need to look into this".

You actually want SQL to use memory allocation... and Target can be a moving target - it is recalculated from time to time according to Windows memory management. If target decreases, then it might indicate external memory pressures, if private bytes exceeds target then it might indicate internal memory pressures.

So, it is kind of hard to say without additional measures - and they need to occur over a time period to see what is really happening.

There are many things to consider : is AWE enabled ? What are min and max memory settings - and have you changed them. If target is below the MIN setting and there is reasonable load, then there is clearly external memory pressures. where as max server memory option limits the maximum amount of memory consumed by the buffer pool.

A reasonable indicator can be from : DBCC MEMORYSTATUS

If the number of "Stolen" pages is high (ie > say 80% or total) then there it does indicate internal memory pressures. Then you have to start looking into the different areas above and beyond the buffer pool (which is the very largest consumer) to try to help identify which other factors are coming into play - and juggling internal resource within SQL is not for the faint hearted, and have to have a very good understanding of the environment, the types of architecture and the nature of data to make sense of how (internal) memory is being consumed.


For example to see the top (n) consumers of the buffer pool via the single page allocator (SPA) then use something like :

select top 20 type, sum(single_pages_kb) as single_pages_kb
from sys.dm_os_memory_clerks
group by type
order by 2 desc

But does that really make a lot of sense ? and then what would you do to start balancing ? Answer is really not a lot for individual components other than to recognise where the efforts are going and consequently point you into a direction of further activity (normall tuning those queries, and checking indexes).

And really what you need to do is to start looking at trends over time...

Try setting up and running perfmon then look at memory pressures indicated by :
Low Buffer cache hit ratio
Low Page life expectancy
High number of Checkpoint pages/sec
High number Lazy writes/sec

Might also want a few other measures in there as well because some memory pressures can be a result of IO pressures, or, high CPU activity.


So, "maybe"...

Not the solution you were looking for? Getting a personalized solution is easy.

Ask the Experts

Author

Commented:
thanks .. it didn't directly answer my question but has heaps of helpful info!
Access more of Experts Exchange with a free account
Thanks for using Experts Exchange.

Create a free account to continue.

Limited access with a free account allows you to:

  • View three pieces of content (articles, solutions, posts, and videos)
  • Ask the experts questions (counted toward content limit)
  • Customize your dashboard and profile

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.