Ask Learn
Preview
Ask Learn is an AI assistant that can answer questions, clarify concepts, and define terms using trusted Microsoft documentation.
Please sign in to use Ask Learn.
Sign inThis browser is no longer supported.
Upgrade to Microsoft Edge to take advantage of the latest features, security updates, and technical support.
Note
Access to this page requires authorization. You can try signing in or changing directories.
Access to this page requires authorization. You can try changing directories.
In the previous blog https://blogs.msdn.microsoft.com/sqlserverstorageengine/2016/02/29/real-time-operational-analytics-simple-example-using-nonclustered-clustered-columnstore-index-ncci/ , I showed you how you can create NCCI on a regular rowstore table and speed up analytics query performance significantly. Now, let us shift our focus to the overhead of maintaining NCCI on transactional workload.
Any additional index that you create on a table will have additional overheard as part of index maintenance. For example, any new row inserted into the table, it needs to be inserted into the index. Similarly, for updates/deletes operations. The first question to be asked is how expensive it is to maintain a columnstore index compared to regular btree index. Here is the table comparing NCCI and regular btree nonclustered index (NCI).
Key points for each of the operations are
I realize this explanation touched upon many internal details that you may not care about. The key takeaway is that NCCI is more expensive to maintain than a regular btree index. If your workload has one or more btree indexes primarily created for range queries, you may be able to drop and replace them by a single NCCI. This may reduce the total number of indexes on the table and it may infact speed up the transactional workload. Not suggesting this is a common case but definitely one of the possibilities.
In the next blogs, we will discuss filtered NCCI and compression delay to minimize the overhead of NCCI maintenance.
Thanks
Sunil
Ask Learn is an AI assistant that can answer questions, clarify concepts, and define terms using trusted Microsoft documentation.
Please sign in to use Ask Learn.
Sign in