Cool Tips‎ > ‎

Dynamic Management View (DMV) Queries

 

Title

Description

Applicability

 

 

Get Time Table Last Accessed

Returns the last scan, seek and lookup times for one or many tables.  Also returns the most recent of the 3 times as a generic 'last accessed' time.

 

 

2000, 2005, 2008, 2008R2, 2012 

 

Get Average Row Size

Returns average row size for data and indexes for one or all tables in the current database.

 

2000, 2005, 2008, 2008R2, 2012 

 

Get Top N CPU Queries

Returns the top N high CPU queries for the server.  Data is valid from the point of last recompile for each query.  Includes individual query statements.

 


2000, 2005, 2008, 2008R2, 2012

 

 

Get Top N Physical Disk Read Queries

Returns the top N high physical disk read queries for the server.  Data is valid from the point of last recompile for each query.  Includes individual query statements.

 

 

2000, 2005, 2008, 2008R2, 2012

 

Get Top N Logical Cache Read Queries

Returns the top N high logical cache read queries for the server.  Data is valid from the point of last recompile for each query.  Includes individual query statements.

 

 

2000, 2005, 2008, 2008R2, 2012

 

 

Get Top N Logical Cache Write Queries

Returns the top N high logical cache write queries for the server.  Data is valid from the point of last recompile for each query.  Includes individual query statements.

 

 

2000, 2005, 2008, 2008R2, 2012

 

 

Get Top N Long Running Queries

Returns the top N long running queries for the server.  Data is valid from the point of last recompile for each query.  Includes individual query statements.

 

 

2000, 2005, 2008, 2008R2, 2012

 

Get Unused Indexes

Returns a list of table indexes that have not been used since the last SQL Server Service/Instance restart.

 

2000, 2005, 2008, 2008R2, 2012

 

Get Disk IO Statistics (Summary)

Combined disk read and write figures per database for the server. 

 

2000, 2005, 2008, 2008R2, 2012

 

Get Disk IO Statistics (Detail)

Disk read and write figures per database file on the server.  Also includes IO wait stats and percentage of total

 

2000, 2005, 2008, 2008R2, 2012

 

 

Get Highest TempDB Users    

Returns a list of sessions and the counts of objects created/destroyed in TempDB.  Also returns selected session attributes (eg host, program and database names)

 

 

2000, 2005, 2008, 2008R2, 2012

 

Get Index Usage

Returns a list of all tables and indexes in a database (including tables with no indexes) along with usage and update stats.

 

2000, 2005, 2008, 2008R2, 2012

 

Get DMV/DMF List


Lists all DMVs and DMFs on the server, along with their columns and data types.

 

2000, 2005, 2008, 2008R2, 2012  

 


Get Top N Buffer Memory Objects

Returns a list of the Top N objects consuming of buffer pool memory.  Scope is server level unless the optional Database Name parameter is supplied.



2000, 2005, 2008, 2008R2, 2012  

 

Get Active Locks

Returns a summary list of all active locks on the server, or for a specific database if the optional Database Name is supplied.

 

2000, 2005, 2008, 2008R2, 2012 

 

Get Blocking Locks

Returns a summary list of all blocking locks on the server, or for a specific database if the optional Database Name is supplied.

 

2000, 2005, 2008, 2008R2, 2012 

 


Get Poor Non Clustered Indexes

Returns a list of non-clustered index where the cost of updating them outweighs their benefit.  Scope is server level unless the optional Database Name is supplied 

 


2000, 2005, 2008, 2008R2, 2012  

 

Get Top Waits

Returns a list of non-system wait states that comprise the top 95% of wait states currently active on the server. 

 

2000, 2005, 2008, 2008R2, 2012 

 

Get CPU Utilization

Returns the summary SQL, Non-SQL and Idle process CPU utilization per minute for the last 30 minutes.

 

2000, 2005, 2008, 2008R2, 2012 

 

General Wait Time and TempDB Statistics

3 small code snippets to return some additional TempDB memory usage and wait time summary stats. 

 

2000, 2005, 2008, 2008R2, 2012 

 

Get Currently Running Processes

Retrieves a list of currently running processes  on the server, or for a specific database if the optional Database Name is supplied.

 

2000, 2005, 2008, 2008R2, 2012 

 

Get Currently Blocking Processes

Retrieves a list of currently blocking and blocked processes  on the server, or for a specific database if the optional Database Name is supplied.

 

2000, 2005, 2008, 2008R2, 2012 

 

Get Cached Plans 

Retrieves a list of cached query plans on the server, or for a specific database if the optional Database Name is supplied.

 

2000, 2005, 2008, 2008R2, 2012 

 

Get Comprehensive Connection Summary

Retrieves extensive connection and resource usage details aggregated by Login and Session, plus the most resource intensive connection.

 

2000, 2005, 2008, 2008R2, 2012 

 

Get Comprehensive Running Requests Summary

Retrieves extensive connection and resource usage details for currently running requests.

 

2000, 2005, 2008, 2008R2, 2012 


Get Top N Data Files with IO Stall 


Retrieves a list of data files where the sessions have incurred IO stall when trying to read or write data.

 

2000, 2005, 2008, 2008R2, 2012 

 

 

 

 

 

 

Comments