Microsoft sql server performance data collection strategies
This presentation is the property of its rightful owner.
Sponsored Links
1 / 17

Microsoft SQL Server Performance Data Collection Strategies PowerPoint PPT Presentation


  • 53 Views
  • Uploaded on
  • Presentation posted in: General

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?

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.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.


- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -

Presentation Transcript


Microsoft sql server performance data collection strategies

Microsoft SQL Server Performance Data Collection Strategies

Date


What about bill

What about Bill?

Follow me on Twitter @BillRamo

39 24 22 15 6 4


About advaiya

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:[email protected]


Welcome to the show

Welcome to the show


Let s go hunting

Let’s go hunting

  • Catch them in the act

  • Catch them after you’ve gone home


What did you just learn

What did you just learn?

  • Activity Monitor for the “now”

  • MDW Reports for the “past”


How does stuff work

How does stuff work?

  • Activity Monitor

  • Data Collector and MDW Reports

  • MDW Schema

  • Stuff you shouldn’t touch


What did you just learn1

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


What does the future hold

What does the future hold?

  • Is System Center Operations Manager in your future?


Scom 2007 r2 provides historical data and actionable ui based on selected sql instance

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.


Sql server dc and mdw provides detailed c ontext

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


Scom now shows health state

SCOM Now Shows Health State

Index created here

Expected range before ending workload


Mdw power comes at a cost

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


How to optimize data collection

How to Optimize Data Collection?


Just in case you didn t ask

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?


Resources

Resources

http://twitter.com/billramo


Microsoft sql server performance data collection strategies

?


  • Login