Database Reference
In-Depth Information
Table 4-3.
Performance Monitor Counters to Analyze Generic SQL Pressure
Object(Instance[,InstanceN])
Counter
SQLServer:Access Methods
FreeSpace Scans/sec
Full Scans/sec
Table Lock Escalations/sec
Worktables Created/sec
SQLServer:Latches
Total Latch Wait Time (ms)
SQLServer:Locks(_Total)
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
Temp Tables Creation Rate
Temp Tables for Destruction
Let's break these down into different areas of concern in order to show the counters within the context where
they would be more useful.
Missing Indexes
To analyze the possibility of missing indexes causing table scans or large data set retrievals, you can use the counter
in Table
4-4
.
Table 4-4.
Performance Monitor Counter to Analyze Excessive Data Scans
Object(Instance[,InstanceN])
Counter
SQLServer:Access Methods
Full Scans/sec
Full Scans/Sec
This counter monitors the number of unrestricted full scans on base tables or indexes. Scans are not necessarily a bad
thing. But they do represent a broader access of data, so they are likely to indicate a problem. A few of the main causes
of a high Full Scans/sec value are as follows:
•
Missing indexes
•
Too many rows requested
•
Not selective enough a predicate
•
Improper T-SQL
•
Data distribution or quantity doesn't support a seek
