4

I have a sql azure database that is used only for analytics.

It is constantly being updated with data pushed from the OLTP system.

The OLTP inserts/updates/deletes are pushed via webhooks to a service that propagates the changes into the analytics db in real time.

As a result, we have a db that must sustain both a OLTP and analytics work loads, althought the analytics queries performance is more important.

I haven't used columnstore indexes before, but after doing minimal research it seems like this type of payload can be good fit for nonclustered columnstore indexes. The clustered index would stay rowstore, for fast insert/update/delete.

But, I have an extra requirement that I haven't seem mentioned: multi-tenancy. When users use our analytics app, they are always associated with a single tenant id, and the analytics queries will always have a WHERE clause on the tenant id. The tenant id column is highly selective. The biggest tenant makes up for less than 5% of all rows and most tenants make up for less than 1% of rows.

Is it still a good idea to use NCCI indexes for multitenants database? If so, should I set up my index in a particular way?

Improve this question
2
  • What's the current record count of your table? How many new records are added during a typical weekly (7-day) period? What's the current clustered key defined as?
    – John Eisbrener
    Oct 31, 2017 at 13:37
  • Over 8 million rows total and growing about 80k per week. The clustered key is (tenantId, id)
    – Clement
    Oct 31, 2017 at 22:23

2 Answers 2

Reset to default
3

Is it still a good idea to use NCCI indexes for multitenants database?

If the alternative is to have a data structure that efficiently filters by tenant, then no.

If TenantId is the leading column in your clustered index, then single-tenant queries will be pretty fast. The NCCI row groups will not be segregated by tenant, and so all the row groups will need to be scanned to find rows for a single tenant.

As rows are inserted, they are inserted into the "middle" of the clustered index, typically at the "end" of the rows for the tenant. But rows are always inserted into the delta store for an CCI/NCCI. And whenever the delta store has 1 million rows in it, it's rebuilt into a columnar row group. So each row group will end up with the last million rows inserted across all tenants.

You can fix this with partitioning by TenantID, which could give each tenant (or group of tenants) a separate physical table (or NCCI). Each partition will have its own delta store, which will fill up with single-tenant rows. If you do partition, each partition might end up too small for a Columnstore to be useful. You'll want a few million rows per partition at least.

In general with multi-tenant data, you want to avoid interleaving tenant data in a way that requires you to read all tenants data to retrieve a single tenant's data.

Improve this answer
7
  • Good answer, but to add one more item: depending on the structure of the table, a filtered NCCI index on date field may provide some value as it could optimize analytical queries that access older data since the engine will take what it can from the filtered NCCI and append any additional data needed from an appropriate row-store index.
    – John Eisbrener
    Oct 31, 2017 at 14:16
  • @David Browne, I already use a clustered key (tenantId, id) and it is very fast on small tenants, but it is slow on the biggest tenants that have so many rows. Many tenants are tiny and nowhere 1M rows, but there are few huge ones for which the reports take too long to generate.
    – Clement
    Oct 31, 2017 at 22:25
  • @JohnEisbrener, I think the problem is that this approach will still merge the data of many tenants into a single partitition.
    – Clement
    Oct 31, 2017 at 22:27
  • One idea comes to mind. For each "big" tenant (e.g. > 500k rows), define a filtered NCCI index with a WHERE = <tenant_id>. What do you think?
    – Clement
    Oct 31, 2017 at 22:28
  • 1
    Instead of a filtered NCCI index for big tenants, I'd use a partition function that put that tenant in an isolated partition. EG if TenantID 1004 is big, then a RANGE RIGHT partition function with endpoints including 1004, 1005.
    – David Browne - Microsoft
    Nov 1, 2017 at 13:27
2

This is not an answer, but instead a (hopefully) better explanation of my comment left on David Browne's (much better and hopefully accepted) answer.

First, David's answer is the best approach in my opinion, and he does a great job of quickly touching on the major concerns with multi-tenant database design. Use partitioning! Use it! Partitioning is the right feature here and works with NCCIs as well.

My comment about further improving NCCI performance by using a filtered NCCI was not meant as an alternative to partitioning, rather (depending on your data behavior) you may want to use a filtered NCCI in addition to partitioning. A filtered NCCI is a good option when the following is true:

  1. For a given table where you're considering a NCCI, said table does not have more than 1 million records inserted per tenant, per week. Per the answer to my initial comment on your question, this looks to be true for your scenario.
  2. For the same table, there is also a column or columns included within its definition that implies new/volatile data or older/stable data. This could be a date field, or a combination of columns, but basically this column or columns will be used as the filter condition, and if no combination of columns exist that represents data volatility, a filtered NCCI becomes more difficult to properly define, and therefore, less useful.

