Saturday, March 19, 2011

SQL Server Performance Using DMVs

SQL Server Performance using Dynamic management Views Part 1

1 Introduction

1.1 What are DMVs ?

Dynamic managment views (DMVs) are introduced in SQL Server 2005 onwards. They are Views and System functions built on top of internal structures.DMVs are handy to troubleshoot performance issue when there is no option of using profiler since troubleshooting with the aid of profiler will take lot of time as well as they take more system resources than DMVs. DMVs need only “View server state / View database state privilege. A correct DMV query can find potential performance bottleneck online in short time. This article illustrates using DMVs how best we can find the following informations

1.2 Types of DMVs

DMVs are in Sys schema stats with dm_* and the following are the types of DMVs

  • dm_exec_*
    • Execution of user code and associated connections
  • dm_os_*
    • Memory, locking & scheduling
  • dm_tran_*
    • Transactions & isolation
  • dm_io_*
    • I/O on network and disks
  • dm_db_*
    • Databases and database objects

    The following query will list all DMVs

    SELECT * FROM sys.all_objects
      WHERE [name] LIKE '%dm_%'
            AND [type] IN ('V', 'TF', 'IF')
    ORDER BY [name]

    1.3 What are things we can get from DMVs

    Using DMVs we can find the following information\

    • Index Tuning / Statistics
    • Identify top resource (IO, CPU, etc...) consuming queries
    • Wait Statistics
    • Locking & blocking
    • Perfmon
    • TempDB
    • I/O
    • Memory

    2.0 Current Execution Requests

    Using Sys.dm_exec_Requests we can find the queries running currently. The following information can be obtained
    • Execution of user code and associated connections
    • Useful in identifying blocking sessions , resource wait time.

    using the below query we can find the resources which are blocing each other

    Select Blocking_Session_ID,Session_Id,Wait_Time,sql_handle from sys.dm_exec_requests
    Where Wait_Time > 0

    Exact query can be retrieved by dbcc inputbuffer (blocking_session_id) Or sys.dm_exec_sql_text (TF).
    By combining with sys.dm_tran_locks,sys.dm_os_waiting_tasks we canfind Blockers and waiters (blocking and waiting tasks) .

    The following query can be used to find all blockers are waiters

    WITH blocking_info AS
    [blocker] = wait.blocking_session_id,
    [waiter] = lock.request_session_id,
    b_handle = br.[sql_handle],
    w_handle = wr.[sql_handle],
    [dbid] = lock.resource_database_id,
    duration = wait.wait_duration_ms / 1000,
    lock_type = lock.resource_type,
    lock_mode = block.request_mode
    sys.dm_tran_locks AS lock
    sys.dm_os_waiting_tasks AS wait
    ON lock.lock_owner_address = wait.resource_address
    sys.dm_exec_requests AS br
    ON wait.blocking_session_id = br.blocking_session_
    sys.dm_exec_requests AS wr
    ON lock.request_session_id = wr.session_id
    sys.dm_tran_locks AS block
    ON block.request_session_id = br.session_id
    block.request_owner_type = 'TRANSACTION'

    [database] = DB_NAME(bi.[dbid]),
    blocker_command = bt.[text],
    waiter_command = wt.[text],
    [duration MM:SS] = RTRIM(bi.duration / 60) + ':'
    + RIGHT('0' + RTRIM(bi.duration % 60), 2),
    blocking_info AS bi
    sys.dm_exec_sql_text(bi.b_handle) AS bt
    sys.dm_exec_sql_text(bi.b_handle) AS wt;

    3.0 Top Consumers

    The following query gives top resource intensive and resource consuming queries at a given time

    qs.total_worker_time/qs.execution_count as [Avg CPU Time],
    (case when qs.statement_end_offset = -1
    then len(convert(nvarchar(max), qt.text)) * 2
    else qs.statement_end_offset end -qs.statement_start_offset)/2)
    as query_text,
    qt.dbid, dbname=db_name(qt.dbid),
    FROM sys.dm_exec_query_stats qs
    cross apply sys.dm_exec_sql_text(qs.sql_handle) as qt
    cross apply sys.dm_exec_query_plan(qs.plan_handle) as qp
    [Avg CPU Time] DESC

    4.0 Causes for the High CPU or I/O

    The following are the root cause for high CPU utilization and high I/O

    • Excessive compilation/recompilation
        1. Query plan cannot be reused due to changes in:Schema, statistics, set options, temp table, WITH RECOMPILE declaration
    • Inefficient query plan
        1. Use DTA to check for index recommendations
        2. Check for bad cardinality estimates (Actual vs. Estimated)
        3. Use restrictive WHERE
        4. Ensure database statistics are keep up to date
        5. Watch for query constructs that may be a problem for cardinality estimate
    • Consider using query hints (last resort)
        1. OPTIMIZE FOR – particular parameter values for optimization
        2. FORCE ORDER – preserves join order
        3. USE PLAN – plan forcing
    • Intra-query parallelism
        1. Identify parallel queries and make sure they are efficient

    4.0 Index usage

    The following DMVs are useful in findign and anlysing index usage

    • Index Usage and Unused Indexes
      • Sys.dm_db_index_usage_stats
    • Index Access, Blocks, Contention e.g. waits
      • Sys.dm_db_index_operational_stats()
      • Sys.dm_db_index_physical_stats()
    • Missing indexes
      • Sys.dm_db_missing_index_group_stats
      • Sys.dm_db_missing_index_groups
      • Sys.dm_db_missing_index_details
      • Sys.dm_exec_query_plan(plan_handle) - Look for

    Having index won’t help always some indexes may not be used at all. When indexes are not used by Query they are overheads as they slow down inserts / updates. The following query used to find the index usage

    declare @dbid int
    select @dbid = db_id()
    select objectname=object_name(s.object_id), s.object_id,, i.index_id
    , user_seeks, user_scans, user_lookups, user_updates
    from sys.dm_db_index_usage_stats s,
    sys.indexes i
    where database_id = @dbid and objectproperty(s.object_id,'IsUserTable') = 1
    and i.object_id = s.object_id
    and i.index_id = s.index_id
    order by (user_seeks + user_scans + user_lookups + user_updates) asc

    4.1 Best practices for Index

    • Avoid long (or wide) custered index key if table has nonclustered (N/C) indexes
      • Leaf of Nonclustered index uses the clustered index key (primary key) to locate the data row,Since a wide clustered index key increases size of N/C, (covered) nonclustered range scans results in more IO

    • Clustered index benefits
      • high volume lookups (avoids RID lookups)
      • Range scans – access to entire data row
    • Non-clustered index benefits
      • Query covering
      • Can be used to avoid sorts
    ... We will dwell into more in Part2..
    Please write your comments to improve this blog.

    1 comment:

    1. This is very nice article just waiting for Part 2. Please be more descriptive, give more examples