Dynamic Management Views(DMVs) are system views that surface the internal counters of the database engine and help present an easily comprehensible dashboard of the database engine performance that can be used to monitor the health of a server instance, diagnose problems, and tune performance.
List of DMV in SQL Server 2005
======================================================
1. sys.dm_broker_activated_tasks
2. sys.dm_broker_connections
3. sys.dm_broker_forwarded_messages
4. sys.dm_broker_queue_monitors
5. sys.dm_clr_appdomains
6. sys.dm_clr_loaded_assemblies
7. sys.dm_clr_properties
8. sys.dm_clr_tasks
9. sys.dm_db_file_space_usage
10. sys.dm_db_index_usage_stats
11. sys.dm_db_mirroring_connections
12. sys.dm_db_missing_index_details
13. sys.dm_db_missing_index_group_stats
14. sys.dm_db_missing_index_groups
15. sys.dm_db_partition_stats
16. sys.dm_db_session_space_usage
17. sys.dm_db_task_space_usage
18. sys.dm_exec_background_job_queue
19. sys.dm_exec_background_job_queue_stats
20. sys.dm_exec_cached_plans
21. sys.dm_exec_connections
22. sys.dm_exec_query_memory_grants
23. sys.dm_exec_query_optimizer_info
24. sys.dm_exec_query_resource_semaphores
25. sys.dm_exec_query_stats
26. sys.dm_exec_query_transformation_stats
27. sys.dm_exec_requests
28. sys.dm_exec_sessions
29. sys.dm_fts_active_catalogs
30. sys.dm_fts_index_population
31. sys.dm_fts_memory_buffers
32. sys.dm_fts_memory_pools
33. sys.dm_fts_population_ranges
34. sys.dm_io_backup_tapes
35. sys.dm_io_cluster_shared_drives
36. sys.dm_io_pending_io_requests
37. sys.dm_os_buffer_descriptors
38. sys.dm_os_child_instances
39. sys.dm_os_cluster_nodes
40. sys.dm_os_hosts
41. sys.dm_os_latch_stats
42. sys.dm_os_loaded_modules
43. sys.dm_os_memory_allocations
44. sys.dm_os_memory_cache_clock_hands
45. sys.dm_os_memory_cache_counters
46. sys.dm_os_memory_cache_entries
47. sys.dm_os_memory_cache_hash_tables
48. sys.dm_os_memory_clerks
49. sys.dm_os_memory_objects
50. sys.dm_os_memory_pools
51. sys.dm_os_performance_counters
52. sys.dm_os_ring_buffers
53. sys.dm_os_schedulers
54. sys.dm_os_stacks
55. sys.dm_os_sublatches
56. sys.dm_os_sys_info
57. sys.dm_os_tasks
58. sys.dm_os_threads
59. sys.dm_os_virtual_address_dump
60. sys.dm_os_wait_stats
61. sys.dm_os_waiting_tasks
62. sys.dm_os_worker_local_storage
63. sys.dm_os_workers
64. sys.dm_qn_subscriptions
65. sys.dm_repl_articles
66. sys.dm_repl_schemas
67. sys.dm_repl_tranhash
68. sys.dm_repl_traninfo
69. sys.dm_tran_active_snapshot_database_transactions
70. sys.dm_tran_active_transactions
71. sys.dm_tran_current_snapshot
72. sys.dm_tran_current_transaction
73. sys.dm_tran_database_transactions
74. sys.dm_tran_tran_locks
75. sys.dm_tran_session_transactions
76. sys.dm_tran_top_version_generators
77. sys.dm_tran_transactions_snapshot
78. sys.dm_tran_version_store
New DMV in SQL Server 2008
======================================================
1. sys.dm_audit_actions
2. sys.dm_audit_class_type_map
3. sys.dm_cdc_errors
4. sys.dm_cdc_log_scan_sessions
5. sys.dm_cryptographic_provider_properties
6. sys.dm_database_encryption_keys
7. sys.dm_db_mirroring_auto_page_repair
8. sys.dm_db_mirroring_past_actions
9. sys.dm_db_persisted_sku_features
10. sys.dm_db_script_level
11. sys.dm_exec_procedure_stats
12. sys.dm_exec_trigger_stats
13. sys.dm_filestream_file_io_handles
14. sys.dm_filestream_file_io_requests
15. sys.dm_fts_fdhosts
16. sys.dm_fts_outstanding_batches
17. sys.dm_os_dispatcher_pools
18. sys.dm_os_dispatchers
19. sys.dm_os_memory_brokers
20. sys.dm_os_memory_node_access_stats
21. sys.dm_os_memory_nodes
22. sys.dm_os_nodes
23. sys.dm_os_process_memory
24. sys.dm_os_spinlock_stacks
25. sys.dm_os_sys_memory
26. sys.dm_resource_governor_configuration
27. sys.dm_resource_governor_resource_pools
28. sys.dm_resource_governor_workload_groups
29. sys.dm_server_audit_status
30. sys.dm_tran_commit_table
31. sys.dm_xe_map_values
32. sys.dm_xe_object_columns
33. sys.dm_xe_objects
34. sys.dm_xe_packages
35. sys.dm_xe_session_event_actions
36. sys.dm_xe_session_events
37. sys.dm_xe_session_object_columns
38. sys.dm_xe_session_targets
39. sys.dm_xe_sessions
List of DMV in SQL Server 2005
======================================================
1. sys.dm_broker_activated_tasks
2. sys.dm_broker_connections
3. sys.dm_broker_forwarded_messages
4. sys.dm_broker_queue_monitors
5. sys.dm_clr_appdomains
6. sys.dm_clr_loaded_assemblies
7. sys.dm_clr_properties
8. sys.dm_clr_tasks
9. sys.dm_db_file_space_usage
10. sys.dm_db_index_usage_stats
11. sys.dm_db_mirroring_connections
12. sys.dm_db_missing_index_details
13. sys.dm_db_missing_index_group_stats
14. sys.dm_db_missing_index_groups
15. sys.dm_db_partition_stats
16. sys.dm_db_session_space_usage
17. sys.dm_db_task_space_usage
18. sys.dm_exec_background_job_queue
19. sys.dm_exec_background_job_queue_stats
20. sys.dm_exec_cached_plans
21. sys.dm_exec_connections
22. sys.dm_exec_query_memory_grants
23. sys.dm_exec_query_optimizer_info
24. sys.dm_exec_query_resource_semaphores
25. sys.dm_exec_query_stats
26. sys.dm_exec_query_transformation_stats
27. sys.dm_exec_requests
28. sys.dm_exec_sessions
29. sys.dm_fts_active_catalogs
30. sys.dm_fts_index_population
31. sys.dm_fts_memory_buffers
32. sys.dm_fts_memory_pools
33. sys.dm_fts_population_ranges
34. sys.dm_io_backup_tapes
35. sys.dm_io_cluster_shared_drives
36. sys.dm_io_pending_io_requests
37. sys.dm_os_buffer_descriptors
38. sys.dm_os_child_instances
39. sys.dm_os_cluster_nodes
40. sys.dm_os_hosts
41. sys.dm_os_latch_stats
42. sys.dm_os_loaded_modules
43. sys.dm_os_memory_allocations
44. sys.dm_os_memory_cache_clock_hands
45. sys.dm_os_memory_cache_counters
46. sys.dm_os_memory_cache_entries
47. sys.dm_os_memory_cache_hash_tables
48. sys.dm_os_memory_clerks
49. sys.dm_os_memory_objects
50. sys.dm_os_memory_pools
51. sys.dm_os_performance_counters
52. sys.dm_os_ring_buffers
53. sys.dm_os_schedulers
54. sys.dm_os_stacks
55. sys.dm_os_sublatches
56. sys.dm_os_sys_info
57. sys.dm_os_tasks
58. sys.dm_os_threads
59. sys.dm_os_virtual_address_dump
60. sys.dm_os_wait_stats
61. sys.dm_os_waiting_tasks
62. sys.dm_os_worker_local_storage
63. sys.dm_os_workers
64. sys.dm_qn_subscriptions
65. sys.dm_repl_articles
66. sys.dm_repl_schemas
67. sys.dm_repl_tranhash
68. sys.dm_repl_traninfo
69. sys.dm_tran_active_snapshot_database_transactions
70. sys.dm_tran_active_transactions
71. sys.dm_tran_current_snapshot
72. sys.dm_tran_current_transaction
73. sys.dm_tran_database_transactions
74. sys.dm_tran_tran_locks
75. sys.dm_tran_session_transactions
76. sys.dm_tran_top_version_generators
77. sys.dm_tran_transactions_snapshot
78. sys.dm_tran_version_store
New DMV in SQL Server 2008
======================================================
1. sys.dm_audit_actions
2. sys.dm_audit_class_type_map
3. sys.dm_cdc_errors
4. sys.dm_cdc_log_scan_sessions
5. sys.dm_cryptographic_provider_properties
6. sys.dm_database_encryption_keys
7. sys.dm_db_mirroring_auto_page_repair
8. sys.dm_db_mirroring_past_actions
9. sys.dm_db_persisted_sku_features
10. sys.dm_db_script_level
11. sys.dm_exec_procedure_stats
12. sys.dm_exec_trigger_stats
13. sys.dm_filestream_file_io_handles
14. sys.dm_filestream_file_io_requests
15. sys.dm_fts_fdhosts
16. sys.dm_fts_outstanding_batches
17. sys.dm_os_dispatcher_pools
18. sys.dm_os_dispatchers
19. sys.dm_os_memory_brokers
20. sys.dm_os_memory_node_access_stats
21. sys.dm_os_memory_nodes
22. sys.dm_os_nodes
23. sys.dm_os_process_memory
24. sys.dm_os_spinlock_stacks
25. sys.dm_os_sys_memory
26. sys.dm_resource_governor_configuration
27. sys.dm_resource_governor_resource_pools
28. sys.dm_resource_governor_workload_groups
29. sys.dm_server_audit_status
30. sys.dm_tran_commit_table
31. sys.dm_xe_map_values
32. sys.dm_xe_object_columns
33. sys.dm_xe_objects
34. sys.dm_xe_packages
35. sys.dm_xe_session_event_actions
36. sys.dm_xe_session_events
37. sys.dm_xe_session_object_columns
38. sys.dm_xe_session_targets
39. sys.dm_xe_sessions
No comments:
Post a Comment