Transcript SQL Server Performance

Microsoft SMS 2003
Management Pack
For More SMS Information
The public SMS Web site
– http://www.microsoft.com/smserver
– Lots of great information on SMS 2003
– Case studies, product overview, What’s New, demos, system
requirements, FAQs
– Evaluation version of SMS 2003
SMS 2003 product documentation
– SMS 2003 online library with SMS 2003
– Also on the public SMS Web site
SMS 2003 Administrator’s Companion
– ISBN: 0-7356-1888-7
Managing Microsoft SQL 2000 with
MOM 2005
Agenda
Why monitor SQL Server?
SQL Management Pack Proof Points
Enterprise Configuration Support
State Monitoring
SQL Server Monitoring Scenarios
MOM Views
Tasks
Summary
Why Monitor SQL?
Availability!
How does MOM help?
Permits operators to monitor large numbers of SQL servers from a central
console
–
Manages widely dispersed deployments
–
Lowers TCO for high-availability operation
Watches all possible indications of problems
–
Intercept small problems early
Facilitates rapid failure detection
–
Lower time-to-resolution
Monitors AD, IIS and base OS
–
Via third party management packs: hardware, AV
SQL Management
Pack Goals
Out-of-box solution
Limited number of highly relevant alerts
(suppress repetitive and related events  lower noise)
Monitor all performance indicators
Knowledge: what to do when a problem occurs
Monitors all SQL servers appropriately
–
Clusters, Named Instances, Replication, Log Shipping etc..
Supporting views and reports
–
System status / capacity planning
SQL MP Proof Points
Events
Monitors the ~400 operationally
relevant events
Operational Knowledge
Script / Synthetic Monitoring
Service Monitoring
Remote Connectivity
Database and log free space
Database health
Configuration monitoring
Replication Monitoring
Long Running Agent Jobs
Blocked SPIDs
Performance
SQL Performance counters
Operating System monitoring
Thresholds
Performance Views
Reporting
Configuration
Performance
Load and Capacity Planning
Enterprise configurations
100% Cluster support
Named instance support
64bit support
MOM Console:
Knowledge Base
Knowledge is a key
feature
Facilitates rapid issue
resolution
Empowers front line
operators
– Less escalation
– Faster resolution
SQL Server Event Collection
Active Directory Helper Service
SQL Server Agent
Backup
Databases and Tables
DBCC
Full Text Search
Log Shipping
Mail
Performance
Query Engine
Replication
Security
Web Assistant
SQL XML
Enterprise Configurations
The SQL Server management pack supports enterprise
configurations:
– 100% support for clustered SQL Servers
– Full support for named instances
– Full support for 64bit SQL Server 2000
Exclusions
– The ability to exclude individual databases from SQL MP
monitoring
– Named instance and cluster aware
Cluster Support
The SQL Server MP 100% supports clusters
– All scripts are fully Active / Passive and Active / Active cluster
aware
– Performance data collected for virtual servers and instance
names without scripts
– Cluster logic for service checking
– Target collection rules on active nodes and virtual servers
– All synthetic transactions cluster aware
– Report on clustered servers
Management Pack Overview
Operations Console - Alerts
Alerts View
Alert ~ Ticket
Manually
resolved
SQL Server State Monitoring
The SQL MP provides state monitoring of key SQL Server
components:
– SQL Server Agent
– Connectivity
– Database Health
– Free Space
– SQL Server Service
All state is SQL Server Instance and database aware
State View
State View
Dynamic
Component Details
SQL Instance
Granularity
Monitoring Scenarios
Is SQL Server available and accepting connections?
Do all databases and logs have sufficient free space?
Can users connect to SQL Server?
Are SQL Server Agent jobs (backup, upload etc) working?
Are all database configured to my enterprise standard?
Is SQL Server configured correctly?
Is my SQL Server secure?
Is SQL Server available and accepting
connections?
Service Availability
– Monitor the availability of SQL Server and SQL Agent services
– Monitor Full Text Search service
– Cluster and Named Instance Aware
Connect to local SQL Server and query for data returned
Is SQL Server available and accepting
connections?
Connectivity and Service state alerts on failure and
success
Red/Green view of SQL Health
Checks for:
– Port bind errors
– Configuration mistakes
– Protocol problems
– Corrupt system databases
Do all databases and logs have
sufficient free space?
Monitor the remaining space in all databases and logs
Check for autogrow, files and file groups
Defined warning and error thresholds for:
– Logs and Databases
– System databases
– TempDb
– User databases
Example – Space Monitoring
Alerts indicate:
– Location of database
– Threshold breached
– Remaining space
Thresholds can be customized to match enterprise standards
Database Health
The SQL Server MP monitors database health
Alert for database in unhealthy states
– Corrupt
– Emergency Mode
Database involved in replication, log shipping and
maintenance are excluded:
– Read Only
– Offline
Can users connect to SQL Server?
Connect to SQL Server remotely to simulate the client
experience
Test database response time with custom TSQL query
Evaluate intermediate network connectivity
Remote Connectivity Settings
Client side monitoring allows you to define the
following remote connectivity settings:
–TSQL Query to execute
–Database to query
–Response Time
‘Client’ machines
Example - Remote Connectivity
1.
Query executed
2.
Response time evaluated
3.
Alert generated on failure or
delayed response
Client
Are SQL Server Agent jobs working?
The SQL Server management pack monitors agent
availability and health
Agent Service monitoring
Potential Agent problems
– Failed SQL Agent Jobs
– Job corruption
– Failed Job Notifications
– SQL Mail problems
Long running agent jobs
The SQL MP monitors long running SQL Agent jobs
Job run time is measured in real time and compared
against a predetermined threshold
The long running job threshold can be adjusted (60
minutes by default)
Long running job scenarios
SQL Server maintenance jobs taking longer than normal
Weekly data upload taking excessive time
Alert on jobs which may run into production hours and
impact performance
Be notified of impending failures
Backup Monitoring
The SQL MP monitors failed and successful backups
Alerts on problem conditions
– Failed backups (incremental, full etc..)
– Restore Errors
Views
– Backup failure related events
Reports
– Backup History
– Total Successful and Failed Backups
Agent and Backup Monitoring
Blocking
The MP monitors blocking SPID scenarios
Alerts are based on block duration threshold time (default
1 minute)
Topped blocked report allows further drill down on data
including top blocking users, application and average
blocking time
Replication Monitoring
Monitor replication failures with script based replication
monitoring
Collection of key replication failure events
Replication performance
– Distributor
– Log Reader
– Snapshot / Merge
Are all database configured to my enterprise
standard?
Alert on configuration inconsistencies in your enterprise for
each database
Alert settings include
– Auto Close
– Auto Create Stats
– Auto Shrink
– Auto Update Stats
– Cross DB Chaining
– Torn Page Detection
Is my SQL Server secure?
MOM monitors SQL Server security and audit events
– Denied administrative functions
– Single user mode startup
– License compliance
– Configuration problems eg cross database chaining
Security Reports
– Failed / Successful Logins by count
– Top 25% Failed / Successful Logins
Exclusions
Databases can be excluded from script and synthetic
monitoring
Exclusions use a text file on the management node called
SQLMPExclude.txt
Exclusion file lists all databases you wish to exclude from
monitoring
– Database Name
– InstanceName\DatabaseName
Performance
The SQL management pack monitors the performance of
SQL Servers
SQL Server counter collection
– Key counters collected for analysis
SQL Server thresholds
– Total User Connections
– Deadlocks
Operating System thresholds
– Disk Read / Write latency
– CPU and Queue Length
SQL Server Performance
Key SQL Server counters are collected for real time analysis
and reports
– Active Transactions
– Average Wait Time
– Logins
– Lock Blocks
– Log Truncations
– Page Write / Allocations
– Memory Grants
Operating System Performance
Collection and threshold of key
performance indicators:
– CPU and Queue Length
– Disk Capacity
– Disk Performance
– Memory Usage
– Network Usage
Views to display all data
SQL MP Views
Reports
The SQL MP contains reports to monitor the health and configuration
of SQL Server
Report data is stored for long term analysis in a dedicated warehouse
Main report categories
– Configuration & Backup
– Performance
– Capacity Planning
– Security & Audit
Report per SQL Server instance for all servers
Configuration Reports
SQL Server Configuration
– Instance Level Configuration Details
– Databases and Configuration
Service Pack Level
– Display all SQL Server version information
– Service Pack Level
Backup History Report
– Failed and Successful Backup Summary
– Backup detail drill down
Performance Reports
Lock Analysis Report
– Evaluate Locks, Deadlock and Locked Blocks
Blocked SPIDs Report
– Report on SQL Sever blocked SPIDs
– Sort by top blocking user, application or block duration
Capacity Planning
User Connections by peak hours
– Chart total user connections by adjustable peak hour time
window
User Connections by day
– Chart user connections by day
All reports can be charted by instance
Security Reports
Security reports leverage SQL Server audit data to display
summary data
Logins by count:
– Failed Logins by count
– Successful Logins by count
Login graphical summary:
– Top 25% Failed Logins
– Top 25% Successful Logins
Tasks
Task allow in context execution of common administrative
tools and functions
You can create your own tasks by leveraging existing shell
tasks to execute custom TSQL code
Management Pack Tasks
Query SQL Server for real time configuration and
performance
– Display user and process information
– Display SQL Configuration Information
– Start / Stop SQL Server Mail
Service manipulation
– SQL Server
– SQL Agent
Common Administration Tools
– SQL Profiler
– Query Analyzer
Upgrading from MOM SP1
The MOM 2005 SQL Server management pack upgrades
seamlessly from MOM SP1
New functionality added such as state monitoring and
tasks
MOM SP1 Access reports do not upgrade and are
replaced with SQL Server Reporting Services
Monitoring Philosophy
Effective front-line monitoring of SQL
 watching the details
 broad spectrum of events and performance counters
