SQL Server 2014 SP2 Now Available

Database Cloning - Clone database is a new DBCC command added that allows Microsoft CSS to troubleshoot 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’). Cloned databases should not be used in production environments. To see if a database has been generated from a clonedatabase you can use the following command, 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 indicating the number of tempdb files and notifying different size/autogrowth of tempdb data files at server startup.

Database Instant File Initialization Logging - A new error log message indicating that Database Instant File Initialization is enabled/disabled at server startup.

Module names in callstack - The Xevent callstack now includes modules names + offset instead of absolute addresses.

New DMF for incremental statistics – This improvement address 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 starting SQL 2014 SP2, rebuilding an index will *not* clear any existing row entry from sys.dm_db_index_usage_stats for that index. The behavior now is to what it was in SQL 2008.

Better 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. Since SQL server does not have "unsigned bigint", casting does not always work. This improvement introduces new XEvent action/filter columns equivalent to query_hash and query_plan_hash except they are defined as INT64 which can help correlating queries between XE and DMVs.

Support for UTF-8 in BULK INSERT and BCP – This improvement addresses connect feedback (370419) where support for export and import of data encoded in UTF-8 character set is now enabled in BULK INSERT and BCP.

Lightweight per-operator query execution profiling – While troubleshooting query performance, although showplan provides lot of information on the query execution plan and cost of operator in the plan but it has limited information on actual runtime statistics like (CPU, I/O Reads, elapsed time per-thread). SQL 2014 SP2 introduces these additional runtime statistics per operator in the Showplan as well as an XEvent (query_thread_profile) to assist troubleshooting query performance.

Change Tracking Cleanup – A new stored procedure sp_flush_CT_internal_table_on_demand is introduced to cleanup 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 Errorlog regarding the timeouts.

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 is functionally equivalent to DBCC INPUTBUFFER.

Mitigation for underestimated and overestimated memory grant - Added new query hints for Resource Governor through MIN_GRANT_PERCENT and MAX_GRANT_PERCENT (KB3107401). This allows you to leverage these hints while running queries by capping their memory grants to prevent memory contention.

Better memory grant/usage diagnostics - A new extended event was added to the list of tracing capabilities in SQL Server (query_memory_grant_usage) to track memory grants requested and granted. This provides better tracing and analysis capabilities for troubleshooting query execution issues related to memory grants (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 will now be reported in the query execution plans (KB3107397) to help improve query performance troubleshooting. This should negate the need to capture SET STATISTICS IO separately. This now allows you to see information related to a residual predicate pushdown in a query plan.

The Service Pack is available for download at the Microsoft Download Center, and will also be made available on Microsoft Update, MSDN, MAPS/MPN, MBS/Partner Source, and VLSC by the end of this week.