Blog

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
↑ Back to top
  1. 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.

  2. 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??

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

      • 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??

        • 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.

          • 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

  3. 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

  4. There’s no x86 version? =/

  5. Thank you very much for this useful information

  6. Pingback: Something for the Weekend - SQL Server Links 28/06/13 • John Sansom

  7. 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.

    • 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.

  8. Pingback: Hekaton is Microsoft’s In-Memory OLTP solution coming to SQL Server 2014 | Scott Stauffer dot C-eh?

  9. 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.

  10. Pingback: SQL Server 2014 CTP1 Released - SQL Server - SQL Server - Toad World

  11. Pingback: [PASS SUMMIT 2013] Best Practices of Optimizing SQL Server with Solid State Disk | Le Post de MCNEXT

  12. 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 :-)

    • 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. ;-)

Leave a Reply

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

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>

css.php