SQL Server 2014 new features: Other

AlwaysOn Enhanced Features

SQL Server 2014 contains the following enhancements for AlwaysOn failover cluster instances and AlwaysOn availability groups:

• The Add Azure Replica Wizard simplifies the creation of hybrid solutions for AlwaysOn availability groups. For more information, see Using the Add Azure Replica Wizard (SQL Server).

• The maximum number of secondary replicas increased from 4 to 8.

• A readable secondary replica now remains available for reading workloads when disconnecting from the primary replica or during a lack of cluster quorum.

• A failover cluster instance (FCI) can now use cluster Shared volumes (CSV) as a cluster shared disk. For more information, see AlwaysOn Failover Cluster instances (SQL Server).

• Provides a new system function Sys.fn_hadr_is_primary_replica and a new DMV sys.dm_io_cluster_valid_path_names.

• The following DMV has been enhanced and now returns FCI information: Sys.dm_hadr_cluster, Sys.dm_hadr_cluster_members, and Sys.dm_hadr_cluster_networks.

Incremental Statistics

The Create STATISTICS and related statistics statements now allow statistics by partition to be created by using the INCREMENTAL option. Related statements allow or report incremental statistics. The affected syntax includes UPDATE STATISTICS, sp_createstats, CREATE INDEX, ALTER INDEX, ALTER DATABASE SET option, DATABASEPROPERTYEX, Sys.databases and Sys.stats. For more information, see Create STATISTICS (Transact-SQL).

Resource Governor enhancements for physical IO control

Resource Governor allows you to specify limits on the amount of CPU, physical IO, and memory used by incoming application requests that can be used 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 number of physical IO that a given resource pool emits to a user thread. For more information, see Resource Governor resource pool and create RESOURCE pool (Transact-SQL).

ALTER RESOURCE Govenor's max_outstanding_io_per_volume setting sets the maximum number of pending I/O operations (IOPS) per disk volume. You can use this setting to adjust IO resource control based on the IO characteristics of a disk volume, and can be used to limit the number of IO emitted at the SQL Server instance boundary. For more information, see ALTER RESOURCE GOVERNOR (Transact-SQL).

Online Index Operation event class

Progress reports for the online index operations event class now have two new data columns: PartitionID and PartitionNumber. For more information, see Progress Report:online Index Operation event class.

Transact-SQL enhanced featuresInline specification for CLUSTERED and nonclustered

For disk-based tables, the inline specification for CLUSTERED and nonclustered indexes is now allowed. Creating a table with an inline index is equivalent to publishing a CREATE TABLE command, followed by the CREATE INDEX statement. Inline indexes do not support include columns and filter criteria.

SELECT ... Into

SELECT ... The INTO statement has been improved and can now be manipulated in parallel. The database must have a minimum compatibility level of 110.

Transact-SQL enhancements for in-memory OLTP

For Transact-SQL changes that support in-memory OLTP, see Transact-SQL support for in-memory OLTP.

System View EnhancementsSys.xml_indexes

SYS.XML_INDEXES (Transact-SQL) has 3 new columns: Xml_index_type, Xml_index_type_description, and path_id.

Sys.dm_exec_query_profiles

SYS.DM_EXEC_QUERY_PROFILES (Transact-SQL) monitors the progress of real-time queries while the query is being executed.

Sys.column_store_row_groups

SYS.COLUMN_STORE_ROW_GROUPS (Transact-SQL) provides clustered Columnstore index information on a per-segment basis to help administrators make system administration decisions.

sys.databases

sys.databases (Transact-SQL) has 3 new columns: is_auto_create_stats_incremental_on, is_query_store_on, and resource_pool_id.

System view enhancements for in-memory OLTP

For information about System view enhancements that support in-memory OLTP, see new and updated properties for in-memory OLTP, system views, stored procedures, wait types, and DMV.

SQL Server 2014 new features: Other