Praveen Srvatsa

1
Top 10 DMVs SQL DBAs must know

• Praveen Srvatsa
• Director AsthraSoft Consulting
• Microsoft Regional Director, Bangalore
• Microsoft MVP, ASP.NET
• http//blogs.asthrasoft.com/praveens
  praveens_at_asthrasoft.com

2
Agenda

• Methodology before SQL2005
• DMV overview
• Diagnosing and Troubleshooting using DMVs

3
Troubleshooting Before SQL2005

• Perfmon
• SQL Trace
• DBCC commands (e.g. dbcc checkalloc)
• Tools like ITW
• Some system tables like sysprocesses and stored
  procs like sp_lock
• Physical Dump and DScript

4
Scenarios in SQL2000

• My application is running slow
• No easy ways to identify which query is running
  slow or taking most resources. Need to enable the
  profiler.
• Use Set Statistics Profile and run the query
• Provides query plan and statistics info
• Issue
• Overhead of running profiler
• Problems not always reproducible (e.g. query plan
  can change)

5
Scenarios .. cont

• My SQL Server is not responding
• Reboot. You loose the context and risk the
  problem re-occurrence
• Take a physical dump. Send it PSS
• PSS may run dscript to identify the problem
• Issues
• Customer dont always want to send the dump

6
Scenarios .. cont

• My work load is very adhoc
• How do I know which indexes are used and which
  are missing?
• How do I know which indexes are used
  occasionally?
• Issue
• ITW but limited to well defined workload. Has
  higher overhead.

7
Scenarios .. cont

• How is my tempdb is used
• Hard to determine which sessions or tasks are
  taking up the tempdb space
• You can potentially analyze query plans and see
  which queries are creating objects in tempdb

8
What is Dynamic Management View (DMV)

• Expose server state in queryable format
• State is generally in memory (not persisted)
• Not new. DMVs in SQL2000 (sysprocesses)
• Low overhead
• Many DMVs expose information that needs to be
  maintained anyway
• Whats new for Yukon?
• Many more DMVs and a new framework

9
What is DMV cont

• Example
• Sys.dm_os_scheduler
• Sys.dm_tran_active_transactions
• Sys.dm_exec_query_stats
• sys.dm_exec_requests
• Sys.dm_db_index_usage_statistics

10
DMV Architecture

• In-memory structures exposed as relational
  dataset
• No indexes
• No push-down of predicates
• Low overhead of running DMVs concurrently

11
Troubleshooting in SQL2005

• Perfmon
• SQL Trace
• DBCC
• DMVs
• DAC
• DTA
• Physical Dump and DScript

12
PSS Scenario Example

• SQL2000
• Server hangs ? Customer calls PSS
• If they get a good PSS engineer, customer is told
  to capture dump and send to MS. Some customers
  refuse to use debugger.
• No guarantee large dump file will successfully
  make it to MS even less likely with 64-bit
  machines
• PSS analyzes dump with SSDF or debugger
• SQL2005
• Server hangs ? Customer calls PSS
• PSS engineer sends customer prepackaged scripts
  and instructs to run using DAC and send results
  to MS
• PSS engineer compares results with known cases
  possibly giving customer fix immediately

13
DMVs vs User Dumps

• Dump Pains
• Debugger not included on Windows CDs, therefore
  customers dont consider it certified.
• Support contracts with hardware vendors may not
  allow them to install products such as debuggers
  independently.
• Dumps can take 5-10min to create and process is
  frozen during that time (this would also cause a
  cluster failover)
• Dont want downtime at all
• International file transfers can take ½ day or
  longer for userdumps
• Data exposure
• 64-bit dumps are just too large
• In SQL2005, query SQL Server state using DMVs

14
Troubleshooting agenda

• Resource bottlenecks detection, causes and
  resolution, followed by a demo
• CPU
• Memory
• TempDB
• Query running slow scenario

15
TroubleshootingGeneral strategy

• Bottleneck major factor that affects
  performance
