New DMVs in SQL Server 2014 CTP1

SQL-Server-2014-CTP1When you download and start playing with SQL Server 2014 CTP1, here’s a few of the new instrumentation tables and views:

New Internal Tables

  • plan_persist_context_settings
  • plan_persist_plan – Hey, lookie there! Implies that we can persist execution plans beyond a SQL Server service restart.
  • plan_persist_query
  • plan_persist_query_text
  • plan_persist_runtime_stats
  • plan_persist_runtime_stats_interval

New Views

  • column_store_row_groups
  • dm_db_merge_requests
  • dm_db_xtp_checkpoint – Note that both the DMVs and the SSMS UI imply that Hekaton will be called Extreme Transaction Processing.
  • dm_db_xtp_checkpoint_files
  • dm_db_xtp_gc_cycle_stats
  • dm_db_xtp_hash_index_stats
  • dm_db_xtp_index_stats
  • dm_db_xtp_memory_consumers
  • dm_db_xtp_object_stats
  • dm_db_xtp_table_memory_stats
  • dm_db_xtp_transactions
  • dm_io_cluster_shared_volumes – Management data about the new Cluster Shared Volumes (CSV) support for clusters.
  • dm_os_buffer_pool_extension_configuration – Management data about buffer pool extensions, storing the buffer pool on SSD. You can read more about that in my Almost Everything You Need to Know About SQL Server 2014 post.
  • dm_resource_governor_resource_pool_volumes
  • dm_xe_database_session_event_actions
  • dm_xe_database_session_events
  • dm_xe_database_session_object_columns
  • dm_xe_database_session_targets
  • dm_xe_database_sessions
  • dm_xtp_consumer_memory_usage
  • dm_xtp_gc_queue_stats
  • dm_xtp_gc_stats
  • dm_xtp_memory_stats
  • dm_xtp_system_memory_consumers
  • dm_xtp_threads
  • dm_xtp_transaction_recent_rows
  • dm_xtp_transaction_stats
  • event_session_actions
  • event_session_events
  • event_session_fields
  • event_session_targets
  • event_sessions
  • hash_indexes
  • selective_xml_index_namespaces
  • selective_xml_index_paths

New Functions

  • fn_dblog_xtp
  • fn_dump_dblog_xtp
  • fn_hadr_is_primary_replica – Makes it much easier to identify when you’re running T-SQL on the primary replica in an AlwaysOn Availability Group. Right now I have to jump through some hoops to do this, so yay!

NEW FUNCTIONS IN USER DATABASES/MSDB

  • fn_sysdac_get_currentusername
  • fn_sysdac_get_username
  • fn_sysdac_is_currentuser_sa
  • fn_sysdac_is_login_creator

