large object in buffer cache

SQL Server performance monitoring and alerting

Moderators: eddie davis, priyasinha, Adam, chriskelly, Chris Lambrou, Chris Spencer

large object in buffer cache

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

"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
stevenhoban
 
Posts: 15
Joined: Thu Apr 18, 2013 4:41 pm

Postby Manfred.Castro » Sat Apr 20, 2013 12:02 am

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
Manfred.Castro
 
Posts: 209
Joined: Mon Apr 23, 2012 2:49 pm

Postby stevenhoban » Sat Apr 20, 2013 6: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 ?
stevenhoban
 
Posts: 15
Joined: Thu Apr 18, 2013 4:41 pm


Return to SQL Monitor 3

Who is online

Users browsing this forum: No registered users and 0 guests