What's New in SQL Server 2014

  • Artykuł

This topic summarizes detailed links to new features in SQL Server 2014 and summarizes services packs for SQL Server 2014

Try it out: Azure Virtual Machine small Have an Azure account? Go to https://ms.portal.azure.com/?flight=1#create/Microsoft.SQLServer2014sp1EnterpriseWindowsServer2012R2 to spin up a Virtual Machine with SQL Server 2014 Service Pack 1 (SP1) already installed.

Tip

Click here for the home documentation page of SQL Server 2014.

What's New articles

SQL Server 2014 has not introduced significant new features to the following features:

SQL Server 2014 Service Pack 1 (SP1)

SQL Server 2014 (SP1) did not introduce significant new features.

SQL Server 2014 Service Pack 2 (SP2)

SQL Server 2014 (SP2) Includes the following improvements:

Performance and Scalability Improvements

  • Automatic Soft NUMA partitioning: With SQL Server 2014 SP2, Automatic Soft NUMA is enabled when Trace Flag 8079 is turned on during instance startup. When Trace Flag 8079 is enabled during startup, SQL Server 2014 SP2 will interrogate the hardware layout and automatically configure Soft NUMA on systems reporting 8 or more CPUs per NUMA node. The automatic, soft NUMA behavior is Hyperthread (HT/logical processor) aware. The partitioning and creation of additional nodes scales background processing by increasing the number of listeners, scaling, and network and encryption capabilities. We recommend that you first test the performance workload with Auto-Soft NUMA, before tuning it in production. For more information, see the blog.
  • Dynamic Memory Object Scaling: SQL Server 2014 SP2 dynamically partitions memory objects based on number of nodes and cores to scale on modern hardware. The goal of dynamic promotion is to automatically partition a thread safe memory object (CMEMTHREAD) if it becomes a bottleneck. Non-partitioned memory objects can be dynamically partitioned by node (number of partitions equals number of NUMA nodes). Memory objects partitioned by node can by further partitioned by CPU (number of partitions equals number of CPUs). For more information, see the blog.
  • MAXDOP hint for DBCC CHECK* commands: This improvement addresses connect feedback (468694). You can now run DBCC CHECKDB with a MAXDOP setting other than the sp_configure value. If MAXDOP exceeds the value configured with Resource Governor, the Database Engine uses the Resource Governor MAXDOP value, described in ALTER WORKLOAD GROUP (Transact-SQL). All semantic rules used with the max degree of parallelism configuration option are applicable when you use the MAXDOP query hint. For more information, see DBCC CHECKDB (Transact-SQL).
  • Enable >8 TB for Buffer Pool: SQL Server 2014 SP2 enables 128 TB of virtual address space for buffer pool usage. This improvement enables SQL Server Buffer Pool to scale beyond 8 TB on modern hardware.
  • SOS_RWLock spinlock Improvement: The SOS_RWLock is a synchronization primitive used in various places throughout the SQL Server code base. As the name implies, the code can have multiple shared (readers) or single (writer) ownership. This improvement removes the need for spinlock for SOS_RWLock and instead uses lock-free techniques similar to in-memory OLTP. With this change, many threads can read a data structure protected by SOS_RWLock in parallel, without blocking each other. This parallelization provides increased scalability. Prior to this change, the spinlock implementation allowed only one thread to acquire the SOS_RWLock at a time, even to read a data structure. For more information, see the blog.
  • Spatial Native Implementation: Significant improvement in spatial query performance is introduced in SQL Server 2014 SP2 through native implementation. For more information, see the knowledge base article KB3107399.

