Microsoft SQL Server Performance Data Collection Date Strategies What about Bill? Follow me on Twitter @BillRamo 39 24 22 15 6 4 2 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 3 Welcome to the show Let’s go How stuff The future hunting works 4 Let’s go hunting DBA450M • Reporting Against the MDW What did you just learn? DBA450M • Reporting Against the MDW How does stuff work? DBA450M • Reporting Against the MDW What did you just learn? DBA450M • Reporting Against the MDW What does the future hold? DBA450M • Reporting Against the MDW Easily go back in time to see trends. Perform and extend tasks based on instance problem Setup Metrics that you care about. Plan UI shows missing index with expected impact Use parameter substitution UI to name the index, remove the Missing Index SQL Click on big big Query optimizerthe stuff to seea run the command and recommends Click on Details comments, missing stuff so click seeView Plan query on command loads index indexagain todetails the problem is solved Server CPU statement in editor for query plan details link line to see Obvious query causing the CPU to decide DBA queries problem Index created Expected range here before ending workload MDW grows around 250GB to 600 GB per day per instance collected compared to Ops Manager DB which has collects much less data Host • Run MDW Wizard against SCOM instance MDW on • Configure DC for Query Stats and Server SCOM Activity via API pointing to MDW on SCOM, but don’t enable collection Try SSMS • Try tools like Activity Monitor to solve problem tools 1st • Query DMVs if necessary Enable • If the problem lingers, enable the DC for instance DC as • Troubleshoot the problem needed • Disable the DC when finished 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? 15 Resources http://twitter.com/billramo Resource Title Bill Ramos on http://blogs.msdn.com/billramo MSDN SQL Sentry 45 day http://www.sqlsentry.net/download- Eval Version trial/trial.asp?ad=Advaiya Query Hash Stats http://code.msdn.microsoft.com/QueryHashStatistics Project System Center http://www.microsoft.com/systemcenter/en/us/opera Operations tions-manager.aspx Manager 16 ?