Important: System Objects/Views/DMV/DMF

Important: System Objects/Views/DMV/DMF

This is the some of the list of System Objects/Views/DMV/DMF.

Catalog View:

All system information is stored on catalog views which look like

Eg. Sys.databases

Compatibile views:

This are views to manage old compatibility

Select * from master.dbo.sysprocesses

Server Side DMV

  1. Sys.dm_exec* (query exection)
  2. Sys.dm_io* (io level)
  3. Sys.dm_db* (database level  like index….)
  4. Sys.dm_tran* (transaction level)
  5. Sys.dm_os* (os level)

Component level DMV:

  1. Sys.dm_Repl* (replication)
  2. Sys.dm_broker* (service broker)
  3. Sys.dm_fts* (full Text Search)
  4. Sys.dm_clr*( CLR)

—————————– Understand the dmv:

Require permission as view database stats or view server stats

1.  select count(1) from sys.all_objects –1841

2.  select * from sys.databases

3.  select count(*) from sys.system_objects –1763

4.  select * from sys.sysobjects –- show all the objects in present database

5.  select * from sys.system_sql_modules where object_id=object_id(‘sys.sysobjects’)—-shows code for system objects

6.  select * from sys.dm_exec_sessions -– like sysprocesses info

7.  select * from sys.dm_exec_requests order by logical_reads desc -– like sysprocesses info

select * from sys.dm_exec_requests r cross apply sys.dm_exec_sql_text(r.sql_handle) where session_id>50

8.  select * from sys.dm_os_waiting_tasks  — current waits

9.  select * from sys.dm_os_wait_stats  — report for waits * from sys.dm_os_latch_stats –report for latchs db_name(database_id),* from sys.dm_io_virtual_file_stats(NULL , NULL )

order by io_stall desc * from sys.dm_os_wait_stats * from sys.dm_db_index_operational_stats(db_id(),NULL,NULL,NULL) * from sys.dm_db_partition_stats * from sys.dm_exec_query_stats


Select * from Sys.dm_exec_query_stats qs

cross apply sys.dm_exec_sql_text(qs.sql_handle)

cross apply sys.dm_exec_query_plan(qs.plan_handle) * from sys.dm_db_index_usage_stats object_name(object_id),* from sys.dm_db_index_physical_stats (

db_id(),null,null,null,default)  — index fragmentation information * from sys.dm_os_latch_stats * from sys.dm_db_missing_index_group_stats

  1. select * from sys.dm_exec_query_optimizer_info
  2. select * from sys.dm_exec_cached_plans * from sys.system_internals_allocation_units  — inside information about object first root and next page address.

  1. select * from sys.system_internals_partitions  –more inforation abt views useful for join of system_internals_allocation_units

22, 23 used to be achieve using DBCC IND for DBCC Page() with dbcc traceon(3604)

  1. select * from sys.dm_io_pending_io_requests  — any pending io
  2. select * from sys.dm_tran_locks  –lock info
  3. select * from sys.dm_tran_active_transactions
  4. select * from sys.dm_os_sys_info  –sys info
  5. select * from sys.dm_os_performance_counters  –perfmon info
  6. select * from sys.dm_os_schedulers  –CPU info
  7. select * from sys.dm_os_ring_buffers  –memory
  8. select * from sys.dm_os_buffer_descriptors  –memory * from sys.dm_os_memory_cached_counters  –Sql server 2008 * from sys.dm_os_sys_memory –Sql server 2008





This entry was posted in DMV, General, Index, Memory, Performance Tuning and tagged , . Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.