Supportability and Diagnostics Improvements

  • Database Cloning: Clone database is a new DBCC command that enhances troubleshooting existing production databases by cloning the schema and metadata without the data. The clone is created with the command DBCC clonedatabase('source_database_name', 'clone_database_name'). Note: Cloned databases should not be used in production environments. Use the following command determine if a database has been generated from a cloned database: select DATABASEPROPERTYEX('clonedb', 'isClone'). The return value of 1 indicates the database is created from clonedatabase while 0 indicates it is not a clone.
  • Tempdb supportability: A new error log message that indicates at start-up both the number of tempdb files, and the size and autogrowth of tempdb data files.
  • Database Instant File Initialization Logging: A new error log message that indicates on server startup, the status of Database Instant File Initialization (enabled/disabled).
  • Module names in callstack: The extended event (XEvent) callstack now includes modules names plus offset, instead of absolute addresses.
  • New DMF for incremental statistics: This improvement addresses connect feedback (797156) to enable tracking the incremental statistics at the partition level. A new DMF sys.dm_db_incremental_stats_properties is introduced to expose information per-partition for incremental stats.
  • Index Usage DMV behavior updated: This improvement addresses connect feedback (739566) from customers where rebuilding an index will not clear any existing row entry from sys.dm_db_index_usage_stats for that index. The behavior will now be the same as in SQL 2008 and SQL Server 2016. For more information, see the blog.
  • Improved correlation between diagnostics XE and DMVs: This improvement addresses connect feedback (1934583). Query_hash and query_plan_hash are used for identifying a query uniquely. DMV defines them as varbinary(8), while XEvent defines them as UINT64. Because SQL server does not have "unsigned bigint", casting does not always work. This improvement introduces new XEvent action and filter columns. The columns are equivalent to query_hash and query_plan_hash, except they are defined as INT64. The INT64 definition helps to correlate queries between XE and DMVs.
  • Support for UTF-8 in BULK INSERT and BCP: This improvement addresses connect feedback (370419). BULK INSERT and BCP can now export or import data that is encoded in the UTF-8 character set.
  • Lightweight profiling of query execution per-operator: Showplan provides information on the cost of each operator in the plan. But actual run-time statistics are limited for things such as CPU, I/O Reads, and elapsed time per-thread. SQL Server 2014 SP2 introduces these additional runtime statistics per operator in the Showplan. R2 also introduces an XEvent named query_thread_profile to assist the troubleshooting of query performance. For more information, see the blog.
  • Change Tracking Cleanup: A new stored procedure sp_flush_CT_internal_table_on_demand is introduced to clean the change tracking internal tables on demand.
  • AlwaysON Lease Timeout Logging Added new logging capability for Lease Timeout messages so that the current time and the expected renewal times are logged. Also a new message was introduced in the SQL Error log regarding the timeouts. For more information, see the blog.
  • New DMF for retrieving input buffer in SQL Server: A new DMF for retrieving the input buffer for a session/request (sys.dm_exec_input_buffer) is now available. This DMF is functionally equivalent to DBCC INPUTBUFFER. For more information, see the blog.
  • Mitigation for underestimated and overestimated memory grant: Added a new query hints for Resource Governor through MIN_GRANT_PERCENT and MAX_GRANT_PERCENT. This new query allows you to leverage these hints while running queries, by capping their memory grants to prevent memory contention. For more information, see knowledge base article KB310740.
  • Better memory grant and usage diagnostics: A new extended event named query_memory_grant_usage was added to the list of tracing capabilities in SQL Server. This event tracks memory grants requested and granted. This event provides better tracing and analysis capabilities for troubleshooting any query execution issues related to memory grants. For more information, see knowledge base article KB3107173.
  • Query execution diagnostics for tempdb spill:- Hash Warning and Sort Warnings now have additional columns to track physical I/O statistics, memory used, and rows affected. We also introduced a new hash_spill_details extended event. Now you can track more granular information for your hash and sort warnings (KB3107172). This improvement is also now exposed through the XML Query Plans in the form of a new attribute to the SpillToTempDbType complex type (KB3107400). Set statistics ON now shows sort worktable statistics.
  • Improved diagnostics for query execution plans that involve residual predicate pushdown: The actual rows read are now reported in the query execution plans, to help improve query performance troubleshooting. These rows negate the need to capture SET STATISTICS IO separately. These rows also allow you to see information related to a residual predicate push-down in a query plan. For more information, see knowledge base article KB3107397.

Additional Information

SQL Server 2014 Resources

SQL Server 2014 Release Notes

SQL Server 2014 Resource Center

SQLCat Web Site