Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


large object inBuffer cache alert


large object inBuffer cache alert

Author
Message
PearlJammer1
PearlJammer1
Old Hand
Old Hand (322 reputation)Old Hand (322 reputation)Old Hand (322 reputation)Old Hand (322 reputation)Old Hand (322 reputation)Old Hand (322 reputation)Old Hand (322 reputation)Old Hand (322 reputation)

Group: General Forum Members
Points: 322 Visits: 1423
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 ?
McSQL
McSQL
SSC-Enthusiastic
SSC-Enthusiastic (162 reputation)SSC-Enthusiastic (162 reputation)SSC-Enthusiastic (162 reputation)SSC-Enthusiastic (162 reputation)SSC-Enthusiastic (162 reputation)SSC-Enthusiastic (162 reputation)SSC-Enthusiastic (162 reputation)SSC-Enthusiastic (162 reputation)

Group: General Forum Members
Points: 162 Visits: 299
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;
PearlJammer1
PearlJammer1
Old Hand
Old Hand (322 reputation)Old Hand (322 reputation)Old Hand (322 reputation)Old Hand (322 reputation)Old Hand (322 reputation)Old Hand (322 reputation)Old Hand (322 reputation)Old Hand (322 reputation)

Group: General Forum Members
Points: 322 Visits: 1423
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 ?
SQL Show
SQL Show
Old Hand
Old Hand (393 reputation)Old Hand (393 reputation)Old Hand (393 reputation)Old Hand (393 reputation)Old Hand (393 reputation)Old Hand (393 reputation)Old Hand (393 reputation)Old Hand (393 reputation)

Group: General Forum Members
Points: 393 Visits: 1078
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?
PearlJammer1
PearlJammer1
Old Hand
Old Hand (322 reputation)Old Hand (322 reputation)Old Hand (322 reputation)Old Hand (322 reputation)Old Hand (322 reputation)Old Hand (322 reputation)Old Hand (322 reputation)Old Hand (322 reputation)

Group: General Forum Members
Points: 322 Visits: 1423
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
SQL Show
SQL Show
Old Hand
Old Hand (393 reputation)Old Hand (393 reputation)Old Hand (393 reputation)Old Hand (393 reputation)Old Hand (393 reputation)Old Hand (393 reputation)Old Hand (393 reputation)Old Hand (393 reputation)

Group: General Forum Members
Points: 393 Visits: 1078
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.
McSQL
McSQL
SSC-Enthusiastic
SSC-Enthusiastic (162 reputation)SSC-Enthusiastic (162 reputation)SSC-Enthusiastic (162 reputation)SSC-Enthusiastic (162 reputation)SSC-Enthusiastic (162 reputation)SSC-Enthusiastic (162 reputation)SSC-Enthusiastic (162 reputation)SSC-Enthusiastic (162 reputation)

Group: General Forum Members
Points: 162 Visits: 299
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.
PearlJammer1
PearlJammer1
Old Hand
Old Hand (322 reputation)Old Hand (322 reputation)Old Hand (322 reputation)Old Hand (322 reputation)Old Hand (322 reputation)Old Hand (322 reputation)Old Hand (322 reputation)Old Hand (322 reputation)

Group: General Forum Members
Points: 322 Visits: 1423
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
SQL Show
SQL Show
Old Hand
Old Hand (393 reputation)Old Hand (393 reputation)Old Hand (393 reputation)Old Hand (393 reputation)Old Hand (393 reputation)Old Hand (393 reputation)Old Hand (393 reputation)Old Hand (393 reputation)

Group: General Forum Members
Points: 393 Visits: 1078
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.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search