RiCo技術農場

上一篇我提到sys.internal_partitions這新的DMV，這一篇我將介紹其他新增加的DMV。

基本名詞介紹

Columnstore:SQL2012推出第一個版本，所有資料將以欄位為單位進行儲存(非heap或B-tree)，

這種索引結構有高度壓縮、低I/O和記憶體使用率優勢，對於data-warehouse查詢類型提供大幅效能改善。

Rowstore:傳統heap or B-tree索引結構。

Rowgroup:是指同時壓縮成資料行存放區格式的一組資料列,

資料列群組包含每一個欄位所定義的相同資料筆數.該群組資料數量取決為每一個欄位唯一性，

目前每一資料列群組資料上限為1048576。

Segment:索引中的資料列群組，有包含一個部分記錄資料單一壓縮欄位和和其他欄位合併的Segment(區段)資訊，

且每一個Segment都有相同的資料數量。

Dictionary:索引中的輔助結構，壓縮前置欄位至欄位區段，

且Dictionary和區段在物理上都會由儲存引擎轉換為獨立BLOB結構。

Deltastore:一種B-tree結構，用於儲存低於102,400的資料並壓縮至Columnstore，

一但Deltastore達到資料列群組最大筆數(1,048,576)，便無法儲存更多資料，且狀態將從open變更為closed。

Delete Bitmap:一種B-tree結構，用於儲存Columnstore Index上被刪除的資料。

Tuple Mover:一條背景處理程序，用於檢查資料列群組為closed狀態，並壓縮資料進入Collumnstore。

 

sys.dm_db_column_store_row_group_operational_stats

傳回目前資料行存放區索引中的資料列層級 I/O、 鎖定和存取方法壓縮的資料列群組。

SELECT TOP 90 * FROM [dbo].StockItemTransactions
ORDER BY StockItemID ,TransactionTypeID

SELECT 
 OBJECT_NAME(csos.[object_id]) AS [Table]
, indx.[name] AS [Index]
, csos.[row_group_id]
, csos.[index_scan_count]
, csos.[scan_count]
, csos.[delete_buffer_scan_count]
, csos.[row_group_lock_count]
, csos.[row_group_lock_wait_count]
, csos.[row_group_lock_wait_in_ms]
FROM sys.dm_db_column_store_row_group_operational_stats AS csos
LEFT OUTER JOIN sys.indexes AS indx
ON indx.[index_id] = csos.[index_id]
 AND indx.[object_id] = csos.[object_id]
ORDER BY [Table], [Index], csos.[row_group_id];

Row_group_id=0，索引掃描3次。

 

sys.column_store_row_groups

用來判斷資料列群組所包含的資料數量和大小。當資料列群組中已刪除的資料列數增加到佔總列數的相當大比例時，

資料表的效率就會降低。 重建資料行存放區索引可縮小資料表，減少讀取資料表所需的磁碟 I/O。

一旦開啟的資料列群組已滿時，其狀態會變更為 Closed Closed 資料列群組會藉由 tuple mover(背景處理序)

壓縮為資料行存放區格式，並在狀態變更為壓縮。

SELECT
 OBJECT_NAME(csrg.[object_id]) AS [Table],
 indx.[name] AS [Index],
 csrg.[row_group_id],
 csrg.[delta_store_hobt_id],
 csrg.[state_description],
 csrg.[total_rows],
 csrg.[deleted_rows],
 csrg.[size_in_bytes]
FROM 
 sys.column_store_row_groups AS csrg
LEFT OUTER JOIN sys.indexes AS indx
ON csrg.[object_id] = indx.[object_id]
 AND csrg.[index_id] =indx.[index_id]
ORDER BY [Table],[Index],csrg.[row_group_id];

有四個資料列群組，狀態都是壓縮，依然可以接受更多資料，且沒有任何被刪除的資料。

 

sys.dm_column_store_object_pool

傳回資料行存放區索引物件的記憶體集區。

SELECT 
 OBJECT_NAME(p.[object_id]) AS [Table], 
 indx.[name] AS [Index Name],
 c.[name] AS [Column Name],
 p.[column_id],
 p.[row_group_id],
 p.[object_type_desc],
 p.[access_count],
 p.[memory_used_in_bytes],
 p.[object_load_time]
FROM sys.dm_column_store_object_pool p
LEFT OUTER JOIN sys.index_columns indxc
ON indxc.[index_column_id] = p.[column_id] 
 AND indxc.[index_id] = p.[index_id] 
 AND indxc.[object_id] = p.[object_id]
