Memory Buffer says table occupies upto 6x more memory space than physical table size

Question

Post reply

Memory Buffer says table occupies upto 6x more memory space than physical table size

sotn

SSCrazy

Points: 2672
More actions
July 18, 2008 at 9:36 am

#188306

I am trying to investigate SQL Memory usage and am running the query below
However for one core table (item) which has a phsyical size of 506MB this query sometimes shows that this table has over 3,000MB in the memory buffer.
Is that possible? Can SQL load the same data into memory more than once? I cannot reproduce this with normal select * from table tests
If not how can this query show a (much) larger value for a table than it physically has.
I got the phsyically has value by running sp_spaceused
The line with the large figure is against the clustered index.
Example values Sp_spaceused reserved 506832 KB
Below Query Buffer_MB 2919
Is there a problem in the query itself? I got it from BOL.
SELECT TOP 25
obj.[name],
i.[name],
i.[type_desc],
count(*)AS Buffered_Page_Count ,
count(*) * 8192 / (1024 * 1024) as Buffer_MB
FROM sys.dm_os_buffer_descriptors AS bd
INNER JOIN
(
SELECT object_name(object_id) AS name
,index_id ,allocation_unit_id, object_id
FROM sys.allocation_units AS au
INNER JOIN sys.partitions AS p
ON au.container_id = p.hobt_id
AND (au.type = 1 OR au.type = 3)
UNION ALL
SELECT object_name(object_id) AS name
,index_id, allocation_unit_id, object_id
FROM sys.allocation_units AS au
INNER JOIN sys.partitions AS p
ON au.container_id = p.hobt_id
AND au.type = 2
) AS obj
ON bd.allocation_unit_id = obj.allocation_unit_id
LEFT JOIN sys.indexes i on i.object_id = obj.object_id AND i.index_id = obj.index_id
WHERE database_id = db_id()
GROUP BY obj.name, obj.index_id , i.[name],i.[type_desc]
ORDER BY Buffered_Page_Count DESC

Viewing 4 posts - 1 through 3 (of 3 total)

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

Venkat Manohar SSC Enthusiast Points: 193 More actions · Answer 1

Hi,

I also have the same doubt. Physical table is around 2 GB and the buffer size is 5 GB. I couldn't able to corelate it.

Hope someone responds to your question.

Thanks,

-Manohar

Lowell SSC Guru Points: 323506 More actions · Answer 2

my first guess is that the table is a HEAP.

if the table is a HEAP (meaning it does not have a clustered index) when individual rows are deleted from the table, they are not released to the database for reuse;

only when a delete featuring TABLOCKX is created will deleted rows get released.

can you check if your table has a clustered index? (exec sp_helpindex TableName)

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

sotn SSCrazy Points: 2672 More actions · Answer 3

thanks for replies, yes it has a clustered index.