• Know when to start looking define problem
• What is your baseline?
• What has changed?
• You will always get rid of one bottleneck to find
  another
• The point of diminishing returns
• Know when to stop define your goals
• Be aware of the system limits
• Running near capacity vs. inefficient use of a
  resource

16
Resource Bottleneck CPUOverall usage detection

• Problem unexpectedly high CPU usage and low
  throughput
• Tools and metrics
• System Monitor Processor object, Processor
  Time counter 80
• Task Manager ? Performance ? CPU Usage
• SQL Server sys.dm_os_schedulers DMV,
  runnable_tasks_count is high
• SQL Server sys.dm_exec_query_stats DMV,
  statistics of currently cached batches/stored
  procedures total_worker_time, execution_count

17
Resource Bottleneck CPUPotential causes

• Excessive compilation/recompilation
• Goal identify excessive recompilation and reduce
  it
• Inefficient query plan
• Goal take steps to write queries with efficient
  plans
• Intra-query parallelism
• Goal identify parallel queries and make sure
  they are efficient

18
Resource Bottleneck CPUExcessive
compilation/recompilation

• Recompilation is CPU intensive. Query plan
  cannot be reused due to changes in
• Schema, statistics, set options, temp table, WITH
  RECOMPILE declaration
• PerfMon SQL Server SQL Statistics object
• Batch Requests/sec SQL Compilations/sec SQL
  Re-Compilations/sec low ratio of recompiles to
  requests
• SQL Trace SPRecompile, SQLStmtRecompile
• DMVs
• sys.dm_exec_query_optimizer_info, optimizations
  and elapsed time counters
• Sys.dm_exec_query_stats, plan_generation_num and
  execution_count and text

19
Resource Bottleneck CPUExcessive
compilation/recompilation

• Solutions (use SQL Trace EventSubClass)
• Monitor set option changes, avoid them in stored
  procedures
• Consider temp tables vs. table variables, KEEP
  PLAN hint
• KEEPFIXED PLAN hint
• Consider automatic statistics update OFF vs. ON
• Use qualified object names (dbo.TableA vs.
  TableA)
• Do not mix DDL/DML statements
• Use DTA create missing indexes to improve
  compile time
• Consider RECOMPILE hint inside stored procedure

20
Resource Bottleneck CPUInefficient query plan

• Finding CPU bound query plans
• DMVs
• sys.dm_exec_query_stats, sys.dm_exec_sql_text
  find CPU intensive queries
• sys.dm_exec_cached_plans look for CPU bound
  operators sort, hash match

21
Resource Bottleneck CPUInefficient query plan

• Solutions
• Use DTA to check for index recommendations
• Check for bad cardinality estimates
• Use restrictive WHERE
• Keep data statistics up to date
• Watch for query constructs that may be a problem
  for cardinality estimate
• Consider using query hints
• OPTIMIZE FOR particular parameter values for
  optimization
• FORCE ORDER preserves join order
• USE PLAN plan forcing

22
Resource Bottleneck CPUIntra-query parallelism

• Queries that run in parallel are expensive
• DMVs
• sys.dm_exec_requests, sys.dm_os_tasks,
  sys.dm_exec_sessions, sys.dm_exec_sql_text,
  sys.dm_exec_cached_plan
• sys.dm_exec_query_stats for total_worker_time
  total_elapsed_time
• SQL Trace RPCCompleted with CPU Duration
• Showplans with Parallelism operators

23
Resource Bottleneck CPUIntra-query parallelism

• Solutions, similar to steps of inefficient query
  plan
• Use DTA
• Keep statistics up to date
• Check for missing statistics
• Check cardinality estimates
• Evaluate if the query can be rewritten
  efficiently in T-SQL

24
Resource Bottleneck MemorySigns of the problem

• Explicit memory-related errors (e.g. out of
  memory, timeout while waiting for memory
  resource)
• Low page life expectancy, low buffer cache hit
  ratio
• I/O utilization is higher than usual
• Overall system slow behavior
• Goal analyze memory consumption, find and
  eliminate offenders (if possible)

