KB2958429 - Bugs that are fixed in SQL Server 2012 Service Pack 2

Supportability:

• Message 35285 is displayed as an informational message as expected and not as an error.

Functionality:

• SQL Writer support COPY_ONLY backup: Data Protection Manager (DPM) full backup on the server with AlwaysOn secondary will be converted to COPY_ONLY backup.

Add support to local cube creation scenario:

• When you use SQL Server 2012 Analysis Services (SSAS 2012) SP2, you can create a local cube that connects to SSAS that is running on a static non-default port that has SQL browser turned off. This new feature is controlled by a new configuration setting in msmdsrv.ini: "ConfigurationSettings\LocalCubeServerPortInConnectionString." To enable this feature, set the value to 1. This setting will have no effect if SSAS 2012 is configured as a failover cluster or is running on the default port.

Add more logging:

• This service pack adds more information to SSAS log file to help improve troubleshooting. This includes SSAS version, computer name, netbios name, number of CPUs, RAM size, IP, port, protocol type, deadlock, lock timeout, and some other information.

SSISDB deadlock and cleanup performance improvement

• Introduction

  • Deadlock in SSISDB database on concurrent calls to SSISDB.catalog.create_execution stored procedure

    • A fix has been done for SSISDB concurrences and deadlock issue. However, this does not resolve all problems. Current workaround is staggering the jobs by a few seconds. But you may not use this workaround.

    • Poor performance on SSISDB maintenance job

  • A hotfix is available for SSISDB maintenance job.

• Architecture

  • Previous Model:

    • Each transaction creates a certificate and symmetric key, which must also be stored and processed in maintenance or cleanup tasks.

  • New Model:

    • Introduce new row in catalog property table SERVER_OPERATION_ENCRYPTION_LEVEL and default to PER_EXECUTION to keep backward compatibility, value can be changed to PER_PROJECT creating one key or certificate pair for each project. A full cleanup is required before changing from PER_EXECUTION to PER_PROJECT. Two new store procedures are introduced for a full cleanup.

  • Change summary:

    • Introduce a new row in SSISDB property table SERVER_OPERATION_ENCRYPTION_LEVEL:

      • Only two values are valid (1 and 2).

      • Value "1": Default value. Encrypted with symmetric keys and certificates according to execution level. High security, but it may has performance or deadlock issue.

      • Value "2": Encrypted with symmetric keys and certificates according to project level. Less security, resolve performance or deadlock issue.

    • Introduce two new stored procedures (SPs) Cleanup_server_logs and cleanup_server_execution_keys

      • SPs are designed to be used when you change encryption level from "1" to "2." You can run Cleanup_server_logs first that can be finished very quickly to bring SSISDB into a good state. The SSISDB database is ready to be changed to encryption level.

      • Cleanup_server_keys takes longer time than Cleanup_server_logs. However, it is run after encryption level is changed to "2" and run periodically at off peak time.

      • Cleanup_server_logs: Only clean up all operation logs.

      • Cleanup_server_execution_keys: clean up symmetric keys and certificates that are used to protect sensitive execution parameters.

      • @cleanup_flag int (in (1,2))

        • cleanup_flag = 1: drop execution level symmetric keys and certificates

        • cleanup_flag = 2: drop project level symmetric keys and certificates (will only drop them if this perticular project has been deleted and all operations logs related to this project has been cleaned).

      • @delete_batch_size int = 1000

        • number of keys or certificates will be dropped at one call of this store procedure.

    • In internal.cleanup_server_retention_window, change delete_batch_size from 10 to 1000.

Optional support for table variable row counts to improve query plans:

• If a table variable is joined with other tables in SQL Server, it may result in slow performance due to inefficient query plan selection because SQL Server does not support statistics or track number of rows in a table variable while compiling a query plan.

• In SQL Server 2012 SP2, a new trace flag is introduced that allows the query optimizer to use information about the number of rows inserted into a table variable in order to select more efficient query plan. Enable trace flag 2453 to activate this behavior.
  
  Notes:

  • In some scenarios, enabling trace flag 2453 may result in some degradation of performance, due to additional compilation required to account for actual number of rows inserted into a table variable during execution time. Typically, you would benefit from this trace flag if a table variable has significant amount of rows joined with other tables, or has more than one row and used at the outer side of a nested loop join operator with a plan on the inner side that processes large amount of rows.

  • Similar behavior may be achieved on other versions of SQL Server through using OPTION (RECOMPILE) query hint. However, the query hint requires detecting and modifying all queries suffering from poor plan choice due to large amount of work driven by table variables, while enabling the trace flag 2453 can impact existing workloads.

Improved performance troubleshooting for parallel query execution plans

• DMV sys.dm_exec_requests now returns accurate value for cpu_time for parallel query execution plans. STATISTICS IO diagnostics now reports accurate logical reads for parallel query execution plans.

Improved hash spill diagnostics

• If a hash join or hash aggregate operator spills data to tempdb database during query execution, corresponding I/O is now reported for STATISTICS IO diagnostics.

Improved full text index diagnostics

• In some cases it may be difficult to understand how results of certain full text queries are returned. The full text search subsystem interacts with external word-breakers and stop-lists, and as a result of this interaction the positional information for the keywords in a document may be different from what is observed in the actual text. This is highly relevant when doing 'phrase' or 'NEAR' searches since the delta between the words is used in internal calculations to find matching documents.

