Professional Microsoft® SQL Server® 2008 Administration

13.6. Monitoring with Dynamic Management Views and Functions

Dynamic management views (DMVs) and dynamic management functions (DMFs) are a godsend to the DBA. They provide plenty of information about server and database state. DMVs are designed to give you a window into what's going on inside SQL Server. They return server state information that you can use to monitor the health of a server instance, diagnose problems, and tune performance. There are two types of DMVs and functions:

• Server-scoped dynamic management views and functions

• Database-scoped dynamic management views and functions

All DMVs and functions exist in the sys schema and follow the naming convention dm_* and fn*, respectively. To view the information from a server-scoped DMV, you have to grant the SERVER VIEW STATE permission to the user. For database-scoped DMVs and functions, you have to grant the VIEW DATABASE STATE permission to the user. Once you grant the VIEW STATE permission, that user can see all the views; to restrict the user, deny the SELECT permission on the dynamic management views or functions that you do not want the user to access. The following example grants the VIEW SERVER STATE permission to the user Aish:

GRANT VIEW SERVER STATE TO [MyDom\Aish]

If you want the user [MyDom\Aish] to be restricted from viewing information in the view sys.dm_os_wait_stats, you need to DENY SELECT as follows:

DENY SELECT ON sys.dm_os_wait_stats TO [MyDom\Aish]

DMVs and functions are generally divided into the ...