Chapter 1
SQL Server 2016 is the latest edition of the Microsoft SQL Server Database. SQL Server 2016 came with a variety of new features and enhancements that deliver breakthrough performance, advanced security, integrated reporting, and capabilities. Many features have been incorporated in SQL Server 2016 which introduced first in the cloud in Microsoft Azure SQL Database. SQL Server includes the capability to dynamically migrate historical data to the cloud.
Some Enchantment and features are given below.
Faster Queries:
In SQL Server faster queries were the major problem, a user wants to data access more as you can give him.
- SQL Server 2016 gives several options for enabling faster queries. Memory- optimization table now supports even faster online transaction processing (OLTP) workloads, with better throughput as a result of new parallelized operations.
- For analytic workloads, you can take advantage of updateable, clustered column store indexes on memory-optimized tables to achieve queries that are up to one hundred times faster.
- Not only is the database engine better and faster in SQL Server 2016, but enhancements to the Analysis Services engine also deliver faster performance for both multidimensional and tabular models.
Some features and enhancements have given following.
- In-Memory OLTP enhancements
- T-SQL enhancements
- In-memory analytics
- Analysis of Services enhancements
In-Memory OLTP enhancements
In-Memory OLTP introduced in SQL Server 2014.
- In-Memory OLTP accelerate transactional workload high concurrency and too many latches by moving data from disk-based table to memory optimize table.
- In-memory OLTP can also help improve the performance of data warehouse staging by using nondurable, memory-optimized tables as staging tables. Although there were many good reasons to use memory-optimized tables in the first release of In-Memory OLTP, several limitations restricted the number of use cases for which In-memory OLTP was suitable.
New
features for memory-optimized table:
In SQL Server 2016, you can implement the
following features in memory-optimization table.
- FOREIGN KEY constraints between
memory-optimized tables, as long as the foreign key references a primary key.
- CHECK constraints.
- UNIQUE constraints.
- Triggers (AFTER) for INSERT/UPDATE/DELETE
operations, as long as you use WITH NATIVE_COMPILATION.
- Columns with large object (LOB)
types—varchar(max), nvarchar(max), and varbinary(max).
- Collation using any code page supported by
SQL Server.
- Indexes for memory-optimized tables now
support the following features:
- UNIQUE indexes.
- Index keys with character columns using any
SQL Server collation.
- NULLable index key columns.
Scaling
memory-optimized tables:
You can put more data in memory-optimized
table and also provide better flow capacity to support bigger workload. You can
store up to 2 terabytes (TB) of data in memory-optimized table when you create
a durable table by using the SCHEMA_AND_DATA option in the CREATE TABLE
statement. You can create a memory-optimized table with a row size greater than
8,060 bytes, even without a large object ( LOB ) column.
Example:
CREATE TABLE
Books
(
bookID
INT IDENTITY PRIMARY KEY NONCLUSTERED,
ISBN
NVARCHAR(4000),
Title
NVARCHAR(4000),
CreatedDate
DATETIME,
Price
DECIMAL(18,2),
Qty INT,
)
WITH (MEMORY_OPTIMIZED = ON);
|
Introducing
native client enhancements:
SQL Server 2016 gives facility to enable
Multiple Active Result Sets(MARS) when connecting to a memory-optimized table
or running a natively compiled stored procedure. This way, the database engine
can begin fetching rows from a new result set before completely retrieving rows
from an earlier request.
Note : Before use of MARS, you must enable it
explicitly in the connection string, like this:
Data
Source=<Your Server>; Initial Catalog=<Your Database>; Integrated
Security=SSPI; MultipleActiveResultSets=True
|
In many part MARS support to memory-optimized
tables physical table but some differences given below.
- If two statement applied to modify one row, a
write conflicts occurs and operation fails.
- As we know each statement runs under
SHANPSHOT iteration and 'is in batched-scoped transaction, the result of an
operation performed by one statement is not visible to another statement.
However, rollback of one batch-scoped transaction does affect other transactions
in the same batch.
- A user transaction cannot perform DDL
Operation.
Exploring
T-SQL enhancement :
Alter table support more functionality so
that you can make changes to existing memory-optimized tables without needing
to first drop table.
Alter
Table Statement:
You can use the ALTER TABLE statement to
change the table definition and indexes on memory-optimized tables.
Example:
-- Add a column and an index
ALTER TABLE dbo.books
ADD Qty INT NULL,
INDEX ix_CreatedDate(CreatedDate);
-- Alter an index by changing the
bucket count
ALTER TABLE dbo.Books
ALTER INDEX ix_UserId REBUILD WITH
( BUCKET_COUNT = 2000000);
-- Drop an index
ALTER TABLE dbo.Books DROP INDEX ix_CreatedDate;
|
In most cases, the ALTER TABLE statement runs
in parallel and writes only metadata changes to the transaction log as a
performance optimization. However, the following single-threaded operations
continue to require writing the entire altered table to the transaction log:
- Adding or altering a column with a LOB data
type.
- Adding or dropping a columnstore index.
- Any operation affecting an off-row column—a
column with a data type of char, nchar, varchar, nvarchar, binary, or varbinary
that does not fit in the 8,060-byte row—except if the operation lengthens a
column that is already off-row.
Natively
compiled modules:
Tables and store procedures compiled natively
introduced in SQL Server 2014 which enables more efficient query execution and
data access for memory-optimized tables. New ability in SQL Server 2016 is the
compile other objects like scalar user-defined functions (UDFs), inline
table-valued functions (TVFs), and triggers. In addition to the support of new
objects, the query surface area for native compilation is expanded and applies
not only to natively compiled stored procedures but to all natively compiled
modules. You can also alter a natively compiled module rather than drop and
re-create it when a change to its functionality is necessary.
Much like you do when creating a natively
compiled stored procedure, you must include the NATIVE_COMPILATION and
SCHEMABINDING options in the WITH clause when you create a natively compiled
scalar UDF. See following example.
Using LOBs in a natively compiled scalar UDF
CREATE FUNCTION dbo.TRIM_CONCAT
(
@str1
NVARCHAR(MAX),
@str2
NVARCHAR(MAX)
)
RETURNS NVARCHAR(MAX) WITH
NATIVE_COMPILATION, SCHEMABINDING
AS
BEGIN ATOMIC WITH
(
TRANSACTION ISOLATION LEVEL = SNAPSHOT, LANGUAGE = N'English'
)
RETURN RTRIM(@str1) + LTRIM(@str2);
END;
|
SQL Server 2016 also supports the following
capabilities for natively compiled modules:
- Use the OUTPUT clause with the INSERT,
UPDATE, and DELETE statements in a natively compiled stored procedure.
- Omit the EXECUTE AS clause when you want
EXECUTE AS CALLER as the execution context of a natively compiled module. You
include an EXECUTE AS clause in a module only if you need to enforce a
different execution context.
- Use any SQL Server collation in an
expression.
- Use any of the built-in math and security
functions.
- Use SELECT DISTINCT in the SELECT clause.
- Use OUTER JOIN, EXISTS, IN, and scalar
sub-queries in the FROM clause.
- Use OR or NOT in the WHERE clause.
- Combine result sets by using the UNION or
UNION ALL operations.
- Configure nested execution of natively
compiled modules by using the EXECUTE statement.
Managing
memory-optimized tables:
We are aware with STATITICS, SQL Server 2016
give facility to update automatically but you have to set database capability
130. Statistics are updated by data sampling rather than by a full scan. If you
are creating memory-optimized table in SQL Server 2014 or SQL Server 2016 then
run the following script to update STATITICS automatically for tables.
-- Enable automatic update on the
database first, if necessary
ALTER DATABASE CURRENT
SET AUTO_UPDATE_STATISTICS
ON; GO
-- Set the compatibility level, if
necessary
ALTER DATABASE CURRENT
SET COMPATIBILITY_LEVEL
= 130; GO
DECLARE @sql NVARCHAR(MAX) = N'';
SELECT @sql += N'UPDATE STATISTICS '
+ quotename(schema_name(t.schema_id))
+ N'.' + quotename(t.name)
+ ';' + CHAR(13) + CHAR(10)
FROM sys.tables AS t
WHERE t.is_memory_optimized = 1;
EXECUTE sp_executesql @sql;
|
Planning
data migration to memory-optimized tables:
SQL Server 2016 includes several tools for
helping you plan a migration of data in existing tables to In-Memory OLTP.
Reports and tasks in SQL Server Management Studio (SSMS) help you evaluate
workloads and create checklists of migration steps. You can also use SQL Server
PowerShell or Windows PowerShell to evaluate candidate objects for migration.
Analysis Services enhancements:
There are two types of modes in Analysis Service to support fast reporting and analytics.
- Multidimensional
- Tabular
Understanding
multidimensional performance improvements:
The performance of queries includes an
unnatural hierarchy which is faster than its previous version. Although natural
hierarchy remains faster yet. Storage structure of an unnatural hierarchy is
unchanged. Instead, the engine is now able to execute the query in a more
optimal fashion when it encounters a drill-down query.
Example:
--Drilling down one level of a
hierarchy
SELECT NON EMPTY
Hierarchize(DrilldownMember({{DrilldownLevel({[Product].[Color-Size].[All]},,, INCLUDE_CALC_MEMBERS)}},
{[Product].[Color-Size].[Color].&[White]},,,INCLUDE_CALC_MEMBERS))
DIMENSION PROPERTIES PARENT_UNIQUE_NAME,HIERARCHY_UNIQUE_NAME ON
COLUMNS
FROM [Adventure Works]
WHERE ([Measures].[Reseller Sales Amount]) CELL PROPERTIES VALUE,
FORMAT_STRING,
LANGUAGE, BACK_COLOR, FORE_COLOR, FONT_FLAGS
|
Understanding
tabular performance improvements:
Tabular-model performance has always been
good because of its use of the same columnar technology as columnstore indexes
and in-memory OLTP. However, it’s even better in SQL Server 2016 because of DAX
optimizations, storage-engine caching improvements, and changes to the way that
T-SQL is generated for DirectQuery mode.
The first DAX optimization is a reduction in
the number of queries sent to the storage engine from Power BI and Power BI
Desktop. Prior to this change, a single chart could potentially send hundreds
of queries to the storage engine. Now the Power BI client tools generate a
single storage-engine query, whether the storage engine retrieves data from
memory or from SQL Server in DirectQuery mode—as long as the query includes
only simple measures. Furthermore, a single DAX query can return multiple
result sets as intermediate results that Power BI can use across multiple
requests.
The next optimization executes queries more
efficiently in any client tool, including Microsoft Excel or SQL Server
Reporting Services (SSRS) in addition to the Power BI client tools. This
efficiency is a result of changes in the following areas that affect measure
execution:
- Variables As we describe in more detail in Chapter 6, “More analytics,” the use of variables in a DAX expression for measures allows you to reuse logic within the same expression, which can reduce overall execution time.
- Conditional expressions Rather than evaluating each branch of an IF or SWITCH conditional expression, a branch with a false condition no longer generates a storage-engine query.
In-memory analytics
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.
Reviewing columnstore index enhancements:
1. 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.
2. 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.
3. SQL Server 2016 also includes support for columnstore indexes in readable secondary databases in Always On Availability Groups.
How to create columnstore index in a disk-based table
CREATE CLUSTERED COLUMNSTORE INDEX ix_cci_Product ON ProductBig WITH (DROP_EXISTING=ON);
|
DROP_EXISTING=ON when you used this option it drop the column before and create a new one.
Create a non-cluster Columnstore index with filtered condition
CREATE NONCLUSTERED COLUMNSTORE INDEX ix_salesorderheaders_ncci
ON Sales.Order (CustomerID, ISBN, QTY, Price, ShippingDate)
WHERE ShippingDate < '2016-01-01';
|