SQL SERVER 2014 New Features

Recently this question has been asked to most of my friends. I have been thinking of writing about the new features from quite some time now. Finally yesterday I started. SO Here are the new features in SQL SERVER 2014.

Ref Links-

https://msdn.microsoft.com/en-us/library/bb510411.aspx

http://www.virtual-dba.com/platforms/ms-sql-server/sql-server-2014-features

This latest release of the SQL Server Database Engine introduces new features and enhancements that increase the power and productivity of architects, developers, and administrators who design, develop, and maintain data storage systems. These are the areas in which the Database Engine has been enhanced.

1. In-Memory OLTP (In-Memory Optimization)

https://msdn.microsoft.com/en-us/library/dn535766.aspx

In-Memory OLTP can significantly improve OLTP database application performance. In-Memory OLTP is a memory-optimized database engine integrated into the SQL Server engine, optimized for OLTP. In-Memory OLTP is a memory-optimized database engine integrated into the SQL Server engine. In-Memory OLTP is optimized for OLTP. Now there are two types of tables – Memory optimized table and Disk based tables.

--CREATE In MEMORY Table

CREATE TABLE TestMem
(
   [ID] [INT] NOT NULL PRIMARY KEY NONCLUSTERED HASH
   WITH ( BUCKET_COUNT = 10000 )
  ,[Name] [int] NOT NULL
)
WITH ( MEMORY_OPTIMIZED = ON , DURABILITY = SCHEMA_AND_DATA )

--

MEMORY_OPTIMIZED=ON indicates that the table type is memory-optimized

This feature is getting the most attention from SQL Server lovers, as it speeds up SQL Server response times for frequently-accessed data.

The new Memory-Optimized table feature of SQL Server 2014 allows us to create tables in memory for improved performance. A second copy of the table exists on disk and is updated shortly after transactions are updated. These tables are also versioned so that concurrent reads and writes are supported with reduced locking.

A non-durable, memory only table is also supported – but in case of a crash all data in these tables will be lost. This feature can improve performance substantially but there is a small window where a crash may cause data loss.

This feature is only available in Enterprise edition of SQL Server 2014 and require a 64 bit installation of SQL Server.

2. SQL Server Data Files in Windows Azure

This feature enables native support for SQL Server database files stored as Windows Azure Blobs. This feature allows you to create a database in SQL Server running in on-premises or in a virtual machine in Windows Azure with a dedicated storage location for your data in Windows Azure Blob Storage.

Here you can run our SQL instance locally, but place our data files into the Azure blobs. This can offer advantages for projects such as migrations, however there is a performance impact, every transaction will be pushed across the internet and this will impact your application.

This option can be used for archives that you have to keep because of some audit or government restriction, and you just want your data live and available for compliance.

This feature allows you to create a database in SQL Server running in on-premises or in a virtual machine in Windows Azure with a dedicated storage location for your data in Windows Azure Blob Storage.

3. Host a SQL Server Database in a Windows Azure Virtual Machine

We can now host a database from an instance of SQL Server in a Windows Azure Virtual Machine.

4. Backup and Restore Enhancements

• SQL Server Backup to URL
• SQL Server Managed Backup to Windows Azure
• Encryption for Backups

For details please visit – https://msdn.microsoft.com/en-us/library/bb510411.aspx#Backup

5. New Design for Cardinality Estimation

Cardinality estimates are a prediction of the number of rows in the query result. The query optimizer uses these estimates to choose a plan for executing the query. The quality of the query plan has a direct impact on improving query performance.

The new cardinality estimator incorporates assumptions and algorithms that work well on modern OLTP and data warehousing workloads. It is based on in-depth cardinality estimation research on modern workloads

How to use new Cardinality Estimation

 
--
ALTER DATABASE JyotiBubla
SET COMPATIBILITY_LEVEL = 110
GO
EXEC [dbo].[GetEmpDetails]
GO
--

6. Delayed Durability

SQL Server 2014 introduces the ability to reduce latency by designating some or all transactions as delayed durable. A delayed durable transaction returns control to the client before the transaction log record is written to disk. Durability can be controlled at the database level, COMMIT level, or ATOMIC block level.

How to Enable Delayed Durability for the database

--
ALTER DATABASE [AdventureWorks2014] SET DELAYED_DURABILITY = ALLOWED
GO
--

7. Updateable Column Store Indexes

