Chalk this post up as an “exploratory” mission based on the following question…
How do I track which LOB pages are associated with a specific columnstore index segment?
Jonathan Kehayias and I discussed this the other day and hashed out a few options for tracking this.
To illustrate this topic and keep it at a granular level – I used a simple table named FactIDDemo with a bigint FactID column that had a unique clustered index on it. Again – exploratory and not intended to be a realistic implementation pattern.
I loaded the table with 1,048,576 rows. And the segment statistics were as follows:
SELECT [partition_id], [segment_id], [row_count], [min_data_id], [max_data_id] FROM sys.[column_store_segments] WHERE [partition_id] = 72057594043236352 ;
How many LOB pages were allocated for this single segment?
SELECT [partition_id], [object_id], [index_id], [partition_number], [in_row_used_page_count], [lob_used_page_count], [used_page_count], [row_count] FROM sys.[dm_db_partition_stats] WHERE [partition_id] = 72057594043236352;
We see 351 used LOB pages and executing DBCC IND confirms this as well, outputting the page ids accordingly:
DBCC IND('BigFactTable', 'FactIDDemo', 2);
That command returned 351 rows – one of which was the IAM page and the remainder text pages.
I also created the following XE session to validate page access (testing on a cold cache using my session ID as well):
CREATE EVENT SESSION [Columnstore Page Access] ON SERVER ADD EVENT sqlserver.physical_page_read( ACTION(sqlserver.session_id) WHERE ([sqlserver].[session_id]=(57))) WITH (MAX_MEMORY=4096 KB,EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS, MAX_DISPATCH_LATENCY=30 SECONDS,MAX_EVENT_SIZE=0 KB,MEMORY_PARTITION_MODE=NONE, TRACK_CAUSALITY=OFF,STARTUP_STATE=OFF) GO
And I used the following test query to initiate the physical page read events:
SELECT COUNT([FactID]) AS [FactIDCount] FROM [dbo].[FactIDDemo] WHERE [FactID] BETWEEN 1 AND 1048576; GO
This query resulted in 349 events related directly to the columnstore index access. That is different from the 351 page count from sys.[dm_db_partition_stats] and DBCC IND output. The XE event didn’t capture the IAM page reference (in my case, page 1870780) – and it also didn’t retrieve page 1870777 which was a pagetype 3 (LOB page) and when I looked at it via DBCC PAGE, didn’t show BLOB_FRAGMENT sections.
Segments are the unit of transfer for columnstore index access. While this is the logical unit of transfer, a segment is stored as one or more LOB pages – and to track that physical access, it seems that the sqlserver.physical_page_read is a viable way of doing so.