Store time-series data in MS SQL

One of the past dumbest idea, that can be realised

So, one day a customer wanted us to store time series data in an existing instance of MS SQL Server.

Well, okay… let’s create small table with index

CREATE TABLE [dbo].[QualityArchive](
 [Timestamp][datetime] NOT NULL,
 [Name] nchar(25) NOT NULL,
 [Quality][int] NOT NULL,
 CONSTRAINT [PK_testT] PRIMARY KEY CLUSTERED
 (
  [Timestamp] ASC,
  [Name] ASC,
  [Quality] ASC
 ) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
) ON [PRIMARY]

I decided to use clustered index on all table colums. The benefit of this approach is that index and data stored in the same partitions. So you can apply partitioning and backup, restore and move older partitions to different servers and locations.

Now, it’s time to put some data into the table.

While time goes, partition will slowly degradade thus every partition index should be reorganized. The good news, is that such procedure required only once (if you not planning to insert data to past partitions).

To check current degree of fragmentation we can use query like that:

USE QualityArchive;  
GO

SELECT a.partition_number, a.index_id, name, avg_fragmentation_in_percent  
FROM sys.dm_db_index_physical_stats (DB_ID(N'QualityArchive'), 
      OBJECT_ID(N'QualityArchive.Quality'), NULL, NULL, NULL) AS a  
    JOIN sys.indexes AS b 
      ON a.object_id = b.object_id AND a.index_id = b.index_id
   where partition_number = 2
   order by partition_number;

GO

Second partition — 60% fragmentation

In case fragmentation is lower than 30% , we can use reorganize

ALTER INDEX PK_testT ON Quality
REORGANIZE PARTITION = 2

Otherwise, we should use rebuild

ALTER INDEX PK_testT ON Quality
REBUILD PARTITION = 2;   
GO

One index reorganisation will took from 10 to 40 minutes. So after all preparations we can finally make some queries to table that contains near 428 291 605 rows and weigth 58GiG.

select count(*)
FROM [QualityArchive].[dbo].[Quality]
where [Timestamp] between '2020-01-01' and '2021-01-01' and [Name] = '47000'

2 112 rows @ 1m 24s

select count(*)
FROM [QualityArchive].[dbo].[Quality]
where [Timestamp] between '2020-01-01' and '2021-01-01' and [Name] = '23435' and Quality >= 27000

1 591 rows @ 1m 29s

Selection time is pretty good, insertion time also was very low.

Now some information about partitioning. When using partition techniques we can move from single storage device to multiple devices. This can be different physical drives or different disk racks.

For example, we can spread partitions to different storage drives by 12 month.

alter database QualityArchive add filegroup FG2020_1;
alter database QualityArchive add filegroup FG2020_2;
alter database QualityArchive add filegroup FG2020_3;
alter database QualityArchive add filegroup FG2020_4;
alter database QualityArchive add filegroup FG2020_5;
alter database QualityArchive add filegroup FG2020_6;
alter database QualityArchive add filegroup FG2020_7;
alter database QualityArchive add filegroup FG2020_8;
alter database QualityArchive add filegroup FG2020_9;
alter database QualityArchive add filegroup FG2020_10;
alter database QualityArchive add filegroup FG2020_11;
alter database QualityArchive add filegroup FG2020_12;

alter database QualityArchive
add file ( name = 'FG2020_1', filename = 'H:\data\fg2020_1.ndf') to filegroup FG2020_1;
alter database QualityArchive
add file ( name = 'FG2020_2', filename = 'I:\data\fg2020_2.ndf') to filegroup FG2020_2;
alter database QualityArchive
add file ( name = 'FG2020_3', filename = 'J:\data\fg2020_3.ndf') to filegroup FG2020_3;
alter database QualityArchive
add file ( name = 'FG2020_4', filename = 'K:\data\fg2020_4.ndf') to filegroup FG2020_4;
alter database QualityArchive
add file ( name = 'FG2020_5', filename = 'L:\data\fg2020_5.ndf') to filegroup FG2020_5;
alter database QualityArchive
add file ( name = 'FG2020_6', filename = 'M:\data\fg2020_6.ndf') to filegroup FG2020_6;
alter database QualityArchive
add file ( name = 'FG2020_7', filename = 'N:\data\fg2020_7.ndf') to filegroup FG2020_7;
alter database QualityArchive
add file ( name = 'FG2020_8', filename = 'O:\data\fg2020_8.ndf') to filegroup FG2020_8;
alter database QualityArchive
add file ( name = 'FG2020_9', filename = 'H:\data\fg2020_9.ndf') to filegroup FG2020_9;
alter database QualityArchive
add file ( name = 'FG2020_10', filename = 'I:\data\fg2020_10.ndf') to filegroup FG2020_10;
alter database QualityArchive
add file ( name = 'FG2020_11', filename = 'J:\data\fg2020_11.ndf') to filegroup FG2020_11;
alter database QualityArchive
add file ( name = 'FG2020_12', filename = 'K:\data\fg2020_12.ndf') to filegroup FG2020_12;

At this point we have database QualityArchive that based on 13 filegroups spreaded to 9 drives. Primary on D drive, and FG2020_* on O-H drives.

Now it’s time to tell database how manage all this partitions.

create partition function pf_dt (datetime)
as range right
for values ( '20200101', '20200201', '20200301', '20200401'
, '20200501', '20200601', '20200701', '20200801'
, '20200901', '20201001', '20201101', '20201201'
, '20210101'
);

create partition scheme ps_dt
as partition pf_dt
to (
[primary],  -- < 2020 year
FG2020_1,
FG2020_2,
FG2020_3,
FG2020_4,
FG2020_5,
FG2020_6,
FG2020_7,
FG2020_8,
FG2020_9,
FG2020_10,
FG2020_11,
FG2020_12,
[primary]   -- > 2020 year
);

Nice, let’s recreate table using “ps_dt” scheme

CREATE TABLE [dbo].[Quality](
 [Timestamp][datetime] NOT NULL,
 [Name] nchar(25) NOT NULL,
 [Quality][int] NOT NULL,
 CONSTRAINT [PK_testT] PRIMARY KEY CLUSTERED
 (
  [Timestamp] ASC,
  [Name] ASC,
  [Quality] ASC
 ) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
) ON ps_dt([Timestamp])

In conclusion, we have created database with only one partitioned table that can store time-series data with admissible query speed.

In my opinion, you should use time-series oriented databases, that can provide more effective storage and selection times.