What's new in SQL Server 2014? SQL Server 2014 New Features | Important
New Features in SQL Server 2014
Let's take a look at the new features and improvements with
SQL Server 2014.
Power BI for
Office 365 Integration
- Power Pivot (formerly PowerPivot).
- Power View
- Power Query (formerly code-named Data Explorer)
- Power Map (formerly code-named GeoFlow)
In-Memory
OLTP Engine
The
In-Memory OLTP engine is designed for high concurrency and uses a new
optimistic concurrency control mechanism to eliminate locking delays.
SQL
Server 2014 enables memory optimization of selected tables and stored
procedures.
In-Memory
OLTP has the following characteristics:
- Only hash and range indexes are available;
- No buffer pool;
- Stream-based storage for durability;
- Full ACID support;
- Core engine uses lock-free algorithms;
- No lock manager, latches or spinlocks;
- Use of T-SQL compiled to machine code, via C code generator (C compiler integrated in SQL Server 2014 engine);
- Invoking procedure is just a DLL entry-point;
- Aggressive optimization at compile-time.
SQL Server
Data Tools for Business Intelligence
The
new SSDT-BI supports SSAS and SSRS for SQL Server 2014 and earlier, but SSIS
projects are limited to SQL Server 2014.
SQL Server
AlwaysOn Improvements
Microsoft
has enhanced AlwaysOn integration. In Sql Server 2014, the maximum number of
secondary replicas is expanding from four to eight.
Even
when the primary replica is unavailable, it provide readable secondary replicas
for read workloads.
SQL
Server 2014 also provides the new Add Azure Replica Wizard. With helps of this,
we can create asynchronous secondary replicas in Windows Azure.
SQL Server
Transactions with Delayed Durability
SQL Server
Query Optimizer
SQL
Server 2014 substantially improved the component of the engine that creates and
optimizes query plans.
SQL Server
Table and Index Operations
Sql
Server 2014 provided very powerful feature to enhance performance. By using
this feature we can rebuild single partitions or we can switch partition and
index rebuild operations can be performed while the table is online.
To
manage lock priority of online operations for tables and indexes has been added
that allowing us to use WAIT_AT_LOW_PRIORITY option.
By
use of this option, you can define operation locks time maximum duration and
abort conditions.
Argument
|
Description
|
MAX_DURATION
|
Is
the wait time in minutes the online operation will wait at low priority
|
ABORT_AFTER_WAIT
|
These
are the actions to be taken by the online operation when it is blocked beyond
MAX_DURATION value.
NONE: Continue
waiting for the lock with normal priority.
SELF: Leaves
current operation without taking any action.
BLOCKERS: Kills transactions
that block the online operation.
|
Here is an example.
ALTER INDEX ALL ON yourTable REBUILD
WITH (ONLINE = ON (
WAIT_AT_LOW_PRIORITY (
MAX_DURATION = 4
MINUTES ABORT_AFTER_WAIT
= BLOCKERS
)
)
)
Incremental
Option for CREATE STATISTICS
This
feature permits statistics creation per partition by setting the INCREMENTAL
option to ON in the CREATE STATISTICS statement.
Here is an example.
CREATE STATISTICS yourStatistic
ON dbo.yourtable
(
tableField
)
WITH FULLSCAN, INCREMENTAL = ON;
GO
Buffer Pool
Extension
Buffer
Pool Extension feature is provided in sql server 2014 for performance
enhancements. This feature enables SQL Server to be configured to use a SSD
disk as an extension for the Database Buffer Pool in order to reduce response
time.
Resource
Governor
We
can set constraints on the physical I/O operations.
In
ALTER RESOURCE GOVERNOR statement, The MAX_OUTSTANDING_IO_PER_VOLUME
argument has been added to allow you to set the maximum outstanding I/O
operations per disk volume according to the disk IO characteristics.
The
two options MAX_IOPS_PER_VOLUME and MIN_IOPS_PER_VOLUME can be used to set
the maximum and minimum IO operations per second respectively.
Columnstore
Index Improvements
Updatable
Clustered Columnstore Indexes has been added in SQL Server 2014. To handle
Columnstore Indexes compression, two arguments have been added to the REBUILD option of the ALTER INDEX statement.
This
batch mode improves CPU utilization by:
- Minimizing instructions per row
- Taking advantage of cache structures
- Highly efficient algorithms
- A better parallelism.
Argument
|
Description
|
COLUMNSTORE_ARCHIVE
|
Compress
Columnstore indexes even more.
|
COLUMNSTORE
|
Default
Columnstore indexes Compression.
|
Security
Enhancements
1:- Backup
Data Encryption
Sql Server 2014 supports several new
encryption algorithms like AES 128, AES 192, AES 256 and 3DES that can be used
to perform encryption on backup data during the backup creation.
The supported encryptions are:
·
AES 128;
·
AES 192;
·
AES 256;
·
Triple DES.
The supported encryptors are:
·
A Certificate;
·
An Asymmetric Key.
2:- Permission
Changes in SQL Server 2014
Following permission has been added in SQL
Server 2014:
Permission
|
Description
|
CONNECT ANY DATABASE
|
is a server level permissions which allows
to connect all current databases and any new databases that might be created
in the future
|
IMPERSONATE ANY LOGIN
|
is a server level permissions which allows
a middle-tier process to impersonate the account of clients connecting to it,
as it connects to databases
|
SELECT ALL USER SECURABLES
|
is a server level permission which allows
to view data in all databases that the login has the permission to connect to
|
ALTER ANY DATABASE EVENT SESSION
|
is a database level permission which allows
a role to read all metadata
|
