Gather sql server performance data with powershell
Download
1 / 19

Gather SQL Server Performance Data with PowerShell - PowerPoint PPT Presentation


  • 738 Views
  • Updated On :

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

Related searches for Gather SQL Server Performance Data with PowerShell

loader
I am the owner, or an agent authorized to act on behalf of the owner, of the copyrighted work described.
capcha
Download Presentation

PowerPoint Slideshow about 'Gather SQL Server Performance Data with PowerShell' - chelsey


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
Gather sql server performance data with powershell

Gather SQL Server Performance Data with PowerShell

Allen White

SQL Server MVP


About me
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


53 mvps coalesce
53 MVPs Coalesce

Book Launch at PASS 2009

Wednesday 11:30 Spotlight Theater

www.SQLServerMVPDeepDives.com


Agenda
Agenda

DBA-451 Gather SQL Server Performance Data with PowerShell

Performance Counters

Capture Options

PowerShell Script

Performance Analysis Report


Goal capture performance baseline
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


Key performance indicators
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


Operating system counters
Operating System Counters

DBA-451 Gather SQL Server Performance Data with PowerShell


Sql server counters
SQL Server Counters

DBA-451 Gather SQL Server Performance Data with PowerShell


Sources for performance data
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


Performance data in powershell
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


Performance database
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


Scripting the data capture
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


Logic flow for getperf ps1
Logic Flow for getperf.ps1

DBA-451 Gather SQL Server Performance Data with PowerShell


Demo

DBA-451 Gather SQL Server Performance Data with PowerShell

getperf.ps1


Creating the analysis reports
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


Define attention levels
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


References
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/


Complete the evaluation form win
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


Thank you

Thank you

for attending this session and the 2009 PASS Summit in Seattle


ad