When you need to troubleshoot a problem, prior to
SQL Server 2005 you have to depend upon PROFILER or query system tables
constantly or some other means to trace the system usage.
SQL Server 2005 onwards DBAs are equipped with
Dynamic Management Views (and Data Mining Extenstions DMX for Analysis Services)
for core database engine to obtain the snapshot of information on resource usage
such as disk, cpu, memory and many more. The 2 types of DMVs are server-scoped
& database scoped whics requires obvious elevated privileges to execute on
that SQL instance. A note to refer about their compatibility between the version
that BOL highlights "...schemas and
the data they return may change in future releases of SQL Server. Therefore,
dynamic management views and functions in future releases may not be compatible
with the dynamic management views and functions in SQL Server
2008.....". Further the DMVs &
functions are divided into various categores such as:
|
Change Data Capture Related Dynamic
Management Views
|
Query Notifications Related Dynamic
Management Views
|
|
Common Language Runtime Related Dynamic
Management Views
|
Replication Related Dynamic Management
Views
|
|
Database Mirroring Related Dynamic
Management Views
|
Resource Governor Dynamic Management
Views
|
|
Database Related Dynamic Management
Views
|
Service Broker Related Dynamic
Management Views
|
|
Execution Related Dynamic Management
Views and Functions
|
SQL Server Extended Events 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
|
|
I/O Related Dynamic Management Views and
Functions
|
Security Related Dynamic Management
Views
|
|
Object Related Dynamic Management Views
and Functions
|
The next thought will come into mind that how many
DMV/DMF are there in SQL Server, to get that information (see Pinal's post)
SELECT name, type, type_desc FROM sys.system_objects WHERE name LIKE 'dm_%'
ORDER BY name
or
SELECT name, type, type_desc FROM sys.system_objects WHERE name LIKE 'dm[_]%' ORDER BY name
Further, the privileges that an user
needs to run must have VIEW SERVER STATE permission to execute the DMVs, in
addition to that if the user needs the DATABASE state of information then VIEW
DATABASE STATE permission is also required. Otherwise DENY operation
takes precedence, if a user has been granted VIEW SERVER STATE permissions but
denied VIEW DATABASE STATE permission, the user can see server-level
information, but not database-level information.
Finally here is the good resource to get the
important DMVs that are useful in DBAs day-to-day troubleshooting fighting
arsenal get SQL Server DMV starter
pack from Redgate, and to get more comfort
on your troubleshooting techniques you might download the evaluation copy of SQL
Response that will alert the problems on SQL Server platform as soon as they
arise. Also fellow MVP Glenn Berry has done excellent work on DMVs path as
DMV a DAY to that I use everytime I get to site on a troubleshooting
project, follow through the link for additional techniques for your DBA
experience. Also the DMV related tags from this site.
