Chapter 14: Monitoring SQL Server Performance - PowerPoint PPT Presentation

1 / 19
About This Presentation
Title:

Chapter 14: Monitoring SQL Server Performance

Description:

User perspective minimize response time per query. ... Cursors and ad hoc queries. Excessive blocking locks and deadlocks. P478-479. 7. Windows 2000 ... – PowerPoint PPT presentation

Number of Views:69
Avg rating:3.0/5.0
Slides: 20
Provided by: facult77
Category:

less

Transcript and Presenter's Notes

Title: Chapter 14: Monitoring SQL Server Performance


1
Chapter 14 Monitoring SQL Server Performance
Activity
2
Chapter Overview
P475
  • Developing a Performance Monitoring Methodology
  • Choosing Among Monitoring Tools
  • Performing Monitoring Tasks

3
Performance Goals
P476
  • User perspectiveminimize response time per
    query.
  • Server perspectivemaximize the number of queries
    serviced.
  • User perspective is subjective.
  • Server perspective can be objectively measured.

4
Performance Bottlenecks
P477
  • Performance limited by excessive demand, bad
    design, and/or insufficient resources.
  • Low numbers can be as meaningful as high numbers.
  • Solving one bottleneck will often reveal another.
  • At some point, resolving bottlenecks ceases to
    appreciably improve performance.

5
Trend Analysis
P477-478
  • Establish an initial performance baseline.
  • Update the performance baseline periodically.
  • Analyze changes in the baseline to identify
    trends.
  • Usage tends to change over time, revealing new
    bottlenecks.

6
Resources and Activities to Monitor
P478-479
  • Inadequate hardware resources and competing
    server applications
  • Network congestion
  • Cursors and ad hoc queries
  • Excessive blocking locks and deadlocks

7
Windows 2000 System Monitor
P480
8
Task Manager
P481-482
9
SQL Profiler
P482-483
10
Current Activity Window in Enterprise Manager
P485
11
Transact-SQL
P485-488
  • System stored procedures
  • Database Consistency Checker (DBCC)
  • Built-in functions
  • Trace flags

12
Resource Use Monitoring
P489-497
  • Task Manager
  • Processes tab
  • Performances tab
  • Windows 2000 System Monitor
  • Chart view
  • Histogram view
  • Report view

13
Memory Objects and Counters
P494
14
I/O Objects and Counters
P495
15
Processor Objects and Counters
P495-497
16
Monitoring Stored Procedures, SQL Batches, and
User Activity
P499-503
  • Long-running query trace
  • Stored procedure performance trace
  • Cause of deadlock trace
  • Logins and logouts
  • Individual user activity

17
Current Locking and User Activity Monitoring
P504-509
18
Chapter Summary
  • Develop a monitoring methodology, and monitor
    periodically to detect developing trends.
  • Understand the range of monitoring tools and
    their capabilities.
  • Use System Monitor and Task Manager to monitor
    resource usage.
  • Use SQL Profiler to detect specific SQL Server
    events.
  • Use the Current Activity window to view current
    processes and locks.

19
Lab
  • Lab 11 Monitoring SQL Server Performance and
    Activity
  • 45 minutes
Write a Comment
User Comments (0)
About PowerShow.com