What’s New in SQL Server 2019 System Tables

The free SQL Server 2019 preview download is out, and here are quite a few things to check out that aren’t documented yet:

New System Objects

Starting with new stored procedures:

  • sys.sp_add_feature_restriction
  • sys.sp_autoindex_cancel_dta
  • sys.sp_autoindex_invoke_dta
  • sys.sp_cloud_update_blob_tier
  • sys.sp_configure_automatic_tuning
  • sys.sp_diagnostic_showplan_log_dbid
  • sys.sp_drop_feature_restriction
  • sys.sp_execute_remote
  • sys.sp_force_slog_truncation
  • sys.sp_internal_alter_nt_job_limits
  • sys.sp_rbpex_exec_cmd
  • sys.sp_set_distributed_query_context
  • sys.sp_set_session_resource_group
  • sys.sp_showinitialmemo_xml
  • sys.sp_xa_commit
  • sys.sp_xa_end
  • sys.sp_xa_forget
  • sys.sp_xa_forget_ex
  • sys.sp_xa_init
  • sys.sp_xa_init_ex
  • sys.sp_xa_prepare
  • sys.sp_xa_prepare_ex
  • sys.sp_xa_recover
  • sys.sp_xa_rollback
  • sys.sp_xa_rollback_ex
  • sys.sp_xa_start
  • sys.xp_copy_file
  • sys.xp_copy_files
  • sys.xp_delete_files
  • sys.sp_change_repl_serverport
  • sys.sp_getdistributorplatform
  • sys.sp_MSget_server_portinfo
  • sys.sp_MSset_repl_serveroptions
  • sys.sp_persistent_version_cleanup
  • sys.sp_persistent_version_store
  • sys.sp_sqljdbc_xa_install
  • sys.sp_sqljdbc_xa_uninstall

New tables and views:

  • sys.dm_column_encryption_enclave
  • sys.dm_column_encryption_enclave_operation_stats
  • sys.dm_db_missing_index_group_stats_query
  • sys.dm_distributed_exchange_stats
  • sys.dm_hadr_ag_threads
  • sys.dm_hadr_db_threads
  • sys.dm_os_job_object
  • sys.dm_tran_aborted_transactions
  • sys.edge_constraint_clauses
  • sys.edge_constraints
  • sys.external_libraries_installed
  • sys.sensitivity_classifications
  • sys._trusted_assemblies
  • sys.persistent_version_store
  • sys.persistent_version_store_long_term
  • sys.tbl_server_resource_stats

New functions:

  • sys.dm_db_page_info
  • sys.fn_dbslog
  • sys.fn_getproviderstring

New Fields in Existing Objects

  • all_sql_modules – inline_type
  • all_sql_modules – is_inlineable
  • all_views – has_snapshot
  • availability_replicas – read_write_routing_url
  • column_master_keys – allow_enclave_computations
  • column_master_keys – signature
  • databases – catalog_collation_type
  • databases – catalog_collation_type_desc
  • databases – physical_database_name
  • dm_db_column_store_row_group_operational_stats – returned_row_count
  • dm_db_column_store_row_group_operational_stats – returned_aggregate_count
  • dm_db_column_store_row_group_operational_stats – returned_group_count
  • dm_db_index_operational_stats – version_generated_inrow
  • dm_db_index_operational_stats – version_generated_offrow
  • dm_db_index_operational_stats – ghost_version_inrow
  • dm_db_index_operational_stats – ghost_version_offrow
  • dm_db_index_operational_stats – insert_over_ghost_version_inrow
  • dm_db_index_operational_stats – insert_over_ghost_version_offrow
  • dm_db_index_physical_stats – version_record_count
  • dm_db_index_physical_stats – inrow_version_record_count
  • dm_db_index_physical_stats – inrow_diff_version_record_count
  • dm_db_index_physical_stats – total_inrow_version_payload_size_in_bytes
  • dm_db_index_physical_stats – offrow_regular_version_record_count
  • dm_db_index_physical_stats – offrow_long_term_version_record_count
  • dm_db_log_info – vlf_encryptor_thumbprint
  • dm_db_log_stats – log_state
  • dm_db_xtp_checkpoint_stats – tail_cache_max_page_count
  • dm_db_xtp_checkpoint_stats – tail_cache_min_needed_lsn
  • dm_db_xtp_checkpoint_stats – merge_outstanding_merges
  • dm_db_xtp_checkpoint_stats – merge_stats_number_of_merges
  • dm_db_xtp_checkpoint_stats – merge_stats_log_blocks_merged
  • dm_db_xtp_checkpoint_stats – merge_stats_bytes_merged
  • dm_db_xtp_checkpoint_stats – merge_stats_user_time
  • dm_db_xtp_checkpoint_stats – merge_stats_kernel_time
  • dm_db_xtp_checkpoint_stats – bytes_of_large_data_serialized
  • dm_exec_external_work – status
  • dm_exec_query_statistics_xml – statement_start_offset
  • dm_exec_query_statistics_xml – statement_end_offset
  • dm_exec_requests – page_resource
  • dm_hadr_availability_replica_states – current_configuration_commit_start_time_utc
  • dm_os_host_info – host_architecture
  • dm_os_memory_clerks – parent_memory_broker_type
  • dm_os_schedulers – ideal_workers_limit
  • dm_os_volume_stats – incurs_seek_penalty
  • dm_os_worker_local_storage – extensibility_ctxt_address
  • dm_resource_governor_workload_groups – request_max_memory_grant_percent_numeric
  • external_data_sources – connection_options
  • external_data_sources – pushdown
  • external_file_formats – first_row
  • external_library_setup_failures – error_timestamp
  • external_library_setup_failures – error_message
  • external_tables – rejected_row_location
  • fn_get_audit_file – connection_id
  • fn_get_audit_file – data_sensitivity_information
  • fn_get_audit_file – host_name
  • resource_governor_workload_groups – request_max_memory_grant_percent_numeric
  • server_event_sessions – has_long_running_target
  • server_file_audits – retention_days
  • sql_modules – inline_type
  • sql_modules – is_inlineable
  • stats – has_persisted_sample
  • stats – stats_generation_method
  • stats – stats_generation_method_desc
  • syscscolsegments – bloom_filter_md
  • syscscolsegments – bloom_filter_data_ptr
  • sysextfileformats – first_row
  • sysextfileformats – extractor
  • sysextfileformats – null_values
  • sysextsources – connection_options
  • sysextsources – pushdown
  • sysexttables – rejected_row_location
  • sysprocesses – page_resource
  • system_sql_modules – inline_type
  • system_sql_modules – is_inlineable
  • system_views – has_snapshot
  • views – has_snapshot

