1 / 19

Gather SQL Server Performance Data with PowerShell

Gather SQL Server Performance Data with PowerShell. Allen White SQL Server MVP. About Me. SQL Server Consultant with Upsearch Over 35 years in IT

chelsey
Download Presentation

Gather SQL Server Performance Data with PowerShell

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. Gather SQL Server Performance Data with PowerShell Allen White SQL Server MVP

  2. About Me DBA-451 Gather SQL Server Performance Data with PowerShell SQL Server Consultant with Upsearch Over 35 years in IT Career covered multiple disciplines – operations, development, telecommunications, network design/administration and database design and administration Started using Sybase in 1992, MS SQL Server in 1995 Microsoft Certified IT Professional: Database Administrator and Database Developer, Microsoft Certified Trainer (MCT) Awarded Microsoft MVP Award for SQL Server for last 3 years

  3. 53 MVPs Coalesce Book Launch at PASS 2009 Wednesday 11:30 Spotlight Theater www.SQLServerMVPDeepDives.com

  4. Agenda DBA-451 Gather SQL Server Performance Data with PowerShell Performance Counters Capture Options PowerShell Script Performance Analysis Report

  5. Goal - Capture Performance Baseline DBA-451 Gather SQL Server Performance Data with PowerShell • Baseline shows normal performance • Deviations from Baseline require investigation • Problem • Data comes from disparate sources • Coordination of multiple gathering tools • Synchronizing data for true baseline analysis • Solution • PowerShell

  6. Key Performance Indicators DBA-451 Gather SQL Server Performance Data with PowerShell Which counters show us system health There’s no “right” answer These are my choices

  7. Operating System Counters DBA-451 Gather SQL Server Performance Data with PowerShell

  8. SQL Server Counters DBA-451 Gather SQL Server Performance Data with PowerShell

  9. Sources for Performance Data DBA-451 Gather SQL Server Performance Data with PowerShell • Perfmon • Save data to .CSV • Use SSIS or PowerShell to import results • DMVs • Great source of SQL Server data • sys.dm_os_performance_counters • Only returns SQL Server current instance counters • WMI • Allows access to all aspects of server • Crunching the numbers can be tricky

  10. Performance Data in PowerShell DBA-451 Gather SQL Server Performance Data with PowerShell • Get-Counter cmdlet (PowerShell 2.0) • Invocation sets own interval handler • System.Diagnostics.PerformanceCounter • Support directly within .NET • Results directly match Perfmon values • Accessible from PowerShell • Demo

  11. Performance Database DBA-451 Gather SQL Server Performance Data with PowerShell Every Admin should have one Store Baseline Data Store Server Side Trace info Store Server and Instance info Keep all management info in one place

  12. Scripting the Data Capture # Initialize Perfcounters $ppt = new-object System.Diagnostics.PerformanceCounter $ppt.CategoryName = 'Processor' $ppt.CounterName = '% Processor Time' $ppt.InstanceName = '_Total' $pptv = $ppt.nextvalue() DBA-451 Gather SQL Server Performance Data with PowerShell Capture the counter data Insert into Performance Database Wait defined interval and do it again

  13. Logic Flow for getperf.ps1 DBA-451 Gather SQL Server Performance Data with PowerShell

  14. Demo DBA-451 Gather SQL Server Performance Data with PowerShell getperf.ps1

  15. Creating the Analysis Reports DBA-451 Gather SQL Server Performance Data with PowerShell Create Basic Report Add Table for Counter Data Add Graphs to see Trends Demo

  16. Define Attention Levels DBA-451 Gather SQL Server Performance Data with PowerShell • Once Baseline is understood • Define deviation amount for warning • Define deviation amount for error condition • Build notification mechanisms • If warning send email • If error send text message • Add Dashboard Report to SSMS • Demo

  17. References DBA-451 Gather SQL Server Performance Data with PowerShell • Master-PowerShell | With Dr. Tobias Weltner • http://powershell.com/cs/blogs/ebook/default.aspx • Let PowerShell do an Inventory of your Servers • http://www.simple-talk.com/sql/database-administration/let-powershell-do-an-inventory-of-your-servers/ • Initialize-SqlpsEnvironment.ps1 script • http://blogs.msdn.com/mwories/archive/2008/06/14/SQL2008_5F00_Powershell.aspx • Midnight DBA – PowerShell webcasts • http://midnightdba.itbookworm.com/

  18. Complete the Evaluation Form & Win! Sponsored by Dell DBA-451 Gather SQL Server Performance Data with PowerShell • You could win a Dell Mini Netbook– every day – just for handing in your completed form! Each session form is another chance to win! Pick up your Evaluation Form: • Within each presentation room • At the PASS Booth near registration area Drop off your completed Form: • Near the exit of each presentation room • At the PASS Booth near registration area

  19. Thank you for attending this session and the 2009 PASS Summit in Seattle

More Related