1 / 30

SQL Performance Management with SCOM 2007

Learn how to use SCOM 2007 to monitor and optimize SQL Server performance. Analyze performance counters, add performance rules, query and analyze performance data, and create custom reports.

sorlando
Download Presentation

SQL Performance Management with SCOM 2007

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. 16th April 2010 SQL Performance Management withSCOM 2007 Bob Duffy Database Architect Prodata SQL Centre of Excellence

  2. Speaker Profile – Bob Duffy • SQL Server MVP • MCA/MCM for SQL Server • 18 years in database sector, 250+ projects • Senior SQL Consultant with Microsoft 2005-2008 • Regular speaker for TechNet, MSDN, Users Groups, Irish and UK Technology Conferences • On MCM 2008 exam working group • Database Architect at Prodata SQL Centre Excellence, Dublin • SQL Geek ;-)

  3. What does SCOM do for SQL SQL Performance Counters Analysing performance in Ops Console Adding Performance Rules Querying/Analysing Performance Data Adding Custom Reports Performance Monitors and Alerts Questions Agenda

  4. What Does SCOM do for SQL

  5. SCOM Architecture “Nobody knows how to monitor Microsoft technology better than Microsoft, and Operations Manager provides us with that needed expertise in a form that’s ready to use.” • – Matthew O’Neill, Group Head of Distributed Systems, HSBC Global IT Operations

  6. Out of the Box Perf Reports

  7. Console based Views, and Dashboards(Out of the box stuff)

  8. Performance Counters • Seven Common Groups of Performance Counters • CPU • Memory • Storage • Buffer Pool • SQL Workload • Database Counters • Locking This is not a complete list, just a good list for a baseline and general performance monitoring/tuning (see references for more links)

  9. 1. CPU Counters

  10. 2. Memory Counters

  11. 3. Storage Counters

  12. 5. Buffer Pool

  13. 6. SQL Workload Tip: Use sp_user_counter1 to set a custom counter

  14. 7. Database Counters

  15. 8. Locking

  16. Adding Performance Rules to SCOM for SQL Server

  17. The Operations Manager DW Model • Real time (ish) Data Warehouse • Three Aggregates • Raw • Hourly • Daily • Views used to abstract tables and prevent locking and support partitioning

  18. Core Performance Objects

  19. Other Nice Fact Tables

  20. Querying and Analysing Performance Data

  21. SCOM Reporting Options SSRS Custom Reports Service Level Dashboard 2.0

  22. Custom Reporting with Report Builder

  23. Automating Performance Monitoring • What do these counters actually mean ? • See Jimmy May’s Blog et al (in references) • SCOM has flexible Monitor rules for perf counters • Static for basic thresholds like P.L.E, %Processor Time • Self Tuning for baseline counters like User Connections • Tip: P.A.L. is fantastic for understanding counters and thresholds. Find it on codeplex…

  24. Adding Monitoring Rules to SCOM

  25. Scorecards and Dashboards • PerformancePoint builds scorecards • Really needs a cube to be built • Solution Accelerator available • Needs SharePoint 2007+

  26. Scorecardsand Dashboards Monitor State Detail Daily Trends Hourly Data Availability Metrics Performance Counters

  27. Wrap Up on SCOM • Good Availability Monitoring Tool • Good Alert/Event Tool • There are better pure “performance” tools • With some work can do performance monitoring • Not really a diagnostic tool: • DMV, Wait Stat and trace files not covered • Can scale to entire organization though… • Can be a vehicle for Service Level Tracking

  28. Questions ?

  29. References/Blogs • Microsoft SQL Server Management Pack for Operations Manager 2007 • http://www.microsoft.com/downloads/details.aspx?FamilyId=8C0F970E-C653-4C15-9E51-6A6CADFCA363&displaylang=en&displaylang=en#filelist • Useful Ops Manager 2007 SQL Queries • http://blogs.technet.com/kevinholman/archive/2007/10/18/useful-operations-manager-2007-sql-queries.aspx • Operations Manager 2007 Reporting Guide • http://blogs.technet.com/momteam/archive/2008/02/26/operations-manager-report-authoring-guide.aspx • SCOM Scorecards and Dashboards Sample Application/Accelerator • http://www.microsoft.com/business/performancepoint/downloads/default.aspx • SCOM 2007 Service Level Dashboard Solution Accelerator • http://technet.microsoft.com/en-us/library/cc463350.aspx • SQL Perfmon Object Counters and Thresholds • http://blogs.msdn.com/jimmymay/archive/2008/10/15/perfmon-objects-counters-thresholds-utilities-for-sql-server.aspx

  30. Thank You!

More Related