sql performance management with scom 2007
Post on 30-Dec-2015
49 Views
Preview:
DESCRIPTION
TRANSCRIPT
SQL Performance Management withSCOM 2007
16th April 2010
Bob DuffyDatabase ArchitectProdata 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
> Health Monitoring & Alertin
g
> Availability
> Configuration
> Security
> Performance
> Historical> Reporting
/ Analysis
> Service Level
Tracking
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 CountersSeven Common Groups of Performance Counters
CPUMemoryStorageBuffer PoolSQL WorkloadDatabase CountersLocking
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 % Processor Time
% Interrupt Time Windows MP
Processor Queue Length
Windows MP
% DPC Time Windows MP
System Context Switches/Sec Windows MP
2. Memory CountersObject 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/SecPages Writes/SecMemory Grants Outstanding
Buffer Cache Hit Ratio SQL MP
SQLServer:Memory Manager Target Server Memory (KB)
Total Server Memory (KB)
Connection memory (KB)
Lock memory (KB)
SQL Cache Memory (KB)
3. Storage CountersObject Counter SCOM?
Logical Disk Avg. Disk Sec/ReadAvg. Disk Sec/WriteAvg. Disk Sec/Transfer
Windows MP
Disk Reads/SecDisk Writes/Sec
Windows MP
Disk Transfers/Sec
Current Disk Queue Length
Windows MP
5. Buffer PoolObject Counter SCOM?
SQLServer:Buffer Manager
Total PagesTarget Pages
Database Pages
Pages Reads/SecPages Writes/Sec
SQServer:Plan Cache Cache Pages
Cache Hit Ratio
6. SQL WorkloadObject Counter SCOM?
SQLServer:General Statistics
User Connections SQL MP
SQLServer:SQL Statistics Batch Requests/sec
Transactions/SecSQL Compilations/secSQL Re-Compilations/sec
SQL MP
SQLServer:Access Methods
Full Scans/secIndex Searches/secRange Scans/secPage Splits/sec
SQL Server: User Settable Query
Tip: Use sp_user_counter1 to set a custom counter
7. Database CountersObject 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. LockingObject 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 SQL MP
SQLServer:Latches Average Latch Wait Time
Adding Performance Rules to SCOM for SQL Server
The Operations Manager DW Model
Real time (ish) Data WarehouseThree Aggregates
RawHourlyDaily
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 countersStatic for basic thresholds like P.L.E, %Processor TimeSelf 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 scorecardsReally needs a cube to be built
Solution Accelerator available
Needs SharePoint 2007+
Scorecards and Dashboards
Availability Metrics
Performance Counters
Monitor State Detail
Hourly Data
Daily Trends
Wrap Up on SCOMGood Availability Monitoring ToolGood Alert/Event ToolThere are better pure “performance” tools With some work can do performance monitoringNot 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/BlogsMicrosoft SQL Server Management Pack for Operations Manager 2007http://www.microsoft.com/downloads/details.aspx?FamilyId=8C0F970E-C653-4C15-9E51-6A6CADFCA363&displaylang=en&displaylang=en#filelist
Useful Ops Manager 2007 SQL Querieshttp://blogs.technet.com/kevinholman/archive/2007/10/18/useful-operations-manager-2007-sql-queries.aspx
Operations Manager 2007 Reporting Guidehttp://blogs.technet.com/momteam/archive/2008/02/26/operations-manager-report-authoring-guide.aspx
SCOM Scorecards and Dashboards Sample Application/Acceleratorhttp://www.microsoft.com/business/performancepoint/downloads/default.aspx
SCOM 2007 Service Level Dashboard Solution Acceleratorhttp://technet.microsoft.com/en-us/library/cc463350.aspx
SQL Perfmon Object Counters and Thresholdshttp://blogs.msdn.com/jimmymay/archive/2008/10/15/perfmon-objects-counters-thresholds-utilities-for-sql-server.aspx
Thank You!
top related