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 = ' Exec sp_addpublication @publication = ' "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 |
No comments:
Post a Comment