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

Memory Buffer says table occupies upto 6x more memory space than physical table size Expand / Collapse
Author
Message
Posted Friday, July 18, 2008 9:36 AM
SSC-Enthusiastic

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

Group: General Forum Members
Last Login: Friday, March 14, 2014 2:56 AM
Points: 123, Visits: 391
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
Post #536911
Posted Wednesday, December 11, 2013 2:35 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, March 24, 2014 3:09 PM
Points: 33, Visits: 312
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
Post #1522063
Posted Wednesday, December 11, 2013 2:56 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 2:26 PM
Points: 12,744, Visits: 31,071
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

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #1522072
Posted Thursday, December 12, 2013 2:43 AM
SSC-Enthusiastic

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

Group: General Forum Members
Last Login: Friday, March 14, 2014 2:56 AM
Points: 123, Visits: 391
thanks for replies, yes it has a clustered index.
Post #1522208
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse