New Extended Events for Tracing in SQL Server 2014

Neue “Erweiterte Ereignisse” (XEvents) für Tracing in SQL Server 2014

Dieser Artikel enthält eine Liste der neuen Erweiterte Ereignisse (“Extended Events”), dem Tracing Framework in SQL Server in dem neuen 2014 Release.

Zunächst gibt es 3 neue Pakete („Packages“), die alle zu der neuen Arbeitsspeicher(„In-Memory“) -OLTP Engine: XTP, aufgeteilt auf 3 DLLs:

This article contains a list of new Extended Events, the tracing framework in SQL Server in the new 2014 release.

To begin with there are 3 new packages, all belonging to the new In-Memory OLTP Engine: XTP, split up into 3 dlls:




Module name


Extended events for the XTP Runtime



Extended events for the XTP Engine



Extended events for the XTP Compile



Die Anzahl der Extended Events ist von 625 in SQL Server 2012 (SP1) auf 870 in SQL Server 2014 (RTM) angestiegen. Das sind 245 neue Events! – Reichlich Möglichkeiten, in Internas einzutauchen :-)
2 Events sind umbenannt worden:

recovery_incremental_checkpoint in


und hekaton_slow_param_passing in


Ja, richtig gelesen. Es gab bereits im SQL Server 2012 eine Anzahl an Events für die XTP Engine. Es kommt recht häufig vor, dass man Bits von zukünftigen Entwicklungen in der aktuellen Release findet.

Nebenbei: SQL Trace wurde nicht im geringsten angefasst. Die Anzahl von SQL Trace Events bleibt mit 180 seit SQL Server 2008 stabil. – Here ist ein Überblick von Extended Events gegen SQL Trace Events in den verschiedenen SQL Server Versionen: Extended Events vs SQL Trace im Vergleich – oder warum SQL Trace & Profiler einfach von gestern sind :-)

Weiter zu XEvents: es gibt keine neuen Prädikate, aber eine Anzahl neuer Maps, was natürlich nicht überrascht bei der Menge neuer Events.
Targets, Actions, Typen und Session Optionen  sind unverändert.

Und hier ist die Liste der neuen Extended Events:

The number of Extended Events increased from 625 in SQL Server 2012 (SP1) to 870 in SQL Server 2014 (RTM). That makes 245 new events! – Plenty of opportunities to look into internals :-)
2 events have been renamed:

recovery_incremental_checkpoint into


and hekaton_slow_param_passing into


Yes, you read correctly. There have already been a couple of events for the new XTP engine within SQL Server 2012. It’s quite common that you can find bits of future developments within the current release.

By the way: SQL Trace has not been touched by the slightest bit. The number of SQL Trace events stays at 180 since SQL Server 2008. – here you find an overview of the number of Extended Events vs SQL Trace events in the various SQL Server versions: Comparing Extended Events vs SQL Trace – or why SQL Trace & Profiler are just a thing of the past :-)

Continuing with XEvents: There are no new predicates, but a couple more maps, which is of course not surprisingly considering the amount of new events.
Targets, actions, types and session options are unchanged.

And here is the list of all new extended events:


