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 2005. 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, the user can see all the views, so in order to restrict the user, deny the user SELECT permission on the dynamic management views or functions that you do not want them to access. Here is an example to grant the VIEW SERVER STATE permission to the user Aish:

 GRANT VIEW SERVER STATE TO [MyDom\Aish] 

Now if you want 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 following categories:

• CLR-related Dynamic Management Views

• I/O-related Dynamic Management Views and Functions

• Database mirroring-related Dynamic Management Views

• Query notifications-related Dynamic Management Views

• Database-related Dynamic Management Views

• Replication-related Dynamic Management Views

• Execution-related Dynamic Management Views and Functions

• Service Broker-related Dynamic Management Views

• Full-Text-Search-related Dynamic Management Views

• SQL Server Operating System-related Dynamic Management Views

• Index-related Dynamic Management Views and Functions

• Transaction-related Dynamic Management Views and Functions

We won't describe all the views here, but we will look at examples for the common tasks a DBA would do to monitor a SQL Server. To find the details of all the DMVs and functions, please refer to the Books Online topic "Dynamic Management Views and Functions."

Following are some of the scenarios in which you can use DMVs and functions. You can also open a sample DMV to get all the scripts. Here we will just provide some examples, but in the sample DMV solution you will find many examples for monitoring your SQL Server.

Viewing the Locking Information

The following query will help you get the locking information in a particular database:

 SELECT l.resource_type, l.resource_associated_entity_id ,OBJECT_NAME(sp.OBJECT_ID) AS ObjectName ,l.request_status, l.request_mode,request_session_id ,l.resource_description FROM sys.dm_tran_locks l LEFT JOIN sys.partitions sp  ON sp.hobt_id = l.resource_associated_entity_id WHERE l.resource_database_id = DB_ID() 

Viewing Blocking Information

The following query will show blocking information on your server:

 SELECT t1.resource_type ,t1.resource_database_id ,t1.resource_associated_entity_id ,OBJECT_NAME(sp.OBJECT_ID) AS ObjectName ,t1.request_mode ,t1.request_session_id ,t2.blocking_session_id FROM sys.dm_tran_locks as t1 JOIN sys.dm_os_waiting_tasks as t2   ON t1.lock_owner_address = t2.resource_address LEFT JOIN sys.partitions sp   ON sp.hobt_id = t1.resource_associated_entity_id 

Index Usage in a Database

