Netherlands: Software

Introductie van Micorosoft SQL Server 2016

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

Contents of this Issue

Navigation

Page 18 of 212

8 C H A P T E R 1 | Faster queries Example 1-5: Generating migration checklists by using Windows PowerShell commands > [System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SMO') > Save-SqlMigrationReport –Server "" -Database "" - FolderPath "" > Save-SqlMigrationReport –Server "" -Database "" -Object -FolderPath "" In-memory analytics Whereas OLTP workloads typically involve frequent, concurrent inserts and updates, analytic workloads are characterized by read operations and aggregations of much larger datasets. Traditionally, these workloads have been implemented in separate environments to avoid the resource contention between OLTP writes and analytic reads. Furthermore, analytic workloads are often moved to other storage structures, such as SQL Server Analysis Services (SSAS) multidimensional cubes or tabular models, to take advantage of special features useful for analytics. However, moving data to another structure introduces latency in the data and requires periodic processes to run to refresh the data. SQL Server 2016 adds more options for performing real-time analytics on rapidly changing in- memory data by adding support for columnstore indexes on memory-optimized OLTP tables. Note If you still need SSAS to support other requirements (such as consolidation with other sources, centralization of business logic, or translations, to name a few), you can configure a cube partition for a multidimensional model to use relational online analytical processing (ROLAP) mode or configure a tabular model to use DirectQuery mode. Both ROLAP mode and DirectQuery mode can take advantage of data compression and faster query response times so that you have less data overall to manage, fewer processes to monitor, and no data to move between environments. When you create an updateable columnstore index on a memory-optimized table, the database engine creates a separate copy of the data that your analytics workload uses while your OLTP workloads continue to run on the table data. That way, resource contention between the two workloads is significantly reduced when they run simultaneously. As changes occur in a table, the database engine automatically manages changes to the index to ensure that it remains current with the underlying table. Reviewing columnstore index enhancements An enhancement to clustered columnstore indexes in SQL Server 2016 is the ability to have a nonclustered B-tree index on a table that also has a clustered columnstore index. This capability is useful for workloads that have a significant percentage of singleton lookups but are mostly analytic in nature. While the columnstore index is extremely efficient for large scanning operations such as data aggregation, the B-tree structure is much more efficient for returning a few records. In addition, this release adds other significant increases in performance and scalability to columnstore indexes. One of the biggest benefits is the improvement in batch-execution mode, a feature of columnstore indexes that allows results to be processed in batches of approximately 1,000 rows rather than row by row. Batch processing greatly reduces execution time and CPU utilization for common data warehouse queries such as aggregations. In SQL Server 2016, batch-execution mode is extended to single-threaded queries, sort operations (such as ORDER BY), and T-SQL window functions. SQL Server 2016 also includes support for columnstore indexes in readable secondary databases in Always On Availability Groups. Furthermore, it includes support for read committed snapshot isolation (RCSI) and snapshot isolation (SI) modes. Consequently, you can design a scale-out data warehouse in which analytic workloads are spread across multiple nodes.

Articles in this issue

Archives of this issue

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