Event name Description
qds query_store_async_shutdown_failed Fired when query store encounters an error during async query store shutdown
qds query_store_background_cleanup_task_failed Fired if the background task for cleanup of QDS stale data is not completed successfully
qds query_store_background_task_creation_failed Fired if the background task for QDS data persistence could not be created
qds query_store_background_task_initialization_failed Fired if the background task for QDS data persistence could not be initialized
qds query_store_background_task_persist_failed Fired if the background task for QDS data persistence is not completed successfully
qds query_store_begin_persist_runtime_stat Fired immediately before current runtime statistics for a query plan is persisted to the database.
qds query_store_bloom_filter_false_positive Fired if the Bloom filter for QDS statement texts gives a false positive result
qds query_store_check_consistency_init_failed Fired when check consistency task fails during initialization
qds query_store_database_initialization_failed Fired if initialization of the Query Store for database has failed. The Query Store will be disabled for this database
qds query_store_db_cleanup__finished Fired if cleanup of QDS stale data finished for particular database.
qds query_store_db_cleanup__started Fired if cleanup of QDS stale data started for particular database.
qds query_store_disk_size_check_failed Fired when a check against Query Store on-disk size limit fails
qds query_store_disk_size_info Fired when a check against QDS on-disk size is performed
qds query_store_execution_runtime_info Fired when runtime information is sent to the query store.
qds query_store_execution_runtime_info_discarded Fired when runtime information sent to the query store is discarded.
qds query_store_execution_runtime_info_evicted Fired when runtime information sent to the query store is evicted.
qds query_store_flush_failed Fired when query store failed to flush dirty data
qds query_store_loaded Fired when query store is loaded
qds query_store_notify_dirty_shutdown_on_partition_startup Fired when previous instance of query store for the partition is shutdown by force in order to allow creation of a new instance
qds query_store_notify_force_failure_failed Fired when query store failed to notify force failure
qds query_store_persist_task_init_failed Fired when persist task fails during initialization
qds query_store_plan_forcing_failed Occurs when forcing of plan from qds fail
qds query_store_plan_persistence_failure Fired if there's a failure to persist plan
qds query_store_plan_removal Fired when plan is removed
qds query_store_query_persistence_failure Fired if there's a failure to persist query
qds query_store_read_write_failed Fired if the read/write to Query Store internal tables failed
qds query_store_statement_not_found Fired in case when statement couldn't be found due to race condition or ambiguous user request.
qds query_store_unloaded Fired when query store is unloaded from memory
sqlos nonpreemptive_long_syncio record long sync io operation in nonpreemptive worker
sqlos stuck_dispatcher_callback_executed Stuck dispatcher callback executed
sqlos wait_completed Occurs when there is a wait completed on a SQLOS controlled resource.  Use this event to track wait completion.
sqlserver after_natively_compiled_proc_entry_removal_on_drop Fired after the procedure cache entry is flushed when dropping a natively compiled procedure.
sqlserver availability_replica_state Occurs when the Availability Replica is starting or shutting down.
sqlserver before_natively_compiled_proc_entry_removal_on_drop Fired before the procedure cache entry is flushed when dropping a natively compiled procedure.
sqlserver before_redo_lsn_update Occurs just prior to the update of the EOL LSN
sqlserver buffer_pool_eviction_thresholds_recalculated Lazywriter and/or worker clock has wrapped the BUF array and thresholds are re-calculated.
sqlserver buffer_pool_extension_pages_evicted Page is evicted from the buffer pool extension cache.
sqlserver buffer_pool_extension_pages_read Page is read from the buffer pool extension cache.
sqlserver buffer_pool_extension_pages_written Page or contiguous set of pages evicted into the buffer pool extension cache.
sqlserver check_phase_tracing Occurs when DBCC CHECK enters a new phase of the checking. Use this event to trace the phases of DBCC CHECK process.
sqlserver check_thread_message_statistics Occurs when a phase of DBCC CHECK is finished. Use this event to collect the number of messages a DBCC CHECK thread has sent or received.
sqlserver check_thread_page_io_statistics Occurs when a phase of DBCC CHECK is finished. Use this event to collect the number of logical, physical, and read-ahead IOs a DBCC CHECK thread has performed.
sqlserver check_thread_page_latch_statistics Occurs when a phase of DBCC CHECK is finished. Use This event to collect the number and time of page latch and IO latch waits.
sqlserver clustered_columnstore_index_rebuild Occurs when clustered index on the table was rebuilt. This event is raised three times for ALTER index rebuild operation on CCSI. The event is raised when the operation takes lock on index rebuild resource, when lock is taken on the table and when S lock on the table is upgraded to SCH_M lock to switch indexes in metadata.
sqlserver column_store_code_coverage Code coverage Xevent for columnstore code.
sqlserver column_store_index_build_low_memory Occurs when Storage Engine detects low memory condition and the rowgroup size is reduced.
sqlserver column_store_index_build_process_segment Occurs when a segment is processed
sqlserver column_store_index_build_throttle Shows the statistics of columnstore index build parallelism throttling
sqlserver columnstore_tuple_mover_begin_compress Occurs when column store tuple mover starts compressing a deltastore.
sqlserver columnstore_tuple_mover_end_compress Occurs when column store tuple mover is done compressing a deltastore.
sqlserver database_xml_deadlock_report Produces a deadlock report for a victim, with information scoped to the victim's database.
sqlserver db_lock_acquired_from_cache Occurs when a DB lock is acquired from the XactWorkspace DB lock cache.
sqlserver db_lock_released_from_cache Occurs when a DB lock is released from the XactWorkspace DB lock cache.
sqlserver ddl_with_wait_at_low_priority A DDL statement was executed using the WAIT_AT_LOW_PRIORITY options
sqlserver diag_quantum_end Occurs when the diag is notified of quantum end.
sqlserver dyn_throttle_checkpoint Occurs when checkpointing has been dynamically throttled with a new Checkpoint rate.
sqlserver feature_extension Occurs when received and parses data for a feature in feature extension.
sqlserver file_handle_in_use Fired when a file handle we're trying to delete is in use and we don't expect it to be. The typical response is dumping all the handles in the system via SysInternals HANDLE.EXE
sqlserver file_read_enqueued File read enqueued
sqlserver file_read_throttled File read throttled
sqlserver file_write_enqueued File write enqueued
sqlserver file_write_throttled File write throttled
sqlserver ghost_cleanup_task_packet_enqueue A task packet is enqueued
sqlserver ghost_cleanup_task_process_packet A task packet is dequeued and processed
sqlserver ghost_cleanup_task_process_pages_for_db_packet Purge the pages that contain ghost records found in a database
sqlserver ghost_cleanup_task_start Ghost cleanup task start
sqlserver ghost_cleanup_task_suspend Ghost cleanup task suspend
sqlserver hadr_tds_synchronizer_payload_skip Hadron Tds Listener Synchronizer skipped a listener payload because there were no changes since the previous payload.
sqlserver lock_request_priority_state The priority state of a lock request
sqlserver log_cache_write_block Writing a log block to the log cache
sqlserver metadata_ddl_add_column Occurs when an ALTER TABLE ADD column operation is updating base index.
sqlserver metadata_ddl_alter_column Occurs when an ALTER TABLE ALTER column operation is updating base index.
sqlserver metadata_ddl_drop_column Occurs when an ALTER TABLE DROP column operation is updating base index.
sqlserver mixed_extent_activation Track mixed extent activation and deactivation operations.
sqlserver mixed_extent_allocation Track mixed extent allocation operations
sqlserver mixed_extent_deallocation Track mixed extent deallocation operations.
sqlserver mixed_page_allocation Track mixed page allocation operations
sqlserver mixed_page_deallocation Track mixed page allocation operations
sqlserver mixed_page_scan_file Track the activity of SGAM scans for mixed page allocation
sqlserver mixed_page_scan_page Track the activity of SGAM scans for mixed page allocation
sqlserver mixed_page_skipextent Track the activity of SGAM scans for mixed page allocation
sqlserver natively_compiled_proc_execution_started Fired before a natively compiled procedure is executed.
sqlserver natively_compiled_proc_slow_parameter_passing Occurs when a Hekaton procedure call dispatch goes to slow parameter passing code path
sqlserver process_killed_by_abort_blockers A process is killed by an ABORT = BLOCKERS DDL statement
sqlserver query_execution_batch_hash_aggregation_finished Occurs at the end of batch hash aggregation.
sqlserver query_execution_batch_hash_children_reversed Occurs each time when hash join reverses build and probe side while processing data spilled to disk.
sqlserver query_execution_batch_hash_join_spilled Occurs each time when hash join spills some data to disk in batch processing.
sqlserver query_optimizer_estimate_cardinality Occurs when the query optimizer estimates cardinality on a relational expression.
sqlserver query_optimizer_force_both_cardinality_estimation_behaviors Both traceflags 2312 and 9481 were enabled, attempting to force both old and new cardinality estimation behaviors at the same time. The traceflags were ignored. Disable one or both of the traceflags.
sqlserver query_store_failed_to_capture_query Fired if the Query Store failed to capture query. The Query Store will not track statistics for this query
sqlserver query_store_failed_to_load_forced_plan Fired if the query failed to load forced plan from QDS. Forcing policy will not be applied
sqlserver query_store_persist_on_shutdown_failed Occurs when SQL Server fails to store dirty entries in Query Store on database shutdown.
sqlserver recovery_indirect_checkpoint Chose to enqueue an indirect checkpoint as we are near our recovery target
sqlserver remove_database_cache Remove database cache
sqlserver server_max_workers Occurs when a request enqueue fails because a worker was not guaranteed.
sqlserver session_recoverable_state_change Occurs when the server determines a state change in term of session recovery on a connectionresilency-enabled connection.
sqlserver sql_transaction_commit_single_phase Occurs when a sql transaction is committed with single phase commit.
sqlserver xfcb_blob_properties_obtained Windows Azure Storage blob property is obtained from response header.
sqlserver xfcb_failed_request Failed to complete a request to Windows Azure Storage.
sqlserver xfcb_header_obtained Response header is obtained from request to Windows Azure Storage.
sqlserver xfcb_read_complete Read complete from Windows Azure Storage response.
sqlserver xfcb_request_opened A request is opened to Windows Azure Storage.
sqlserver xfcb_send_complete Request send to Windows Azure Storage is complete.
sqlserver xfcb_write_complete Request send to Windows Azure Storage is complete.
sqlserver xtp_create_procedure Occurs at start of XTP procedure creation.
sqlserver xtp_create_table Occurs at start of XTP table creation.
sqlserver xtp_db_page_allocation_allowed Indicates that page allocations for the database are allowed.
sqlserver xtp_db_page_allocation_disallowed Indicates that page allocations for the database are disallowed due to memory pressure.
sqlserver xtp_deploy_done Occurs at completion of XTP object deployment.
sqlserver xtp_matgen Occurs at start of MAT generation.
sqlserver xtp_offline_checkpoint_scan_start Fired by XTP offline checkpoint when the checkpoint thread begins.
sqlserver xtp_offline_checkpoint_scan_stop Fired by XTP offline checkpoint when the checkpoint thread stops.
sqlserver xtp_recover_done Occurs at completion of log recovery of XTP table.
sqlserver xtp_recover_table Occurs at start of log recovery of XTP table.
sqlserver xtp_storage_table_create Occurs at just before the XTP storage table is created.
ucs ucs_connection_rejected_by_proxy_whitelist After a connection attempt to the UCS proxy endpoint is rejected by whitelist check
ucs ucs_proxy_connect_next_hop UCS proxy next hop connection
ucs ucs_proxy_receive_proxy_connect_message UCS proxy receive proxy connect message
ucs ucs_proxy_route_add UCS proxy route added
ucs ucs_proxy_route_disable UCS proxy route disabled
ucs ucs_proxy_route_refresh UCS proxy route refreshed
ucs ucs_proxy_send_proxy_connect_message UCS proxy send proxy connect message
XtpCompile cgen Occurs at start of C code generation.
XtpCompile invoke_cl Occurs prior to the invocation of the C compiler.
XtpCompile mat_export Occurs at start of MAT export.
XtpCompile pitgen_procs Occurs at start of PIT generation for procedures.
XtpCompile pitgen_tables Occurs at start of PIT generation for tables.
XtpEngine after_changestatetx_event Fires after transaction changes state.
XtpEngine alloctx_event  
XtpEngine attempt_committx_event Is raised when a transaction is asked to commit.
XtpEngine before_changestatetx_event Fires before transaction changes state.
XtpEngine dependency_acquiredtx_event Raised after transaction takes a dependency on another transaction.
XtpEngine endts_acquiredtx_event Fires after transaction acquires an end timestamp.
XtpEngine gc_base_generation_evaluation Indicates that an evaluation of updating the GC base generation has been made.
XtpEngine gc_base_generation_updated Indicates that the oldest active transaction hint used for calculating the GC base generation has been updated.
XtpEngine gc_cycle_completed Indicates that a GC notification has been enqueued.
XtpEngine gc_notification Indicates that GC is processing a notification.
XtpEngine redo_single_hk_record Redo on a HK log record
XtpEngine trace_add_delta_filter_begin Adding delta filter.
XtpEngine trace_add_duplicate_delta_filter Adding duplicate delta filte.
XtpEngine trace_adding_tx_filter Adding tx filter.
XtpEngine trace_begin_close_ckpt_processing Beginning close checkpoint processing: checkpoint flush fence = $2 ($3 state).
XtpEngine trace_cfd_entry_deleted Cleaned up data CFD entry for file $2 $3.
XtpEngine trace_cfd_rows_created Successfully created CFD rows.
XtpEngine trace_cfdtable_dump CfdTable dump
XtpEngine trace_cfp_handles_opened Opened CFP handles for {data: $2 delta: $3} in section $4.
XtpEngine trace_cfp_removed_from_freelist Removed CFP from freelist for destination file.
XtpEngine trace_checkpoint_file_flush Indicating that a file has started a flush operation.
XtpEngine trace_checkpoint_load_begin Starting Checkpoint load with checkpoint timestamp=$2.
XtpEngine trace_checkpoint_write_io Logs an event indicating that an IO has started (when event_type == IoStarted) or that an IO has completed (when event_type == IoComplete).
XtpEngine trace_checksum_validation_succeeded Checksum validation for Hekaton checkpoint file during backup succeeded.
XtpEngine trace_ckpt_close_begin Closing checkpoint in active/passive state.
XtpEngine trace_ckpt_close_signaled Signaling checkpoint close. The checkpoint policy object is flagged to close the current checkpoint
XtpEngine trace_ckpt_closed Checkpoint closed
XtpEngine trace_ckpt_load_thread_stats Statistics of the checkpoint load thread.
XtpEngine trace_ckpt_mrt_dump Mrt dump
XtpEngine trace_ckpt_serialization_state_dump Checkpoint serialization state dump.
XtpEngine trace_ckpt_stream_io_stats Io statistics for this particular stream.
XtpEngine trace_close_file_log_rec_created Successfully created close file log record.
XtpEngine trace_data_file_pages_txs This event gives the number of pages and the transaction range for a data file.
XtpEngine trace_data_from_page_deserialized Deserialized data from page $2 in file $4.
XtpEngine trace_data_row_modified Data row modified/deleted.
XtpEngine trace_delete_cfd_rows_begin Matching MRT not found for the Data CFD. Deleting corresponding CFD Rows.
XtpEngine trace_delete_DeletedObjectTable_row_begin Deleting row from DeletedObjectTable in the context of transaction: $5.
XtpEngine trace_delete_merged_source_file Deleting merged source $7 file $5:$6. DeleteLSN = $2:$3:$4.
XtpEngine trace_delete_mrtrow_for_target_begin Deleting mrt row for target.
XtpEngine trace_delta_entry_skipped Skipping delta {$3, $4, $5} because it is beyond the snapshot checkpoint: $6.
XtpEngine trace_delta_file_future_count_rows_pages_update Updating future count pages and count rows for delta file.
XtpEngine trace_delta_file_lastgood_count_rows_pages_update Updating lastgood count pages and count rows for delta file.
XtpEngine trace_delta_file_pages_txs This event gives the number of pages and the transaction range while reading a delta file for a particular operations.
XtpEngine trace_delta_file_processed Completed processing of Delta file.
XtpEngine trace_delta_file_set_clean_or_dirty Delta file set clean or dirty.
XtpEngine trace_delta_filter_removed Removing delta filter from DeltaFilterMap due to transaction filter.
XtpEngine trace_delta_watermark_removed Removed delta watermark.
XtpEngine trace_delta_watermark_updated Updated delta watermark row for FileId: $4.
XtpEngine trace_deserialize_chained_records Info pertaining to the deserialized content of the mrtRecords chain.
XtpEngine trace_deserialize_data_from_page_begin Deserializing data from page $2 in file $4.
XtpEngine trace_deserialize_source_delta_file_begin Deserializing source delta file $4: page range [$2, $3).
XtpEngine trace_dump_cfd_row Dump cfd row
XtpEngine trace_dump_mrt_row Merge Request
XtpEngine trace_file_close_skipped Skipping file close for $2 as the cfp had been merged, It could not be found in the Storage array.
XtpEngine trace_file_skipped Skipping file $4 due to reason $5.
XtpEngine trace_file_skipped_with_tx_range Skipping file $5 with TX range { $3 -> $4 } due to MaxTxId == InvalidTxId.
XtpEngine trace_first_page_header_flushed Flushed first page header for file.
XtpEngine trace_flush_io_operation_for_delta_file This trace is trying to catch pages issued for flush that didn't make it to the file, or were lacking the correct DeltaWatermark Timestamp.
XtpEngine trace_flush_skipped_for_closed_data_file Skipping flush for data file ('$2') as it is closed.
XtpEngine trace_future_count_pages_update Updating future count pages.
XtpEngine trace_initial_merge Doing initial merge for destination $3 at CheckpointTs: $2.
XtpEngine trace_insert_DeletedObjectTable_row_begin Inserting row to DeletedObjectTable in the context of transaction: $5.
XtpEngine trace_intialize_worker_file_begin Processing file close.
XtpEngine trace_mark_data_file_closed Marking the active data file as closed to prevent the offline worker fom flushing them.
XtpEngine trace_merge_cancelled Merge was cancelled for destination $2.
XtpEngine trace_merge_complete_log_rec_ignored MergeCompleteLogRecord ignored for destination file $3.
XtpEngine trace_merge_complete_log_rec_processed Successfully processed MergeCompleteLogRecord.
XtpEngine trace_mrt_dump Mrt dump.
XtpEngine trace_mrt_row_inplace_update Mrt row updated in place.
XtpEngine trace_mrtrow_not_found Could not find row with Destination ID: $2 in MRT table.
XtpEngine trace_new_delta_watermark_inserted Inserted new delta watermark row for FileId $3.
XtpEngine trace_offline_process_stale_merge_item Offline processing of stale merge item {$2, $3} Destination $4.
XtpEngine trace_pending_tx Pending Transaction
XtpEngine trace_populate_storage_array Populating the storage array.
XtpEngine trace_post_writefile This event is fired after issuing IO on the proxy.
XtpEngine trace_process_file_close_begin Processing file close.
XtpEngine trace_process_merge_complete_log_record Process merge complete log record.
XtpEngine trace_process_merge_request Process Merge Request
XtpEngine trace_process_source_file Processing source $4 file $2.
XtpEngine trace_process_target_file Processing target $4 file $2.
XtpEngine trace_queue_merge_work_item Queue work item: {$2, $3} $5 - $6 with CheckpointTs as $4.
XtpEngine trace_recovered_existing_file Recovered exising files files allocated during forward processing that are not already part of the CCB.
XtpEngine trace_resyncing_state_of_file Resyncing state of file $4 ('$5') post recovery:  $2 pages, $3 rows.
XtpEngine trace_serialization_object_cleaned Cleaned serialization object.
XtpEngine trace_serialize_abort_transaction_begin Serializing the abort transaction filter to the delta cache corresponding to its own transaction range.
XtpEngine trace_serialize_tx_begin Serialize TS begin for completing the checkpoint in active state.
XtpEngine trace_serialize_tx_end Serialize TS end for completing the checkpoint.
XtpEngine trace_serialized_delta_rows_to_file Serialized delta rows to file.
XtpEngine trace_source_file_load_stats Load stats for source files in merge.
XtpEngine trace_storage_refreshed_for_file Storage refreshed for file.
XtpEngine trace_uninitialize_file uninitializing_file
XtpEngine trace_wrote_delta_watermark Wrote delta watermark for file: $3, checkpointTs: $2.
XtpEngine waiting_for_dependenciestx_event Raised when we have explicitly waited for dependencies to clear.
XtpEngine xtp_before_create_log_record Fired before submitting a request to the host to serialize a non-transactional log record.
XtpEngine xtp_checkpoint_file_flush Indicates the point at which a given file has been flushed to disk.
XtpEngine xtp_checkpoint_file_flush_complete Indicates the point at which all in-flight buffers have been flushed to disk.
XtpEngine xtp_checkpoint_file_read Indicates reading of a file in XTP checkpoint recovery.
XtpEngine xtp_checkpoint_worker_active_log Indicates that the checkpoint worker has started processing the log records in the active portion of the log.
XtpEngine xtp_checkpoint_worker_started Indicates that the checkpoint worker has started processing the log records.
XtpEngine xtp_checkpoint_worker_stopped Indicates that the checkpoint worker has started processing the log records.
XtpEngine xtp_checkpoint_write_io Indicates that the checkpointing subsystem has issued or completed a write IO.
XtpEngine xtp_complete_checkpoint Indicates the begin and end of complete checkpoint processing.
XtpEngine xtp_create_log_record Fires when the XTP engine creates a log record.
XtpEngine xtp_delta_marked_dirty Indicates that a delete/update was serialized and marked a delta file dirty
XtpEngine xtp_merge_complete_log_record Indicates merge complete log record is posted to the log.
XtpEngine xtp_merge_log_complete_log_record Indicates merge log record was interpreted by the offline worker.
XtpEngine xtp_merge_process_log_record Indicates merge log record was interpreted by the offline worker.
XtpEngine xtp_merge_request_log_record Indicates merge request log record is posted to the log.
XtpEngine xtp_merge_request_started Indicates merge request has been picked up by the merge worker thread.
XtpEngine xtp_root_deserialized Indicates that the load of a checkpoint root is complete.
XtpEngine xtp_root_serialized Indicates that the write of the checkpoint root is complete.
XtpRuntime bind_md Occurs prior to binding metadata for a memory optimized table.
XtpRuntime bind_tables Occurs prior to binding tables for a natively compiled procedure.
XtpRuntime create_table Occurs prior to creating memory optimized table.
XtpRuntime deserialize_md Occurs prior to deserializing metadata.
XtpRuntime load_dll Occurs prior to loading the generated DLL.
XtpRuntime recover_done Occurs at completion of checkpoint recovery of a memory optimized table.
XtpRuntime recover_table Occurs at start of checkpoint recovery of a memory optimized table.
XtpRuntime serialize_md Occurs prior to serializing metadata.
XtpRuntime unload_dll Occurs prior to unloading the generated DLL.