Columnstore indexes can be updated with SQL SERVER 2014. This means you no longer have to drop and re-create columnstore indexes every time you need to load your warehouse tables. Not only that, but updateability also means you may be able to look at columnstore indexes for certain OLTP applications.

Note – The catch is that you must have a clustered columnstore index on the table. Non-clustered columnstores aren’t supported.

Additional compression of Columnstore indexes is now available by using Alter Index Rebuild with the COLUMNSTORE_ARCHIVE option. The use of Columnstore indexes requires Enterprise Edition of SQL Server 2014.

--

CREATE CLUSTERED COLUMNSTORE INDEX Ix_CstoreEMP ON dbo.EMP

INSERT T1
SELECT 'Avika Khowal' , 'Fin' , 'Address - 1' , 122001

--

8. Incremental Statistics & Partition Enhancement

Now we can create unique statistics for each partition by using the INCREMENTAL option. This allows for improved performance when distribution of data varies between partitions. Partitions are only supported on Enterprise Edition of SQL Server 2014.

Whenever statistics need to be rebuilt, you have to update all the rows. This means if a table has 200 million rows and only 40 million changes need to update, all 200 million rows will be updated in order to pick up those changes.

INCREMENTAL = { ON | OFF }

When ON, the statistics created are per partition statistics. When OFF, stats are combined for all partitions. The default is OFF.

--

CREATE INDEX Ix_ID ON TestMem(ID)
WITH ( ONLINE = ON , Statistics_Incremental = ON ) 

--

CREATE STATISTICS  statsMem ON TestMem(ID)
WITH FULLSCAN, INCREMENTAL = ON

--

9. Buffer Pool Extension to Solid State Drives (SSDs). 

The Buffer Pool Extension provides the seamless integration of solid-state drives (SSD) as a nonvolatile random access memory (NvRAM) extension to the Database Engine buffer pool to significantly improve I/O throughput.

Creating a buffer pool extension for SQL Server 2014 is like being able to define a different page file in Windows. As data pages move into memory, they begin to fill up the buffer pool. If the buffer pool fills up, the less frequently used pages will be paged to disk. Then when they’re needed again, they’ll be swapped with something else in the buffer pool and moved back into memory. The buffer pool extension option allows you to define an SSD as a buffer file location. Because SSD is so much faster than spinning disk, the paging is considerably quicker, which increases performance dramatically in some cases. You can define a buffer pool extension file up to 32 times the size of your memory.

http://www.infoworld.com/article/2606990/database/147960-10-best-new-features-in-SQL-Server-2014.html#slide10

9. Managing Locks in Online Index

In SQL Server 2014, you can specify how your re-index operation will handle being blocked. You specify how long it will wait and what to do when the wait is over. Will you have it follow traditional behavior and wait indefinitely? Will you have it terminate and move to the next table? Or will you kill the blocking query, so your re-indexing can complete? It’s your choice.

http://www.infoworld.com/article/2606990/database/147960-10-best-new-features-in-SQL-Server-2014.html#slide10

10. Always On Improvements

SQL Server 2014 contains the following enhancements for AlwaysOn Failover Cluster Instances and AlwaysOn Availability Groups:

•An Add Azure Replica Wizard simplifies creating hybrid solutions for AlwaysOn availability groups. For more information, see Use the Add Azure Replica Wizard (SQL Server).
•The maximum number of secondary replicas is increased from 4 to 8.
•When disconnected from the primary replica or during cluster quorum loss, readable secondary replicas now remain available for read workloads.
•Failover cluster instances (FCIs) can now use Cluster Shared Volumes (CSVs) as cluster shared disks. For more information, see AlwaysOn Failover Cluster Instances (SQL Server).
•A new system function, sys.fn_hadr_is_primary_replica, and a new DMV, sys.dm_io_cluster_valid_path_names, is available.
•The following DMVs were enhanced and now return FCI information: sys.dm_hadr_cluster, sys.dm_hadr_cluster_members, and sys.dm_hadr_cluster_networks.

11. Resource Governor Enhancements

The Resource Governor enables you to specify limits on the amount of CPU, physical IO, and memory that incoming application requests can use within a resource pool. In SQL Server 2014, you can use the new MIN_IOPS_PER_VOLUME and MAX_IOPS_PER_VOLUME settings to control the physical IOs issued for user threads for a given resource pool.

I will explain all the above new features in detail in coming weeks.

Keep Learning

MSBISkills.com