New Stored Procedures IN MASTER

  • sp_cci_tuple_mover
  • sp_db_ebcdic277_2
  • sp_db_enable_clustered_columnstores
  • sp_db_selective_xml_index
  • sp_MSgetgenstatus4rows
  • sp_xtp_merge_checkpoint_files
  • sp_set_cardinality_estimation_model_110 – This one’s only in the user databases, not master. It would imply that there’s a new cardinality estimation model. (Never exactly clear on what I’m allowed to say due to NDAs, so…I’ll just leave that there.

New Fields in Existing Views

  • all_parameters – new field: is_nullable
  • all_sql_modules – new field: uses_native_compilation
  • availability_databases_cluster – new field: truncation_lsn
  • databases – new field: containment_desc
  • databases – new field: target_recovery_time_in_seconds
  • dm_exec_query_stats – new field: statement_sql_handle
  • dm_exec_query_stats – new field: statement_context_id
  • dm_exec_requests – new field: statement_sql_handle
  • dm_exec_requests – new field: statement_context_id
  • dm_hadr_database_replica_states – new field: low_water_mark_for_ghosts
  • dm_io_pending_io_requests – new field: io_handle_path
  • dm_logpool_stats – new field: total_pages
  • dm_logpool_stats – new field: private_pages
  • dm_os_buffer_descriptors – new field: is_in_bpool_extension
  • dm_os_memory_cache_entries – new field: time_to_generate
  • dm_os_memory_cache_entries – new field: use_count
  • dm_os_memory_cache_entries – new field: average_time_between_uses
  • dm_os_memory_cache_entries – new field: time_since_last_use
  • dm_os_memory_cache_entries – new field: probability_of_reuse
  • dm_os_memory_cache_entries – new field: value
  • dm_os_worker_local_storage – new field: filestream_address
  • dm_os_worker_local_storage – new field: qe_cc_address
  • dm_os_worker_local_storage – new field: xtp_address
  • dm_resource_governor_configuration – new field: max_outstanding_io_per_volume
  • dm_resource_governor_resource_pools – new field: min_iops_per_volume
  • dm_resource_governor_resource_pools – new field: max_iops_per_volume
  • dm_resource_governor_resource_pools – new field: read_io_queued_total
  • dm_resource_governor_resource_pools – new field: read_io_issued_total
  • dm_resource_governor_resource_pools – new field: read_io_completed_total
  • dm_resource_governor_resource_pools – new field: read_io_throttled_total
  • dm_resource_governor_resource_pools – new field: read_bytes_total
  • dm_resource_governor_resource_pools – new field: read_io_stall_total_ms
  • dm_resource_governor_resource_pools – new field: read_io_stall_queued_ms
  • dm_resource_governor_resource_pools – new field: write_io_queued_total
  • dm_resource_governor_resource_pools – new field: write_io_issued_total
  • dm_resource_governor_resource_pools – new field: write_io_completed_total
  • dm_resource_governor_resource_pools – new field: write_io_throttled_total
  • dm_resource_governor_resource_pools – new field: write_bytes_total
  • dm_resource_governor_resource_pools – new field: write_io_stall_total_ms
  • dm_resource_governor_resource_pools – new field: write_io_stall_queued_ms
  • dm_resource_governor_resource_pools – new field: io_issue_violations_total
  • dm_resource_governor_resource_pools – new field: io_issue_delay_total_ms
  • dm_xe_sessions – new field: session_source
  • master_files – new field: credential_id
  • parameters – new field: is_nullable
  • resource_governor_resource_pools – new field: min_iops_per_volume
  • resource_governor_resource_pools – new field: max_iops_per_volume
  • sql_modules – new field: uses_native_compilation
  • system_parameters – new field: is_nullable
  • system_sql_modules – new field: uses_native_compilation
  • table_types – new field: is_memory_optimized
  • tables – new field: is_memory_optimized
  • tables – new field: durability
  • tables – new field: durability_desc
  • xml_indexes – new field: xml_index_type
  • xml_indexes – new field: xml_index_type_description
  • xml_indexes – new field: path_id

New Fields in Existing Functions

  • dm_exec_cursors – new field: statement_sql_handle
  • dm_exec_cursors – new field: statement_context_id
  • dm_logpool_consumers – new field: log_consumer_ref_counter
  • fn_dblog – new field: Log Record
  • fn_dump_dblog – new field: Log Record
  • fn_get_audit_file – new field: audit_schema_version
  • fn_get_audit_file – new field: sequence_group_id
Previous Post
Optimize for… Mediocre?
Next Post
The Elephant and the Mouse, or, Parameter Sniffing in SQL Server

27 Comments. Leave new

  • Aaron Morelli
    June 25, 2013 12:43 pm

    I remember when I first moved over to SQL Server from DB2 z/OS in 2007 (and still a rookie DBA) being really surprised and confused that SQL never persisted query plans. “Wait, isn’t that going to lead to crazy plan instability?” It felt like a really bad choice to me, bordering on arrogance re: the strength of their optimizer.

    Over time I began to see that Mainframe DB2 and SQL Server just grew up under really different paradigms, and what was logical for one platform wasn’t so intuitive/necessary/logical for the other.

    Reply
  • Hy Brent,

    Can SQL2014CTP1 be installed on machine having other SQL instances?? I find it hard to believe but when I tired to install, it fails with the message “A SQL product other than SQL 2014 CTP1 is found. You cannot install this release without uninstalling the existing versions of SQL.” . Looks like I have to uninstall all previous versions unless I start a new VM and install it or is there any work around??

    Reply
    • Sapyam – correct, CTP1 needs to be installed on a fresh VM.

      Reply
      • Thanks Brent!! Any thoughts on why this is a requirement. Obviously, it cannot be there in RTM versions. Did SQL 2012 CTP had the same requirement??

        Reply
        • No, sorry, no idea. Microsoft probably wants to get previews out as quickly as possible, and they probably wanted to ship the preview rather than wait longer to do troubleshooting. Any CTP should be on a blank VM anyway in my opinion.

          Reply
          • Thanks Brent!!

          • Actually the idea is not to break your prior installs since CTPs are not that regressively tested. Hence the limitation to be on a safer side. There is a hack which I am gonna blog about at sqlactions.com

  • ishtiaq ahmed
    June 25, 2013 5:31 pm

    Hello sapyam

    Today i installed SQL Server 2014 CTP on a fresh VM.

    http://dbaishtiaq.wordpress.com/2013/06/25/sql-server-2014-ctp-1-installation/

    Ishtiaq

    Reply
  • There’s no x86 version? =/

    Reply
  • Thank you very much for this useful information

    Reply
  • Hi Brent,

    I am newbie to SQL Server. All this DMV’s and system tables are tough to remember(even what do they do) because they are to many.

    Is there a trick you have to remember this or it is one of those thing that comes by experience.

    Reply
    • Hi! I don’t even try to remember ’em – I use IntelliSense, which automatically completes the names and whatnot for me. I love how SQL Server 2012 searches in the names, not just the beginning parts.

      Reply
  • Hy brent, I see that trace flag 9806,9807,9808 were enabled in SQL 2014 for default installation. any idea on what they are?? Thanks.

    Reply
  • CTP2 can be installed on machine having other SQL instances, if anyone need it (work for me with 2012 & 2008r2 on the same machine).
    * by the way, Great summary of what’s new, I liked this as always i like your posts 🙂
    Now we need to start writing a new blog on each of those 🙂

    Reply
    • Thanks! Just FYI, I would never install preview code side-by-side on my production desktop. I’d highly recommend using a virtual machine. Just because something works and installs once doesn’t mean it’s bug-free. 😉

      Reply

Leave a Reply

Your email address will not be published. Required fields are marked *

Fill out this field
Fill out this field
Please enter a valid email address.

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