Dynamic management views and functions in SQL Server

In this post named “Dynamic management objects in SQL Server”, we will explore the DMVs and DMFs in SQL Server. Dynamic management views (DMVs) and dynamic management functions (DMFs) are used to extract current internal information of the Server State using T-SQL commands. Formerly such types of data were only available in Performance Monitor (a Microsoft tool to analyze System performance), now SQL Server has exposed such information with some other important information related to current server state through dynamic management views and dynamic management functions. Both dynamic management views and dynamic management functions are collectively known as Dynamic Management Objects (DMOs). These objects were introduced primarily in SQL Server 2005 which allow us to monitor the current internal status of SQL Server using simple T-SQL.

DMVs and DMFs are very useful in collecting current performance related data of the system. We can categorize these objects in two groups as per their scope ;

1. Server scoped DMVs and DMFs
2. Database scoped DMVs and DMFs

All DMVs and DMFs are stored in master database inside sys schema. They fulfill various kind of requirements which covers – CLR related, Transaction related, Execution related, Database mirroring related, Index related, I/O related, Full Text Search related, Query notification related, Replication related, Service broker related, SQL operation system related.

How to execute DMVs and DMFs

We can execute these DMVs and DMFs as like we execute any other views and function in SQL Server. But remember that they reside inside sys schema and we have to call it with sys.objectname.

Demo Example

SELECT * FROM sys.dm_os_performance_counters
SELECT * FROM sys.dm_db_index_physical_stats(DB_ID('AdventureWorks2012'), NULL, NULL, NULL, NULL)

Notice that we have executed the above view and function as like any other view and function.

Required Permissions to access dynamic management views and functions

To allow access on these dynamic management objects, we GRANT permission as below;

Server scoped DMVs and DMFs – Requires “VIEW SERVER STATE” permission
Database scoped DMVs and DMFs – Requires “VIEW DATABASE STATE” permission

GRANT VIEW SERVER STATE TO UserName
GRANT VIEW DATABASE STATE TO UserName

Note: If any user has access on VIEW SERVER STATE but denied on VIEW DATABASE STATE, that user will be able to access server level DMOs but will not be a be to access database level DMOs because DENY takes precedence over GRANT.

Thanks for reading this post. Also don’t forget to put your comments and suggestions below and please must rate it.