25
Resource Bottleneck MemoryMemory related errors

• 701 - There is insufficient system memory to run
  this query
• 802 - There is insufficient memory available in
  the buffer pool
• 8628 - A time out occurred while waiting to
  optimize the query. Rerun the query
• 8645 - A time out occurred while waiting for
  memory resources to execute the query. Rerun the
  query
• 8651 - Could not perform the requested operation
  because the minimum query memory is not
  available. Decrease the configured value for the
  'min memory per query' server configuration option

26
Resource Bottleneck MemoryMemory support
27
Resource Bottleneck MemoryMemory pressures and
causes

• Differentiate
• Internal vs. external
• Physical vs. virtual
• When occurs

28
Resource Bottleneck MemoryDetection and
analysis part I

• Task Manager
• Mem usage, VM Size
• Physical Memory, Commit charge (PF usage)
• PerfMon
• Process object Working set, Private bytes
• Memory object Available KBytes, System Cache
  Resident Bytes, Committed bytes, Commit Limit
• SQL Server Buffer Manager object
• Buffer cache hit ratio, Page life expectancy,
  Checkpoint pages/sec, Lazy writes/sec

29
Resource Bottleneck MemoryDetection and
analysis part II

• DMVs
• sys.dm_os_memory_clerks
• sys.dm_os_memory_cache_clock_hands
• sys.dm_os_memory_cache_counters
• sys.dm_os_ring_buffers
• sys.dm_os_virtual_address_dump
• DBCC MEMORYSTATUS
• Buffer distribution buffer counts global memory
  objects query memory objects gateways

30
Resource Bottleneck MemoryGeneral steps to
resolution

• Identify external pressure and take care of it
  first
• Verify server memory configuration parameters
  (unusual/inconsistent settings)
• Min memory per query min/max server memory awe
  enabled
• Lock pages in memory privilege
• Take successive snapshots of DMVs/DBCC
  MEMORYSTATUS and collect perf. counters for
  further analysis (ideally compare to a baseline)
• Check workload (number of queries/sessions)
• Understand reason for increased memory
  consumption and try to eliminate it (not always
  possible) it may be normal

31
Resource Bottleneck I/OGeneral information

• Major contributors of I/O activity
• Moving database pages between memory and disk
• Log file operations
• TempDB operations
• Signs of the problem slow response time, timeout
  error messages, I/O subsystem operates at its max
  capacity
• Goal identify I/O bound bottleneck

32
Resource Bottleneck I/ODetection

• PerfMon Physical Disk object
• Disk Time 50
• Avg. Disk Queue Length 2
• Avg. Disc sec/Read or Avg. Disc sec/Write 10-20
  ms
• Avg. Disk Reads/sec or Avg. Disk Writes/sec 85
  of disk capacity
• Adjust for RAID
• Raid 0 I/Os per disk (reads writes) / number
  of disks
• Raid 1 I/Os per disk reads (2 writes) /
  2
• Raid 5 I/Os per disk reads (4 writes) /
  number of disks
• Raid 10 I/Os per disk reads (2 writes) /
  number of disks
• DMVs
• sys.dm_os_wait_stats for wait_type like
  PAGEIOLATCH
• sys.dm_io_pendion_io_requests with
  sys.dm_io_virtual_file_stats
• sys.dm_exec_query_stats _reads, _writes columns

33
Resource Bottleneck I/OAnalysis and resolution

• Find I/O bound queries
• Verify that they use optimal plans
• Possibly rewrite follow inefficient query plan
  guidelines
• High I/O may indicate a memory bottleneck
• Check for memory pressure and consider adding
  memory
• Increase I/O bandwidth
• Faster drives, faster controllers with more cache
• Be aware of the system capacity

34
Resource Bottleneck TempDBGeneral information

• TempDB use
• Explicitly created user objects
• SQL Server created internal objects
• Features using version store MARS, online index,
  triggers and snapshot based isolation levels
