large object in buffer cache

Monitor, diagnose and receive alerts to problems across your SQL Servers.

large object in buffer cache

Postby stevenhoban » Thu Apr 18, 2013 3:49 pm

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.
Posts: 15
Joined: Thu Apr 18, 2013 3:41 pm

Postby Manfred.Castro » Fri Apr 19, 2013 11:02 pm

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 ... tions.aspx and ... 10696.aspx.
Manfred Castro
Product Support
Red Gate Software
Posts: 209
Joined: Mon Apr 23, 2012 1:49 pm

Postby stevenhoban » Sat Apr 20, 2013 5:09 pm

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 ?
Posts: 15
Joined: Thu Apr 18, 2013 3:41 pm

Return to SQL Monitor Previous Versions

Who is online

Users browsing this forum: No registered users and 0 guests