7/12/2016

SQL Server 2014 Service Pack 2

TSQL2SDAY-150x150
This months T-SQL Tuesday topic is: "treat yourself to a birthday gift".

As it so happens Microsoft provided us an excellent gift in the form of SQL Server 2014 Service Pack 2. Included are many fixes and improvements. I saw the individual KB RSS feed posts coming through just prior to the SP2 release. It looked like there were some good improvements in there, so I was particularly interested in looking through those.

Before I get into the distilled list of improvements I do want to point out this fix because it looks like a pretty big deal.  It's also listed last in the release notes, so there's a good chance some people may overlook it.
TYPE: Restore
VSTS bug number 6589587
KB article number 3065060
Description FIX: "Unable to create restore plan due to break in the LSN chain" error when you restore differential backup in SSMS
Notes "When you restore the full backup file by using the NORECOVERY option, and then you restore the differential backup file by using the RECOVERY option in SSMS, the operation fails"

On to the Improvements... There's some good stuff in here UTF-8 support, CLONEDATABASE, additional tempdb logging, additional events for availability groups, and change tracking manual cleanup for example.
TYPE: BULK / bcp
VSTS bug number 6715815
KB article number 3136780
Description UTF-8 encoding support for the BCP utility and BULK INSERT Transact-SQL command in SQL Server 2014 SP2
Notes "Import support is added to the BCP utility and to the BULK INSERT Export support is added to the BCP utility BULK INSERT WITH (CODEPAGE = '65001', DATAFILETYPE = 'Char') bcp… -C 65001"

TYPE: DBCC
VSTS bug number 6935655
KB article number 3177838
Description DBCC CLONEDATABASE (Transact-SQL) is introduced in SQL Server 2014 Service Pack 2
Notes "DBCC CLONEDATABASE (source_database_name, target_database_name) Generate a schema and statistics only copy. "DBCC CLONEDATABASE isn't supported to be used as a production database and is primarily intended for troubleshooting and diagnostic purposes"

TYPE: Dynamic Management Function
VSTS bug number 5990425
KB article number 3170114
Description Update to add DMF sys.dm_db_incremental_stats_properties in SQL Server 2014
Notes Sys.dm_db_incremental_stats_properties

TYPE: Dynamic Management View
VSTS bug number 6588995
KB article number 3107398
Description Improved memory grant diagnostics when you use DMV in SQL Server 2012 and 2014
Notes Adds new (grant/parallelism) colums to sys.dm_exec_query_stats

TYPE: Extended Events
VSTS bug number 6589007
KB article number 3107172
Description Improve tempdb spill diagnostics by using Extended Events in SQL Server 2012 and 2014
Notes New extended event: hash_spill_details

VSTS bug number 6589008
KB article number 3107173
Description Improved memory grant diagnostics using Extended Events in SQL Server 2012 and 2014
Notes New extended event: query_memory_grant_usage.
"You can define a memory limit as a filter for this new extended event so that the extended event fires only when the memory grant of a query exceeds the limit."

VSTS bug number 6685025, 3512277
KB article number 3170113
Description Update to expose per-operator query execution statistics in showplan XML and Extended Event in SQL Server 2014 SP2
Notes "New Extended Event: query_thread_profile Provides per-operator query execution statistics"

TYPE: Extended Event / Performance Monitor
VSTS bug number 6988068
KB article number 3173156
Description Update adds AlwaysOn extended events and performance counters in SQL Server 2014
Notes Adds and changes multiple Extended Events and performance counters for Availibility Groups.

TYPE: Extended Event / SQL Server Error Log
VSTS bug number 6589061, 6589062, 6589063, 6589064
KB article number 3112363
Description Improvements for SQL Server AlwaysOn Lease Timeout supportability in SQL Server 2012 and 2014
Notes The availability_group_lease_expired and hadr_ag_lease_renewal XEvents have been improved (Lease stages detail)
New SQL Server Error Messages: 19419-19424