First, what's so great about a filtered NCCI?

Filtered NCCIs are pretty cool in that when data being returned by a query isn't entirely contained within a filtered NCCI, the engine will still use it to pull out what data it can and then go to the (traditional) row-store indexes to pull the remaining data. This is especially helpful regarding analytical/aggregate workloads where NCCIs tend to out perform their row-store index counterparts.

This concept is better explained in this MS article, but I find this info-graphic, also taken from that article, to be quite effective at summing it all up:

enter image description here

What's the downside of using an NCCI?

It's important to know that after a NCCI is created, any data stored within a RowGroup effectively becomes read-only. Changes are not applied to the RowGroup itself, rather a record is marked as deleted and the updated data is stored in a different RowGroup within the NCCI. These changes are cataloged in the Deleted Buffer and the Deleted Bitmap so the engine knows what's valid in a given RowGroup. Proper maintenance will help minimize the performance costs of changing data, but if you have a lot of activity occurring on the newest data within a table (e.g. the "hot" data) between maintenance windows, a filtered NCCI may be the best way to segment the less volatile/warm data from the more volatile/hot data.

Why the assumptions are important?

If the first assumption is false and you will see more than 1 million records inserted into a table per tenant per week, then you can forgo a filtered NCCI and should instead create the NCCI using the additional COMPRESSION_DELAY keyword. There's a query MS recommends you run before doing this, found here, that will give you a better idea of what this value should be, but the maximum value is 10080 or 7 days time. This will keep a delta store open until either 1 million records are accumulated or the specified period of time passes and then that delta store will be converted into a NCCI RowGroup. This basically allows the NCCI to automatically work like a filtered NCCI with a sliding window. However, if you don't have enough incoming data to periodically fill a RowGroup to take advantage of this use-case, a filtered NCCI may be the better approach.

If the second assumption is false, and you don't have a column that can easily define what data is warm vs hot, a filtered NCCI becomes no better than a normal NCCI. Again, use the COMPRESSION_DELAY keyword with an appropriate value and go from there.

The cost of a filtered NCCI

Filtered NCCIs don't come without a cost; the main issue being the definition of the filter. As hot data eventually becomes warm data, the filter may also need to be adjusted so this additional data can be included within the filtered NCCI. You are unable to change the filter of a NCCI using ALTER INDEX, so changing the definition of the filter requires that you to drop the index and recreate it with the updated filter clause. This obviously will carry ramifications in that the index won't be accessible during this change, so if your database doesn't afford a long enough maintenance window for this sort of operation, don't use a filtered NCCI. Filtered NCCIs also have some other technical limitations as fully outlined here.

The benefit of a filtered NCCI will outweigh its cost when your new data is very volatile and a NCCI isn't providing a good performance boost over its row-store index counterparts. Testing is the name of the game here, but in the right situation, a filtered NCCI sitting on top of a partitioned table for your multi-tenant database could translate to big gains in performance.

Hopefully that does a better job of explaining my comment to David's earlier answer.... and again, this is not an answer, just a very long-winded comment.

Improve this answer
3
  • Thanks, I really appreciate the detailed answer. One concern I have is that our fact table is denormalized, which makes it very 'hot'. It's basically a Sales fact table. For example, whenever a product name is updated, the Sales fact table is updated to reflect the change, which could update a lot of rows, including very 'old' rows. The idea was to avoid joins on multiple tables to improve query performance, but I think this will make column store indexes unusable.
    – Clement
    Nov 2, 2017 at 5:11
  • @Clement Is data across the entire table changed on a relatively frequent basis, or is it possible that an infrequent update could touch old data? While similar, these are not the same situations and the later scenario doesn't necessarily invalidate the usefulness of a NCCI. The best answer though is to test it out and see what happens.
    – John Eisbrener
    Nov 2, 2017 at 13:30
  • The former unfortunately. I suppose I will need to do some testing, although it seems tricky because it sounds like ncci coukd perform well at first but then degrade as the delete bitmap increase. This may be a good reason for us to consider changing to a normalized star schema so that the fact table is much colder.
    – Clement
    Nov 2, 2017 at 21:09

Your Answer

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

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