Troubleshooting Bottlenecks Using Dynamic Memory Views : Part III

1. Windows System Monitor – PART I
2. DBCC commands – PART II
3. Dynamic Memory Views – PART III
4. SQL Server Profiler – – PART IV

There are 2 types of dynamic views, System and Database views.

System views are located at System Databases -> Master -> Views -> DM_OS_XXXX

sys.dm_os_buffer_descriptors
sys.dm_os_memory_pools
sys.dm_os_child_instances
sys.dm_os_nodes
sys.dm_os_cluster_nodes
sys.dm_os_performance_counters
sys.dm_os_dispatcher_pools
sys.dm_os_process_memory
sys.dm_os_hosts
sys.dm_os_schedulers
sys.dm_os_latch_stats
sys.dm_os_stacks
sys.dm_os_loaded_modules
sys.dm_os_sys_info
sys.dm_os_memory_brokers
sys.dm_os_sys_memory
sys.dm_os_memory_cache_clock_hands
sys.dm_os_tasks
sys.dm_os_memory_cache_counters
sys.dm_os_threads
sys.dm_os_memory_cache_entries
sys.dm_os_virtual_address_dump
sys.dm_os_memory_cache_hash_tables
sys.dm_os_wait_stats
sys.dm_os_memory_clerks
sys.dm_os_waiting_tasks
sys.dm_os_memory_nodes
sys.dm_os_workers
sys.dm_os_memory_objects

Database views are located at User Database ->views -> DM_DB_XXX , DM_Exec_XXX etc

sys.dm_exec_background_job_queue
sys.dm_exec_query_optimizer_info
sys.dm_exec_background_job_queue
sys.dm_exec_query_plan
sys.dm_exec_background_job_queue_stats
sys.dm_exec_query_resource_semaphores
sys.dm_exec_cached_plans
sys.dm_exec_query_stats
sys.dm_exec_cached_plan_dependent_objects
sys.dm_exec_requests
sys.dm_exec_connections
sys.dm_exec_sessions
sys.dm_exec_cursors
sys.dm_exec_sql_text
sys.dm_exec_plan_attributes
sys.dm_exec_text_query_plan
sys.dm_exec_procedure_stats
sys.dm_exec_trigger_stats
sys.dm_exec_query_memory_grants
sys.dm_exec_xml_handles

sys.dm_db_file_space_usage
sys.dm_db_session_space_usage
sys.dm_db_partition_stats
sys.dm_db_task_space_usage
sys.dm_db_persisted_sku_features