IMP Views and Functions that Every DBA should know

This blog is the continuation of blog IMP DMVs, SPs & Functions that Every DBA should know.

In this blog, we will talk about some of the IMP and mostly used Views and Functions that every DBA should be aware of.

1) sys.tables

Type: View

Description: This View Returns a row for each user table in SQL Server.

Ex:

select * from sys.tables

2) sys.views

Type: View

Description: This View Returns a row for each view object.

Ex:

select * from sys.views

 

3) sys.procedures

Type: View

Description: This View Returns a row for each object that is a procedure of some kind, with sys.objects.type = P, X, RF, and PC.

Ex:

select * from sys.procedures

 

4) sys.triggers

Type: View

Description: This View Returns a row for each object that is a trigger, with a type of TR or TA.

Ex:

select * from sys.triggers

 

5) sys.sql_modules

Type: View

Description: This View Returns a row for each object that is an SQL language-defined module in SQL Server, including natively compiled scalar user-defined function. 

Ex:

select * from sys.sql_modules

 

6) sys.objects

Type: View

Description: This View Returns a row for each user-defined, schema-scoped object that is created within a database

Ex:

select * from sys.objects

 

7) sys.all_objects

Type: View

Description: This View Shows the UNION of all schema-scoped user-defined objects and system objects.

Ex:

select * from sys.all_objects

 

8) sys.dm_exec_cached_plans

Type: View

Description: Returns a row for each query plan that is cached by SQL Server for faster query execution. You can use this dynamic management view to find cached query plans, cached query text, the amount of memory taken by cached plans, and the reuse count of the cached plans.

Ex:

select * from sys.dm_exec_cached_plans

 

9) sys.dm_exec_query_plan 

Type: SQL_INLINE_TABLE_VALUED_FUNCTION

Description: Returns the Showplan in XML format for the batch specified by the plan handle. The plan specified by the plan handle can either be cached or currently executing.

Ex:

1) select * from sys.dm_exec_query_plan(plan_handle)

2) select * from sys.dm_exec_query_plan(0x06000800C2AAA402D06203F81302000001000000000000000000000000000000000000000000000000000000)

 

10) sys.dm_exec_query_stats

Type: View

Description: Returns aggregate performance statistics for cached query plans in SQL Server. The view contains one row per query statement within the cached plan, and the lifetime of the rows are tied to the plan itself. When a plan is removed from the cache, the corresponding rows are eliminated from this view.

Ex:

select * from sys.dm_exec_query_stats

 

11) sys.dm_exec_sql_text

Type: SQL_INLINE_TABLE_VALUED_FUNCTION

Description: This returns the Transact-SQL query based on the [sql_handle] passed to this DMF.

Ex:

1) select * from sys.dm_exec_sql_text(sql_handle | plan_handle)

2) select * from sys.dm_exec_sql_text(0x06000800C2AAA402F07103F81302000001000000000000000000000000000000000000000000000000000000)

 

12) sys.dm_db_missing_index_details

Type: View

Description: Returns detailed information about missing indexes, excluding spatial indexes.

Ex:

select * from sys.dm_db_missing_index_details

 

13) sys.dm_db_index_physical_stats

Type: SQL_INLINE_TABLE_VALUED_FUNCTION

Description: Returns size and fragmentation information for the data and indexes of the specified table or view in SQL Server. 

Ex:

1) select * from sys.dm_db_index_physical_stats ( { database_id | NULL | 0 | DEFAULT } , { object_id | NULL | 0 | DEFAULT } , { index_id | NULL | 0 | -1 | DEFAULT } , { partition_number | NULL | 0 | DEFAULT } , { mode | NULL | DEFAULT } ) 

2) SELECT * FROM sys.dm_db_index_physical_stats (NULL, NULL, NULL, NULL, NULL);  

3) SELECT * FROM sys.dm_db_index_physical_stats (1, 133575514, 0, NULL, NULL); 

 

14) sys.dm_db_stats_properties 

Type: SQL_INLINE_TABLE_VALUED_FUNCTION

Description: Returns properties of statistics for the specified database object (table or indexed view) in the current SQL Server database. 

Ex:

1) select * from sys.dm_db_stats_properties (object_id, stats_id)

2) select * from sys.dm_db_stats_properties (661577395, 2)

3) select * from sys.dm_db_stats_properties (object_id('Suppliers'), 2)

Note:

Get stats_id from sys.stats DMV

 

15) sys.stats 

Type: View

Description: Returns properties of statistics for the specified database object (table or indexed view) in the current SQL Server database. 

Ex:

select * from sys.stats

 

16) sys.dm_os_volume_stats 

Type: SQL_INLINE_TABLE_VALUED_FUNCTION

Description: Returns information about the operating system volume (directory) on which the specified databases and files are stored in SQL Server.

Ex:

select * from sys.dm_os_volume_stats (database_id, file_id)

select * from sys.dm_os_volume_stats (DB_ID(), file_id)

Note:

Get Filename and file_id from sys.database_files DMV

17) sys.dm_exec_sessions 

Type: View

Description: Returns a row for every session on the queried SQL Server instance, along with details such as the name of the program that initiated the session, session status, SQL Server login, various time counters, and more

Ex:

select * from sys.dm_exec_sessions

 

18) sys.dm_exec_requests

Type: View

Description: Returns a row for every user and system request being executed on the SQL Server instance. To find the blocked requests, search for the requests where the status column value is ‘suspended’

Ex:

select * from sys.dm_exec_requests

 

19) sys.dm_exec_query_stats

Type: View

Description: Returns a row for every query statement in a cached query plan. It provides information about average, minimal, maximal, and total processor time used by the plan, along with other useful information for performance analysis

Ex:

select * from sys.dm_exec_query_stats

 

20) sys.dm_os_performance_counters

Type: View

Description: Returns a row for each SQL Server performance counter. It provides a quick way to find out current performance counter values.

Ex:

select * from sys.dm_os_performance_counters

 

21) sys. dm_os_wait_stats

Type: View 

Description: Returns information about all the waits encountered by threads that executed. 

Ex:

select * from sys.dm_os_wait_stats

22) fn_get_audit_file

Type: Function

Description: Returns information from an audit file created by a server audit in SQL Server.

Ex:

fn_get_audit_file ( file_pattern,   

    { default | initial_file_name | NULL },   

    { default | audit_record_offset | NULL } )