This article is half-done without your Comment! *** Please share your thoughts via Comment ***
SQL Server 2016 introduced sys.dm_column_store_object_pool system view for finding the memory pool usage by different types of Columnstore Index objects.
I am sharing one T-SQL Script to find memory pool usage by Columnstore Indexes.
You can find one object_type column in the result set of below query.
The information of object_type column is:
1 = COLUMN_SEGMENT
2 = COLUMN_SEGMENT_PRIMARY_DICTIONARY
3 = COLUMN_SEGMENT_SECONDARY_DICTIONARY
4 = COLUMN_SEGMENT_BULKINSERT_DICTIONARY
5 = COLUMN_SEGMENT_DELETE_BITMAP
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 |
SELECT OBJECT_NAME(csop.[object_id]) AS TableName ,[i].[name] AS IndexName ,[c].[name] AS ColumnName ,[csop].[column_id] ,[csop].[row_group_id] ,[csop].[object_type_desc] ,[csop].[access_count] ,[csop].[memory_used_in_bytes] ,[csop].[object_load_time] FROM sys.dm_column_store_object_pool AS csop LEFT OUTER JOIN sys.index_columns AS ic ON [ic].[index_column_id] = [csop].[column_id] AND [ic].[index_id] = [csop].[index_id] AND [ic].[object_id] = [csop].[object_id] LEFT OUTER JOIN sys.columns AS c ON [ic].[object_id] = [c].[object_id] AND [ic].[column_id] = [c].[column_id] LEFT OUTER JOIN sys.indexes AS i ON [i].[object_id] = [csop].[object_id] AND [i].[index_id] = [csop].[index_id] ORDER BY [csop].[memory_used_in_bytes] DESC GO |
Leave a Reply