Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

large object inBuffer cache alert Expand / Collapse
Author
Message
Posted Saturday, April 20, 2013 11:21 AM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Yesterday @ 8:43 AM
Points: 236, Visits: 934
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 ?
Post #1444725
Posted Monday, April 22, 2013 2:14 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, July 16, 2014 3:44 AM
Points: 146, Visits: 282


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;
Post #1444891
Posted Thursday, April 25, 2013 6:37 AM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Yesterday @ 8:43 AM
Points: 236, Visits: 934
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 ?
Post #1446459
Posted Thursday, April 25, 2013 6:57 AM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Friday, March 21, 2014 9:46 AM
Points: 387, Visits: 1,078
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?
Post #1446470
Posted Thursday, April 25, 2013 7:52 AM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Yesterday @ 8:43 AM
Points: 236, Visits: 934
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
Post #1446503
Posted Thursday, April 25, 2013 7:59 AM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Friday, March 21, 2014 9:46 AM
Points: 387, Visits: 1,078
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.
Post #1446509
Posted Thursday, April 25, 2013 8:03 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, July 16, 2014 3:44 AM
Points: 146, Visits: 282
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.
Post #1446512
Posted Thursday, April 25, 2013 8:10 AM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Yesterday @ 8:43 AM
Points: 236, Visits: 934
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
Post #1446519
Posted Thursday, April 25, 2013 8:20 AM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Friday, March 21, 2014 9:46 AM
Points: 387, Visits: 1,078
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.
Post #1446532
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse