Share via

SQL Server: Verifying Columnstore Segment Elimination

  • Article

You can determine what segments were eliminated during query processing using the sqlserver.column_store_segment_eliminate Xevent. The following script illustrates how to monitor this event:

-- find Xevent for segment elimination
select * from sys.dm_xe_objects x
where x.name like '%segment%'
go

create event session SegmentEliminationSession on server
add event sqlserver.column_store_segment_eliminate
(action (sqlserver.database_id,sqlserver.session_id,sqlserver.sql_text,
 sqlserver.plan_handle))
add target package0.asynchronous_file_target
(set filename = 'c:\temp\filename.xel', metadatafile = 'c:\temp\filename.xem')
go

alter event session SegmentEliminationSession on server state = start;
go 

-- Run a query that causes segment elimination.
-- ... (put your own query here)
 
-- See event output
select * from sys.fn_xe_file_target_read_file('C:\temp\.xel', 'C:\temp\metafile.xem', null, null)

To get text output messages that show what segments (row groups) were eliminated during query processing, enable trace flag 646 at the global level. The following example illustrates how to do this.

use master
go

drop database SegElimTest
go

create database SegElimTest
go

use SegElimTest
go

create partition function pf(int) as range left for values (100, 200)
go

create partition scheme ps as partition pf all to ([primary])
go

create table t1(a int) on ps(a)
go

insert into t1 values(50)
insert into t1 values (150)
insert into t1 values(250)
go

create columnstore index i1 on t1(a)
go

-- Turn on text output that shows when segments (row groups) are eliminated.
dbcc traceon(646, -1)
go

-- Direct trace flag text output to the SQL Server error log file.
dbcc traceon(3605, -1)
go

dbcc tracestatus
go

select * from t1 where a < 140
go 

The query above causes this output to be displayed in the error log:

Xact (xactid=389339632) skipped row group (dbid=7 rowsetid=72057594039304192 rowgroupid=0)

Trace flag 646 is not documented and is thus not supported, but it can be a useful tool to understand segment elimination.

Return to main columnstore performance tuning page.