Happy tracing,



Received MVP Award for SQL Server // Ausgezeichnet mit dem MVP-Award

Ausgezeichnet mit dem MVP-Award für SQL Server

Gestern, am 1. April 2014 erhielt ich die frohe Botschaft: Für meine Beiträge zur Community bin ich von Microsoft mit dem MVP Award (MVP=Most Valuable Professional) ausgezeichnet worden (zum MVP-Profil).

Yesterday, on 1 April 2014, I got great news:
I have been awarded with the MVP Award by Microsoft (MVP=Most Valuable Professional) for my contributions to the community (click here for MVP profile).







Ich freue mich riesig über diese Anerkennung meiner zahlreichen Konferenz-Beiträge, Usergroup-Meetings und anderen Aktivitäten!

So bin ich seit 2009 bis Stand heute auf 27 Konferenzen als Redner zu verschiedenen SQL Server Themen, von Sicherheit über Performance & Indexdesign bis hin zu Reporting Services aufgetreten und habe auf weiteren 34 Usergroup-Meetings Vorträge gehalten.

2013 hat sich meine Aktivität noch einmal gesteigert, so dass ich allein in diesem Jahr auf 11 Konferenzen, von Istanbul/Türkei bis Charlotte/USA, und 14 PASS Regionalgruppentreffen (Liste) als Sprecher angetreten bin.
Und auch 2014 habe ich bereits 2 Konferenzen und 4 Regionalgruppentreffen (Liste) hinter mir – weitere werden folgen: Portugal, England und wenn es hoffentlich? klappt zum 6. Mal in den USA :-)

