1 / 29

SQL Server Performance Tools

SQL Server Performance Tools. Cindy Gross, Microsoft Dedicated Support Engineer and Microsoft Certified Master (MCM) – SQL Server 2008 http:// blogs.msdn.com/cindygross. Cindy Gross Dedicated Support Engineer Microsoft’s Premier Field Engineering Team Boise, ID

taite
Download Presentation

SQL Server Performance Tools

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. SQL Server Performance Tools Cindy Gross, Microsoft Dedicated Support Engineer and Microsoft Certified Master (MCM) – SQL Server 2008 http://blogs.msdn.com/cindygross

  2. Cindy Gross Dedicated Support Engineer Microsoft’s Premier Field Engineering Team Boise, ID Microsoft Certified Master : SQL Server Cindy.Gross@Microsoft.com http://blogs.msdn.com/cindygross

  3. The Microsoft Certified Master program for SQL Server is the deepest technical certification available from Microsoft, validating and recognizing the top SQL Server talent in the world • To achieve this certification, candidates must pass the following two exams in the following sequence: • Exam 88-970: “SQL Server 2008 Microsoft Certified Master: Knowledge Exam” (4 hours) • Exam 88-971: “SQL Server 2008 Microsoft Certified Master: Lab Exam” (6 hours) Designed for the real-world demands of top-tier SQL Server professionals, find out more about the new program requirements for MCM: SQL Server 2008 by visiting: http://www.microsoft.com/learning/en/us/certification/master-sql-path.aspx

  4. Performance Tools - Agenda Methodology SQLDiag / PSSDiag SQLNexus Other Tools References

  5. Methodology per SQL Server 2008 Internals and Troubleshooting Know your SLAs Define the problem and exit criteria Understand baselines Gather data Analyze data Test potential solutions Post Mortem Refine solution Update standards/best practices, apply to other systems

  6. Gather Data

  7. Why Gather Data? Save baselines Compare performance before and after coding, index, or hardware changes Save off SQL Server configuration information Gather information to troubleshoot various problems – SQLDiag is good for “something is slow” type issues

  8. SQLDiag / PSSDiag Myths It’s too heavy It doesn’t capture what I need Just use the individual tools SQLDiag/PSSDiag are totally different

  9. SQLDiag Ships with SQL Server Can be made into a service and/or scheduled With /X gathers a snapshot: evt/err logs, configs Else gathers snapshot + PerfMon + Profiler Controlled by XML files – edit with true/false<EventlogCollector enabled="false" startup="false" shutdown="true" />

  10. PSSDiag Wrapper around SQLDiag + Custom Diags Examples: DMV information, clone db, msdbbackup, PerfStats Controlled by the same XML file as SQLDiag Usually has custom collectors bundled with it

  11. Custom Collectors Example from PerfStats download: SQLDiagPerfStats_Detailed_Trace2008.XML <CustomDiagnostics> <CustomGroup name="SQL 2008 Perf Stats" enabled="true" /> <CustomTask enabled="true" groupname="SQL 2008 Perf Stats" taskname="SQL 2008 Perf Stats Script" type="TSQL_Script" point="Startup" wait="No" cmd="SQL_2008_Perf_Stats.sql" pollinginterval="0" />

  12. SQLDiagConfiguration Demo Open dos-prompt with “run as administrator” Example location: C:\Program Files\Microsoft SQL Server\100\Tools\Binn Run with /X for snapshot and to generate XML files Review SD_Detailed.XML Edit and save to a new file, pass with /I to SQLDiag SQLDiag creates a server-side Profiler trace

  13. Best Practices – Gathering Data

  14. Best Practices - Planning Know your goal If collecting for a long time, collect fewer/less detailed events Be prepared for multiple iterations, get more detailed once you narrow it down

  15. Best Practices - Output • Few (one) traces at a time. • Avoid the GUI, SQLDiag uses server side. 929728 • Trace to local disk with write cache, never to a network share.  307786 • Never trace to UNC path, even if UNC points to local disk. • Use fastest available volume not already used by data or log files. Avoid RAID-5 (write intensive). • Whether the trace impacts instance perf can depend on disk speed.  • Test in QA environment or during non-peak hours.

  16. Best Practices - Collection • Avoid high frequency events like Object:Opened, Lock:Acquired/Released, etc. • Default to batch-level, not statement-level. • Filtering reduces file size and I/O cost of tracing but increases CPU cost. Try to filter on integer column (dbid, duration, etc) instead of a text column (database name, textdata, etc) and only when filter removes >10% of events.

  17. Best Practices - Collection • No real value in eliminating rare but high-value events (e.g. Hash Warning, Exception, Data File Auto Grow, etc). • Events that greatly impact trace size:SQL:StmtCompletedSP:StmtCompleted      Degree of Parallelism Lock:Timeout      Show Plan Statistics SQL:BatchStartingRPC:Starting

  18. Best Practices – Performance Events • All the Showplan events have high overhead and should be used only for a short time • Actual plans for most uses: ShowplanStatistics Profile or Showplan XML Statistics Profile • Estimated plans for when queries never seem to complete: Showplan All or Showplan XML • Text versions are subsets: no rows, execs, etc. • Non-XML will be deprecated

  19. PerfMon – Basic Counters • Logical disk • Avg disk sec/read < 10-20ms, log sec/write < 3-5ms • Disk reads/sec, Disk writes/sec • Memory - Available MBytes • MSSQL Buffer Manager - Page Life Expectancy • MSSQL Databases - Active Transactions, Backup/Restore Throughput/sec, Repl. Pending Xacts, Repl. Trans. Rate, Transactions/sec • MSSQL Memory Manager - Total Server Memory, Target Server Memory • MSSQL Plan Cache - Cache Hit Ratio (all instances) • MSSQL Wait Statistics - (all) • Process - % Processor Time (all instances) • Processor - % Processor Time (all instances)

  20. Analyze Data

  21. SQL Nexus • Download Nexus from Codeplex.com • Install RML tools • Optimize your load: Pre-size your Nexus db, use simple recovery, set autogrow ALTER DATABASE [NexusAdvWorks] SET RECOVERY SIMPLE WITH NO_WAIT ALTER DATABASE [NexusAdvWorks] MODIFY FILE ( NAME = N'NexusAdvWorks', SIZE = 100MB , FILEGROWTH = 10MB ) ALTER DATABASE [NexusAdvWorks] MODIFY FILE ( NAME = N'NexusAdvWorks_log', SIZE = 100MB ) • Import your SQLDiag + PerfStats output • Analyze your Data • Repeat

  22. SQL Nexus Demo Show import options Review available reports Narrow down the problem Find your bottlenecks and worst performing queries Gather more detailed data for problem areas

  23. Additional Data Gathering

  24. Profiler Demo Show Showplan output options Show Export feature

  25. XEvents New in 2008, SSMS GUI in Denali Lighter weight than SQL Profiler / SQL Trace Using SQL Server Extended Events http://blogs.msdn.com/b/extended_events/

  26. Other Tools Performance Analysis of Logs (PAL) RML Utilities– Readtrace, Ostress, ORCA SQLIO – disk subsystem performance SQLIOSim – data correctness, ships with SQL 2008+ StorPort tracing enhancements

  27. References Waits and Queues White Paper PerfStats Download SQLDiag Parameters/Usage

  28. Professional Association for SQL Server Thank you to our sponsor

  29. Save 25%: Register by April 12th www.sqlpass.org/sqlrally May 11-13, Orlando, FL Register by March31st: save 40% and have the chance to win a cruise to Alaska! “24HR11” code gets you $100 off www.sqlpass.org/summit Oct 11-14, Seattle, WA

More Related