Chapter 9 Overview

Reasons to monitor SQL Server

Performance Monitoring and Tuning

Tools for Monitoring SQL Server

Common Monitoring and Tuning Tasks

Reasons to Monitor SQL Server

Minimize application or query response

time

Maximize volume of transactions

processed

Performance Tuning Strategies

Optimize Response time

Requires knowledge of the

application, the Environment and

users requirements and activities

Optimize throughput

Requires knowledge of how SQL

Server handles data access

concurrency and interaction with

Windows 2000

Performance Tuning

Tune Client Application:

• Rewrite queries that limit searches

• Create useful indexes

• Minimize lock contention, avoid deadlocks

• Use stored procedures that reduce contention and increase concurrency.

• Offload data and only process from Server when appropriate

Tune Database: refine logical design – check to see if it is

normalized to reduce redundancy; in exceptional circumstances de-normalize to reduce joins required

refine physical design – add or modify indexes, distribute files across drives, …

Performance Tuning Approaches

Tune SQL Server: evaluate storage design – distribute data

files across drives, de-fragment data and indexes

Tune Hardware configuration: add memory, faster processor, faster hard

drives

Performance Tuning (ctd)

Establish Performance Baseline

Determine baseline value of key factors influencing database performance

• Workload: volume of server activity• Throughput: total number of queries in a

given time period• System resources: physical capacity of

computer hardware• Optimization : application and database

design• Contention: competition for the data records

SQL Server Monitoring Tools

• Windows 2000 Event Viewer

• Windows System Monitor with SQL Server

• Current Activity window in Enterprise

Manager

• Transact SQL Tools

• SQL Profiler

• SQL Query Analyzer

Common Monitoring tasks

1.System level monitoring ( use W2000 Event Viewer or Windows System Monitor) for: • Hardware• Operating System• Application

2. SQL Server specific monitoring (use EM, Transact SQL, or SQL Profiler) for:• SQL Server activity• Data consistency

3. Query performance (use Query Analyzer, SQL Profiler or Index Tuning wizard)

Windows Event Viewer

Windows 2000 Event Viewer displays error,

warning and information messages in logs

Logs include:

•Windows Application log

•Windows System log

•Windows Security log

Windows Performance System Monitor

Windows Performance System Monitor can

measure:

• SQL Server I/O

• SQL Server Memory usage

• SQL Server User connections

• SQL Server Locking

• Replication Activity

Enterprise Manager Current Activity

Enterprise Manager’s Current Activity window

displays:

1.SQL Server Process Information

by process ID

by object

2. Locks, blocking and deadlocks information

System Administrator can take actions to

inform users or terminate processes

Locking

• A lock is used to indicate that a user has some dependency on a resource

• Lock prevents other users to perform operations on the resource that would adversely affect the dependencies of the user owning the lock

• Locks are managed internally by system software and are acquired and released based on actions taken by the user.

Lock Modes

• There are several lock modes: shared, update, exclusive, intent, and schema

• lock mode indicates the level of dependency the connection has on the locked object

• SQL Server controls how the lock modes interact. For example, an exclusive lock cannot be obtained if other connections hold shared locks on the resource.

Lock Granularity

• Levels of lock of granularity: locks can be acquired on rows, pages, keys, ranges of keys, indexes, tables, or databases

• SQL Server dynamically determines the appropriate level at which to place locks for each Transact-SQL statement.

• Level at which locks are acquired can vary for different objects referenced by the same query; for example 1table may be very small and have a table lock applied, while another, larger table may have row locks applied.

Locking

• Each instance of SQL Server ensures that locks granted at one level of granularity respect locks granted at another level.

• For example, if UserA attempts to acquire a share lock on a row, but if UserB has an exclusive lock at the page or table level, UserA is blocked from acquiring locks until the lock held by UserB is freed.

Lock Contention

• If a connection needs a lock that conflicts with a lock held by another connection, the connection attempting to acquire the lock is blocked until: • the conflicting lock is freed and the

connection acquires the lock it requested• the time-out interval for the connection

expires. There is no default time-out interval but some applications set a time-out interval to prevent an indefinite wait

• locks are granted on a first-come, first-serve basis as the preceding connections free their locks.

Transact SQL Tools

System stored procedures:

• sp_who: Current SQL Users and processes

• sp_lock: Active locks, as well as blocking and

deadlock information

• sp_monitor: SQL Server statistics such as

total processing time, number of reads and

writes, and connection

SQL Profiler

SQL Profiler provides you with the ability to trace server and database activity including login, user and application activity. You can capture the data to a table, file, or Transact SQL statement for further analysis. Strategy:

Choose event to Monitor

Choose trace Criteria

Choose data to capture

Group data meaningfully

SQL Query Analyzer

• Query Analyzer can be used to track and

display:

• Query execution plan

• Server trace

• Server side statistics

• Client side statistics

• Query Analyzer’s Index Tuning Wizard can

also be used.

Common Monitoring Requirements

Information that is commonly monitored includes:

• Memory use

• Threads and Processor Use

• Hard disk input/output

• Locks

• Inefficient Queries

Memory Use Monitoring

Memory Available Bytes Monitors number of bytes available for use

by processes to execute This value should always be more than

5000 KB. Low value indicates shortage of physical memory.

Memory: Pages/Sec Monitors number of pages read from and

written to hard disk Counter should never be consistently

greater than 0.

Threads and Processor Use Monitoring

Processor: % processor time Monitors percentage of time that processor is

busy processing non-idle threads Counter should be less than 90.

System: Processor Queue Length Monitors number of threads waiting for

processor time Counter should not be consistently > 2.

Threads,Processor Use Monitoring(ctd)

Processor: % User time Monitors percentage of time processor

spends executing user processes(including SQL Server)

Can indicate if other processes are affecting SQL Server operations

Hard Disk I/O Monitoring

Physical Disk: %Disk time

Monitors percentage of time that the hard disk services read/write request. Should be less than 90.

Physical Disk: Avg. Disk Queue length

Monitors average number of read/write requests that are queued. Should not be more than 2 times the number of spindles.

Hard Disk I/O Monitoring (ctd)

Physical Disk: Disk Reads/see

Monitors rate of read operations. This counter should be consistently less than the capacity of your hard disk subsystem.

Physical Disk: Disk writes/see

Monitors rate of write operations. This counter should be constantly less than the capacity of hard disk subsystem.

Locks Monitoring

Deadlocks:

Automatically detected and killed

Blocking Locks:

Administrator intervention required

Managing Locks:

Use Windows System Monitor, SQL Profiler, EM or SP.

Query Monitoring

Identify Query Performance

Use SQL Profiler, traces and Stored procedure

Investigate and Improve Causes of Poor Query Performance

Causes may include network, memory, out-of-date statistics, indexes, inefficient query structure.