Database Reference
In-Depth Information
update dbo.CCI
set Col2 = REPLICATE('z',4000)
where Col1 = 2000001; -- Newly inserted row (Delta Store)
Now it is time to find the data pages that are used by delta store and delete bitmap. We will use the
undocumented
sys.dm_db_database_page_allocations
system function, as shown in Listing 35-3.
Listing 35-3.
Delta store and Delete bitmap: Analyzing page allocations
select object_id, index_id, partition_id
,allocation_unit_type_desc as [Type]
,is_allocated,is_iam_page,page_type,page_type_desc
,allocated_page_file_id as [FileId]
,allocated_page_page_id as [PageId]
from sys.dm_db_database_page_allocations
(db_id(), object_id('dbo.CCI'),NULL, NULL, 'DETAILED')
You can see the output of the query in Figure
35-5
. As you know, SQL Server stores columnstore segments in
LOB_DATA
allocation units. Delta store and delete bitmap are using
IN_ROW_DATA
allocation.
Figure 35-5.
Delta store and Delete bitmap: Allocation units
Let's look at the data pages using another undocumented command,
DBCC PAGE
, with the code shown in
Listing 35-4. Obviously, the database, file, and page IDs would be different in your environment.
Listing 35-4.
Delta store and Delete bitmap: Analyzing page data
-- Redirecting output to console
dbcc traceon(3604)
-- Analyzing content of a page
dbcc page
(
9 -- Database Id
,1 -- FileId
,306 -- PageId
,3 -- Output style
)
Figure
35-6
shows the partial content of a data page, which is a delta store page. As you can see, SQL Server
stores data in regular row-based storage. There is one internal column,
CSILOCATOR
, in addition to two table columns.
CSILOCATOR
is used as an internal unique identifier of the row in delta store.