What’s New in sp_Configure Options

3 new options show up in sp_configure and sys.configurations:

“allow filesystem enumeration” – defaults to 1, can be 0 or 1. If you’re trying to host a secure SQL Server and you don’t want people looking at the file system, like say you’re running Azure SQL DB, you might want to disable folks from browsing the file system. This would be useful for other hosting providers, too.

“column encryption enclave type” – default 0, can be 0 or 1.

“polybase enabled” – default 0, can be 0 or 1. Polybase helps you get your Hadoop on, and is part of the new Big Data Cluster stuff.

What’s New in Perfmon Counters

  • SQLServer:Availability Group – Active Hadr Threads – worker thread exhaustion has been a painful issue when managing hundreds of databases across multiple Availability Group replicas, and this is a step in the right direction for preventative monitoring
  • SQLServer:Databases – Active parallel redo threads – similarly, this feature came out in 2016 to fix a single-threaded problem with AGs, but it didn’t always run faster.
  • SQLServer:Databases PVS in-row diff generated/sec – PVS is the new Permanent Version Store
  • SQLServer:Databases PVS in-row diff retrieved/sec
  • SQLServer:Databases PVS off-row pages allocated/sec
  • SQLServer:Databases PVS off-row pages deleted/sec
  • SQLServer:Databases PVS off-row record generated/sec
  • SQLServer:Databases PVS off-row record retrieved/sec
  • SQLServer:Databases WPR add lsn bucket miss – WPR is the new Write Page Recorder, mentioned in the new sys.messages
  • SQLServer:Databases WPR bucket swaps
  • SQLServer:Databases WPR stale check bucket miss
  • SQLServer:External Scripts Partition By Executions

As always with new versions, some features may not be turned on yet, may require a trace flag, and may be wildly unreliable. But hey, that’s the fun, right? Hold my beer.

Previous Post
What’s New in SQL Server 2019’s sys.messages: More Unannounced Features
Next Post
When You Need to Tune A View, Don’t Just Get Its Plan

6 Comments. Leave new

  • I’ve noticed that the value and value_in_use differ for allow filesystem enumeration differs on my 2019 test VM. I’m not sure if this is intentional and set after the instance has started but it could cause some issues as I don’t believe any of the other settings behave this way.

  • You really do dig deep don’t you!

  • Hi Brent, I noticed a new(?) extended stored proc with the name: ‘sys.xp_delete_files’. Do you know if this xp replaces the old ‘sys.xp_delete_file’ (without the trailing ‘s’)?
    And if it does replace the old xp, did MS change the code? or just added the obvious ‘s’ in the name?
    And if they changed the code, will it now delete files with exts other than ‘BAK’ or ‘TRN’?
    So many questions, sorry bruh 🙂
    I haven’t checked out SS2019 yet and that’s not going to happen very soon, but would you be willing to test this ‘new’ xp please? I’m pretty sure I made you curious too! Thanks! :p

  • No problem, Brent, I will test this one once I’ve installed SS2019 myself, I was just curious and sharing thoughts as a collegue dba, not trying to get a free consult. 😉

Menu
{"cart_token":"","hash":"","cart_data":""}