1 / 34

3 Methods to End the Madness

3 Methods to End the Madness. Using Profiler & XEvents to Diagnose Slowness Mindy Curnutt, Director - Database Architecture, TMW Systems. About Me. Wife & Mother Busy-Bee Musician Soap Making Baking Crocheting Business Owner Event Planning. About Me.

tbullock
Download Presentation

3 Methods to End the Madness

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. 3 Methods to End the Madness Using Profiler & XEvents to Diagnose Slowness Mindy Curnutt, Director - Database Architecture, TMW Systems

  2. About Me • Wife & Mother • Busy-Bee • Musician • Soap Making • Baking • Crocheting • Business Owner • Event Planning

  3. About Me Director, Database Architecture, TMW SQL Server MVP SQL Server since 6.5 / 1995 (20 Yrs) SQLPASS Speaker, 2005, 2012, 2013, 2015 PASS Program Manager 2015, 2016 Twitter: @sqlgirl Email: mindycurnutt@hotmail.com

  4. Agenda Overview Profiler or Extended Events? Long Running Queries A Thousand Paper Cuts The Sounds of Silence Summary

  5. Overview Profiler or Extended Events • When to use • How to Use 3 Methods • Long Running Queries • A Thousand Paper Cuts • The Sounds of Silence

  6. Agenda Overview Profiler or Extended Events? Long Running Queries A Thousand Paper Cuts The Sounds of Silence Summary

  7. Profiler Trace Less Than 200 Events Heavier than XEvents No New Events after SQL 2008 Deprecated by Microsoft Easier to Use prior to SQL 2012

  8. Extended Events Like Profiler's Older Brother (or Sister!) - Bigger, More Mature, More Versatile Lighter Weight Easy to use with SQL 2012+

  9. Events Captured by SQL Version

  10. Events to Track

  11. Events and Actions

  12. Agenda Overview Profiler or Extended Events? Long Running Queries A Thousand Paper Cuts The Sounds of Silence Summary

  13. Long Running Queries Resource Consumers Blockers Easy to Spot Low Hanging Fruit?

  14. Long Running Queries When Useful? • Occasional Slowness • Blocking Issues • Specific Application Area Slowness • No Budget for 3rd Party Tools • After the Fact Analysis • Need Drill Down (Stmt within Proc)

  15. Long Running Queries Events to Capture • RPC: Completed • SQL Batch: Completed • SP: Statement Completed Filter • Duration > X (example: 5 seconds*) Length of Trace • Long (Can run daily, all day) Performance Hit • Low

  16. Long Running Queries Profiler Demo • Capture • Review XEvents Demo • Capture • Review

  17. Agenda Overview Profiler or Extended Events? Long Running Queries A Thousand Paper Cuts The Sounds of Silence Summary

  18. A Thousand Paper Cuts Aka - Being Buried Alive by Pebbles! Problem: Many, Many Individually Quick Statements RBAR – “Row by Agonizing Row”

  19. A Thousand Paper Cuts Resource Consumers Takes Analysis to Spot Diagnosis may require causing temporary Performance Degradation Typical Causes: • A Data Access Layer writing SQL code • Non Set-Based SQL • Scalar Functions • Trigger that fire other Triggers

  20. A Thousand Paper Cuts When Useful? • Specific Application Area Slowness • Working Directly with User or QA • No Budget for 3rd Party Tools • Long Running Query Approach Comes Up Dry

  21. A Thousand Paper Cuts Events to Capture • RPC: Completed • SQL Batch: Completed Filter • Duration Filter = 0 (be careful!) • User Specific Item (spid, login name…) Length of Trace • Very Short Performance Hit • High

  22. A Thousand Paper Cuts Profiler Demo • Capture • Review XEvents Demo • Capture • Review

  23. Agenda Overview Profiler or Extended Events? Long Running Queries A Thousand Paper Cuts The Sounds of Silence Summary

  24. The Sounds of Silence Hello Darkness My Old FriendI’ve Come to Talk with You Again… Fools, said I, You do not knowSilence like a cancer grows.Hear my words that I might teach you.Take my arms that I might reach you.But my words like silent raindrops fellAnd echoed in the wells of silence

  25. The Sound of Silence • SQL not responsible for the • Slowness being experienced. • Problem: • User experience is poor • SQL utilization low • LRQ Traces show nothing • TPCs are minimal • Is something causing delaybetween SQL calls? • Waitstats will not show this

  26. The Sounds of Silence Takes Analysis to Spot Diagnosis may require causing temporary Performance Degradation Typical Causes: • WAN • Firmware • Overloaded Application Host • Application making a synchronous call to something else

  27. The Sounds of Silence When Useful? • Specific Application Area Slowness • Working Directly with User or QA • No Budget for 3rd Party Tools • Long Running Query Approach Comes Up Dry • Thousand Paper Cut Analysis Comes Up Dry • Waitstats look fine

  28. The Sounds of Silence Scenario 1: Latency

  29. The Sounds of Silence Scenario 2: Pauses within the Application

  30. The Sounds of Silence Events to Capture • RPC: Completed • SQL Batch: Completed Filter • Duration Filter = 0 (be careful!) • User Specific Item (spid, login name…) Length of Trace • Very Short Performance Hit • High

  31. The Sounds of Silence Profiler Demo • Review XEvents Demo • Review

  32. Agenda Overview Profiler or Extended Events? Long Running Queries A Thousand Paper Cuts The Sounds of Silence Summary

  33. Summary LRQ = Long Running QueryTPC = Thousand Paper CutsSOS = Sounds of Silence

  34. Questions? Mindy CurnuttDirector, Database ArchitectureTMW Systems Twitter: @sqlgirlEmail: mindycurnutt@hotmail.com

More Related