Compression and query performance

Let's start by dropping both indexes from the demo fact table, the NCCI and the CI, to make a heap again:

USE WideWorldImportersDW; 
-- Drop the NCCI 
DROP INDEX NCCI_FactTest 
  ON dbo.FactTest; 
-- Drop the CI 
DROP INDEX CL_FactTest_DateKey 
  ON dbo.FactTest; 
GO 

Now let's create the CCI:

CREATE CLUSTERED COLUMNSTORE INDEX CCI_FactTest 
  ON dbo.FactTest; 
GO 

And, of course, the next step is to recheck the space used by the test fact table:

EXEC sys.sp_spaceused N'dbo.FactTest', @updateusage = N'TRUE'; 
GO 

The result is as follows:

Name          rows     reserved   data       index_size  unused 
------------  -------  ---------  ---------  ----------  -------
dbo.FactTest  2279810   23560 KB  23392 KB       0 KB    168 KB

The CCI even uses slightly less space than the NCCI. You can check the number of segments with the following query:

SELECT ROW_NUMBER() OVER (ORDER BY s.column_id, s.segment_id) AS rn, 
 COL_NAME(p.object_id, s.column_id) AS column_name, 
 S.segment_id, s.row_count,  
 s.min_data_id, s.max_data_id, 
 s.on_disk_size 
FROM sys.column_store_segments AS s    
INNER JOIN sys.partitions AS p    
    ON s.hobt_id = p.hobt_id    
INNER JOIN sys.indexes AS i    
    ON p.object_id = i.object_id   
WHERE i.name = N'CCI_FactTest' 
ORDER BY s.column_id, s.segment_id; 

This time, the number of segments is 44. The CCI does not show a dictionary segment per row group; it has a global dictionary that apparently covers the whole table. How does that influence the queries?

..................Content has been hidden....................

You can't read the all page of ebook, please click here login for view all page.
Reset
3.143.254.90