List of CPU based DMVs in SQL Server 2005 - best used for hardware analysis

Published 05 August 07 12:32 PM | SQL Master 

Dynamic Management Views (DMV) are very useful to get server state information to monitor the status, but not always you have to still continue in monitoring using SYSMON & PROFILER that are available by default with SQL installation. The engine within SQL Server 2005 improves accuracy by using the high-resolution counters that are capable to provider further analysis.

The list of DMVs I usually follow to capture hardware based information are as follows:

Dynamic management views:
• sys.dm_exec_query_stats
• sys.dm_exec_requests
• sys.dm_exec_sessions
• sys.dm_io_pending_io_requests
• sys.dm_os_ring_buffers
• sys.dm_os_sys_info
• sys.dm_io_virtual_file_stats
• sys.dm_os_wait_stats

Also I/O based DMVs such as sys.dm_io_pending_io_requests & sys.dm_io_virtual_file_stats

\

Comments

# SSQA.net - SqlServer-QA.net said on August 5, 2007 2:17 PM:

Dynamic Management Views (DMV) are very useful to get server state information to monitor the status

# Other SQL Server Blogs around the Web said on August 6, 2007 9:53 PM:

Dynamic Management Views (DMV) are very useful to get server state information to monitor the status

# TrackBack said on August 16, 2007 6:13 AM:
# TrackBack said on August 16, 2007 6:13 AM:
# SQL Server Security, Performance & Tuning (SSQA.net) said on September 20, 2007 9:07 AM:

Here comes another Frequently Asked Question (FAQ) on the forums and Frequently Posted Blog (FPB) here

Anonymous comments are disabled

About SQL Master

**__________________________________** SQL Server MVP, Sr. DBA & industry expert. - Knowledge is of two kinds. We know a subject ourselves or we know where we can find information on it. It is also a power and you will gain by sharing it.