October 26, 2016 at 10:49 am
My question regarding index compression is; how do you monitor/measure how the index gets decompressed in memory. I know that the whole page/row (compressed) is read into memory and only the data that is being queried gets uncompressed, but I am not sure what to look for showing this behavior.
Please…?
October 26, 2016 at 11:12 am
SQL Server's buffer cache holds pages in their original compressed format.
You can query the space allocated in the page buffer cache for individual objects using the following script.
SELECT TOP 10
db_name(database_id) Database_Name,
obj.[name] Object_Name,
i.[name] Index_Name,
i.[type_desc] Index_Type,
count_big(*) * 8192 / (1024 * 1024) as Object_BufferPool_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 database_id, obj.name, obj.index_id , i.[name],i.[type_desc]
ORDER BY count_big(*) DESC;
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
October 26, 2016 at 2:09 pm
Thank you Eric. 🙂
I will give this a try before and after index compression to view the sizes.
Regards
October 26, 2016 at 2:15 pm
Anchelin (10/26/2016)
Thank you Eric. 🙂I will give this a try before and after index compression to view the sizes.
Regards
You'll want to clear out the page buffer cache before each test.
DBCC DROPCLEANBUFFERS;
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
October 26, 2016 at 3:16 pm
Eric M Russell (10/26/2016)
SQL Server's buffer cache holds pages in their original compressed format.You can query the space allocated in the page buffer cache for individual objects using the following script.
SELECT TOP 10
db_name(database_id) Database_Name,
obj.[name] Object_Name,
i.[name] Index_Name,
i.[type_desc] Index_Type,
count_big(*) * 8192 / (1024 * 1024) as Object_BufferPool_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 database_id, obj.name, obj.index_id , i.[name],i.[type_desc]
ORDER BY count_big(*) DESC;
Nice, Eric. I'm going to have to play around with that one tomorrow. 😛
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply