April 20, 2013 at 11:21 am
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 ?
April 22, 2013 at 2:14 am
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;
April 25, 2013 at 6:37 am
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 ?
April 25, 2013 at 6:57 am
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?
April 25, 2013 at 7:52 am
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
April 25, 2013 at 7:59 am
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.
April 25, 2013 at 8:03 am
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.
April 25, 2013 at 8:10 am
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
April 25, 2013 at 8:20 am
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