The following query will give you index usage for the database in which you run the query. It will create a table and store the results in that table so that later you can analyze it. This query can be very helpful for determining which indexes are truly useful in your application and which are not. If certain indexes are not used, then you should consider dropping them because they will take unnecessary time to create or maintain. The results stored in the second table, NotUsedIndexes, will indicate which indexes are not used. Make sure you run these queries for several days, which will give you a better idea of the overall picture than looking at data for just a day. Remember to keep in mind that dynamic management views are volatile, and whenever SQL Server is restarted, these views are initialized again.

 ---------------------------------------------------------------------------------- IF OBJECT_ID('dbo.IndexUsageStats') IS NULL CREATE TABLE dbo.IndexUsageStats (  IndexName sysname NULL ,ObjectName sysname NOT NULL ,user_seeks bigint NOT NULL ,user_scans bigint NOT NULL ,user_lookups bigint NOT NULL ,user_updates bigint NOT NULL ,last_user_seek datetime NULL ,last_user_scan datetime NULL ,last_user_lookup datetime NULL ,last_user_update datetime NULL ,StatusDate datetime NOT NULL ,DatabaseName sysname NOT NULL ) GO ----Below query will give you index USED per table in a database. INSERT INTO dbo.IndexUsageStats (  IndexName ,ObjectName ,user_seeks ,user_scans ,user_lookups ,user_updates ,last_user_seek ,last_user_scan ,last_user_lookup ,last_user_update ,StatusDate ,DatabaseName ) SELECT  si.name AS IndexName ,so.name AS ObjectName ,diu.user_seeks ,diu.user_scans ,diu.user_lookups ,diu.user_updates ,diu.last_user_seek ,diu.last_user_scan ,diu.last_user_lookup ,diu.last_user_update ,GETDATE() AS StatusDate ,sd.name AS DatabaseName FROM sys.dm_db_index_usage_stats diu JOIN sys.indexes si   ON diu.object_id = si.object_id  AND diu.index_id = si.index_id JOIN sys.all_objects so   ON so.object_id = si.object_id JOIN sys.databases sd   ON sd.database_id = diu.database_id WHERE is_ms_shipped <> 1   AND diu.database_id = DB_ID() ---------------------------------------------------------------------------------- --This will store the indexes which are not used. IF OBJECT_ID('dbo.NotUsedIndexes') IS NULL CREATE TABLE dbo.NotUsedIndexes (  IndexName sysname NULL ,ObjectName sysname NOT NULL ,StatusDate datetime NOT NULL ,DatabaseName sysname NOT NULL ) ----Below query will give you index which are NOT used per table in a database. INSERT dbo.NotUsedIndexes (  IndexName ,ObjectName ,StatusDate ,DatabaseName ) SELECT  si.name AS IndexName ,so.name AS ObjectName ,GETDATE() AS StatusDate ,DB_NAME() FROM sys.indexes si JOIN sys.all_objects so   ON so.object_id = si.object_id WHERE si.index_id NOT IN (SELECT index_id                           FROM sys.dm_db_index_usage_stats diu                           WHERE si.object_id = diu.object_id                             AND si.index_id = diu.index_id                           )   AND so.is_ms_shipped <> 1 

View Queries Waiting for Memory Grants

The following query will indicate the queries that are waiting for memory grants. SQL Server will analyze a query and determine how much memory it needs based on the estimated plan. If memory is not available at that time, the query will be suspended until the memory required is available. If a query is waiting for a memory grant, an entry will show up in the DMV sys.dm_exec_query_memory_grants.

 SELECT  es.session_id AS SPID ,es.login_name ,es.host_name ,es.program_name, es.status AS Session_Status ,mg.requested_memory_kb ,DATEDIFF(mi, mg.request_time, GETDATE()) AS [WaitingSince-InMins] FROM sys.dm_exec_query_memory_grants mg JOIN sys.dm_exec_sessions es   ON es.session_id = mg.session_id WHERE mg.grant_time IS NULL ORDER BY mg.request_time 

Connected User Information

The following query will tell you which users are connected, and how many sessions each of them has open:

 SELECT login_name, count(session_id) as session_count FROM sys.dm_exec_sessions GROUP BY login_name 

Query Plan and Query Text for Currently Running Queries

Use the following query to find out the query plan in XML and the query text for the currently running batch for a particular session. Make sure that you are using a grid to output the result in SQL Server Management Studio. When you get the result, you can click the link for the XML plan, which will open an XML editor inside Management Studio. If you want to look at the graphical query plan from this XML plan, save the XML plan with the .sqlplan extension. Then open that file in SQL Server management studio and you will see the graphical execution plan. Here is the query:

 SELECT  er.session_id ,es.login_name ,er.request_id ,er.start_time ,QueryPlan_XML = (SELECT query_plan FROM sys.dm_exec_query_plan(er.plan_handle)) ,SQLText = (SELECT Text FROM sys.dm_exec_sql_text(er.sql_handle)) FROM sys.dm_exec_requests er JOIN sys.dm_exec_sessions es   ON er.session_id = es.session_id WHERE er.session_id >= 50 ORDER BY er.start_time ASC 

Memory Usage

The following query will indicate the memory used, in KB, by each internal SQL Server component:

 SELECT  name ,type ,SUM(single_pages_kb + multi_pages_kb) AS MemoryUsedInKB FROM sys.dm_os_memory_clerks GROUP BY name, type ORDER BY SUM(single_pages_kb + multi_pages_kb) DESC