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 ;

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.

Related Posts

About Gopal Krishna Ranjan

I am Gopal Krishna Ranjan, having 8 years of industry experience in Software development. I have a head down experience in Database, Data Warehouse, Big Data and cloud technologies and have implemented end to end Database, Data Warehouse, Big Data and Cloud Solutions.
I have extensively worked on SQL Server, Python, Hadoop, Hive, Spark, Azure, Machine Learning, and MSBI (SSAS, SSIS, and SSRS). I also have good experience in windows and web application development using ASP.Net and C#.