That command returned 351 rows \u2013 one of which was the IAM page and the remainder text pages.<\/p>\n

I also created the following XE session to validate page access (testing on a cold cache using my session ID as well):<\/p>\n

\r\nCREATE EVENT SESSION [Columnstore Page Access] ON SERVER\r\nADD EVENT sqlserver.physical_page_read(\r\nACTION(sqlserver.session_id)\r\nWHERE ([sqlserver].[session_id]=(57)))\r\nWITH (MAX_MEMORY=4096 KB,EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS,\r\nMAX_DISPATCH_LATENCY=30 SECONDS,MAX_EVENT_SIZE=0 KB,MEMORY_PARTITION_MODE=NONE,\r\nTRACK_CAUSALITY=OFF,STARTUP_STATE=OFF)\r\nGO\r\n<\/pre>\n
And I used the following test query to initiate the physical page read events:<\/p>\n
\r\nSELECT\u00a0 COUNT([FactID]) AS [FactIDCount]\r\nFROM [dbo].[FactIDDemo]\r\nWHERE [FactID] BETWEEN 1 AND 1048576;\r\nGO\r\n<\/pre>\n
This query resulted in 349 events related directly to the columnstore index access.\u00a0 That is different from the 351 page count from sys.[dm_db_partition_stats] and DBCC IND output.\u00a0 The XE event didn\u2019t capture the IAM page reference (in my case, page 1870780) \u2013 and it also didn\u2019t retrieve page 1870777 which was a pagetype 3 (LOB page) and when I looked at it via DBCC PAGE, didn\u2019t show BLOB_FRAGMENT sections.<\/p>\n
Segments are the unit of transfer for columnstore index access.\u00a0 While this is the logical unit of transfer, a segment is stored as one or more LOB pages \u2013 and to track that physical access, it seems that the sqlserver.physical_page_read is a viable way of doing so.<\/p>\n","protected":false},"excerpt":{"rendered":"
Chalk this post up as an \u201cexploratory\u201d mission based on the following question\u2026 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 […]<\/p>\n","protected":false},"author":7,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[16],"tags":[],"class_list":["post-954","post","type-post","status-publish","format-standard","hentry","category-columnstore-indexes"],"yoast_head":"\n