September 22, 2015 at 4:13 pm
run the following script and found that about 69.6% db buffer is used by tempdb
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 = 'Database pages';
--select @total_buffer
--select * from sys.dm_os_performance_counters WHERE RTRIM([object_name]) LIKE '%Buffer Manager'
-- AND counter_name = 'Database 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;
-- result
db_namedb_buffer_pagesdb_buffer_MBdb_buffer_percent
tempdb44783193498669.636
CZuza15570181216424.211
MLFxRmmService33231925965.167
And then run the following script to get which objects in tempdb are in memory, and found that a lot of pages with TEXT_MIX_PAGE type which cannot be mapped to any tables/indexes. the Text_MIX_PAGE type pages are for small LOB data. but I am not sure why they belongs to tempdb.
SELECT DBName = CASE WHEN database_id = 32767 THEN 'RESOURCEDB'
ELSE DB_NAME(database_id) END,
ObjName = o.name,
obd.page_type,
Size_MB = COUNT(1)/128.0
FROM sys.dm_os_buffer_descriptors obd
left JOIN sys.allocation_units au
ON obd.allocation_unit_id = au.allocation_unit_id
left JOIN sys.partitions p
ON au.container_id = p.hobt_id
left JOIN sys.objects o
ON p.object_id = o.object_id
WHERE obd.database_id = 2
--AND o.type != 'S'
GROUP BY obd.database_id, o.name, obd.page_type
ORDER BY 3 DESC
September 22, 2015 at 9:48 pm
Those TEXT_MIX_PAGEs store the LOB values for whatever objects in tempdb i.e. temp tables, table variables etc..
😎
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply