List of DMVs & DMFs available in SQL Server 2005

I’ve listed down all the DMV’s that are related to SQL server 2005

Server-Scoped:

CLR related dmvs
Sys.dm_clr_appdomains
Sys.dm_clr_loaded_assemblies
Sys.dm_clr_properties
Sys.dm_clr_tasks

Database related dmvs
Sys.dm_db_file_space_usage
Sys.dm_db_session_space_usage

Database Mirroring related dmvs
Sys.dm_db_mirroring_connections

Execution related DMVs and functions
Sys.dm_exec_background_job_queue
Sys.dm_exec_background_job_queue_stats
Sys.dm_exec_cached_plans
Sys.dm_exec_connections
Sys.dm_exec_cursors
Sys.dm_exec_plan_attributes
Sys.dm_exec_query_memory_grants
Sys.dm_exec_query_optimizer_info
Sys.dm_exec_query_plan
Sys.dm_exec_query_resource_semaphores
Sys.dm_exec_query_stats
Sys.dm_exec_requests
Sys.dm_exec_sessions
Sys.dm_exec_sql_text

Full Text Search Related DMVs
Sys.dm_fts_active_catalogs
Sys.dm_fts_index_population
Sys.dm_fts_memory_buffers
Sys.dm_fts_memory_pools
Sys.dm_fts_population_ranges

InputOutput Related DMVs and Functions
Sys.dm_io_backup_tapes
Sys.dm_io_cluster_shared_drives
Sys.dm_io_pending_io_requests
Sys.dm_io_virtual_file_stats

Index Related DMVs
Sys.dm_db_index_operational_stats
Sys.dm_db_index_physical_stats
Sys.dm_db_index_usage_stats
Sys.dm_db_missing_index_columns
Sys.dm_db_missing_index_details
Sys.dm_db_missing_index_group_stats
Sys.dm_db_missing_index_groups

Query Notifications Related DMVs
Sys.dm_qn_subscriptions

Service Broker Related DMVs
Sys.dm_broker_activated_tasks
Sys.dm_broker_connections
Sys.dm_broker_forwarded_messages
Sys.dm_broker_queue_monitors

SQL Server Operating System Related DMVs
Sys.dm_os_buffer_descriptors
Sys.dm_os_child_instances
Sys.dm_os_cluster_nodes
Sys.dm_os_hosts
Sys.dm_os_latch_stats
Sys.dm_os_loaded_modules
Sys.dm_os_memory_cache_clock_hands
Sys.dm_os_memory_cache_counters
Sys.dm_os_memory_cache_entries
Sys.dm_os_memory_cache_hash_tables
Sys.dm_os_memory_clerks
Sys.dm_os_memory_objects
Sys.dm_os_memory_pools
Sys.dm_os_performance_counters
Sys.dm_os_schedulers
sys.dm_os_stacks
sys.dm_os_sys_info
sys.dm_os_tasks
sys.dm_os_threads
sys.dm_os_virtual_address_dump
sys.dm_os_wait_stats
sys.dm_os_waiting_tasks
sys.dm_os_workers

Transaction Related DMVs & Functions
sys.dm_tran_active_snapshot_database_transactions
sys.dm_tran_active_transactions
sys.dm_tran_current_snapshot
sys.dm_tran_current_transaction
sys.dm_tran_database_transactions
sys.dm_tran_locks
sys.dm_tran_session_transactions
sys.dm_tran_top_version_generators
sys.dm_tran_transactions_snapshot
sys.dm_tran_version_store

Database scoped

Database related dmvs
Sys.dm_db_partition_stats

Replication Related DMVs
Sys.dm_repl_articles
Sys.dm_repl_schemas
Sys.dm_repl_tranhash
Sys.dm_repl_traninfo

Leave a Reply

Leave a comment

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

*


x

Related Posts

sys.dm_db_stats_properties DMV
Service pack 1 for SQL Server 2012 has been released on 9th Nov 2012. It took couple of weeks for me to install it in our servers. Before I settl...
New DMV's in SQL Server 2011 aka Denali
Denali CTP3 is already released and we have new DMV's accompanied with this version which will be very useful for us. I'm going to cover the DMV ...
Database Management Views & Functions - SQL 2005
DMV's are system views and its a new feature started SQL server 2005 with nearly 80 DMVs. DMV's are system view that surface the internal counter...
powered by RelatedPosts