Without this detailed monitoring
 few preemptive actions possible
 SQL is managed in a reactive mode
The SQL Management Pack facilitates such monitoring
 High SQL Server availability
Microsoft SQL 2000
Management Pack
Mgmt Notification
Sign up for Management Update Notification Service
– Notice of updates to
– New/Updated Management Packs
– Microsoft Management Product News
– Solutions
– http://www.microsoft.com/management/
notifyme/default.mspx
Resources
MOM Product Info
http://www.microsoft.com/mom/
Management Packs
http://www.microsoft.com/mom/downloads/managementpacks/
Community
http://www.microsoft.com/mom/community/
SQL Server Management Pack Guide
http://www.microsoft.com/technet/prodtechnol/mom/maintain/sqlmpak.mspx
MOM Partner
http://www.microsoft.com/management/mma
Questions?
Attend a free chat or web cast
http://www.microsoft.com/communities/chats/default.mspx
http://www.microsoft.com/usa/webcasts/default.asp
List of newsgroups
http://communities2.microsoft.com/
communities/newsgroups/en-us/default.aspx
MS Community Sites
http://www.microsoft.com/communities/default.mspx
Locate Local User Groups
http://www.microsoft.com/communities/usergroups/default.mspx
Community sites
http://www.microsoft.com/communities/related/default.mspx
Please fill out a session evaluation on CommNet
Q1:
Overall satisfaction with the session
Q2:
Usefulness of the information
Q3:
Presenter’s knowledge of the subject
Q4:
Presenter’s presentation skills
Q5:
Effectiveness of the presentation
Community Resources
Microsoft Community Resources
http://www.microsoft.com/communities/default.mspx
Non-Microsoft Community Resources
http://www.microsoft.com/communities/related/default.mspx
Newsgroups
Converse online with Microsoft Newsgroups, including Worldwide
http://www.microsoft.com/communities/newsgroups/default.mspx
User Groups
Meet and learn with your peers
http://www.microsoft.com/communities/usergroups/default.mspx
Attend a free chat
http://www.microsoft.com/communities/chats/default.mspx
Attend a free web cast
http://www.microsoft.com/usa/webcasts/default.asp
Most Valuable Professional (MVP)
http://mvp.support.microsoft.com/
Contact Information
Gordon McKenna
Ancoris
Microsoft Management Consultant
MVP – MOM
[email protected]
© 2003-2005 Microsoft Corporation. All rights reserved.
This presentation is for informational purposes only. Microsoft makes no warranties, express or implied, in this summary.
http://www.microsoft.com/uk/technet