SQL Server Performance Monitoring by DMV

SQL Server Performance Monitoring by DMV

 The learning objective is to identify MS SQL Database performance issue by using DMV (Dynamic Management View).
DMV can help to answer the following questions
  • What are DMV and how to use DMV for performance troubleshooting?
  • Any unused or little-used indexes?
  • What were/are slowest-running queries?
  • What were/are queries that are blocked the most, queries?
  • What queries use the most CPU, and queries that use the most I/O?
  • What queries are not able to run, what resources they’re waiting for?
  • What transactions may have locking, blocking, and deadlocks?
Prerequisite
  • Basic understanding of SQL Server/SQL Query
  • Basic Performance testing methodology
 
Course Name SQL Server Performance Monitoring by DMV
Speaker Tony Hsu
Duration 8 hours
Language Mandarin
Target Audience Attendee whom may be interested or involved with

  • Database administrators
  • System Engineers
  • RD/QA who working on projects related to MS SQL.
Agenda
Session 1
  • What is DMV?
  • How to use DMV to identify SQL performance issue?
Session 2
  • DMV usage best practices
  • Identify Costly missing indexes
  • Finding the most-costly unused indexes
  • Finding which indexes aren’t used at all
  • Finding the most-fragmented indexes
Session 3
  • How to find a cached plan
  • Finding the queries that take the longest time to run
  • Finding the queries that spend the longest time being blocked
  • Finding the queries that use the most CPU
Session 4
  • Finding queries that have missing statistics
  • Finding unused stored procedures
  • What’s running now?
  • Why are you waiting?
  • What’s blocking my SQL query?
  • Locks, blocks, and deadlocks

Leave a Reply

Your email address will not be published. Required fields are marked *