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.
