|
|
|
|
I was just poking around in SQL Server 2008 and wanted to know if there were any new Dynamic Management Views. There are a total of 33 new Dynamic Management Views. The list of them is below, I think their names make clear what the dmv is supposed to do (or at least I think I know)
dm_cdc_errors
dm_cdc_log_scan_sessions
dm_cryptographic_provider_algorithms
dm_cryptographic_provider_keys
dm_cryptographic_provider_properties
dm_cryptographic_provider_sessions
dm_database_encryption_keys
dm_db_mirroring_auto_page_repair
dm_db_mirroring_past_actions
dm_filestream_oob_handles
dm_filestream_oob_requests
dm_os_dispatcher_pools
dm_os_memory_brokers
dm_os_memory_nodes
dm_os_nodes
dm_os_process_memory
dm_os_spinlock_stats
dm_os_sys_memory
dm_resource_governor_configuration
dm_resource_governor_resource_pools
dm_resource_governor_workload_groups
dm_sql_referenced_entities
dm_sql_referencing_entities
dm_tran_commit_table
dm_xe_map_values
dm_xe_object_columns
dm_xe_objects
dm_xe_packages
dm_xe_session_event_actions
dm_xe_session_events
dm_xe_session_object_columns
dm_xe_session_targets
dm_xe_sessions
If you want to find out what the names of all the new DMVs are by running a query on your SQL Server 2008 box and you don't have a linked server to a SQL Server 2005 box then don't worry I have prepared the query below for you. SELECT * FROM sysobjects
WHERE name LIKE 'dm_%'
AND name NOT IN('dm_os_hosts',
'dm_exec_cached_plan_dependent_objects',
'dm_os_memory_allocations',
'dm_os_loaded_modules',
'dm_db_task_space_usage',
'dm_os_memory_objects',
'dm_os_schedulers',
'dm_os_threads',
'dm_exec_requests',
'dm_db_missing_index_columns',
'dm_repl_tranhash',
'dm_qn_subscriptions',
'dm_db_session_space_usage',
'dm_exec_query_optimizer_info',
'dm_tran_top_version_generators',
'dm_os_waiting_tasks',
'dm_exec_background_job_queue',
'dm_exec_cursors',
'dm_db_missing_index_details',
'dm_clr_properties',
'dm_os_sublatches',
'dm_exec_query_memory_grants',
'dm_tran_current_snapshot',
'dm_io_virtual_file_stats',
'dm_exec_xml_handles',
'dm_os_wait_stats',
'dm_exec_text_query_plan',
'dm_broker_connections',
'dm_os_stacks',
'dm_os_ring_buffers',
'dm_db_missing_index_groups',
'dm_exec_cached_plans',
'dm_exec_sessions',
'dm_broker_forwarded_messages',
'dm_os_memory_clerks',
'dm_repl_articles',
'dm_fts_memory_buffers',
'dm_fts_index_population',
'dm_tran_current_transaction',
'dm_os_child_instances',
'dm_exec_connections',
'dm_exec_background_job_queue_stats',
'dm_fts_active_catalogs',
'dm_tran_database_transactions',
'dm_os_memory_cache_clock_hands',
'dm_repl_schemas',
'dm_db_mirroring_connections',
'dm_db_index_operational_stats',
'dm_db_partition_stats',
'dm_io_pending_io_requests',
'dm_os_memory_cache_entries',
'dm_os_virtual_address_dump',
'dm_tran_transactions_snapshot',
'dm_exec_query_plan',
'dm_os_memory_cache_hash_tables',
'dm_exec_query_stats',
'dm_clr_tasks',
'dm_os_worker_local_storage',
'dm_db_index_usage_stats',
'dm_db_index_physical_stats',
'dm_os_buffer_descriptors',
'dm_tran_active_snapshot_database_transactions',
'dm_tran_active_transactions',
'dm_db_file_space_usage',
'dm_broker_activated_tasks',
'dm_broker_queue_monitors',
'dm_os_memory_cache_counters',
'dm_tran_session_transactions',
'dm_clr_appdomains',
'dm_exec_sql_text',
'dm_os_memory_pools',
'dm_os_latch_stats',
'dm_io_backup_tapes',
'dm_fts_memory_pools',
'dm_os_sys_info',
'dm_tran_locks',
'dm_exec_query_transformation_stats',
'dm_exec_query_resource_semaphores',
'dm_repl_traninfo',
'dm_db_missing_index_group_stats',
'dm_fts_population_ranges',
'dm_os_performance_counters',
'dm_os_workers',
'dm_io_cluster_shared_drives',
'dm_os_tasks',
'dm_exec_plan_attributes',
'dm_tran_version_store',
'dm_os_cluster_nodes',
'dm_clr_loaded_assemblies')
Enjoy exploring these DMVs
I also did a reverse query and all the DMVs that were in SQL Server 2005 are also in SQL Server 2008, none were dropped
I wonder if they will add more DMVs once the product is feature complete?
I also wonder if the good doctor Louis will include these in his DMV book?
Comment Notification
If you would like to receive an email when updates are made to this post, please register here
Subscribe to this post's comments using
About Denis Gobo
I was born in Croatia in 1970, when I was one I moved to Amsterdam (and yes Ajax is THE team in Holland) and finally in 1993 I came to the US. I have lived in New York City for a bunch of years and currently live in Princeton, New Jersey with my wife and 3 kids. I work for Dow Jones as a Database architect in the indexes department, one drawback: since our data goes back all the way to May 1896 I cannot use smalldates ;-(
I have been working with SQL server since version 6.5 and compared to all the other bloggers here I am a n00b.
Some of you might know me from http://sqlservercode.blogspot.com/ or even from some of the newsgroups where I go by the name Denis the SQL Menace
If you are a Tek-Tips user then you might know me by the name SQLDenis, I am one of the guys answering SQL Questions in the SQL Programming forum.
|
|
|
|
|