Praveen Srvatsa Director | AsthraSoft Consulting Microsoft Regional Director, Bangalore Microsoft MVP, ASP.NET

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@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 don’t 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 What’s 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 don’t 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) • Don’t 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 [Re]compilation 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: SP:Recompile, SQL:StmtRecompile 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: RPC:Completed 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.microsoft.com/fwlink/?LinkId=44375 Read about Recompilation and Plan Cachinghttp://www.microsoft.com/technet/prodtechnol/sql/2005/recomp.mspx ‘Troubleshooting performance problems’ whitepaper on TechNet  SQL  Technologies  Database Engine  Operationshttp://www.microsoft.com/technet/prodtechnol/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@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@asthrasoft.com