Damit bin ich fast so viel auf Konferenzen unterwegs wie drei weitere deutsche MVP-Kollegen, die diese Auszeichnung bereits hochverdient für ihren Einsatz für die deutsche SQL Server Community PASS Deutschland e.V. erhalten haben: Tillmann Eitelberg, Oliver Engels – beide im Vorstand der PASS – und Constantin Klein, der unter anderem den Just Community e.V. und die .NET User Group Frankfurt leitet. – Ich freue mich, zu dieser illustren Runde dazustoßen zu können!

Dieser Award ist für mich daher eine tolle Anerkennung und ein Ansporn weiterzumachen – was natürlich nicht heißt, dass ich es nicht auch ohne diesen tun würde, denn die Interaktion mit der Community ist für sich gesehen schon eine echte Bereicherung, von der alle profitieren.

An dieser Stelle möchte ich einigen Personen persönlich meinen Dank aussprechen, die meine Nominierung zum MVP auf verschiedene Weise unterstützt haben:

I am very excited about this recognition of my many contributions to conferences, usergroup meetings, and other activities!

In fact, since 2009 till today, I have appeared as a speaker in 27 conferences, speaking on different SQL Server topics from security to performance & index design to reporting services, and have given presentations in 34 usergroup meetings.

2013 saw yet another boost in my activities: That year alone I was speaker at 11 conferences, from Istanbul/Turkey to Charlotte/USA, and at 14 PASS regional group meetings (list).
In 2014, too, I can already look back on 2 conferences and 4 regional group meetings (list) – with more to come: Portugal, England, and – hopefully it will work out – USA for the 6th time. :-)

