Databases Reference
In-Depth Information
TABLE 10-6:
Key SQL Server PerfMon Counters
COUNTER
WHAT TO LOOK FOR
PROBLEM CONDITION
MSSQL
SQL Statistics
Batch Requests/sec
Number of T-SQL batches pro-
cessed by SQL server; higher is
better. Useful for a baseline and
should be considered
when making any
comparisons.
>1000 Indicates a
server with high
activity
MSSQL
SQL Statistics
SQL Compilations/sec
Number of batches requiring
plan compilations per second.
High compilations indicate
either poor plan reuse or many
ad hoc queries.
>20% Batch
Requests/sec
MSSQL
SQL Statistics
SQL Recompilations/sec
Number of statement recompiles
per second
>20% Batch
Requests/sec
MSSQL
General
Statistics
Processes Blocked
Number of currently blocked
processes
Investigate when >0
MSSQL
Locks
Lock Waits/sec
Number of user requests wait-
ing for locks per second. Can be
indicative of blocking.
>0
MSSQL
Locks
Lock Timeouts/sec
Number of lock timeouts per
second; anything greater than 1
should be investigated.
>0
MSQQL
Transac tions
Free Space in tempdb
(KB)
Reports free space in tempdb
in KB
<100MB
Wait Stats Analysis
SQL Server wait stats record the amount of time SQL Server spends waiting for each resource. A
number of these wait types are exposed as PerfMon counters:
➤
Lock waits
➤
Log write waits
➤
Network I/O waits
➤
Non-page latch waits
➤
Page I/O latch waits
Search WWH ::
Custom Search