Monitoring SQL Server with DMVs

Monitoring SQL Server with DMVs

• Dr Greg Low
• Sunday @ 9am

What we will cover

• DMVs Introduced
• A New Insight Into Existing Technologies
• An Insight Into Newer Technologies
• Usimg DMVs in Custom Reports

DMVs Introduced

• SQL Server 2005+
• Internal state/helath of server
• Previously used system tables, DBCC, Profiler, Perfmon
• Diagnose problems, tune performance
• DMVs and DMFs

Scope and Permissions

• Require SELECt permission plus:
• Server scope – VIEW SERVER STATE
• Database scope – VIEW DATABASE STATE
• Create user in master and DENY to restrict across database
• Sys schema and dm_* naming

A New Insight Into Existing Technologies

• O/S
  • Sys.dm_os_performance_counters
• DB
  • Sys.dm_db_partition_stats
  • Sys.dm_db_index_usage_stats
  • Sys.dm_db_physical_stats
  • Sys.dm_db_operational_stats
  • Sys.dm_db_missing_index_details
  • Sys.dm_db_missing_index_columns
• Statistics
  • Sp_helpstats ‘Production.Product’;
  • CREATE STATISTICS ColorStats ON Production.Product(Color) WITH FULLSCAN;
  • DBCC SHOW_STATISTICS(‘Production.Product’,ColorStats);
• Server
  • Sys.dm_exec_sessions
  • Sys.dm_exec_requests
  • Sys.dm_exec_sql_text(…)
  • Sys.dm_exec_query_status
  • Sys.dm_exec_cached_plans
• Legacy
  • EXEC sp_who2
  • Master..sysprocesses
  • Master..syscacheobjects

An Insight into Newer Technologies

• sys.dm_clr_properties
• sys.dm_clr_appdomains
• sys.dm_clr_tasks
• sys.dm_db_mirroring_connections
• sys.dm_broker_connections
• sys.dm_broker_queue_monitors
• sys.dm_tran_top_version_generators
• sys.dm_tran_version_store

Using DMV’s in Custom Reports

• Look at Standard Reports to learn how to use DMVs
• Run a Custom report (generated in SSRS)
• Warning message: Trojan reports!
• Custom report runs in the context of the currently selected database – might not be the appropriate context
• Running the report once throws it into the drop down of recently used reports
• Added in SQL Server 2005 Service Pack 2
• Same familiar RDL format
• Object-Related Reports - Using existing object context
• SSMS 2008 can’t run SSRS 2008 reports! Must be SSRS 2005.

Learning to Use DMVs

• Report Samples are shipped with the product
• Good examples of end-to-end user of DMVs and DMFs
• Buck Woody – blogs