Thus, I am participating in conferences to an almost similar extent as three other German MVP colleagues who have already been awarded, quite deservedly, the very award for their commitment to the German SQL Server Community PASS Deutschland e.V.: Tillmann Eitelberg, Oliver Engels – both of them in the chair of the PASS – and Constantin Klein who heads the Just Community e.V. and the .NET User Group Frankfurt, among others. – I am honored to be joining this illustrious circle!

This award is indeed a fantastic acknowledgement and incentive to continue – of course, this doesn’t mean I wouldn’t do so without this award, because the interaction with the community is in itself already an enrichment from which everyone is benefiting.

I would like to express my personal thanks to the following people who have supported my nomination to MVP in various ways:


Anna Feth (Microsoft MVP Programmkoordinator Region DACH), @AnnaFeth), Constantin Klein (MVP, Blog, @KostjaKlein), Daniel Weinmann (Product Marketing Manager - SQL Server/ BI Microsoft GmbH), Florian Endres (Community Program Manager, @senorf), Heike Ritter (Technical Evangelist Microsoft Deutschland GmbH, @HeikeRitter), Jack Richins (Principal Development Lead Microsoft SQL Server Security), Katrin Letzel (Microsoft MVP Community Program Manager, @katko), Niko Neugebauer (MVP, Blog, @NikoNeugebauer), Oliver Engels (MVP, @oengels), Scott Klein (SQL Server Evangelist, Microsoft, @SQLScott), Tillmann Eitelberg (MVP, Blog, @_Tillmann) and the Team of the datafino GmbH, to which I have a strong partnership as well as all anonymous supporters and technical sparring partners.


