1 / 45

“Perfmon and Profiler 101 - Performance Tuning Secrets Revealed!”

Explore the world of performance tuning with Brent Ozar, a former SQL DBA and expert in SQL Server from Quest Software. Learn about tools such as Perfmon for capturing metrics, trace analysis with Profiler, and effective mitigation strategies. Discover how to interpret metrics, analyze trace data, and implement solutions to optimize system performance. Uncover performance issues, mitigate problems, and fine-tune your SQL Server environment for optimal efficiency.

mcwhirter
Download Presentation

“Perfmon and Profiler 101 - Performance Tuning Secrets Revealed!”

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. Perfmon and Profiler 101

  2. About Me: Brent Ozar • SQL Server Expert for Quest Software • Former SQL DBA • Managed >80tb SAN, VMware • Dot-com-crash experience • Specializes in performance tuning

  3. Today’s Agenda • The Honda Civic and the Nissan GT-R • Metrics, Trace, Mitigations • A New Secret Weapon • Resources and Q&A

  4. If You Don’t Need to Go Fast… From http://flickr.com/photos/stevekeys/2755142278/

  5. But The Faster You Want To Go

  6. The More You Have To Measure

  7. Windows “Check Engine” Light

  8. And If You Wanna Go Fast:

  9. Two Approaches to Detection • Exceptions Monitoring: Check Engine • Proactive Monitoring: Detailed Gauges

  10. Where Do We Start?

  11. Capture Metrics With Perfmon • Performance Monitor, aka Perfmon • Ships with all Windows versions • Polls any server from your desktop • Pulls performance metrics • Writes them to a file • Requires some OS permissions • Does not include alerts or analytics

  12. 11

  13. Memory Counters • Memory – Available Mbytes • Paging File - % Usage • SQLServer:Buffer Manager – • Buffer cache hit ratio • Page life expectancy • SQLServer:Memory Manager – Memory Grants Pending

  14. Storage Metrics: Physical Disk • % Disk Time • Avg. Disk Queue Length • Avg. Disk sec/Read • Avg. Disk sec/Write • Disk Reads/sec • Disk Writes/sec

  15. CPU Metrics • Processor - % Processor Time • System – Processor Queue Length • SQLServer:General Statistics – User Connections (not CPU, just “other”)

  16. The Raw Output: CSV Files 16

  17. Adding Analytical Formulas 17

  18. That’s a Lot of Zeroes! 18

  19. Sorting High to Low 19

  20. What To Look For, In Order • System – Processor Queue Length • Memory – Available Mbytes • Lock pages in memory!

  21. What To Look For Next • Disk metrics on the page file drive • Disk metrics on the log file drive • Disk metrics on the data file drive • Disk metrics on the TempDB drive

  22. Got Everything on One Drive? • Narrow it down with the DMV sys.dm_io_virtual_file_stats

  23. What If We Could Data Mine It?

  24. Table Analysis Tools For The Cloud

  25. Detecting Categories of Load

  26. Capture Queries with a Trace

  27. Columns to Capture What’s Going On What The Impact Was CPU Reads Writes Duration Start Time End Time • Text Data • DatabaseID and/or DatabaseName • Login Name • Host Name • Application Name

  28. Profiler’s Results: A Trace Table

  29. Order By Duration Descending

  30. Casting and Grouping

  31. Another Cloud Analytics Candidate

  32. Another Way: Perf Dashboard

  33. Correlate Metrics & Trace • Show a cause and effect relationship • Fields to mentally “join” on: • Date/Time ranges • CPU • Reads/Writes • Duration

  34. Sample Problem #1 • Metrics tell us: • Very high disk queue lengths on data drive • Trace tells us: • Report queries doing table scans w/o indexes • Many scheduled reports run simultaneously

  35. Ways We Can Mitigate It • Add covering indexes • Modify existing indexes • Run reports serially, not all at once • Add hard drives to the data file array • Add memory to cache scanned tables

  36. Sample Problem #2 • Metrics tell us: • Page file drive queue lengths average >20 • Page file use averages >1% • Available memory averages <200mb • Buffer cache hit ratio andpage life expectancy are high • Trace tells us: • No unusual queries

  37. Memory Configuration

  38. Ways We Can Mitigate It • Add memory and enable AWE/PAE • Add memory and upgrade to 64-bit • Reduce SQL’s min/max memory sizes • Move the app to its own server

  39. Sample Problem #3 • Metric looks OK, but every 15 minutes: • Long drive queues on the log file drive • Page life expectancy drops near zero • Network traffic jumps • Trace tells us: • Transaction log backups are running

  40. Ways We Can Mitigate It • Stop doing log backups • Put the databases in simple mode • Add drives to the transaction log array • Throttle the transaction log backups

  41. Sample Problem #4 • Metrics tell us: • CPU average is high • Disk, memory look OK • Trace tells us: • Queries are using cursors • Operating on individual records, not sets

  42. How We Can Mitigate It • Change cursor to set-based query • Buy really fast processors • Spend a lot on licensing

  43. Wrapping Things Up • Double-check the event log first • Don’t get overwhelmed: focus with the Metric – Trace – Mitigation process • Show a clear cause and effect • Use cloud-based BI to get an edge

  44. Resources On The Web • My posts about Perfmon and analytics:www.BrentOzar.com/perfmonwww.BrentOzar.com/perfmoncloud • Excel Table Analysis Tools for the Cloud:www.SQLServerDataMining.com/cloud • SQL Server community:SQLServerPedia.com

More Related