Competition: What’s your favorite Redgate tool? Enter now.

large object in buffer cache

stevenhobanstevenhoban Posts: 21
edited April 20, 2013 1:09PM in SQL Monitor Previous Versions
"LARGE OBJECT IN BUFFER CACHE"
Can anybody advise me on the action to take when i see this alert in sql monitor.
We have it set at the default values and I am wondering on the best techniques to remedy the situation. Some of our overnight ETL jobs are taking 3 hours when they used to run in 20 mins and i am wondering if this 'large object in buffer cache' is having an impact.
Thanks

Comments

  • This alert is raised when the amount of memory used in the buffer cache by the largest object (based on the number of pages) goes above a specified threshold. It checks the sys.dm_os_buffer_descriptors to identify the object, and returns the relative percentage used.
    It is very common for one or two objects to be responsible for using a large amount of the buffer cache. To increase the efficiency of the buffer cache area, these objects may benefit from a schema revision (datatype changes or sparse columns), and are great candidates for compression.

    For more information, see http://blogs.msdn.com/b/chadboyd/archiv ... tions.aspx and http://www.simple-talk.com/community/bl ... 10696.aspx.
    Manfred Castro
    Product Support
    Red Gate Software
  • Thanks for the reply. Unfortunately I have seen this post as it is just from the 'SQL Monitor' description field.
    I have checked out the scripts in the link. What i am really asking here is what to do once i have identified this large object in the buffer cache. Do i somehow flush it from the cache, then maybe use one of the methods listed so it doesnt take up as much space the next time it is loaded in memory ?
Sign In or Register to comment.