Transcript SQL Performance Management withSCOM 2007

16th April 2010
SQL Performance Management with
SCOM 2007
Bob Duffy
Database Architect
Prodata SQL Centre of Excellence
Speaker Profile – Bob Duffy
• SQL Server MVP
• MCA/MCM for SQL Server
• 18 years in database sector, 250+ projects
• Senior SQL Consultant with Microsoft 2005-2008
• Regular speaker for TechNet, MSDN, Users Groups, Irish
and UK Technology Conferences
• On MCM 2008 exam working group
• Database Architect at Prodata SQL Centre Excellence,
Dublin
• SQL Geek ;-)
Agenda
•
•
•
•
•
•
•
•
What does SCOM do for SQL
SQL Performance Counters
Analysing performance in Ops Console
Adding Performance Rules
Querying/Analysing Performance Data
Adding Custom Reports
Performance Monitors and Alerts
Questions
What Does SCOM do for SQL
Availability
Service Level
Tracking
Configuration
Health
Monitoring
& Alerting
Historical
Reporting/
Analysis
Security
Performance
SCOM Architecture
“Nobody knows how to monitor Microsoft technology better than Microsoft, and
Operations Manager provides us with that needed expertise in a form that’s ready to
use.”
>– Matthew O’Neill, Group Head of Distributed Systems, HSBC Global IT Operations
Out of the Box Perf Reports
Console based Views, and
Dashboards
(Out of the box stuff)
Performance Counters
Seven Common Groups of Performance Counters
CPU
Memory
Storage
Buffer Pool
SQL Workload
Database Counters
Locking
This is not a complete list, just a good list for a baseline and general
performance monitoring/tuning (see references for more links)
1. CPU Counters
Object
Counter
SCOM?
Processor
% Processor Time
Windows MP
% Privileged Time
Windows MP
Process\sqlserver.exe
System
% Processor Time
% Interrupt Time
Windows MP
Processor Queue Length
Windows MP
% DPC Time
Windows MP
Context Switches/Sec
Windows MP
2. Memory Counters
Object
Counter
SCOM?
Memory
Pages per Second
Windows MP
Page Reads/Sec
Windows MP
Page Writes /Sec
Windows MP
Pool Non Paged Bytes
Windows MP
Pool Paged Bytes
Windows MP
Available Mbytes
Windows MP
Paging File
% Usage
Windows MP
SQLServer:Buffer Manager
Page Life Expectancy
Pages Reads/Sec
Pages Writes/Sec
Memory Grants Outstanding
Buffer Cache Hit Ratio
SQLServer:Memory Manager
Target Server Memory (KB)
Total Server Memory (KB)
Connection memory (KB)
Lock memory (KB)
SQL Cache Memory (KB)
SQL MP
3. Storage Counters
Object
Counter
SCOM?
Logical Disk
Avg. Disk Sec/Read
Avg. Disk Sec/Write
Avg. Disk Sec/Transfer
Windows MP
Disk Reads/Sec
Disk Writes/Sec
Windows MP
Disk Transfers/Sec
Current Disk Queue Length
Windows MP
5. Buffer Pool
Object
Counter
SQLServer:Buffer Manager
Total Pages
Target Pages
Database Pages
Pages Reads/Sec
Pages Writes/Sec
SQServer:Plan Cache
Cache Pages
Cache Hit Ratio
SCOM?
6. SQL Workload
Object
Counter
SCOM?
SQLServer:General
Statistics
User Connections
SQL MP
SQLServer:SQL Statistics
Batch Requests/sec
Transactions/Sec
SQL Compilations/sec
SQL Re-Compilations/sec
SQLServer:Access Methods
Full Scans/sec
Index Searches/sec
Range Scans/sec
Page Splits/sec
SQL Server: User Settable
Query
SQL MP
Tip: Use sp_user_counter1 to set a custom counter
7. Database Counters
Object
Counter
SCOM?
SQLServer:Databases
Transactions/Sec
SQL MP
Log Growths
Data File(s) Size (KB)
SQL MP
Log File(s) Size (KB)
SQL MP
Log Bytes Flushed/Sec
Write Transactions/Sec
8. Locking
Object
Counter
SCOM?
SQLServer:Locks
Number of Deadlocks/Sec
SQL MP
Lock Timeouts per sec
SQL MP
Lock Waits/Sec
Average Wait Time
Lock Requests/Sec
SQLServer:Latches
Average Latch Wait Time
SQL MP
Adding Performance Rules to
SCOM for SQL Server
The Operations Manager DW Model
Real time (ish) Data Warehouse
Three Aggregates
Raw
Hourly
Daily
Views used to abstract tables and prevent locking and
support partitioning
Core Performance Objects
Other Nice Fact Tables
Querying and Analysing
Performance Data
SCOM Reporting Options
SSRS Custom Reports
Service Level Dashboard 2.0
Custom Reporting
with Report Builder
Automating Performance Monitoring
What do these counters actually mean ?
See Jimmy May’s Blog et al (in references)
SCOM has flexible Monitor rules for perf counters
Static for basic thresholds like P.L.E, %Processor Time
Self Tuning for baseline counters like User Connections
Tip: P.A.L. is fantastic for understanding counters and
thresholds. Find it on codeplex…
Adding Monitoring Rules to
SCOM
Scorecards and Dashboards
PerformancePoint builds scorecards
Really needs a cube to be built
Solution Accelerator available
Needs SharePoint 2007+
Scorecards and Dashboards
Monitor State Detail
Daily Trends
Availability Metrics
Performance Counters
Hourly Data
Wrap Up on SCOM
Good Availability Monitoring Tool
Good Alert/Event Tool
There are better pure “performance” tools
With some work can do performance monitoring
Not really a diagnostic tool:
DMV, Wait Stat and trace files not covered
Can scale to entire organization though…
Can be a vehicle for Service Level Tracking
Questions ?
References/Blogs
Microsoft SQL Server Management Pack for Operations Manager 2007
http://www.microsoft.com/downloads/details.aspx?FamilyId=8C0F970EC653-4C15-9E516A6CADFCA363&displaylang=en&displaylang=en#filelist
Useful Ops Manager 2007 SQL Queries
http://blogs.technet.com/kevinholman/archive/2007/10/18/usefuloperations-manager-2007-sql-queries.aspx
Operations Manager 2007 Reporting Guide
http://blogs.technet.com/momteam/archive/2008/02/26/operationsmanager-report-authoring-guide.aspx
SCOM Scorecards and Dashboards Sample Application/Accelerator
http://www.microsoft.com/business/performancepoint/downloads/default.as
px
SCOM 2007 Service Level Dashboard Solution Accelerator
http://technet.microsoft.com/en-us/library/cc463350.aspx
SQL Perfmon Object Counters and Thresholds
http://blogs.msdn.com/jimmymay/archive/2008/10/15/perfmon-objectscounters-thresholds-utilities-for-sql-server.aspx
Thank You!