Microsoft 228-10805 User Manual
Mission-Critical Performance
Technical White Paper
Published: March 2016
Applies to: Microsoft SQL Server 2016
Summary: As the volume and complexity of data continue to increase, organizations require a new
approach to their mission-critical capabilities. This white paper examines how capabilities built into
Microsoft SQL Server—including enterprise-grade performance, security, availability, and scalability—
define a “new mission critical” that answers what kinds of capabilities organizations need to compete in a
dynamic global landscape.
Page 2
Copyright
The information contained in this document represents the current view of Microsoft Corporation on the
issues discussed as of the date of publication. Because Microsoft must respond to changing market
conditions, it should not be interpreted to be a commitment on the part of Microsoft, and Microsoft
cannot guarantee the accuracy of any information presented after the date of publication.
This white paper is for informational purposes only. MICROSOFT MAKES NO WARRANTIES, EXPRESS,
IMPLIED, OR STATUTORY, AS TO THE INFORMATION IN THIS DOCUMENT.
Complying with all applicable copyright laws is the responsibility of the user. Without limiting the rights
under copyright, no part of this document may be reproduced, stored in, or introduced into a retrieval
system, or transmitted in any form or by any means (electronic, mechanical, photocopying, recording, or
otherwise), or for any purpose, without the express written permission of Microsoft Corporation.
Microsoft may have patents, patent applications, trademarks, copyrights, or other intellectual property
rights covering subject matter in this document. Except as expressly provided in any written license
agreement from Microsoft, the furnishing of this document does not give you any license to these
patents, trademarks, copyrights, or other intellectual property.
© 2016 Microsoft Corporation. All rights reserved.
Microsoft, Active Directory, Microsoft Azure, Excel, SharePoint, SQL Server, Windows, and Windows Server
are trademarks of the Microsoft group of companies.
All other trademarks are property of their respective owners.
Page 3
Contents
Microsoft SQL Server evolution...................................................................................................... 5
Mission-critical performance with SQL Server ............................................................................ 7
Performance ............................................................................................................................................................7
In-Memory Online Transaction Processing......................................................................................................................... 7
In-memory analytics ................................................................................................................................................................. 10
Real-time operational analytics ............................................................................................................................................ 12
Query-processing enhancements........................................................................................................................................ 14
Reduced database size and increased performance: data and backup compression ........................................ 15
Proactive troubleshooting and diagnostics: Performance Data Collector and Management Studio............ 15
New in SQL Server 2016 ........................................................................................................................................................... 16
Security .................................................................................................................................................................. 21
Secure by default: lowering vulnerability ........................................................................................................................... 21
New in SQL Server 2016 ...........................................................................................................................................................22
Enhanced in SQL Server 2016 ................................................................................................................................................27
Availability ............................................................................................................................................................ 29
High availability of mission-critical systems ......................................................................................................................29
AlwaysOn .....................................................................................................................................................................................29
New in SQL Server 2016 ........................................................................................................................................................... 31
Enhanced in SQL Server 2016 ................................................................................................................................................ 31
Online database operations ...................................................................................................................................................33
Predictable, efficient, and flexible data backups .............................................................................................................33
Scalability.............................................................................................................................................................. 34
Support for Windows Server Core ...................................................................................................................................... 34
Faster live migration ................................................................................................................................................................ 34
Live migration for non-clustered virtual machines .........................................................................................................35
Cluster-Aware Updating..........................................................................................................................................................35
Dynamic Quorum ......................................................................................................................................................................35
Enhanced in SQL Server 2016 ................................................................................................................................................35
Conclusion ........................................................................................................................................ 37
Page 4
More information ........................................................................................................................... 37
Feedback............................................................................................................................................................... 37
Page 5
Microsoft SQL Server evolution
By developing and enhancing new features, Microsoft SQL Server continues to evolve and stay ahead of
organizational data needs. Customers have responded to this evolution by showing confidence in using
SQL Server to manage their mission-critical data. Industry analysts have also responded positively. For
example, Gartner recently rated SQL Server as having the most complete vision of any operational
database management system (Figure 1).
Figure 1: Gartner Magic Quadrant for operational database management systems (2015)
In addition, SQL Server has consistently added groundbreaking functionality over the last 15 years (Figure
2).
Page 6
Figure 2: SQL Server functionalities added across releases
SQL Server 2016 introduces many new features and enhancements, including:
Performance improvements via In-Memory OLTP (online transaction processing), Real-Time
Analytics, Query Store, and more
Security improvements via Always Encrypted and Row-Level Security
Availability improvements via failover clustering, availability groups, and AlwaysOn improvements
Scalability, security, and availability improvements through support for Windows Server and
Server Core
Page 7
Mission-critical performance with SQL
Server
SQL Server’s evolution mirrors the growing critical nature of data for enterprises. Data is the new currency,
and it has become a major competitive differentiator. Companies with a data-centric culture are
harnessing increasingly diverse data. This data is not just relational or internal but includes both relational
and non-relational, as well as internal and external data sources. These companies are using new
analytical models to review historical data and apply it to help predict the future. Insights from the data
are many, which are shared more broadly in the organization. And, in many cases, much of this analysis is
dispensed at near real-time speeds. Companies with a data-centric culture are more productive, more
efficient with their operations, and they innovate faster—all leading to improvements in their bottom line.
More than ever, organizations need mission-critical operations that are easy to deploy and that are
balanced with faster time-to-solution. This white paper reviews four main areas in which SQL Server
continues to deliver in this manner: performance, security, availability, and scalability.
Performance
SQL Server consistently leads in performance benchmarks, such as TPC-E and TPC-H, and in real-world
application performance. With SQL Server 2016, performance is enhanced with a number of new
technologies, including in-memory enhancements, Query Store, and temporal support, to name a few.
SQL Server’s integrated in-memory toolset goes far beyond isolated features to support dramatic
performance improvements in a wide range of scenarios. These technologies include In-Memory Online
Transaction Processing (In-Memory OLTP), primarily for transactional workloads, In-Memory Columnstore
for decision-support workloads (discussed in the Microsoft SQL Server: Deeper Insights Across Data white
paper), and the new Query Store feature, which allows you to monitor and optimize query plans for
particular application scenarios over time―providing additional performance-tuning opportunities.
In-Memory Online Transaction Processing
In-memory technology for SQL Server dramatically improves the throughput and latency of SQL Server
OLTP capabilities. It is designed to meet the requirements of the most demanding transaction processing
applications, and Microsoft has worked closely with a number of companies to prove these gains. The
feature set of In-Memory OLTP includes the following:
Memory-optimized tables: There are two types of memory-optimized tables. Durable tables are
fully logged and persist over server restarts. Non-durable tables do not persist over server restarts
and are most commonly used in lieu of global temp tables in the user database or in scenarios
where persistence is not needed, such as staging tables in an Extract Transform Load (ETL)
process.
Memory-optimized table variables: These variables are created using memory-optimized table
types. Variables are stored in-memory, leading to more efficient data access because they use the
Page 8
same memory-optimized algorithms and data structures as memory-optimized tables—
particularly when using natively compiled stored procedures.
Natively compiled stored procedures: SQL Server can natively compile stored procedures that
access memory-optimized tables. Native compilation enables faster data access and more
efficient query execution than interpreted (traditional) Transact-SQL. Natively compiled stored
procedures are parsed and compiled when they are loaded to native DLLs (dynamic-link libraries).
This is in contrast to other stored procedures that are compiled on first run. They have an
execution plan created and reused, and they use an interpreter for execution.
Natively compiled scalar user-defined functions (UDFs): These replace traditional scalar UDFs
that do not perform data access, and this replacement reduces UDF runtime. Natively compiled
scalar UDFs cannot access disk-based tables. If data access is required, consider migrating the
table to memory-optimized (if no data access occurs, migration is not required).
In-Memory OLTP is designed on the following architectural principles:
Optimize for main-memory data access. Storage-optimized engines (such as the current OLTP
engine in SQL Server) will retain hot data in a main-memory buffer pool based on frequency of
access. The data access and modification capabilities, however, are designed so that data may be
paged in or out to disk at any point. With In-Memory OLTP, you place tables used in the extreme
transaction-processing portion of an application into memory-optimized main-memory
structures. The remaining application tables, such as reference data details or historical data, are
left in traditional storage-optimized structures. This approach enables you to optimize hotspots
for memory use, without having to manage multiple data engines. Main-memory structures for
In-Memory OLTP eliminate the overhead and indirection of the storage-optimized view while still
providing the full atomicity, consistency, isolation, and durability (ACID) properties you expect
from a database system.
Include tools for migration. To identify the appropriate tables and memory structures for
utilizing In-Memory OLTP, SQL Server 2016 Management Studio includes tools designed to assist
users in transitioning to In-Memory OLTP. These include transaction performance analysis to
identify objects that would benefit from migration, migration advisors to assist in migrating diskbased tables to memory-optimized tables, and migration of traditional stored procedures and
functions to natively compiled objects.
Accelerate business-logic processing. In-Memory OLTP, queries, and procedural logic in
procedures that are stored in Transact-SQL (T-SQL) are compiled directly into machine code
through aggressive optimizations that are applied at compilation time. Consequently, the stored
procedure can be executed at the speed of native code.
Provide frictionless scale-up. In-Memory OLTP implements a highly scalable concurrency
control mechanism and uses a series of lock-free data structures to eliminate traditional locks and
latches while guaranteeing the correct transactional semantics that ensure data consistency.
Integrate into SQL Server. One of the most impressive things about In-Memory OLTP is that it
achieves breakthrough improvements in transactional processing capabilities without requiring a
separate data management product or new programming model. This enables an integrated
developer and database administrator (DBA) experience with the same T-SQL, client stack,
tooling, backup and restore, and AlwaysOn capabilities. By offering in-memory functionality
Page 9
within SQL Server, your total cost of ownership ends up being lower than it would be if you were
to purchase, manage, and maintain a separate system for handling in-memory processing.
In-Memory OLTP enhancements in SQL Server 2016
Performance and scaling improvements
Improvements to In-Memory OLTP enable scaling to larger databases and higher throughput in order to
support bigger workloads. In addition, a number of limitations on tables and stored procedures have been
removed to make it easier to migrate your applications and leverage the benefits of In-Memory OLTP.
Scalability improvements include:
Multiple threads to persist memory-optimized tables
Multi-threaded recovery and merge operations
Dynamic management views
Parallel plan support for accessing memory-optimized tables using interpreted T-SQL
Parallel support for hash indexes
Transact-SQL improvements
Query surface area in native modules has been improved to include support for subqueries in SELECT
statements, nested execution of natively compiled modules, natively compiled inline table-valued
functions, built-in security functions, and increased support for built-in math functions. Additional native
support for query constructs such as UNION, DISTINCT, OUTER JOIN, OR, and NOT is now available.
Memory-optimized tables now support nullable index key columns; large object (LOB) data types;
constraints such as FOREIGN KEY, CHECK, and UNIQUE; and triggers (AFTER) for insert, update, and delete
operations.
For more information: Supported features
Cross-feature support
System-versioned temporal tables provide a solution for scenarios such as data auditing and point-intime analysis of OLTP workloads. System-versioned temporal tables for memory-optimized tables are able
to provide high transactional throughput and lock-free concurrency while simultaneously storing large
amounts of historical data. Implementing system-versioned temporal tables with memory-optimized
tables is simple and provides an easier route than manual implementation for maintaining historical data.
For more information: System-versioned temporal tables
Query Store allows you to monitor the performance of natively compiled code for workloads running
In-Memory OLTP. Compile and runtime statistics are collected and exposed just as they are for disk-based
workloads. You can continue using Query Store views in SQL Server Management Studio as well as
custom scripts you have developed for disk-based workloads on migrated In-Memory OLTP workloads.
This saves your investment in learning Query Store technology and makes it generally usable for
troubleshooting all types of workloads.
For more information: Query Store with In-Memory OLTP
Page 10
Row-Level Security (RLS) is supported for memory-optimized tables. RLS is a new feature introduced in
SQL Server 2016 that allows developers to restrict access to rows based on filter and block predicates.
Filter predicates seamlessly exclude rows from query results, returning only those records for which a user
is authorized. Block predicates prevent unauthorized users from making changes to records for which a
user is not authorized, particularly in the case of bulk update/delete commands. The execution of both
predicate types is transparent to end users.
For more information: Row-Level Security in memory-optimized tables
Multiple Active Result Sets (MARS) support using queries and natively compiled stored procedures.
MARS enables data requests from multiple queries without the need to retrieve each result set before
sending a request to fetch rows (via MARS-enabled connection).
For more information: MARS
Transparent Data Encryption (TDE) improvements include support for the storage of memory-optimized
tables that can be encrypted to enable TDE of the database.
In-memory analytics
With the explosion of devices (such as mobile phones, smart meters, and smart cars) generating data,
organizations are faced with the challenge of managing exponentially growing data while still delivering
effective, interactive data-analysis response times for making faster business decisions. The traditional
storage model for storing data continues to work well for transactional workloads, but it increasingly falls
short when processing large numbers of rows efficiently for analytics workloads.
SQL Server 2012 addresses these challenges with an alternate storage model that stores data as columns
rather than rows. This means you can leverage columnstore index to achieve high data compression,
typically 10 times more, while speeding up the performance of analytics queries by up to 100 times.
Figure 3 shows a simple relational table stored as individual columns. Each white box represents
approximately 1 million values of a column stored together. The values in a column across multiple rows
are drawn from the same domain. More compression can be achieved in this manner than with page
compression, for example, where data is stored as rows.
Page 11
Figure 3: Conceptual diagram of a columnstore
In addition to reducing storage, this can speed up analytic queries (which typically scan millions or even
billions of rows) by reducing I/O significantly. Columnstore storage also enables the fetching of only those
columns needed to execute the query. This, in turn, can further reduce the I/O. For example, if the
analytics query only accesses columns C1, C2, and C3, the other columns—C4 and C5—are skipped. This
differs from a rowstore, where all columns in a row must be fetched regardless of the references in the
query. Speed can be increased even more (typically four times) with a new batch mode execution mode
that processes a set of rows (typically around 1,000) together. Perhaps the biggest benefit is that no
application changes are required once you migrate your application to use columnstore index. All queries
run unchanged but significantly faster.
The columnstore index in SQL Server 2016 has been significantly enhanced with the following key
improvements:
Ability to create one or more traditional nonclustered indexes on a table with a clustered
columnstore index. This enables users to enforce primary and foreign key (PK/FK) constraints and
speed up the query performance for short-range queries.
Ability to use RCSI (read-committed snapshot) and SI (snapshot isolation) to get transactional
consistent results with no blocking with concurrent DML (data manipulation language)
operations.
Ability to offload analytics workload to readable secondary in AlwaysOn configuration.
Improved analytics query performance with string predicate pushdown, aggregate pushdown,
and new batch mode operators such as SORT and Window Aggregates. You will get these
benefits with no changes to your analytics application after you upgrade to SQL Server 2016.
Online columnstore index maintenance to defragment the index by removing the deleted rows.
Improved monitoring and troubleshooting with new DMVs (dynamic management views),
extended events (XEvents), and performance counters.
Updateable nonclustered columnstore index (NCCI) to enable real-time operational analytics
(described in detail in the next section).
Page 12
Real-time operational analytics
SQL Server 2016 introduces real-time operational analytics—the ability to run both analytics and
transactional workloads on the same database tables concurrently. SQL Server enables you to create an
updateable nonclustered columnstore index on traditional rowstore tables. Transactional workloads run
against the rowstore, while analytics workloads run against the columnstore index (Figure 4). SQL Server
automatically maintains all changes to the indexes, so the transactional changes are always up-to-date for
analytics. The time it takes to maintain a columnstore index can be minimized or eliminated by using
compression delay or by creating a filtered columnstore index on colder data. These factors make it
possible and practical to run analytics in real-time as opposed to traditional data warehouse
implementations that require data migration/transformation from a transactional system to a dedicated
data warehouse. Real-time operational analytics is supported both on disk-based and memory-optimized
tables.
Figure 4: Running of analytics on columnstore indexes
Real-time operational analytics for disk-based tables
To get started with real-time analytics, you simply create a nonclustered columnstore index for analytics
queries and drop all other indexes that were created for analytics. You do not need to change your
application, because the query optimizer estimates the performance cost and will automatically choose
the best index for each query. Analytics queries will run against the columnstore index, and the
transactional workload continues to run against the rowstore using B-tree indexes (Figure 5). The
transactional workload runs without requiring any changes but may incur additional overhead to maintain
the columnstore.
Loading...