LEFT OUTER JOIN sys.columns c
ON indxc.[object_id] = c.[object_id] 
 AND indxc.[column_id] = c.[column_id]
LEFT OUTER JOIN sys.indexes indx
ON indx.[object_id] = p.[object_id] 
 AND indx.[index_id] = p.[index_id]
ORDER BY p.[memory_used_in_bytes] DESC;

查詢記憶體使用量。

 

sys.dm_db_column_store_row_group_physical_stats

提供資料行存放區索引中所有的資料列群組相關資訊。

SELECT 
 OBJECT_NAME(indx.[object_id]) AS [Table],
 indx.[name] AS [Index], 
 csps.[row_group_id],
 csps.[delta_store_hobt_id],
 csps.[state_desc],
 csps.[total_rows],
 csps.[deleted_rows],
 csps.[size_in_bytes],
 csps.[trim_reason_desc],
 csps.[transition_to_compressed_state_desc],
 csps.[has_vertipaq_optimization],
 csps.[generation],
 csps.[created_time],
 csps.[closed_time],
 100 * ( ISNULL ( csps.[deleted_rows], 0 ) ) / total_rows 
  AS 'Fragmentation'
FROM sys.indexes AS indx
INNER JOIN sys.dm_db_column_store_row_group_physical_stats AS csps
ON indx.[object_id] = csps.[object_id] 
 AND indx.index_id = csps.index_id  
ORDER BY [Table],[Index],csps.[row_group_id];

查看碎片程度並適時重建資料行存放區索引。

 

透過下面TSQL，留意索引碎片資訊

--查看資料表所有索引的I/O相關資訊
SELECT
 OBJECT_NAME(os.[object_id]) AS [Table],
 indx.[name] AS [Index],
 indx.[type_desc] AS [Index Type],
 ip.[internal_object_type_desc],
 os.[range_scan_count],
 os.[singleton_lookup_count],
 os.[page_lock_count],
 os.[page_io_latch_wait_count],
 os.[page_io_latch_wait_in_ms],
 os.[tree_page_io_latch_wait_count],
 os.[tree_page_io_latch_wait_in_ms]
FROM 
 sys.dm_db_index_operational_stats (DB_ID(), NULL, NULL, NULL) 
  AS os
INNER JOIN sys.indexes AS indx
ON indx.[index_id] = os.[index_id]
 AND indx.[object_id] = os.[object_id]
LEFT OUTER JOIN sys.internal_partitions AS ip
ON ip.[hobt_id] = os.[hobt_id]
 AND ip.[index_id] = os.[index_id]
 AND ip.[object_id] = os.[object_id]
 AND ip.[partition_number] = os.[partition_number]
WHERE OBJECT_NAME(os.[object_id]) IN ('StockItemTransactions')
ORDER BY [Table], [Index], ip.[row_group_id];
GO

--查看資料表所有所以碎片資訊和資料行存放區索引有關delete_buffer狀態
SELECT
 indx.[name] AS [Index],
 indx.[type_desc] AS [Index Type],
 ip.[internal_object_type_desc],
 ip.[rows],
 ps.[avg_fragmentation_in_percent],
 ps.[columnstore_delete_buffer_state_desc]
FROM 
 sys.dm_db_index_physical_stats
  (DB_ID(), NULL, NULL, NULL , 'LIMITED') AS ps
INNER JOIN
 sys.indexes AS indx
ON indx.[index_id] = ps.[index_id]
 AND indx.[object_id] = ps.[object_id]
LEFT OUTER JOIN sys.internal_partitions AS ip
ON 
 ip.[hobt_id] = ps.[hobt_id]
 AND ip.[index_id] = ps.[index_id]
 AND ip.[object_id] = ps.[object_id]
 AND ip.[partition_number] = ps.[partition_number]
WHERE OBJECT_NAME(ps.[object_id]) IN ('StockItemTransactions')
ORDER BY OBJECT_NAME(ps.[object_id]), [Index];

 

Enjoy SQL Server 2016

參考

Concepts of SQL Server 2014 Columnstore

sys.column_store_row_groups (Transact-SQL)

sys.dm_column_store_object_pool (Transact-SQL)

sys.dm_db_column_store_row_group_operational_stats (TRANSACT-SQL)

sys.dm_db_column_store_row_group_physical_stats (TRANSACT-SQL)

Columnstore Indexes Guide

Columnstore Indexes Versioned Feature Summary

SQL 2016: Columnstore row group Merge policy and index maintenance improvements

SQL Server Columnstore, B-Tree and Hybrid Index Performance Comparison for Data Warehouses

Exploring Columnstore Index Metadata, Segment Distribution and Elimination Behaviors