I have a Clustered Columnstore Index Table for our IOT metrics (timeseries data). It contains more than 1 billion rows and structured like this:
CREATE TABLE [dbo].[Data](
[DeviceId] [bigint] NOT NULL,
[MetricId] [smallint] NOT NULL,
[TimeStamp] [datetime2](2) NOT NULL,
[Value] [real] NOT NULL
)
CREATE CLUSTERED INDEX [PK_Data] ON [dbo].[Data] ([TimeStamp],[DeviceId],[MetricId]) --WITH (DROP_EXISTING = ON)
CREATE CLUSTERED COLUMNSTORE INDEX [PK_Data] ON [dbo].[Data] WITH (DROP_EXISTING = ON, MAXDOP = 1, DATA_COMPRESSION = COLUMNSTORE_ARCHIVE)
There are some 10,000 distinct DeviceId values and TimeStamps range from 2008 till now. A typical query against this table looks like this:
SET STATISTICS TIME, IO ON
SELECT
[DeviceId]
,[MetricId]
,DATEADD(hh, DATEDIFF(day, '2005-01-01', [TimeStamp]), '2005-01-01') As [Date]
,MIN([Value]) as [Min]
,MAX([Value]) as [Max]
,AVG([Value]) as [Avg]
,SUM([Value]) as [Sum]
,COUNT([Value]) as [Count]
FROM
[dbo].[Data]
WHERE
[DeviceId] = 6077129891325167032
AND [MetricId] = 1000
AND [TimeStamp] BETWEEN '2017-07-01' AND '2017-07-30'
GROUP BY
[DeviceId]
,[MetricId]
,DATEDIFF(day, '2005-01-01', [TimeStamp])
ORDER BY
[DeviceId]
,[MetricId]
,DATEDIFF(day, '2005-01-01', [TimeStamp])
When I execute this query, I get this for performance metrics:
Because at the moment a query like stated above does too many Segment reads I believe:
Table 'Data'. Scan count 2, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 5257, lob physical reads 9, lob read-ahead reads 4000.
Table 'Data'. Segment reads 11, segment skipped 764.
This is not well optimized I believe as there were 11 segments read to retrieve only 212 out of 1 billion source rows (before grouping/aggregation)
So then I ran Niko Neugebauer's great scripts to validate our setup and the Columnstore Alignment https://github.com/NikoNeugebauer/CISL/blob/master/Azure/alignment.sql, I get this result after rebuilding the Columnstore Clustered Index:
MetricId and TimeStamp columns have optimal alignment score of 100%. How can we ensure that the DeviceId column is also well aligned? I played with the column order in the initial Clustered (Rowstore) index, is that where things can be optimized?