large object inBuffer cache alert

  • Hi

    Can anbody advise on how to handle an alert that tells me i have a large object in the buffer cache. Do i need to clear them out or does sql server flush these out after a certain retention period ? How do i find out what the object is that is causing the issue ?

  • I use the below query to check the contents of the buffer cache, to list the objects and count;

    -- Breaks down buffers used by current database by object (table, index) in the buffer cache

    SELECT OBJECT_NAME(p.object_id) AS [ObjectName], p.object_id,

    p.index_id, COUNT(*)/128 AS [buffer size(MB)], COUNT(*) AS [buffer_count]

    FROM sys.allocation_units AS a

    INNER JOIN sys.dm_os_buffer_descriptors AS b

    ON a.allocation_unit_id = b.allocation_unit_id

    INNER JOIN sys.partitions AS p

    ON a.container_id = p.hobt_id

    WHERE b.database_id = db_id()

    AND p.object_id > 100

    GROUP BY p.object_id, p.index_id

    ORDER BY buffer_count DESC;

    -- Tells you what tables and indexes are using the most memory in the buffer cache

    Basically, as a general rule, you should plan your hardware to cater for the buffer growth, and the data in there will save you having to go to disk every time for queries, if you can obtain the necessary results from the cache.

    There can be occasions where inefficient queries are causing a large amount of data to be cached however, though by identifying these and eliminating them, you will eliminate the problem, as opposed to clearing the cache and having an adverse effect on EVERYTHING.

    This query should give you a good start in helping to identify the underlying queries causing your environment the most amount of I/O;

    -- TOP I/O Statements

    SELECT TOP(50) OBJECT_NAME(qt.objectid) AS [SP Name],

    (qs.total_logical_reads + qs.total_logical_writes) /qs.execution_count AS [Avg IO],

    SUBSTRING(qt.[text],qs.statement_start_offset/2,

    (CASE

    WHEN qs.statement_end_offset = -1

    THEN LEN(CONVERT(nvarchar(max), qt.[text])) * 2

    ELSE qs.statement_end_offset

    END - qs.statement_start_offset)/2) AS [Query Text]

    FROM sys.dm_exec_query_stats AS qs

    CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS qt

    WHERE qt.[dbid] = DB_ID()

    ORDER BY [Avg IO] DESC;

  • Sorry for the late reply to your post.

    Thank you very much for those queries.

    Can i ask though, once I have identified these large objects what is the recommended action ? Is it, like you say, a case of maybe upgrading the memory on the server ? Or do you somehow 'clear' that object from memory ?

  • Wont recommend "clear" of that object from Buffer Pool.

    Whats your max memory server setting? what is size of that big object in BPool? Do you face any performance problem bec of that big object?

  • The sql server has 24Gb and the server's max memory is set to approx 19Gb.

    From the 1st query listed above (to return the objects in the buffer) i get the following:

    Object Name OnjectID IndexID BufferSize(MB) Buffer_Count

    DIM_User 1290604532 1 121 15586

    Fact_Cases 1914606755 0 56 7246

    Im guessing that buffer size of 121, and 56 above is not big !!

    However what does the Buffer_count column represent as this looks to be large !

    Thanks

  • stehoban (4/25/2013)


    The sql server has 24Gb and the server's max memory is set to approx 19Gb.

    From the 1st query listed above (to return the objects in the buffer) i get the following:

    Object Name OnjectID IndexID BufferSize(MB) Buffer_Count

    DIM_User 1290604532 1 121 15586

    Fact_Cases 1914606755 0 56 7246

    Im guessing that buffer size of 121, and 56 above is not big !!

    However what does the Buffer_count column represent as this looks to be large !

    Thanks

    Buffer_count in your query represents Number of 8kb buffer Pool pages.

    Dim_user is just 121 Mb. Compared to your 19gb Bpool, its not a big thing.

  • Are you sure there's not something other than SQL on the box using a reasonable amount of memory?

    If you've got 19GB and the top cache object is 121MB (I'm assuming the total cache size isn't huge?), yet you have memory problems, then chances are there's something else causing issues on the box?

    Antivirus installed? Running frequent scans for example?

    What sort of problems were you experiencing in the first place?

    If it's just this alert, you can reconfigure the alert to a slightly higher threshold or you can probably ignore it in this case.

    It is a "relatively" large object, but if it's being queried often, then it's in there correctly.

  • ahhh right that makes sense then. So 8k multiplied by 15586 is approx 121MB - which is what the query shows me.

    So if the sql max memory setting is at 19gb then a tiny 121MB shouldnt be an issue.

    I might have to re-visit the sql monitor alert settings and change the thresholds.

    Thanks

  • I would recommend a constant monitoring of BPool usage by the same query for a couple of days (particularly Peak Hours) to get a usage limit. So that you can set your alert accordingly.

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply