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

Sys.xxx

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

10.select * from sys.dm_os_latch_stats –report for latchs

11.select db_name(database_id),* from sys.dm_io_virtual_file_stats(NULL , NULL )

order by io_stall desc

12.select * from sys.dm_os_wait_stats

13.select * from sys.dm_db_index_operational_stats(db_id(),NULL,NULL,NULL)

14.select * from sys.dm_db_partition_stats

15.select * from sys.dm_exec_query_stats

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)

16.select * from sys.dm_db_index_usage_stats

17.select object_name(object_id),* from sys.dm_db_index_physical_stats (

db_id(),null,null,null,default)  — index fragmentation information

18.select * from sys.dm_os_latch_stats

19.select * 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

22.select * 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

32.select * from sys.dm_os_memory_cached_counters  –Sql server 2008

33.select * from sys.dm_os_sys_memory –Sql server 2008

DBCC MEMORYSTATS

Thanx.

Vinay

Reference:

http://technet.microsoft.com/en-us/library/bb838723(office.12).aspx

Today while being on forum found this information so though to blog for future reference. thanx Uri Dimant for sharing this. 

WITH cte 

AS 

( 

SELECT object_name(ic.object_id) as object_name , index_name = i.name,
‘column’ = c.name,
        ‘column usage’ = CASE ic.is_included_column
                    WHEN 0 then ‘KEY’
                    ELSE ‘INCLUDED’
        END
FROM sys.index_columns ic JOIN sys.columns c
    ON ic.object_id = c.object_id
    AND ic.column_id = c.column_id
JOIN sys.indexes i
    ON i.object_id = ic.object_id
    AND i.index_id = ic.index_id 

) SELECT * FROM cte WHERE object_name =’tablename’ 

——————————– 

Below is the query which give information about parallel queries: 

–This first thing to check if CPU is at 100% is to look for parallel queries: 

– Tasks running in parallel (filtering out MARS requests below): 

select * from sys.dm_os_tasks as t  

 where t.session_id in ( select t1.session_id from sys.dm_os_tasks as t1  group by t1.session_id having count(*) > 1  

 and min(t1.request_id) = max(t1.request_id)); 

– Requests running in parallel: 

 select * from sys.dm_exec_requests as r  

 join (select t1.session_id, min(t1.request_id) 

 from sys.dm_os_tasks as t1  

 group by t1.session_id  

 having count(*) > 1  

 and min(t1.request_id) = max(t1.request_id) 

 ) as t(session_id, request_id) 

 on r.session_id = t.session_id  

 and r.request_id = t.request_id; 

 Thanx. 

Vinay 

Reference: 

http://social.msdn.microsoft.com/Forums/en-US/transactsql/thread/383c0712-9416-4e23-9f45-d74148202596 

http://social.msdn.microsoft.com/Forums/en-US/sqldatabaseengine/thread/e5a72c22-56c9-420a-bf2f-05b834665e41