So bin ich mindestens genauso dankbar für all die wertvollen Kontakte, die Gesprächsmöglichkeiten und den Austausch untereinander wie für den Award selber.

Thus, I am at least equally grateful for all the valuable contacts, opportunities for dialogue and mutual exchange as for the award itself.



MCSM, MCM and MVP :-)

DISABLE and DENY LOGIN, DENY USER & Effect on Impersonation and Permissions

DISABLE und DENY LOGIN, DENY USER & Effekt auf Impersonierung und Berechtigungen

Ein kurzer Artikel zu den Effekten – oder fehlenden Effekten – in Bezug auf das Deaktivieren & Verbieten von Connect für Logins und Users auf Impersonierung und Berechtigungen.

A short article on the effects - or missing effects - regarding the disabling & denying connect of Logins & Users on impersonation and permission.

Immer mal wieder kann man beobachten, dass Logins oder Usern die Connect-Berechtigung verboten bekommen wurde, oder ein Login deaktiviert wurde.

Die richtige Erwartung und Verständnis kann daher kritisch sein.

Sehen wir uns also eine einfache Demo an:
Wir werden das eingebaute sa-Konto, welches von vielen unter anderem als Datenbankbesitzer (mehr dazu bald in einem anderen Artikel – zwischenzeitlich lade ich Sie dazu ein, noch Daten zu der Umfrage zu diesem Thema einzusenden), ein weiteres frisch angelegtes Konto und eine Datenbank, genannt ImpersonateLogin mit dem entsprechenden User + einem weiteren User ohne Login: SQLUser.

Every once in a while one can observe that Logins or Users have been denied the Connect permission or a Login has been disabled.

Therefore a correct expectation and understanding can be critical.

So let’s see a simple demo:
We will use the built-in sa-Account, which is used by many as database owner among other (more on that soon in another article - meanwhile I do invite you to still send in data for the survey on that topic), another freshly created Account DeniedLogin and a database called ImpersonateLogin with the according User + another User without Login: SQLUser.


Ich deaktiviere also das sa-Konto ebenso wie das „DeniedLogin“-Konto – letzterem verbiete ich außerdem die Connect-Berechtigung (Erinnern wir uns daran: „Berechtigungen können nicht für sa, dbo, Entitäts-Besitzer, information_schema, sys oder für den Benutzer selbst erteilt, verweigert oder aufgehoben werden.“)

Der Datenbank-User „SQLUser“ bekommt die Connect-Berechtigung auf die Datenbank verboten.
In der GUI sieht das Ergebnis so aus:

So I am disabling the sa-account as well as the “DeniedLogin”-Account – the latter I also Deny the Connect permission (Remember we “Cannot grant, deny, or revoke permissions to sa, dbo, entity owner, information_schema, sys, or yourself.”)

The Database-User “SQLUser” gets denied the Connect permission on the database.

In the GUI the result looks like this:



Nun führen wir 4 Tests durch:

Now let’s run 4 tests.


Was diese Abfragen im Wesentlichen machen, ist, zu versuchen, den entsprechenden Login oder User zu impersonieren – und den Erfolg dadurch belegen, dass sie die dann jeweils aktiven Rollen-Mitgliedschaften zurückgeben.

So essentially what those queries do, is trying to impersonate the respective Login or User – and proofing success by returning the then respective active role-memberships.



DeniedLogin: Impersonierung funktioniert + kein Verlust an Berechtigungen.
In other words: Denying Connect to a Login does not disallow Impersonation.
Impersonation is actually another permission which one can use and is not affected even by Disabling the Login!

DeniedLogin: Impersonation works + No loss of permissions.
In other words: Denying Connect to a Login does not disallow Impersonation.
Impersonation is actually another permission which one can use and is not affected even by Disabling the Login!


