際際滷

際際滷Share a Scribd company logo

How To Write A SQL Server Performance Review

Download as PPTX, PDF
Quest Software
Quest Software

Learn how to write a report that managers and developers will love. Use Perfmon and Profiler to document your application's bottlenecks, and turn those into a reader-friendly report.

Convert to study guideBETA

Transform any presentation into a summarized study guide, highlighting the most important points and key insights.

1 of 35
Downloaded 140 times
How to Write a SQL Server Performance Review Brent Ozar, SQL Server Expert for Quest Software http://flickr.com/photos/sean002/2510541359/ Photo Licensed with Creative Commons Copyright 息 2007 Quest Software
What The Heck Do I Know? SQL Server Expert for Quest Software Former SQL DBA, SAN admin, VMware admin Recovering ASP coder Specializes in performance tuning Came up through management, not IT
Todays Agenda The Deliverable Gathering The Data Finding the Problems Mitigating the Problems Writing the Report Resources and Q&A
The Deliverable: A Report
Key Parts of Our Review Capture objective, definitive and credible metrics Analyze problems causing bad metrics List of ways we could mitigate each problem
Definitive Metrics Start with Perfmon: www.BrentOzar.com/perfmon Gather for longest time possible Check results daily to refine metrics
Tracing the Problems Focus the trace on the problem statistics (CPU, reads, writes) Filter duration > 2000ms at first Refine duration filter down over time Put results in a different SQL Server Very fast server, faster than what youre tracing Write into a simple mode database, no backups
Querying the Trace Table
ORDER BY Duration DESC
Casting and Grouping
Correlate the Metrics & Queries Demonstrate good investigation skills Show a cause and effect relationship Fields to mentally join on: Date/Time ranges CPU Reads/Writes Duration
DO NOT FIX ANYTHING YET! No matter how easy it looks No matter whose fault it is You have to show fruits from your labor We want clear before & after snapshots Worst thing we can tell our bosses: Its OK now. I didnt change much.
Mitigations For Each Problem Photo From:http://flickr.com/photos/sneakums/1345233680/
A Problem Has Many Mitigations EVERY problem has multiple choices: Money Time Manpower Go out of your way to identify all of them
Explain Mitigations That Suck Shows you know about the option May come in handy for another problem Explain why it doesnt work in this case Displays wise decision-making The PM may know something you dont
Be Willing To Implement Them Photo From: http://flickr.com/photos/ninjapoodles/1130650313/
Sample Problem Metric: Very high disk queue lengths on data file drive Trace tells us: Report queries doing table scans without indexes Many scheduled reports run simultaneously
Ways We Can Mitigate It Add indexes Run reports serially, not all at once Add hard drives to the data file array Add memory to cache scanned tables
Percent vs Order of Magnitude Incremental (Percentage): Any improvement from 1% to 100%. Order of Magnitude: Any improvement over 100% faster. How to explain it in your doc: This mitigation should give an incremental (percentage) improvement. This mitigation should give an order of magnitude improvement.
How To Explain It To Readers In this performance review, I classify mitigations as giving us a Percentage Improvement (meaning 1- 100% faster) or an Order of Magnitude Improvement (meaning over 100% faster). In addition, because were talking about making multiple improvements simultaneously across different parts of the app and server, its hard to give exact numbers when one improvement might impact another area. A new index or server configuration change might improve performance for more than just one query or process.
This Is How DBAs Write:
Use A Different Way of Writing Be friendly and upbeat ideas at: http://delicious.com/brento/powerpoint Document like a consultant Write like youre an outsider looking in Create clear, concise steps that managers can assign to people
Dont Point Fingers
Goal: Progress, Not Debating
Moving Your Career Forward
Your Target Reader Reads Fortune, not SQL Magazine Doesnt understand indexes or locks Wants answers, not questions Only reads the section headers
The Executive Summary
How To Write A SQL Server Performance Review
(Bunch of stuff here clipped out)
How To Write A SQL Server Performance Review
How To Write A SQL Server Performance Review
How To Write A SQL Server Performance Review
How To Write A SQL Server Performance Review
Wrapping It Up Gather data with Profiler &Perfmon Analyze the queries like its BI Make a list of mitigation options Write an upbeat, positive report Aim for forwards, not replies Save your reports for raise time!
My Resources, Your Questions My blog about SQL and performance: www.BrentOzar.com www.BrentOzar.com/perfreport My bookmarks: Delicious.com/brento SQL Server community w/expert Q&A: SQLServerPedia.com Performance tools for a second opinion: www.Quest.com

