> This concept is introduced in SQL Server 2005 version.
> The main purpose we can monitor SQL Server without consuming hardware resources like DBCC queries.
> The DMV’S newly introduced in SQL Server 2005 gives the database administrator information about the current state of the SQL Server machine.
> These Values will help the administrator to diagnose problems and tune the server for optimal performance.
> The DMV’S in SQL Server are designed to give you a window into what’s going on inside SQL Server
> They can provide information on what’s currently happening inside the server as well as the objects it’s strong. They are designed to be used instead of system tables and various functions.
> DMV’S are stored in sys schema and they start with dm_in the name
To know list of DMV’S:
SELECT name, type, type_desc FROM sys.system_objects WHERE name LIKE 'dm_%' ORDER BY name
List of DMV'S
2005 version of Sql -89….. 2008 version of sql-176……2012 version of sql-900+
There are two types of dynamic management views and functions:
Server-scoped dynamic management views and functions: These require VIEW SERVER STATE permission on the server.
Database-scoped dynamic management views and functions: These require VIEW DATABASE STATE permission on the database.
There are multiple categories close to 17... In which these views and functions have been organized
We have 85 of these views and functions. To give a further split, 76 of these are views and 9 of them are functions... Below are the 4 types of DMV which can be used frequently.
1. SQL Server Related [Hardware Resources] DMV’S
2. Database Related DMV’S
3. Index Related DMV’S
4. Execution Related DMV’S
5. Replication Related DMV’S
6. Query notifications Related DMV’S
7. SQL Operating System Related DMV’S
8. I/O Related DMV’S
9. Transaction Related DMV’S
1. SQL Server related [Hardware Resources] DMV’S:
> This section contains the dynamic management views that are associated with the SQL Server Operating System (SQLOS). The SQLOS is responsible for managing operating system resources that are specific to SQL Server.
Select * from Sys.dm_tran_locks:
Returns information about locks
Select * from sys.dm_os_waiting_tasks:
> Returns information about the wait queue of tasks that are waiting on some resource.
> Returns information about all the waits encountered by threads that executed. You can use this aggregated view to diagnose performance issues with SQL Server and also with specific queries and batches.
2. Database Related DMV’S:
1. Returns a row for every automatic page-repair attempt on any mirrored database on the server instance.
2. This view contains rows for the latest automatic page-repair attempts on a given mirrored database, with a maximum of 100 rows per database.
Returns a row for each connection established for database mirroring.
3. INDEX related DMV’S
Fragmentation: DMV's to find 2005 onwards
1. Select * from sys.dm_db_index_physical_stats:
To find Column to verify the fragmentation value:
Select * from sys.dm_db_missing_index_details:
Returns detailed information about missing indexes, excluding spatial indexes.
Returns counts of different types of index operations and the time each type of operation was last performed in SQL Server.
4. Execution related DMV’S:
> 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.
> Returns information about the connections established to this instance of SQL Server and the details of each connection.
> shows information about all active user connections and internal tasks. This information includes client version, client program name, client login time, login user, current session setting, and more.
> Returns information about the cursors that are open in various databases.
5. Replication related DMV’S:
> Returns information about database objects published as articles in a replication topology.
> Returns information about transactions being replicated in a transactional publication.
> Returns information about table columns published by replication.
> Returns information on each replicated or change data capture transaction.