Copyright © 2008-2019 Paula DiTallo

Tag Cloud

Which DMVs are most helpful? Is there a list of them?

As with most questions asked about monitoring/assessing/administering MS SQL Server installations, the answer is "it depends".

There are many other blogs/resources/etc. online that can get into the specifics--think of this mini-blog as a snake-bit kit! :) Overall, to get to a list of objects that fall under the DMV (Dynamic Management Views) category, type this:

-- This will show you the views and the functions of all dmv objects
SELECT name, type, type_desc
FROM sys.system_objects
 WHERE name LIKE 'dm_%'
-- and type = 'V'
  ORDER BY name

Here are a few useful DMVs to get started with...

-- Query Plans
-- stats about cached query plans
select * from sys.dm_exec_query_stats

-- identifies query plans that are cached
select * from sys.dm_exec_cached_plans

-- I/O
select * from sys.dm_io_pending_io_requests
select * from sys.dm_io_virtual_file_stats(null,null)  -- //
stats for all data and log files

-- Reqs/Executions

-- lists active user/internal task connects
select * from sys.dm_exec_sessions

-- established connects
select * from sys.dm_exec_connections

-- lists info about all reqs executing
select * from sys.dm_exec_requests

Friday, September 27, 2013 7:23 PM


No comments posted yet.

Post A Comment