More Related Content

How To Write A SQL Server Performance Review

  • 1. How to Write a SQL Server Performance Review Brent Ozar, SQL Server Expert for Quest Software http://flickr.com/photos/sean002/2510541359/ Photo Licensed with Creative Commons Copyright 息 2007 Quest Software
  • 2. What The Heck Do I Know? SQL Server Expert for Quest Software Former SQL DBA, SAN admin, VMware admin Recovering ASP coder Specializes in performance tuning Came up through management, not IT
  • 3. Todays Agenda The Deliverable Gathering The Data Finding the Problems Mitigating the Problems Writing the Report Resources and Q&A
  • 5. Key Parts of Our Review Capture objective, definitive and credible metrics Analyze problems causing bad metrics List of ways we could mitigate each problem
  • 6. Definitive Metrics Start with Perfmon: www.BrentOzar.com/perfmon Gather for longest time possible Check results daily to refine metrics
  • 7. Tracing the Problems Focus the trace on the problem statistics (CPU, reads, writes) Filter duration > 2000ms at first Refine duration filter down over time Put results in a different SQL Server Very fast server, faster than what youre tracing Write into a simple mode database, no backups
  • 11. Correlate the Metrics & Queries Demonstrate good investigation skills Show a cause and effect relationship Fields to mentally join on: Date/Time ranges CPU Reads/Writes Duration
  • 12. DO NOT FIX ANYTHING YET! No matter how easy it looks No matter whose fault it is You have to show fruits from your labor We want clear before & after snapshots Worst thing we can tell our bosses: Its OK now. I didnt change much.
  • 13. Mitigations For Each Problem Photo From:http://flickr.com/photos/sneakums/1345233680/
  • 14. A Problem Has Many Mitigations EVERY problem has multiple choices: Money Time Manpower Go out of your way to identify all of them
  • 15. Explain Mitigations That Suck Shows you know about the option May come in handy for another problem Explain why it doesnt work in this case Displays wise decision-making The PM may know something you dont
  • 16. Be Willing To Implement Them Photo From: http://flickr.com/photos/ninjapoodles/1130650313/
  • 17. Sample Problem Metric: Very high disk queue lengths on data file drive Trace tells us: Report queries doing table scans without indexes Many scheduled reports run simultaneously
  • 18. Ways We Can Mitigate It Add indexes Run reports serially, not all at once Add hard drives to the data file array Add memory to cache scanned tables
  • 19. Percent vs Order of Magnitude Incremental (Percentage): Any improvement from 1% to 100%. Order of Magnitude: Any improvement over 100% faster. How to explain it in your doc: This mitigation should give an incremental (percentage) improvement. This mitigation should give an order of magnitude improvement.
  • 20. How To Explain It To Readers In this performance review, I classify mitigations as giving us a Percentage Improvement (meaning 1- 100% faster) or an Order of Magnitude Improvement (meaning over 100% faster). In addition, because were talking about making multiple improvements simultaneously across different parts of the app and server, its hard to give exact numbers when one improvement might impact another area. A new index or server configuration change might improve performance for more than just one query or process.
  • 21. This Is How DBAs Write:
  • 22. Use A Different Way of Writing Be friendly and upbeat ideas at: http://delicious.com/brento/powerpoint Document like a consultant Write like youre an outsider looking in Create clear, concise steps that managers can assign to people
  • 26. Your Target Reader Reads Fortune, not SQL Magazine Doesnt understand indexes or locks Wants answers, not questions Only reads the section headers
  • 29. (Bunch of stuff here clipped out)
  • 34. Wrapping It Up Gather data with Profiler &Perfmon Analyze the queries like its BI Make a list of mitigation options Write an upbeat, positive report Aim for forwards, not replies Save your reports for raise time!
  • 35. My Resources, Your Questions My blog about SQL and performance: www.BrentOzar.com www.BrentOzar.com/perfreport My bookmarks: Delicious.com/brento SQL Server community w/expert Q&A: SQLServerPedia.com Performance tools for a second opinion: www.Quest.com