When 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
27 Comments. Leave new
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.
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
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
There’s no x86 version? =/
No, time to stop running SQL Server on that Asus netbook of yours.
haha, just for testing =P we dont have test servers here =( just asked windows team if they have a spare 1 to give me~ home pc & work laptop both on win7 x86
What?
I cannot run my cluster with openfiler on an eeepc anymore? 🙁
Thank you very much for this useful information
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.
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.
Hi! Short story: leave those alone for now and you don’t need to focus on them. CTPs sometimes have temporary trace flags to duct tarp things together.
okie dokie…Thanks Brent!!
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. 😉