gather sql server performance data with powershell
Download
Skip this Video
Download Presentation
Gather SQL Server Performance Data with PowerShell

Loading in 2 Seconds...

play fullscreen
1 / 19

Gather SQL Server Performance Data with PowerShell - PowerPoint PPT Presentation


  • 753 Views
  • Uploaded 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

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

slide14
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