• In SQL Server 2012 SP2 a new Dynamic Management Function (DMF) was added to provide access to positioning information for keywords indexed in a document. The new DMF is similar to existing DMF sys.dm_fts_index_keywords_by_document, and has the following syntax:sys. dm_fts_index_keywords_position_by_document
  (
  DB_ID('database_name'), OBJECT_ID('table_name')
  )

• Instead of occurrence count, it returns information about each occurrence and its position in the indexed document.

• This information can be used to look at the mapping between the text and the internal index which will show the difference in delta, if any, for the search phrase. This can help understanding results from phrase search.

Supportability

• Transactional Replication:

  • Verbose message added to the stats in history tables and more detailed messages in the agent logs:

    • Added text message to <stats> xml output to define different states in both logreader and distributor agents.

    • Added article level stats for distributor and logreader parameterized commands.

    • Added number of skipped commands to output file in distributor agent.

    • Added time spent doing schema changes in distributor agent.

  • LogReader: log records are added in .TXT file when a user dump is captured.

  • Peer To Peer: Insert, update and delete procedures are updated to provide below details to the conflict message:

    • Table Name

    • Primary Key column name and values

    • Current version

    • Pre-version

    • Post-version

• Merge Replication:

  • The output of trace flag 101 that is added in log file when the output verbose level for the merge agent is set to 4.

Functionnality

• Transactional Replication:

  • Peer to Peer Replication now supports partial updates made to text column using Update or .Write semantic.

• Merge Replication:

  • Cleanup stored procedure has been redesigned to avoid redundant upload of data.

Logging Message with Processing Errors and Warnings to Prevent common calls to CSS, Large HTML Reports.

• Overview:

  • Detection of the report design to determine if the report design will net a large HTML report and potentially cause performance issues. This should be easily detected by examination of the Interactive Page Height and Width settings.

• User scenario:

  • You create a report in the Report Builder. You enter value '0' in the InteractiveSize property. You receive a pop-up window that contains the following message warning about potential performance impact:

    Setting interactive heigth to 0 mat have a performance impact.

Performance enhancements

• Several performance enhancements in Service Pack 2 as described in the following articles:

  • Performance problems occur when database lock activity increases in SQL Server 2012

  • Performance problems occur in NUMA environments during foreign page processing in SQL Server 2012

  • Performance decreases after an ALTER INDEX…ONLINE operation is aborted in SQL Server 2012

• DMV enhancements

  • sys.dm_db_session_space_usage reflects the amount of space taken in tempdb database by user object per session. When the number of rows deleted is beyond 1024 pages, the de-allocation of these pages are deferred. In SQL Server 2012 Service Pack 2, the DMV will also include space usage for allocation units that are deferred in a new column called user_objects_deferred_dealloc_page_count.

  • sys.dm_server_services will now correctly specify the node name in cluster_nodename column if SQL Server is installed on a failover cluster.

• Ring buffer enhancements

  • The ring buffer for the app domains RING_BUFFER_CLRAPPDOMAIN includes the reason why the app domain was unloaded in the <DoomReason> field:

    • 1 = unloaded by DDL operations such as drop assembly.

    • 2 = unloaded by security related operation such as changing permission.

    • 3 = unloaded because of memory pressure handling.

    • 4 = unloaded because of failed initialization.

  • The corresponding Extended Event app_domain_ring_buffer_recorded includes the reason for unload in doom_reason event data field.

  • RING_BUFFER_RESOURCE_MONITOR has entries when a change is detected in the state monitored by Resource Monitor. Additionally, for system and process resource notifications, pool notifications are also available in the <IndicatorsPool> </IndicatorsPool> node.

  • Resource_monitor_ring_buffer_recorded extended event includes pool wide notifications in the pool_indicators data field.

  • RING_BUFFER_NONYIELD_PROCESSTABLE includes process name in addition to other information up to 8192 threads that are running in the system at the time of recording the ring buffer entry.

• Backup and Restore Enhancements

  • Backup of tail log of model with norecovery option is no longer allowed starting with Service Pack 2.

  • Backup and Restore completion messages in error log includes duration and throughput information:

    • BACKUP DATABASE successfully processed 298 pages in 0.026 seconds (89.261 MB/second).

    • RESTORE DATABASE successfully processed 298 pages in 0.016 seconds (145.050 MB/second).

• Extended Events

  • Following extended events have been added or modified in SQL Server 2012 Service Pack 2:

    • Memory Allocation:

      • Extended events page_allocated and page_freed can be used to see the top consumers of SQL Server memory. Starting with Service Pack 2, these extended events also report reserved memory that is allocated and freed during query execution.

    • Ghost Cleanup:

      • When a record is deleted in a table or index, such a delete operation never physically removes records from pages, it only marks them as having been deleted, or ghosted. This is a performance optimization that allows delete operations to complete more quickly. A background task called the ghost cleanup task then physically removes all the deleted records. Several extended events have been added in Service Pack 2 to provide insights into the various phases of this task:

        • ghost_cleanup_task_start

        • ghost_cleanup_task_suspend

        • ghost_cleanup_task_packet_enqueue

        • ghost_cleanup_task_process_packet

        • ghost_cleanup_task_process_pages_for_db_packet