Sql Server – ColumnStore Indexes – Metadata – Stats & Fragmentation

Background

Let us quickly review metadata on Sql Server Column Store Indexes.

 

Metadata

Overview

  1. Statistics
    • DBCC SHOW_STATISTICS
    • sys.dm_db_stats_properties
  2. Index Fragmentation
    • sys.dm_db_index_physical_stats

 

In-depth

Statistics

DBCC SHOW_STATISTICS

Syntax
DBCC SHOW_STATISTICS
 ( 
       table-name 
     , statistics-name 
)
Sample
Row Store – Clustered Index

Code


DBCC SHOW_STATISTICS  
( 
     '[Fact].[SaleLargeRowStore]'
   , 'PK_Fact_SaleLargeRowStore' 
)	
WITH NO_INFOMSGS

 

Image

Column Store – Clustered Index

Code


DBCC SHOW_STATISTICS  
( 
     '[Fact].[SaleLargeColumnStoreClustered]'
   , 'INDX_FACT_SaleLargeColumnStoreClustered' 
)	
WITH NO_INFOMSGS  	 


Image

 

Explanation

  1. All panels are empty
Column Store – NonClustered Index

Code



DBCC SHOW_STATISTICS  
( 
     '[Fact].[SaleLargeColumnStoreNonClustered]'
   , 'INDX_InvoiceDateKeyYear_DeliveryDateKeyYear' 
)	
WITH NO_INFOMSGS   

Image

 

sys.dm_db_stats_properties



declare @mode sysname

declare @tblTarget TABLE
(
	  [name]			sysname
	, [sequenceNbr]		smallint
	, [objectID]		int
)

--set @mode = 'detailed'
--set @mode = 'sampled'
set @mode = 'limited'

/*
	Populate entries for tables
*/
insert into @tblTarget
([name], [sequenceNbr])
--select '[Fact].[Sale]', 1
--union
select '[Fact].[SaleLargeRowStore]', 1
union
select 'Fact.SaleLargeColumnStoreClustered', 2
union
select 'Fact.SaleLargeColumnStoreNonClustered', 2

/*
	Get Object ID
*/
update  @tblTarget
set [objectID] = object_id([name])

select 

		[object]
			= 
				  object_schema_name(tblIndex.[object_id])
				+ '.'
				+ object_name(tblIndex.[object_id])

		, [index]
			= tblIndex.[name]

		, [indexType]
			= tblIndex.[type_desc]

		, [statID]
			= tblStatProp.stats_id

		, [lastUpdated]
			= convert(varchar(30), tblStatProp.last_updated,100)

		, [numberofRecords]
			= tblStatProp.[rows]

		, [numberofRecordsSampled]
			= tblStatProp.rows_sampled

		, [%Sampled]
			= cast
				(
					(
						(tblStatProp.rows_sampled * 100.00)
						/ tblStatProp.[rows]
					)
					as decimal(10,2 )
				)


		, [numberofSteps]
			= tblStatProp.steps

		, [numberofUnfilteredRecords]
			= tblStatProp.unfiltered_rows

		, [numberofRecordsModified]
			= tblStatProp.modification_counter

	from    sys.objects tblObject

	inner join sys.schemas tblSchema

			on tblObject.schema_id = tblSchema.schema_id

	inner join sys.indexes tblIndex

			on tblObject.object_id = tblIndex.object_id

	inner join sys.data_spaces da 

			on da.data_space_id= tblIndex.data_space_id

	OUTER APPLY sys.dm_db_stats_properties
		(
			  tblIndex.[object_id]
			, tblIndex.[index_id]
		) tblStatProp

inner join  @tblTarget tblT

	on tblIndex.[object_id] = tblT.objectID

order by
	   [sequenceNbr]	 asc
	 , [object]			 asc 
	 , tblIndex.[name]   asc
	  

Output

Explanation
  1. In the case of Column Store Index, sys.dm_db_stats_properties does not expose statistics metadata such as
    • last update timestamp
    • number of records
    • number of records sampled
    • number of steps
    • number of unfiltered records
    • number of records modified since last time stats was taken

 

Index Fragmentation

sys.dm_db_index_physical_stats

Sample
Code


declare @mode sysname

declare @tblTarget TABLE
(
	  [name]		sysname
	, [sequenceNbr]		smallint
	, [objectID]		int
)

--set @mode = 'detailed'
--set @mode = 'sampled'
set @mode = 'limited'

/*
	Populate entries for tables
*/
insert into @tblTarget
([name], [sequenceNbr])
select '[Fact].[Sale]', 1
union
select '[Fact].[SaleLargeRowStore]', 1
union
select 'Fact.SaleLargeColumnStoreClustered', 2
union
select 'Fact.SaleLargeColumnStoreNonClustered', 2

/*
	Get Object ID
*/
update  @tblTarget
set [objectID] = object_id([name])

select 

		[object]
			= 
				  object_schema_name(tblIndex.[object_id])
				+ '.'
				+ object_name(tblIndex.[object_id])

		, [index]
			= tblIndex.[name]

		, [indexType]
			= tblIndex.[type_desc]

		, tblIS.[page_count]

		, tblIS.avg_fragmentation_in_percent

		, tblIS.columnstore_delete_buffer_state

		, tblIS.[columnstore_delete_buffer_state_desc]


	from    sys.objects tblObject

	inner join sys.schemas tblSchema

			on tblObject.schema_id = tblSchema.schema_id

	inner join sys.indexes tblIndex

			on tblObject.object_id = tblIndex.object_id

	inner join sys.data_spaces da 

			on da.data_space_id= tblIndex.data_space_id

	cross apply sys.dm_db_index_physical_stats
	(
		 db_id()
				
		, tblIndex.[object_id]

		, tblIndex.[index_id]

		, NULL

		, @mode

	) tblIS 

inner join  @tblTarget tblT

	on tblIndex.[object_id] = tblT.objectID

order by
	   [sequenceNbr]     asc
	 , [object]	     asc 
	 , tblIndex.[name]   asc
	  
Output

Explanation
  1. Column
    • Page Count, Fragmentation
      • Exists
        • Row Store – Clustered / Non Clustered
      • Does not exist
        • Column Store – Clustered / Non Clustered
    • columnstore_delete_buffer_state, columnstore_delete_buffer_state_desc
      • Exists for Column Store Index

 

References

  1. Docs / SQL / Relational databases / System dynamic management views
    • sys.dm_db_stats_properties (Transact-SQL)
      • sys.dm_db_stats_properties (Transact-SQL)
        Link
  2. Docs / SQL / T-SQL Statements
    • Update Statistics
      • Update Statistics
        Link

 

Summary

Through the means discussed above, unfortunately, index stats data such as sampling, last occur timestamp, and histogram are not exposed on Column Store Indexes.

Neither is fragmentation data.

One thought on “Sql Server – ColumnStore Indexes – Metadata – Stats & Fragmentation

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s