Dynamic Management View (DMV) Queries

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.

Get Average Row Size - Returns average row size for data and indexes for one or all tables in the current database.

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.

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.

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.

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.

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.

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

Get Disk IO Statistics (Summary) - Combined disk read and write figures per database for the server.

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

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)

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.

Get DMV/DMF List - Lists all DMVs and DMFs on the server, along with their columns and data types.

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.

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.

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.

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

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

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

General Wait Time and TempDB Statistics - 3 small code snippets to return some additional TempDB memory usage and wait time summary stats.

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.

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.

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.

Get Comprehensive Connection Summary - Retrieves extensive connection and resource usage details aggregated by Login and Session, plus the most resource intensive connection.

Get Comprehensive Running Requests Summary - Retrieves extensive connection and resource usage details for currently running requests.

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.