Dasselbe gilt für den sa: Impersonierung funktioniert + kein Verlust a Berechtigungen.

Im Folgenden ein Test für den User, dem die Connect-Berechtigung auf die Datenbank entzogen worden ist – und nicht als Login verwendet werden kann.

Same applies for sa: Impersonation works + No loss of permissions.

In the following test for the User which has been denied the Connect-permission onto the database – and cannot be used as a Login.



Msg 15517, Level 16, State 1, Line 3

Die Ausführung als Datenbankprinzipal ist nicht möglich, weil der Prinzipal 'DeniedLogin' nicht vorhanden ist,
für diesen Typ von Prinzipal kein Identitätswechsel möglich ist, oder Sie nicht die erforderliche Berechtigung haben.


Msg 916, Level 14, State 1, Line 3

Der Serverprinzipal 'S-1-9-3-4049223906-1289824279-1154161590-488313048.'
kann unter dem aktuellen Sicherheitskontext nicht auf die ImpersonateLogin-Datenbank zugreifen.


Msg 15517, Level 16, State 1, Line 3

Cannot execute as the database principal because the principal "DeniedLogin" does not exist,
this type of principal cannot be impersonated, or you do not have permission.


Msg 916, Level 14, State 1, Line 3

The server principal "S-1-9-3-4049223906-1289824279-1154161590-488313048."
is not able to access the database "ImpersonateLogin" under the current security context.


Das Ergebnis ist für beide Datenbank-User effektiv das gleiche.

Die GUID repräsentiert keinen reellen Server-Prinzipal, denn der User SQLUser hat keinen entsprechenden Login.
Daher sagt es uns, dass die User nicht innerhalb der Datenbank impersoniert werden können.

Der Unterschied für den 2. User ist, dass dieser User nur innerhalb der Datenbank existiert, aber zugleich expliziert verboten wurde, sich mit ihr zu verbinden Das hat im Endeffekt dasselbe Resultat, wie ihn zu deaktivieren – genau wie der Guest-User es ist.

The GUID does not represent a real server-principal, because the User SQLUser does not have a matching Login.
So it tells us, that the users cannot be impersonated inside the database.

The difference for the second user is, that this user only exists inside the database but at the same time has been explicitly denied to connect to it. This has essentially the same result as “disabling” it – just as the guest-user is.


Damit wäre gezeigt, dass das Deaktivieren von Logins keinerlei Sicherheit gegenüber Angriffen von Innen gibt. Und sogenannte Privilegien Erweiterung findet in aller Regel z.B. von innen heraus statt.

Auch der alte „Trick“, die Standard-Datenbank des Logins zu löschen, ist da keine Hilfe.

Für Datenbank-User hat es durchaus Effekt und verhindert das Anmelden an der jeweiligen Datenbank – auch „von Innen heraus“.


Thereby it is shown, that disabling of Logins does not give any security against attacks from inside. And so-called privilege elevation (/-escalation) usually takes part from internal.

Also the old “trick”, to drop the default-database of a Login, is of little help.

For database-users is indeed does have an effect and prevents logon/connect to the respective database – also “from inside”.


Konsequenterweise bleiben alle Berechtigungen (natürlich abgesehen von dem jeweiligen Deny) der jeweiligen Logins und User absolut unbeeinflusst von einer Deaktivierung jeglicher Weise.

Das gilt auch im Zusammenhang mit „External Access“-Berechtigung für Logins basierend auf asymmetrischen Schlüsseln.
(Hier ein Forum-Thread, in dem die Frage auftauchte: “SQL Login "disabled" flag does not work with asymmetric key??”)

ALTER LOGIN ist auch hier in BOL erklärt:


Ich hoffe, diese Dinge erklären einiges und speziell Empfehlungen in Sicherheits-Aspekten.

Consequentially all permissions (besides the one denied of course) of the respective Login and User stay totally unaffected by and method of deactivation.

This is also true in the context of “external access”-permission for Logins based on asymmetric keys.
(Here a forum-thread where the question appeared: “SQL Login "disabled" flag does not work with asymmetric key??”)

ALTER LOGIN is also explained in BOL here:


I hope those things clarified some things and especially recommendations in security-matters


Happy securing


Upcoming Conferences: SQLSaturdays in Denmark and Portugal. – Presenting In-Memory OLTP Deep-Dive for Administrators


Im März und April finden 2 europäischen SQLSaturdays statt, die Potential zu einem Geheimtipp haben:

Am 29. März in Kopenhagen, Dänemark
ind am 12. April in Lissabon, Portugal.

Da ich auch letztes Jahr bereits in Kopenhagen als Sprecher dabei war, kann ich aus erster Hand für die exzellente Organisation und Sprecherauswahl verbürgen.

Und für Portugal verbürgt mein sehr geschätzter Kollege Niko Neugebauer (Blog, Twitter:@NikoNeugebauer) – nach allem, was bisher „geleaked“ ist, wird es wieder ein Kongeniales Event. Würdig der ersten SQLSaturday-Location in Europa überhaupt.

In March and April, 2 European SQLSaturdays take place, which have the potential for a real insiders’ tip:

On March 29th in Copenhagen, Denmark
and April 12th in Lisbon, Portugal.

Since I already participated as a speaker in Copenhagen last year, I can avouch from “insider information” for the excellent organization and speaker choice.

And my much valued collegue Niko Neugebauer (Blog, Twitter:@NikoNeugebauer) vouches for Portugal – after all what has “leaked” in th emeantine, it will be another congenial event. Condign to the first SQLSaturday-Location in Europe ever.


Ich möchte zumindest einige, mir persönlich bekannte Sprecher namentlich erwähnen, aber gleich betonen, dass auch alle anderen Sessions definitiv einen Blick Wert sind(!):

In Dänemark sind dabei:

Die vollständige Agenda befindet sich hier:

