O SQL Server 2014 introduziu uma série de novos Eventos Estendidos, que fazem parte do framework de rastreamento. Esses eventos fornecem informações valiosas sobre o funcionamento interno do SQL Server e podem ser usados para solução de problemas e ajuste de desempenho.
Uma das principais adições no SQL Server 2014 é o mecanismo In-Memory OLTP, também conhecido como XTP. Esse mecanismo traz melhorias significativas de desempenho para determinadas cargas de trabalho, utilizando tabelas otimizadas em memória e procedimentos armazenados compilados nativamente. Para suportar esse novo mecanismo, foram introduzidos três novos pacotes de Eventos Estendidos:
- XtpRuntime: Eventos estendidos para o tempo de execução do XTP
- XtpEngine: Eventos estendidos para o mecanismo XTP
- XtpCompile: Eventos estendidos para a compilação do XTP
Esses pacotes são divididos em três DLLs e fornecem um total de 245 novos eventos. Isso permite que desenvolvedores e administradores mergulhem profundamente nos detalhes do mecanismo In-Memory OLTP e obtenham uma melhor compreensão de seu comportamento.
Vale ressaltar que o SQL Trace, o mecanismo de rastreamento mais antigo no SQL Server, não foi atualizado no SQL Server 2014. O número de eventos do SQL Trace permanece o mesmo que no SQL Server 2008, com 180 eventos. Isso enfatiza ainda mais a mudança para os Eventos Estendidos como o método preferido de rastreamento no SQL Server.
Os Eventos Estendidos oferecem várias vantagens em relação ao SQL Trace, incluindo um maior número de eventos, melhor desempenho e mais flexibilidade na captura de eventos específicos de interesse. Eles também fornecem uma forma mais moderna e eficiente de rastrear e monitorar o SQL Server.
Aqui está uma comparação do número de Eventos Estendidos versus eventos do SQL Trace em diferentes versões do SQL Server:
Versão do SQL Server | Eventos Estendidos | SQL Trace |
---|---|---|
SQL Server 2012 (SP1) | 625 | 180 |
SQL Server 2014 (RTM) | 870 | 180 |
Além dos novos eventos, o SQL Server 2014 também introduziu uma série de eventos renomeados. Por exemplo, “recovery_incremental_checkpoint” foi renomeado para “recovery_indirect_checkpoint” e “hekaton_slow_param_passing” foi renomeado para “natively_compiled_proc_slow_parameter_passing”. Essas mudanças refletem o contínuo desenvolvimento e aprimoramento do mecanismo In-Memory OLTP.
Os Eventos Estendidos no SQL Server 2014 oferecem uma ampla variedade de eventos para diversos fins, incluindo gerenciamento do query store, processamento de transações, operações em tabelas otimizadas em memória e muito mais. Aqui está uma lista de alguns dos novos Eventos Estendidos:
- qds_query_store_async_shutdown_failed
- qds_query_store_background_cleanup_task_failed
- qds_query_store_background_task_creation_failed
- qds_query_store_background_task_initialization_failed
- qds_query_store_background_task_persist_failed
- qds_query_store_begin_persist_runtime_stat
- qds_query_store_bloom_filter_false_positive
- qds_query_store_check_consistency_init_failed
- qds_query_store_database_initialization_failed
- qds_query_store_db_cleanup__finished
- qds_query_store_db_cleanup__started
- qds_query_store_disk_size_check_failed
- qds_query_store_disk_size_info
- qds_query_store_execution_runtime_info
- qds_query_store_execution_runtime_info_discarded
- qds_query_store_execution_runtime_info_evicted
- qds_query_store_flush_failed
- qds_query_store_loaded
- qds_query_store_notify_dirty_shutdown_on_partition_startup
- qds_query_store_notify_force_failure_failed
- qds_query_store_persist_task_init_failed
- qds_query_store_plan_forcing_failed
- qds_query_store_plan_persistence_failure
- qds_query_store_plan_removal
- qds_query_store_query_persistence_failure
- qds_query_store_read_write_failed
- qds_query_store_statement_not_found
- qds_query_store_unloaded
- sqlos_nonpreemptive_long_syncio
- sqlos_stuck_dispatcher_callback_executed
- sqlos_wait_completed
- sqlserver_after_natively_compiled_proc_entry_removal_on_drop
- sqlserver_availability_replica_state
- sqlserver_before_natively_compiled_proc_entry_removal_on_drop
- sqlserver_before_redo_lsn_update
- sqlserver_buffer_pool_eviction_thresholds_recalculated
- sqlserver_buffer_pool_extension_pages_evicted
- sqlserver_buffer_pool_extension_pages_read
- sqlserver_buffer_pool_extension_pages_written
- sqlserver_check_phase_tracing
- sqlserver_check_thread_message_statistics
- sqlserver_check_thread_page_io_statistics
- sqlserver_check_thread_page_latch_statistics
- sqlserver_clustered_columnstore_index_rebuild
- sqlserver_column_store_code_coverage
- sqlserver_column_store_index_build_low_memory
- sqlserver_column_store_index_build_process_segment
- sqlserver_column_store_index_build_throttle
- sqlserver_columnstore_tuple_mover_begin_compress
- sqlserver_columnstore_tuple_mover_end_compress
- sqlserver_database_xml_deadlock_report
- sqlserver_db_lock_acquired_from_cache
- sqlserver_db_lock_released_from_cache
- sqlserver_ddl_with_wait_at_low_priority
- sqlserver_diag_quantum_end
- sqlserver_dyn_throttle_checkpoint
- sqlserver_feature_extension
- sqlserver_file_handle_in_use
- sqlserver_file_read_enqueued
- sqlserver_file_read_throttled
- sqlserver_file_write_enqueued
- sqlserver_file_write_throttled
- sqlserver_ghost_cleanup_task_packet_enqueue
- sqlserver_ghost_cleanup_task_process_packet
- sqlserver_ghost_cleanup_task_process_pages_for_db_packet
- sqlserver_ghost_cleanup_task_start
- sqlserver_ghost_cleanup_task_suspend
- sqlserver_hadr_tds_synchronizer_payload_skip
- sqlserver_lock_request_priority_state
- sqlserver_log_cache_write_block
- sqlserver_metadata_ddl_add_column
- sqlserver_metadata_ddl_alter_column
- sqlserver_metadata_ddl_drop_column
- sqlserver_mixed_extent_activation
- sqlserver_mixed_extent_allocation
- sqlserver_mixed_extent_deallocation
- sqlserver_mixed_page_allocation
- sqlserver_mixed_page_deallocation
- sqlserver_mixed_page_scan_file
- sqlserver_mixed_page_scan_page
- sqlserver_mixed_page_skipextent
- sqlserver_natively_compiled_proc_execution_started
- sqlserver_natively_compiled_proc_slow_parameter_passing
- sqlserver_process_killed_by_abort_blockers
- sqlserver_query_execution_batch_hash_aggregation_finished
- sqlserver_query_execution_batch_hash_children_reversed
- sqlserver_query_execution_batch_hash_join_spilled
- sqlserver_query_optimizer_estimate_cardinality
- sqlserver_query_optimizer_force_both_cardinality_estimation_behaviors
- sqlserver_query_store_failed_to_capture_query
- sqlserver_query_store_failed_to_load_forced_plan
- sqlserver_query_store_persist_on_shutdown_failed
- sqlserver_recovery_indirect_checkpoint
- sqlserver_remove_database_cache
- sqlserver_server_max_workers
- sqlserver_session_recoverable_state_change
- sqlserver_sql_transaction_commit_single_phase
- sqlserver_xfcb_blob_properties_obtained
- sqlserver_xfcb_failed_request
- sqlserver_xfcb_header_obtained
- sqlserver_xfcb_read_complete
- sqlserver_xfcb_request_opened
- sqlserver_xfcb_send_complete
- sqlserver_xfcb_write_complete
- sqlserver_xtp_create_procedure
- sqlserver_xtp_create_table
- sqlserver_xtp_db_page_allocation_allowed
- sqlserver_xtp_db_page_allocation_disallowed
- sqlserver_xtp_deploy_done
- sqlserver_xtp_matgen
- sqlserver_xtp_offline_checkpoint_scan_start
- sqlserver_xtp_offline_checkpoint_scan_stop
- sqlserver_xtp_recover_done
- sqlserver_xtp_recover_table
- sqlserver_xtp_storage_table_create
- ucs_ucs_connection_rejected_by_proxy_whitelist
- ucs_ucs_proxy_connect_next_hop
- ucs_ucs_proxy_receive_proxy_connect_message
- ucs_ucs_proxy_route_add
- ucs_ucs_proxy_route_disable
- ucs_ucs_proxy_route_refresh
- ucs_ucs_proxy_send_proxy_connect_message
- XtpCompile_cgen
- XtpCompile_invoke_cl
- XtpCompile_mat_export
- XtpCompile_pitgen_procs
- XtpCompile_pitgen_tables
- XtpEngine_after_changestatetx_event
- XtpEngine_alloctx_event
- XtpEngine_attempt_committx_event
- XtpEngine_before_changestatetx_event
- XtpEngine_dependency_acquiredtx_event
- XtpEngine_endts_acquiredtx_event
- XtpEngine_gc_base_generation_evaluation
- XtpEngine_gc_base_generation_updated
- XtpEngine_gc_cycle_completed
- XtpEngine_gc_notification
- XtpEngine_redo_single_hk_record
- XtpEngine_trace_add_delta_filter_begin
- XtpEngine_trace_add_duplicate_delta_filter
- XtpEngine_trace_adding_tx_filter
- XtpEngine_trace_begin_close_ckpt_processing
- XtpEngine_trace_cfd_entry_deleted
- XtpEngine_trace_cfd_rows_created
- XtpEngine_trace_cfdtable_dump
- XtpEngine_trace_cfp_handles_opened
- XtpEngine_trace_cfp_removed_from_freelist
- XtpEngine_trace_checkpoint_file_flush
- XtpEngine_trace_checkpoint_load_begin
- XtpEngine_trace_checkpoint_write_io
- XtpEngine_trace_checksum_validation_succeeded
- XtpEngine_trace_ckpt_close_begin
- XtpEngine_trace_ckpt_close_signaled
- XtpEngine_trace_ckpt_closed
- XtpEngine_trace_ckpt_load_thread_stats
- XtpEngine_trace_ckpt_mrt_dump
- XtpEngine_trace_ckpt_serialization_state_dump
- XtpEngine_trace_ckpt_stream_io_stats
- XtpEngine_trace_close_file_log_rec_created
- XtpEngine_trace_data_file_pages_txs
- XtpEngine_trace_data_from_page_deserialized
- XtpEngine_trace_data_row_modified
- XtpEngine_trace_delete_cfd_rows_begin
- XtpEngine_trace_delete_DeletedObjectTable_row_begin
- XtpEngine_trace_delete_merged_source_file
- XtpEngine_trace_delete_mrtrow_for_target_begin
- XtpEngine_trace_delta_entry_skipped
- XtpEngine_trace_delta_file_future_count_rows_pages_update
- XtpEngine_trace_delta_file_lastgood_count_rows_pages_update
- XtpEngine_trace_delta_file_pages_txs
- XtpEngine_trace_delta_file_processed
- XtpEngine_trace_delta_file_set_clean_or_dirty
- XtpEngine_trace_delta_filter_removed
- XtpEngine_trace_delta_watermark_removed
- XtpEngine_trace_delta_watermark_updated
- XtpEngine_trace_deserialize_chained_records
- XtpEngine_trace_deserialize_data_from_page_begin
- XtpEngine_trace_deserialize_source_delta_file_begin
- XtpEngine_trace_dump_cfd_row
- XtpEngine_trace_dump_mrt_row
- XtpEngine_trace_file_close_skipped
- XtpEngine_trace_file_skipped
- XtpEngine_trace_file_skipped_with_tx_range
- XtpEngine_trace_first_page_header_flushed
- XtpEngine_trace_flush_io_operation_for_delta_file
- XtpEngine_trace_flush_skipped_for_closed_data_file
- XtpEngine_trace_future_count_pages_update
- XtpEngine_trace_initial_merge
- XtpEngine_trace_insert_DeletedObjectTable_row_begin
- XtpEngine_trace_intialize_worker_file_begin
- XtpEngine_trace_mark_data_file_closed
- XtpEngine_trace_merge_cancelled
- XtpEngine_trace_merge_complete_log_rec_ignored
- XtpEngine_trace_merge_complete_log_rec_processed
- XtpEngine_trace_mrt_dump
- XtpEngine_trace_mrt_row_inplace_update
- XtpEngine_trace_mrtrow_not_found
- XtpEngine_trace_new_delta_watermark_inserted
- XtpEngine_trace_offline_process_stale_merge_item
- XtpEngine_trace_pending_tx
- XtpEngine_trace_populate_storage_array
- XtpEngine_trace_post_writefile
- XtpEngine_trace_process_file_close_begin
- XtpEngine_trace_process_merge_complete_log_record
- XtpEngine_trace_process_merge_request
- XtpEngine_trace_process_source_file
- XtpEngine_trace_process_target_file
- XtpEngine_trace_queue_merge_work_item
- XtpEngine_trace_recovered_existing_file
- XtpEngine_trace_resyncing_state_of_file
- XtpEngine_trace_serialization_object_cleaned
- XtpEngine_trace_serialize_abort_transaction_begin
- XtpEngine_trace_serialize_tx_begin
- XtpEngine_trace_serialize_tx_end
- XtpEngine_trace_serialized_delta_rows_to_file
- XtpEngine_trace_source_file_load_stats
- XtpEngine_trace_storage_refreshed_for_file
- XtpEngine_trace_uninitialize_file
- XtpEngine_trace_wrote_delta_watermark
- XtpEngine_waiting_for_dependenciestx_event
- XtpEngine_xtp_before_create_log_record
- XtpEngine_xtp_checkpoint_file_flush
- XtpEngine_xtp_checkpoint_file_flush_complete
- XtpEngine_xtp_checkpoint_file_read
- XtpEngine_xtp_checkpoint_worker_active_log
- XtpEngine_xtp_checkpoint_worker_started
- XtpEngine_xtp_checkpoint_worker_stopped
- XtpEngine_xtp_checkpoint_write_io
- XtpEngine_xtp_complete_checkpoint
- XtpEngine_xtp_create_log_record
- XtpEngine_xtp_delta_marked_dirty
- XtpEngine_xtp_merge_complete_log_record
- XtpEngine_xtp_merge_log_complete_log_record
- XtpEngine_xtp_merge_process_log_record
- XtpEngine_xtp_merge_request_log_record
- XtpEngine_xtp_merge_request_started
- XtpEngine_xtp_root_deserialized
- XtpEngine_xtp_root_serialized
- XtpRuntime_bind_md
- XtpRuntime_bind_tables
- XtpRuntime_create_table
- XtpRuntime_deserialize_md
- XtpRuntime_load_dll
- XtpRuntime_recover_done
- XtpRuntime_recover_table
- XtpRuntime_serialize_md
- XtpRuntime_unload_dll
Esses eventos abrangem uma ampla variedade de cenários e fornecem informações valiosas para solução de problemas e análise de desempenho. Ao utilizar os Eventos Estendidos, desenvolvedores e administradores podem obter uma compreensão mais profunda do comportamento do SQL Server e otimizar suas aplicações de acordo.
Boa rastreabilidade!