New Zealand's new Copyright Law presumes 'Guilt Upon Accusation' and will Cut Off Internet Connections without a trial. is against this unjust law - help us

A Space for All Things .NET Related

Monitoring SQL Server with DMVs

By James Hippolite, in , posted: 7-Dec-2008 10:12

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

Other related posts:
Exam 70-300
Exam 70-442
Exam 70-441

Add a comment

Please note: comments that are inappropriate or promotional in nature will be deleted. E-mail addresses are not displayed, but you must enter a valid e-mail address to confirm your comments.

Are you a registered Geekzone user? Login to have the fields below automatically filled in for you and to enable links in comments. If you have (or qualify to have) a Geekzone Blog then your comment will be automatically confirmed and shown in this blog post.

Your name:

Your e-mail:

Your webpage:

JamesHip's profile

James Hippolite
New Zealand

Welcome to my technical blog. 

Here, I attempt to distill the Microsoft Certified Professional Developer knowledge I have accumulated since first qualifying MCP in 1996.  This blog started on 13 September 2007 as an off-shoot from my mixed up personal blog.  But it took a shot in the arm from Scott Hanselman's talk at TechEd New Zealand 08 "32 Ways To Make Your Blog Suck Less".