Presenter: Jeremy Lull
Jeremy@datarealized.com
DMV – Dynamic Management Views (and functions)
- Returns server state information that can be used to monitor the health of a server instance, diagnose problems, and tune performance.* MSFT – 2008 BOL
DMV Categories (Not all are going to be in this session)
- Change Data Capture
- Query Notifications
- Common Language Runtime
- Replication
- Database Mirroring
- Resource Governor
- Database Related
- Service Broker
- Execution
- SQL Server Extended Events
- Full-Text Search
- SQL Server Operating System
- Index
- Transaction
- I/O
- Security
- Object
DMV’s help with the ever growing infrastructure and complexities. Allow you to monitor and manage your environment.
Key DMV’s
- Sys.dm_os_sys_info
- General Server Info
- Sys.dm_exec_requests
- Sp_who(2)
- Sys.dm_db_index_operational_stats
- Sp_lock
- Sys.dm_db_index_usage_stats
- Base DMV for Index Analysis and Recommendations
- Msdb.dbo.backupset
- Base table for backup history
- Sys.dm_exec_cached_plans
- Shows query plans that are cached by SQL Server
- For query plans, this DMV maps to the syscacheobjects table in SQL Server 2000
- Sys.dm_exec_query_stats
- Performance statistics for cached query plans
- Top 10
- Sys.dm_io_virtual_file_stats
- I/O stats for data and log files
- Sys.dm_os_memory_pools
- Monitors Cache memory
- Sys.dm_exec_sql_text
- Returns the text of the SQL batch that is identified by the specified sql_handle.
- This table-valued function replaces the system function fn_get_sql
- Obtained from:
- Sys.dm_exec_query_stats
- Sys.dm_exec_requests
- Sys.dm_exec_cursors
- Sys.dm_exec_xml_handles
- Sys.dm_exec_query_memory_grants
- Sys.dm_exec_connections
“Use it early, use it often” – Jeremy 
Love it that every technical person presenting a demo has hit at least one minor glitch. Let’s me know it’s not just me. Ha!
Resources
- http://www.sswug.org
- Local User Group
- Books On Line
Popularity: 5% [?]
