Netherlands: Software

Introductie van Micorosoft SQL Server 2016

Issue link: http://hub-nl.insight.com/i/692679

Contents of this Issue

Navigation

Page 20 of 212

10 C H A P T E R 1 | Faster queries Note You can create a nonclustered columnstore index for disk-based tables only. SQL Server 2016 does not support this type of index for memory-optimized tables. If your OLTP workload performs many updates and deletes, the nonclustered columnstore index might become significantly fragmented and consequently less efficient. In this situation, you should consider using the COMPRESSION_DELAY option as described at "Real-time Operational Analytics: Compression Delay Option for Nonclustered Columnstore Index (NCCI)," https://blogs.msdn.microsoft.com/sqlserverstorageengine/2016/03/06/real-time-operational- analytics-compression-delay-option-for-nonclustered-columnstore-index-ncci/. Figure 1-3: Indexing for hot and warm data in a disk-based table to support analytic queries. If your OLTP table is an in-memory table, then columnstore technology benefits both the OLTP and analytic workloads. Use the INDEX…CLUSTERED COLUMNSTORE argument when you create the table, as shown in Example 1-8. When you use this argument, all columns in the table are stored as a clustered columnstore index. Example 1-8: Creating an in-memory table with a columnstore index CREATE TABLE UserSession ( SessionID INT IDENTITY(1,1) NOT NULL PRIMARY KEY NONCLUSTERED, UserID INT NOT NULL, CreatedDate DATETIME2(7) NOT NULL, ShoppingCartId INT, INDEX ix_usersession_cci CLUSTERED COLUMNSTORE ) WITH (MEMORY_OPTIMIZED = ON); Note Even when implementing a columnstore index, your analytics queries might require significant server resources such as CPU, I/O, or memory that adversely impact your OLTP workloads. In this case, another option to consider is using an AlwaysOn configuration to offload the analytics workload to a readable secondary.

Articles in this issue

Links on this page

Archives of this issue

view archives of Netherlands: Software - Introductie van Micorosoft SQL Server 2016