• Problems
• Running out of TempDB space
• Bottleneck in system tables due to excessive DDL
  operations
• Allocation contention
• Goal monitor space usage/excessive DDL, find and
  possibly eliminate offenders

35
Resource Bottleneck TempDBMonitoring space

• DMVs
• sys.dm_db_file_space_usage (user, internal
  objects and version store sizes)
• sys.dm_tran_active_snapshot_database_transactions
  (longest running transaction ? most row version
  space)
• sys.dm_db_session_space_usage (accounted at the
  end of a task)
• sys.dm_db_task_space_usage
• PerfMon
• SQL Server Transactions object
• Version Generation/Cleanup rates

36
Resource Bottleneck TempDBResolution

• TempDB capacity planning
• Account for new features that use TempDB
• Preallocate space for TempDB
• Many TempDB files of equal size ( CPUs) to
  reduce contention
• User objects identify and eliminate offenders
  if possible
• Version store
• Eliminate longest transactions
• Account for size 2 version store data
  generated per min longest runtime of the
  transaction
• Excessive DDL
• Consider where temp tables are created
• Consider query plans that create many internal
  temp objects and verify if they are efficiently
  written, rewrite as needed

37
Slow Running Query General information

• Sources of the problem
• May be waiting for logical locks (which is
  normal)
• Resource bottlenecks as considered earlier
• Blocking due to (at least the following)
• Poor application design (poor concurrency)
• Bad query plans
• Missing indexes
• Improper server configuration
• Goal identifying blockers and long blocks
  objects that are waited on analyzing waits

38
Slow Running Query Detection

• DMVs
• sys.dm_os_wait_stats (overall wait statistics)
• sys.dm_os_waiting_tasks (session/task specific)
• sys.dm_tran_locks (currently active lock manager
  resources)
• sys.db_index_operational_stats (advanced index
  usage stats, including blocking)
• sys.dm_index_usage_stats (efficient index usage
  identifying dead indexes)
• SQL Trace/Profiler (for long blocks)
• Errors and Warnings Blocked process report (with
  sp_configure blocked process threshold)

39
Slow Running Query Resolution

• After analyzing blockers, sources of long blocks
  and waits consider
• Is application design efficient in terms of
  concurrency?
• Are there any dead/poor indexes (overhead of
  maintaining) or missing indexes (unnecessary
  scans)?
• Is server configured properly?
• Are there any resource bottlenecks?

40
Session Summary

• DMV Advantages
• Ease of use
• Not intrusive
• Quick diagnostics
• You can poll the DMVs and can mine for problems
• Evolution of DMVs need to account for that

41
Next Steps

• Read about DMVs in Books Onlinehttp//go.microsof
  t.com/fwlink/?LinkId44375
• Read about Recompilation and Plan
  Cachinghttp//www.microsoft.com/technet/prodtechn
  ol/sql/2005/recomp.mspx
• Troubleshooting performance problems whitepaper
  on TechNet ? SQL ? Technologies ? Database Engine
  ? Operationshttp//www.microsoft.com/technet/prod
  technol/sql/2005/dbengine.mspx

42
Where Can I Get Help?

• Attend a free chat or webcast
• www.microsoft.com/technet/community/chats
• www.microsoft.com/technet/community/webcasts
• List of newsgroups
• www.microsoft.com/technet/community/newsgroups
• Microsoft community sites
• www.microsoft.com/technet/community
• Community events
• www.microsoft.com/technet/community/events
• Community columns
• www.microsoft.com/technet/community/columns

43
Feedback / QnA

• Your Feedback is Important!
• Please take a few moments to fill out our online
  feedback form
• For detailed feedback, use the form at
  http//www.connectwithlife.co.in/vtd/helpdesk.aspx
  
• Or email us at vtd_at_microsoft.com
• Use the Question Manager on LiveMeeting to ask
  your questions now!

44
Contact (optional slide)

• Blog Address
• blogs.asthrasoft.com/praveens
• Email Address
• praveens_at_asthrasoft.com

45
(No Transcript)