Nonclustered Columnstore Indexes Tuning and Tracing

Nonclustered columnstore (NCCIs) indexes are exposed to SQL Server tracing and tuning tools. For example, the SQL Server profiler can display information about which columnstore indexes are used in the system. This information makes it easy for you to assess the cost of maintaining an NCCI. It allows you to make informed decisions about any adjustments that might be required.

SIFT keys versus NCCIs

When data is inserted, updated, or deleted in a table, the SIFT keys that are defined and enabled for that table are maintained. Maintaining these SIFT indexes has performance overhead. The size of the performance overhead depends on the number of keys and SumIndexFields that have been defined for each table. So give careful consideration to the number of SIFT keys that you define. Only maintain the SIFT keys that are important for your application.

With an NCCI, only one index structure exists and needs to be maintained. So there's no need to consider which query scenarios should be indexed.

See Also

NCCI Overview)
NCCI and SQL Server
NCCI Performance
Migrating from SIFT to NCCI
SumIndexField Technology (SIFT)
FlowFields