• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 291
  • Last Modified:

dbcc take ups all may buffer cache

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?
0
david_32
Asked:
david_32
  • 6
  • 3
  • 3
1 Solution
 
Mark WillsTopic AdvisorCommented:
What dbcc commands are you running and how frequently ? Some of them can use a lot of resource, some require exclusive access...
0
 
Raja Jegan RSQL Server DBA & ArchitectCommented:
Kindly provide me the command which you exactly used.
0
 
Mark WillsTopic AdvisorCommented:
@rrjegan : it should be "us", not just "me", we are all trying to help :)
0
Configuration Guide and Best Practices

Read the guide to learn how to orchestrate Data ONTAP, create application-consistent backups and enable fast recovery from NetApp storage snapshots. Version 9.5 also contains performance and scalability enhancements to meet the needs of the largest enterprise environments.

 
Raja Jegan RSQL Server DBA & ArchitectCommented:
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..
0
 
Mark WillsTopic AdvisorCommented:
@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...
0
 
Raja Jegan RSQL Server DBA & ArchitectCommented:
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..
0
 
david_32Author 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.
0
 
Mark WillsTopic AdvisorCommented:
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.
0
 
Mark WillsTopic AdvisorCommented:
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.

0
 
david_32Author 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?
0
 
Mark WillsTopic AdvisorCommented:
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"...

0
 
david_32Author Commented:
thanks .. it didn't directly answer my question but has heaps of helpful info!
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 6
  • 3
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now