You are previewing Performance Tuning with SQL Server Dynamic Management Views.
O'Reilly logo
Performance Tuning with SQL Server Dynamic Management Views

Book Description

This is the book that will de-mystify the process of using Dynamic Management Views to collect the information you need to troubleshoot SQL Server problems. It will highlight the core techniques and "patterns" that you need to master, and will provide a core set of scripts that you can use and adapt for your own requirements.

Table of Contents

  1. Copyright
  2. About the Authors
    1. Louis Davidson
    2. Tim Ford
    3. Glenn Berry (technical review and additional material)
  3. Acknowledgements
    1. Louis Davidson
    2. Tim Ford
  4. Introduction
    1. Code Examples
  5. 1. Using Dynamic Management Objects
    1. Compatibility Views, Catalog Views, and DMOs
    2. DMO Security and Permissions
    3. Performance Tuning with DMOs
      1. Navigating through the DMOs
      2. Point-in-time versus cumulative data
      3. Beware of the watcher effect
      4. Using DMOs with other performance tools
    4. Summary
  6. 2. Connections, Sessions and Requests
    1. Sysprocesses versus DMOs
    2. Connections and Sessions
      1. sys.dm_exec_connections
      2. sys.dm_exec_sessions
      3. Who is connected?
      4. Who is connected by SSMS?
      5. Session-level settings
      6. Logins with more than one session
      7. Identify sessions with context switching
      8. Identify inactive sessions
      9. Identify idle sessions with orphaned transactions
    3. Requests
      1. Overview of sys.dm_exec_requests
        1. SQL and plan handles
        2. Identification columns
        3. Blocking and locking columns
        4. Activity and workload columns
      2. Overview of sys.dm_exec_sql_text
      3. Returning the SQL text of ad hoc queries
      4. Isolating the executing statement within a SQL handle
      5. Investigating work done by requests
      6. Dissecting user activity
        1. Who is running what, right now?
        2. A better version of sp_who2
    4. Summary
  7. 3. Query Plan Metadata
    1. Why Cached Plan Analysis with DMOs?
    2. An Overview of DMOs for Query Plan Metadata
    3. Flushing the Cache?
    4. Viewing the Text of Cached Queries and Query Plans
      1. Returning the plan using sys.dm_exec_query_plan
      2. Dissecting the SQL text
      3. Returning the plan using sys.dm_exec_text_query_plan
    5. Cached Query Plan Statistics
      1. The sys.dm_exec_cached_plans DMV
      2. Investigating plan reuse
        1. The plan reuse "distribution curve"
        2. Examining frequently used plans
        3. Examining ad hoc single-use plans
    6. Query Plan Attributes
    7. Gathering Query Execution Statistics
      1. Overview of sys.dm_exec_query_stats
      2. Putting sys.dm_exec_query_stats to work
    8. Investigating Expensive Cached Stored Procedures
    9. Getting Aggregate Query Optimization Statistics for All Optimizations
    10. Summary
  8. 4. Transactions
    1. What is a transaction, anyway?
    2. Investigating Locking and Blocking
      1. DMOs, Activity Monitor and sp_who2
      2. An overview of the sys.dm_tran_locks DMV
        1. Lock types
        2. Lock modes
      3. Investigating locking
      4. Blocking analysis using sys.dm_tran_locks and sys.dm_os_waiting_tasks
    3. Analyzing Transactional Activity
      1. Transactional DMOs vs. DBCC OPENTRAN
      2. sys.dm_tran_session_transactions
      3. sys.dm_tran_active_transactions
      4. sys.dm_tran_database_transactions
      5. Assessing transaction log impact
    4. Snapshot Isolation and the tempdb Version Store
      1. SNAPSHOT and READ_COMMITTED_SNAPSHOT modes
      2. Investigating snapshot isolation
        1. DMVs for current snapshot activity
          1. sys.dm_tran_active_snapshot_database_transactions
          2. sys.dm_tran_current_snapshot
          3. sys.dm_tran_transactions_snapshot
        2. Current snapshot activity
        3. Version store usage
          1. Using sys.dm_tran_version_store
          2. sys.dm_tran_top_version_generators
    5. Summary
  9. 5. Indexing Strategy and Maintenance
    1. The Indexing System Catalog Views
    2. Using the Indexing DMOs
    3. Index Strategy
          1. Clustered indexes and PKs
          2. Covering indexes
          3. High selectivity
          4. Neither too many nor too few
          5. Narrow indexes (within reason)
      1. Investigating index usage (index_usage_stats)
        1. Identify indexes that have never been accessed
        2. Identify indexes that are being maintained but not used
        3. Identify inefficient indexes
      2. Determine usage patterns of current indexes (index_operational_stats)
        1. Detailed activity information for indexes not used for user reads
        2. Identify locking and blocking at the row level
        3. Identify latch waits
        4. Identify lock escalations
        5. Identify indexes associated with lock contention
      3. Find missing indexes
        1. Missing index details
        2. Missing index columns
        3. Missing index groups
        4. Missing index group statistics
        5. Limitations of the missing index DMOs
        6. Finding the most beneficial missing indexes
    4. Index Maintenance (index_physical_stats)
      1. A brief overview of index fragmentation
      2. Fragmentation statistics (index_physical_stats)
      3. Detecting and fixing fragmentation
    5. Summary
  10. 6. Physical Disk Statistics and Utilization
    1. Minimizing I/O
    2. Tuning the Disk I/O Subsystem
    3. Getting Physical Statistics on your Tables and Indexes
      1. Size and structure
        1. Total number of rows in a table
        2. Number of rows per partition
      2. Investigating fragmentation
        1. The sys.dm_db_index_physical_stats DMF
        2. Fragmentation in clustered tables
        3. Fragmentation in heaps
    4. Diagnosing I/O Bottlenecks
      1. An overview of sys.dm_io_virtual_file_stats
      2. Using sys.dm_io_virtual_file_stats
      3. Investigating physical I/O and I/O stalls
      4. Viewing pending I/O requests
    5. Finding the Read:Write Ratio
      1. Amount of data read versus written
      2. Number of read and write operations
      3. Number of reads and writes at the table level
    6. Getting Stats about tempdb Usage
    7. Summary
  11. 7. OS and Hardware Interaction
    1. Wait Statistics
      1. A brief overview of sys.dm_os_wait_stats
      2. Finding the most common waits
      3. Finding the longest cumulative waits
      4. Investigating locking waits
      5. Investigating CPU pressure
    2. SQL Server Performance Counters
      1. Directly usable counter types
        1. Monitoring shrinkage and growth of the transaction log
        2. Deprecated feature use
      2. Ratios
      3. Per second averages
      4. Average number of operations
    3. Monitoring Machine Characteristics
    4. Investigating CPU Usage
      1. An overview of sys.dm_os_schedulers
      2. CPU waits
      3. Insufficient threads
      4. Context switching
      5. Is NUMA enabled?
      6. CPU utilization history
    5. Investigating Memory Usage
      1. System-wide memory use
      2. Process memory use
      3. Memory use in the buffer pool
      4. Memory clerks and memory grants
      5. Investigate memory using cache counters
    6. Investigating Latching
    7. Summary