question

My server has 64GB RAM, and 97% is used. I run the following script to check the memory used by each database, the tempdb is on top of it.![alt text][1] DECLARE @total_buffer INT; SELECT @total_buffer = cntr_value FROM sys.dm_os_performance_counters WHERE RTRIM([object_name]) LIKE '%Buffer Manager' AND counter_name = 'Total Pages'; ;WITH src AS ( SELECT database_id, db_buffer_pages = COUNT_BIG(*) FROM sys.dm_os_buffer_descriptors --WHERE database_id BETWEEN 5 AND 32766 GROUP BY database_id ) SELECT [db_name] = CASE [database_id] WHEN 32767 THEN 'Resource DB' ELSE DB_NAME([database_id]) END, db_buffer_pages, db_buffer_MB = db_buffer_pages / 128, db_buffer_percent = CONVERT(DECIMAL(6,3), db_buffer_pages * 100.0 / @total_buffer) FROM src ORDER BY db_buffer_MB DESC; Then I checked which DB objects are cached in memory using the following script and it seems the tempdb cached objects is much less ![alt text][2] USE tempdb; GO ;WITH src AS ( SELECT [Object] = o.name, [Type] = o.type_desc, [Index] = COALESCE(i.name, ''), [Index_Type] = i.type_desc, p.[object_id], p.index_id, au.allocation_unit_id FROM sys.partitions AS p INNER JOIN sys.allocation_units AS au ON p.hobt_id = au.container_id INNER JOIN sys.objects AS o ON p.[object_id] = o.[object_id] INNER JOIN sys.indexes AS i ON o.[object_id] = i.[object_id] AND p.index_id = i.index_id WHERE au.[type] IN (1,2,3) AND o.is_ms_shipped = 0 ) SELECT src.[Object], src.[Type], src.[Index], src.Index_Type, buffer_pages = COUNT_BIG(b.page_id), buffer_mb = COUNT_BIG(b.page_id) / 128 FROM src INNER JOIN sys.dm_os_buffer_descriptors AS b ON src.allocation_unit_id = b.allocation_unit_id WHERE b.database_id = DB_ID() GROUP BY src.[Object], src.[Type], src.[Index], src.Index_Type ORDER BY buffer_pages DESC; Then I run the following script to check what types of page cached in the memory. it turns out that most of the pages are TEXT_MIX_PAGE, I know that this type of pages are for small LOB data, but the question is how could we know those pages belongs to which tables? select page_type, COUNT_BIG(*) totalpages, COUNT_BIG(*)*8/2014 totalsize_MB FROM sys.dm_os_buffer_descriptors a where a.database_id = 2 group by page_type order by totalsize_MB desc --result page_type totalpages totalsize_MB TEXT_MIX_PAGE 3757561 14925 DATA_PAGE 523270 2078 TEXT_TREE_PAGE 203178 807 INDEX_PAGE 3632 14 IAM_PAGE 2203 8 PFS_PAGE 1056 4 BOOT_PAGE 1 0 DIFF_MAP_PAGE 1 0 GAM_PAGE 32 0 SGAM_PAGE 32 0 FILEHEADER_PAGE 1 0 ML_MAP_PAGE 1 0 [1]: /storage/temp/2854-untitled.jpg [2]: /storage/temp/2855-untitled.jpg