Monday, November 12, 2012

System Performance Counter for SQL Server

Monitoring Server
Using System Monitor (Performance Monitor)
Object (Instance)
Analyzing Memory

Available Bytes
Free Physical memory
System dependent

Rate of hard pages faults
Average value < 50

Pages faults/sec
Rate of total page faults

Pages Input/sec
Rate of input page faults

Pages output/sec
Rate of output page faults

SQLServere:Buffer Manager
Buffer cache hit ratio
Percentage of requests served out of buffer cache
Average value >= 90%

Buffer cache hit ratio
Percentage of requests served out of buffer cache
Average value >= 90%

Page Life Expectancy
Time page spends in buffer
Average value > 300

Checkpoint pages/sec
Pages written to disk by checkpoint
Average value < 30

Lazy writes/sec
Dirty aged pages flushed from buffer
Average value < 20
SQLServer:Memory Manager

Memory Grants pending
Number of processes waiting for memory grant
Average value = 0

Target Server memory (KB)
Maximum physical memory sql server can consume on the box

Total server memory (KB)
Physical memory currently assigned to sql server


Private Bytes
Size, in bytes, of memory that this process has allocated that cannot be shared with other processes

Process:Page Faluts/sec
Which process causing excessive paging

Analyzing Disk

PhysicalDisk(Data-disk, Log-disk)

% Disk Time
Percentage of time disk was busy
Average value < 85%

Current Disk Queue length
Number of outstanding disk requests at the time performance data is collected
Average value < 2 per disk

Avg. Disk Queue Length
Average number of queued disk requests during the sample interval
Average value < 2 per disk

Disk Transfers/sec
Rate of read/write operations on disk
Maximum value < 100 per disk

Disk Bytes/sec
Amount of data transfer to/from per disk per second
Maximum value < 10MB per second

Avg. Disk Sec/Read
Average time in ms to read from disk
Average value < 10 ms

Avg. Disk Sec/Write
Average time in ms to write to disk
Average value < 10 ms
Analyzing Processor


% Processor Time
Percentage of time processor was busy
Average value < 80%

% Privileged Time
Percent of processor time spent in privileged mode
Average value < 10%

Processor Queue Length
Number of requests outstanding on the processor
Average value <2

Context Switches/sec
Rate at which processor is switched per processor from one thread to another
Average value < 1000
SQL Server:SQL Statistics

Batch Requests/sec
SQL command batches received per second

SQL Compilations/Sec
Number of times SQL is compiled
Average value > 100

SQL Recompilations/sec
Number of recompiles

Analyzing Network

Network Intergace

Bytes Total/Sec
Rate at which bytes are transferred on the NIC
Average value < 50% of NIC capacity
Network Segment
% Net Utilization
Percentage of network band width in use on a network segment
Average value < 80% of network bandwidth
SQL Server Overall Performance

SQLServer:Access Methods
FreeSpace Scans/sec
Non ordered table (Heap file)

Full Scans/sec
Number of unrestricted full scan on tables or indexes

Table Lock Escalations/Sec

Worktables Created/Sec


Total Latch Wait Time (ms)


Lock Timeouts/sec


Lock Wait Time (ms)

Number of Deadlocks/sec

SQLServer:SQL Statistics

Batch Requests/Sec

SQL Re-Compilations/sec

SQLServer:General Statistics

Processes Blocked

User Connections

Using SQL Profiler:
Event Class
Stored Procedures
An RPC completion event

A stored procedure completion event

A SQL statement completion event within a stored procedure
A T-SQL batch completion event

A T-SQL Statement completion event
Security Audit

Audit Login
Audit Logout
Keeps track of database connections when users connect to and disconnect from SQL Server
Existing Connection
Represents all the users connected to SQL server before the trace was started
Errors and Warnings

Represents the intermediate termination of a request caused by actions such as query cancellation by a client or broken database connections

Indicates the occurrence of an exception in SQL Server

Execution Warnings
Indicates the occurrence of any warning during the execution of a query or SP

Hash Warning
Indicates the occurrence of an error in a hashing operation

Missing Column statistics
Indicates that the statistics of a column, required by the optimizer to decide a processing strategy, are missing.

Missing Join Predicate
Indicates that a query is executed with no joining predicate between two tables

Sort Warnings
Indicates that a sort operation performed in a query such as select did not fit into memory

Flags the presence of a deadlock

Lock:Deadlock Chain
Show a trace of the chain of queries creating the deadlock

Signifies that the lock has exceeded the timeout parameter, which is set by SET LOCK_TIMEOUT timeout_period(ms)
Stored Procedures
Indicates that an execution plan for a SP has to be recompiled, because one did not exist, a recompilation was forced, ot the existing execution plan could not be reused.

Represents the starting of a stored SP:StmtStarting procedure and a SQL statement within a stored procedure, respectively. They are useful to identify queries that started but could not finish because of an operation that caused an Attention event.

Provides information about a database transaction, including information such as when a transaction started/completed, the duration of the transaction, and so on.

No comments:

Post a Comment