0

I've got a SQL Server 2019 with a large fact table (currently ~100 million rows but will most likely be >1 billion within a year and continue to increase for a long time).
The primary objective is to make it efficient to query the table by a date range (done via power bi).

The table definition (omitting a bunch of stuff not relevant for the question):

CREATE TABLE [fact].[dv](
    [system_id] [int] NOT NULL,
    [system_row_id] [bigint] NOT NULL,
    [timestamp] [datetime2](3) NOT NULL,
    [equipment_id] [int] NOT NULL,
    [event_id] [int] NOT NULL,
    [event_value] [int] NOT NULL,
    [date] AS cast([timestamp] as date) PERSISTED,

    constraint pk__fact_dv primary key clustered(system_id, system_row_id)
)

Example query:

select
    x,y,z
from fact.dv
where [date] = '20231001'
    and system_id = 20
    and equipment_id in (1,2,3)
    and event_id in (5,6,7)

The current design is useless for querying by date so my first thought was to create a non-clustered compound index with the date as the leading column. Since I need to be able to query for all columns this would effectively double the size of my largest table and in practice double the size of the database.

My second idea (and this is where I need input from people more experienced than me) is to:

  • Replace the current primary key with a unique constraint (and keept the not null constraints on the two columns).
  • Use a clustered compound index with date as the leading column.

If I'm correct this solution would let me:

  • Keep the current data integrity (assuring that none of system_id or system_row_id is null and that their combination is unique).
  • Make it efficient to query the table by date.
  • Leave me with roughly the same table size (mainly adding the size of the index that supports the unique constraint)

Are there any drawbacks with this solution except for the fact that you would react on not seeing a primary key on the table?
Are any of my assumptions wrong?
Any other suggestions?

9
  • 2
    a) Your PK does not have to be clustered. If it does not make sense, don't do it. Date is a logical cluster for some situations. b) Partitioning might help here (date or various IDs) depending on how many and how they are allocated. c) Data warehouses sometime do omit enforced integrity as they're built from known data in a known way. If you trust your ETL you can drop unique constraints, and even PK/FK if it makes sense to do so. Commented Oct 24, 2023 at 9:01
  • 1
    the drawback of your solution is that a clustered index key (and you want to make it compound and not unique) is a part of any nonclustered index so the overall volume will be increased. if there is no unique combination even if you use datetime2(3) as a part of clustered key, the volume will be increased by adding a hidden integer to make it unique. yore condition on the date could be rewritten as a range condition on timestamp but as you said no other column added to id makes a couple unique so it's a problem for your greate volum of data
    – sepupic
    Commented Oct 24, 2023 at 9:13
  • 1
    It may be a solution to make clustered index as unique on (date,system_id, system_row_id) this way the combination is unique and uses 3 + 4 + 8 bytes vs old key of 4 + 8, it's important that it is unique and when you create you nonclusterd unique index it will have the minimum bytes to be added, on the leaf level you'll have only date-part to be added as a link to clustered index, as other 2 columns are part of non-clustered index key. two of the leading fields of this clustered index are usefull for your query too.
    – sepupic
    Commented Oct 24, 2023 at 9:28
  • 2
    @sepupic The uniquifier is only added on pages where there are duplicates in the clustered key. Otherwise it takes up no space (although it is present in the query plan and in hashtables etc). But then we aren't enforcing uniqueness as you point out. Commented Oct 24, 2023 at 10:44
  • 1
    @J.D. The unique nonclustered index wil be created for certain: "Replace the current primary key with a unique constraint" and it will contain the compound clustered index key on the leaf level (the biggest one)
    – sepupic
    Commented Oct 24, 2023 at 12:08

2 Answers 2

1

As mentioned in the comments by LoztInSpace, your primary key doesn't need to also be the clustered index. That is just the default (since most times it makes sense). But in a data warehouse especially, it may make sense to have an alternative clustered index defined.

Note that the following is resource intensive on larger tables and should likely be done during a maintenance window.

You can drop the existing clustered index from your data warehouse table like so (note this will drop the Primary Key as well):

ALTER TABLE fact.dv
DROP CONSTRAINT pk__fact_dv;

Then you can re-cluster the table like so, for example:

CREATE CLUSTERED INDEX WhateverNameYouWantToGiveIt ON fact.dv ([date], system_id, equipment_id, event_id);

If you want to keep the Primary Key on the table, then you'll need to re-add it like so:

ALTER TABLE fact.dv
ADD PRIMARY KEY (system_id, system_row_id);

Note that this will create it with a nonclustered index backing it. If you already have the data integrity managed for these two fields in your source data system, then as LoztInSpace mentioned, you can consider the possibility of not enforcing it in your data warehouse at the tradeoff of saving space.

Depending on the size of your table, how often the data is updated as opposed to inserted, and what you're actually doing in the SELECT list, you may find a clustered columnstore index serves you best because of its compression type and batch mode execution.

You can create a columnstore index like so:

