1 / 17

Microsoft SQL Server Performance Data Collection Strategies

Microsoft SQL Server Performance Data Collection Strategies. Date. What about Bill?. Follow me on Twitter @ BillRamo. 39 24 22 15 6 4. About Advaiya. Frustrated trying to justify resources? Interested in migrating Oracle, MySQL, Sybase, Access to SQL Server?

kathie
Download Presentation

Microsoft SQL Server Performance Data Collection Strategies

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. Microsoft SQL Server Performance Data Collection Strategies Date

  2. What about Bill? Follow me on Twitter @BillRamo 39 24 22 15 6 4

  3. About Advaiya • Frustrated trying to justify resources? • Interested in migrating Oracle, MySQL, Sybase, Access to SQL Server? • Do you need guidance in monitoring your systems? • Mailto:Bill.Ramos@Adviaya.com

  4. Welcome to the show

  5. Let’s go hunting • Catch them in the act • Catch them after you’ve gone home

  6. What did you just learn? • Activity Monitor for the “now” • MDW Reports for the “past”

  7. How does stuff work? • Activity Monitor • Data Collector and MDW Reports • MDW Schema • Stuff you shouldn’t touch

  8. What did you just learn? • Activity Monitor using tempdb at a regular interval • DC using SSIS and collection sets to collect data • MDW houses the data with reports to see history • Script collection sets to see what is collected • Don’t mess with the jobs

  9. What does the future hold? • Is System Center Operations Manager in your future?

  10. SCOM 2007 R2 Provides Historical Data and Actionable UI Based on Selected SQL Instance Easily go back in time to see trends. Perform and extend tasks based on instance problem Setup Metrics that you care about.

  11. SQL Server DC and MDW Provides Detailed Context Plan UI shows missing index with expected impact Use parameter substitution UI to name the index, remove the comments, run the command and the problem is solved Click on big stuff to see query details Click on the big stuff again to see query plan details Query optimizer recommends a missing index so click on View Plan link Missing Index Details command loads index statement in editor for DBA to decide Click on SQL Server CPU line to see queries Obvious query causing the CPU problem

  12. SCOM Now Shows Health State Index created here Expected range before ending workload

  13. MDW Power Comes at a Cost MDW grows around 250GB to 600 GB per day per instance collected compared to Ops Manager DB which has collects much less data

  14. How to Optimize Data Collection?

  15. Just in case you didn’t ask AKA FAQ • Can you run the reports using SSRS? • Why doesn’t this work with SQL Server 2005? • How do I uninstall the Data Collector? • How many servers can a single MDW handle? • How come I can’t start the Utility Information collection set? • How come you didn’t talk about the Utility Control Point?

  16. Resources http://twitter.com/billramo

  17. ?

More Related