TYPE: LOG: Cluster log
VSTS bug number 6651348
KB article number 3156304
Description Improvement for SQL Server AlwaysOn Lease Timeout supportability in SQL Server 2012 and 2014
Notes "System performance objects are reported in the Cluster log following a lease timeout: Date/Time, Processor time(%), Available memory(bytes), Avg disk read(secs), Avg disk write(secs)"

TYPE: LOG: SQL Server error logs
VSTS bug number 6944985
KB article number 3170019
Description Update to add info about SQL Server startup account to security policy in SQL Server 2014 error log
Notes Information about whether Database Instant File Initalization is enabled is now in the error log

VSTS bug number 6945027
KB article number 3170020
Description Informational messages added for tempdb configuration in the SQL Server error log in SQL Server 2014 SP2
Notes "New informational startup messages:
The tempdb database has %ld data file(s).
The tempdb database data files are not configured to use the same initial size and autogrowth settings…"

TYPE: Memory
VSTS bug number 6936784
KB article number 3170022
Description Update lets SQL Server 2014 use all the user-mode virtual address space for a process
Notes "Starting in Windows 8.1 and Windows Server 2012 R2, the user-mode virtual address space for each 64-bit process in 64-bit Windows is 128 TB"

TYPE: Query Hint
VSTS bug number 6588981
KB article number 3107401
Description New query memory grant options are available (min_grant_percent and max_grant_percent) in SQL Server 2012
Notes OPTION (min_grant_percent = x, max_grant_percent = y)

TYPE: Showplan XML
VSTS bug number 6589011
KB article number 3107397
Description Improved diagnostics for query execution plans that involve residual predicate pushdown in SQL Server 2012 and 2014
Notes New showplan XML attribute: Actual Rows Read

VSTS bug number 6589060
KB article number 3107400
Description Improved tempdb spill diagnostics in Showplan XML schema in SQL Server 2012 and 2014
Notes "SortSpillDetails and HashSpillDetails Warning section of the tooltip for the Sort operation or the Hash operation in the graphical execution plan output"

TYPE: Dynamic Management Function
VSTS bug number 6170317
KB article number 3170112
Description Update to expose maximum memory enabled for a single query in Showplan XML in SQL Server 2014
Notes New Attributes: OptimizerHardwareDependentProperties\MaxCompileMemory and MemoryGrantInfo\MaxQueryMemory

VSTS bug number 6170324
KB article number 3170115
Description Information about enabled trace flags is added to the showplan XML in SQL Server 2014 SP2
Notes Includes the scope details of the enabled trace flags

VSTS bug number 2197153
KB article number 3172997
Description Update to add memory grant warning to the Showplan XML in SQL Server 2014 SP2
Notes New warning: "MemoryGrantWarning"

TYPE: Spatial Data
VSTS bug number 6588999
KB article number 3107399
Description Spatial performance improvements in SQL Server 2012 and 2014
Notes Trace flag 6533
Spatial data type performance improvement (no details).
Don't use this if you use the STRelate and/or STAsBinary functions."

TYPE: Stored Procedure
VSTS bug number 4297600
KB article number 3170123
Description Supports DROP TABLE DDL for articles that are included in transactional replication in SQL Server 2014
Notes Exec sp_changepublication @publication = '', @property = 'allow_drop', @value = 'true'
Exec sp_addpublication @publication = '', ..., @allow_drop = N'true'
"A table can be dropped only if the allow_drop property is set to TRUE on all the publications"
Will this drop the table on the subscriber? (The article doesn't go into those details.)

VSTS bug number 7579503
KB article number 3173157
Description Adds a stored procedure for the manual cleanup of the change tracking side table in SQL Server 2014 SP2
Notes Sp_flush_CT_internal_table_on_demand [ @TableToClean= ] 'TableName'
Addresses this: Change Tracking Cleanup Limitation