CREATE CLUSTERED COLUMNSTORE INDEX WhateverNameYouWantToGiveIt ON fact.dv ([date], system_id, equipment_id, event_id, x, y, z);
6
  • First of all there is no way to drop clustered index without dropping the constraint mentioned in the question: pk__fact_dv primary key clustered(system_id, system_row_id), so it's completely wrong to think "This won't affect your primary key, which will remain in place", re-creating of clustered key in the case described in the question means: 1) alter table [fact].[dv] drop constraint pk__fact_dv wich will completely remove any uniqueness in that table and turn in into heap. Second, if you create clustered index that I suggested at least copy-paste it as it is, with the UNIQUE clause.
    – sepupic
    Commented Oct 24, 2023 at 12:45
  • And the third. There is no gain in clustered columnstore in this case as the typical query does not read all the rows and just some columns but reads all the columns "Since I need to be able to query for all columns" with the range filter
    – sepupic
    Commented Oct 24, 2023 at 12:48
  • @sepupic "no way to drop clustered index without dropping the constraint mentioned in the question: pk__fact_dv primary key" - It's not often that I move my clustered index off my primary key, so I could've been misremembering. Updated my answer in that regard until I can test at a computer. "completely remove any uniqueness in that table and turn in into heap" - It's only a heap while no clustered index exist, once a clustered index is created, it's no longer a heap. That has nothing to do with uniqueness.
    – J.D.
    Commented Oct 24, 2023 at 12:51
  • "if you create clustered index that I suggested at least copy-paste it as it is, with the UNIQUE clause." - For what reason do you believe this? "There is no gain in clustered columnstore in this case as the typical query does not read all the rows and just some columns but reads all the columns" - I don't think you can make the prediction that you know batch mode execution won't be beneficial here without knowing the statistical properties of their data and the query results cardinality - short of actually testing it.
    – J.D.
    Commented Oct 24, 2023 at 12:51
  • 1
    @sepupic "I just wonder how do you hope to maintain the uniqueness" - As I already mentioned, I updated my answer mentioning they can choose to re-add the PK if they decide, to maintain uniqueness integrity. No need to also add additional columns to the clustered index just so it can be defined as UNIQUE then. And as LoztInSpace mentioned in the comments, because it's a data warehouse, it's not uncommon to not enforce the same constraints as the source OLTP database that's already maintaining the data integrity. So it's not as much of a concern here anyway. OP's choice to add back the PK.
    – J.D.
    Commented Oct 24, 2023 at 13:03
0

You are on a SQL Server 2019, so as mentioned briefly by J.D , you should definitely go to a CLUSTERED COLUMNSTORE INDEX ("CCI")... ONLY if you never DELETE and never UPDATE, due to the huge drawback cost of this instructions.

By example, we have a CCI table where we delete 50 million rows a day then insert 52 million rows, it is a nightmare for the engine, we have to revert it.

Pay also attention that DELETE are SOFT DELETE behind the scene, so every periodical REORG of indexes may last hours and hours.

The biggest advantages of using CCI's are:

  • Specially designed for DWH and BI tool queries such as Power BI
  • All your columns will be aggregated in Rowgroups\segments (then i.e. "equipment_id in (1,2,3)" becomes easy and fast for the engine)
  • Compression will be enormous: around 10 times more compared to clustered index
  • Batch mode instead of row mode. This makes your queries to run very fast.

ATTENTION POINT:

CLUSTERED COLUMNSTORE INDEX compression ratio greatly depends on sorted input data. This will be the case for your D2D data load, but the initial feeding should respect it too!

Another drawback in your case, computed columns are not supported in CCI's, so you will have to insert your timestamp twice - first in [Timestamp], second casted into [date] (see below)

=== In summary ===

Initial creation :

CREATE TABLE [fact].[dv_new](
    [system_id] [int] NOT NULL,
    [system_row_id] [bigint] NOT NULL,
    [timestamp] [datetime2](3) NOT NULL,
    [equipment_id] [int] NOT NULL,
    [event_id] [int] NOT NULL,
    [event_value] [int] NOT NULL,
--    [date] AS cast([timestamp] as date) PERSISTED, -- Computed columns are not allowed in CCI
    [date] AS date
)

CREATE CLUSTERED COLUMNSTORE INDEX ON [fact].[dv_new]

Initial feeding :

INSERT INTO 
    [fact].[dv_new]
    ([system_id],[system_row_id],[timestamp],[equipment_id],[event_id], [event_value],[date])
SELECT 
    [system_id],
    [system_row_id],
    [timestamp],
    [equipment_id],
    [event_id],
    [event_value],
    [date]
FROM 
    [fact].[dv]
ORDER BY
    [date] -- Very important for the compression ratio !

Day 2 day data load :

-- Your future Day to day data load, your INSERTS will have to include 1 additional column  :

INSERT INTO 
    [fact].[dv_new]
    ([system_id],[system_row_id],[timestamp],[equipment_id],[event_id], [event_value],[date])
VALUES
    ([system_id],[system_row_id],[timestamp],[equipment_id],[event_id], [event_value],cast([timestamp] as date))

Afterwards just rename your initial table into [dv_old] and rename the new into [dv]

I hope it would help.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.