DMVS USED IN SQL SERVER 2005/2008/R2/ 2012

The DMVs Used in  SQL Server 2005

    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

    Input/output 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 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

These new DMVs of SQL Server 2008 R2 SP1 and SQL Server 2012 

sys.dm_os_windows_info

sys.dm_os_volume_stats

sys.dm_server_services

sys.dm_exec_query_stats

sys.dm_server_memory_dumps,

sys.dm_server_services

TempDB files Recommendations for SQLSERVER

•             Preallocate space for all tempdb files by setting the file size to a value large enough to accommodate the typical workload in the environment. This prevents tempdb from expanding too frequently, which can affect performance. 

•             The tempdb database should be set to autogrow, but this should be used to increase disk space for unplanned exceptions. 

•             Create as many files as needed to maximize disk bandwidth. Using multiple files reduces tempdb storage contention and yields significantly better scalability. However, do not create too many files because this can reduce performance and increase management overhead. As a general guideline, create one data file for each CPU on the server (accounting for any affinity mask settings) and then adjust the number of files up or down as necessary. Note that a dual-core CPU is considered to be two CPUs.

•             Make each data file the same size; this allows for optimal proportional-fill performance.

•             Put the tempdb database on a fast I/O subsystem. Use disk striping if there are many 

directly attached disks.

•             Put the tempdb database on disks that differ from those that are used by user databases.

•             No. Of CPU v/s Data Files Recommendation

No. of Cores       Data files recommended

<8           = No. of Cores

>=8 to <32           = No. of Cores/2

>=32      = No. of Cores/4