I would like to at least name some speakers, which I personally know, by name, but I would also like to stress immediately, that also all other sessions are definitely worth looking at just as well:

In Denmark there will be:

I am excited to see all of you again soon!

The complete Agenda can be found here:

I am excited to see all of you again soon in Copenhagen!


In Portugal sind dabei:

Die vollständige Agenda befindet sich hier:

In Portugal there will be:

The complete Agenda can be found here:

I am excited to see all of you again soon in Lisbon!

Und meine Wenigkeit, was präsentiere ich?
Nachdem ich mich nun seit November 2013, wo ich die neue Technologie in Deutschland erstmalig öffentlich präsentierte, eingehend mit eXtreme Transactional Processing, der neuen, integrierten, In-Memory Engine des SQL Server beschäftige, gibt es nun den Deep-Dive für Administratoren:

And my humble self, what am I presenting?
After I have been engaged deeply with eXtreme Transactional Processing, the new, integrated, In-Memory Engine of SQL Server, since November 2013, where I presented this new technology for the first time publically in Germany, I will now give a Deep-Dive for Administrators:


SQL Server 2014 In-Memory OLTP / XTP Management Deep Dive
@SQLSaturday Denmark & Portugal

You have heard about Hekaton, respectively the new acronym XTP (eXtreme Transactional Processing) and you want to know how it works under the cover? In this session we will take a closer look at the architecture, where the performance benefits come from and how it works in detail. What kind of files is SQL Server using for transactional consistency, what happens when you delete data inside the log and inside the data files. We will see the Merge-Process and the Garbage Collector in action, see how memory management works for XTP enabled databases, and thereby get an in-depth understanding of how this completely new storage engine works and how to optimize for it.


CU in Copenhagen, Lisbon.. or at another conference soon :-)



Spezialrabattaktionen: 22% zum Jubiläumsjahr der PASS Deutschland e.V für Sicherheits- XEvent Tracing, sowie SQL Server 2014 Seminare + 10% Microsoft TechNet Aktion für alle

Damit auch alle, die vor 2 Wochen nicht auf der SQL Konferenz in Darmstadt ( waren, oder nicht regelmäßig bei TechNet vorbeischauen, eine Chance haben, poste ich es hier einmal öffentlich:

1)       Zum Jubiläumsjahr der PASS Deutschland e.V erhalten alle Mitglieder den „Jubiläums-Rabatt“ von 22 Prozent (!) auf die folgenden SQL Server Master-Classes:


(SES) SQL Server Security Essentials für Entwickler & Administratoren – am 3. April 2014 in Düsseldorf
- Dieses Seminar ist für alle Einsteiger geeignet, die hier alle Grundlagen für die Sicherheitsverwaltung & Architektur von SQL Server erlernen.

(SIA) Securityworkshop for SQL Server Administrators (advanced) – 4. April 2014 in Düsseldorf
- In diesem Workshop werden sicherheitsrelevante Internas der Authentifizierung, das Konzept der Contained Databases, Daten- & Backupverschlüsselung mit Transparent Data Encryption (TDE) bis hin zu Überwachungstechniken in SQL Server erlernt.

(XE1) Einsteiger-Workshop Tracing mit Extended Events in SQL Server - am 7. März 2014 in Frankfurt am Main
- Das Grundlagentraining für alle, die den Nachfolger von SQL Trace & Profiler von Grund auf beherrschen möchten.

(XE3) FastTrack to Tracing with Extended Events for SQL Server - am 1. April 2014 in Frankfurt am Main
- Für Einsteiger, die die Funktionalitäten und Einsatzmöglichkeiten der Extended Events von A-Z an einem verlängerten Tag kennenlernen möchten.

(UP2014) Aktualisierung der Kenntnisse von SQL Server 2012 auf SQL Server 2014 - am 15. Mai 2014 in Frankfurt am Main
- Der neue SQL Server 2014 steht vor der Tür. Von Buffer-Pool Erweiterungen bis zu In-Memory OLTP enthält dieser viele wichtige neue Technologien, die an diesem Tag praxisnah kennengelernt werden.

(XTC) Workshop In-Memory OLTP & ColumnStore - New Storage Engines in SQL Server 2014 - am 16. Mai 2014 in Frankfurt am Main
- An diesem eintägigen Workshop werden die neuen und verbesserten In-Memory Technologien XTP für extrem performantes OLTP und Clustered Columnstore Indexes für DataWarehousing in aller Tiefe praktisch kennengelernt.

Und das ist der Gutschein-Code (exklusiv für Mitglieder der PASS Deutschland e.V.):
10PASSDE2014 *1 *2

Tipp: jeder kann sich jederzeit noch schnell und kostenlos(!) als Mitglied bei der PASS Deutschland e.V. anmelden – Details dazu auf der Webseite der PASS:


2)       Außerdem freue ich mich, noch auf eine andere Aktion, und zwar in Zusammenarbeit mit der  Microsoft Technet IT Pro Academy hinweisen zu können. Für ausgewählte Seminare gibt es einen speziellen Rabattcode *1, der hier über die TechNet-Seite zu finden ist:, für 10% auf weitere Seminare:

(PAT) Workshop Performance und Analyse, Techniken & -Tools
- 17./18. März 201

(SHA) Workshop Hochverfügbarkeit für SQL Server - 6./7. Mai 2014

*1  Rabatt-Codes können nicht mit anderen Codes kombiniert werden
*2 Dieser Code ist gültig bei Verwendung bis zum bis 30.4.2014

Und hier findet Ihr die Gesamtübersicht der SQL Server Master-Classes und Links zur Anmeldung:

Viel Spaß beim Lernen wünscht

Der Andreas


Microsoft Certified Solutions Master Data Platform (SQL Server 2012)
Microsoft Certified Master SQL Server 2008
Microsoft Certified Trainer

1 2 3 4 5 6 7 8 9 10 »