System Performance Counter for SQL Server

Monitoring Server

Using System Monitor (Performance Monitor)

Object (Instance)	Counter	Desc	Values
Analyzing Memory
Memory	Available Bytes	Free Physical memory	System dependent
	Pages/sec	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
Process
	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(_Total)
	% Processor Time	Percentage of time processor was busy	Average value < 80%
	% Privileged Time	Percent of processor time spent in privileged mode	Average value < 10%
System
	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
SQLServere:Latches
	Total Latch Wait Time (ms)
SQLServere:Locks(_Total)
	Lock Timeouts/sec		0
	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	Event	Description
Stored Procedures	RPC:Completed	An RPC completion event
	SP:Completed	A stored procedure completion event
	SP:StmtCompleted	A SQL statement completion event within a stored procedure
TSQL	SQL:BatchCompleted	A T-SQL batch completion event
	SQL:StmtCompleted	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
Sessions	Existing Connection	Represents all the users connected to SQL server before the trace was started
Errors and Warnings
	Attention	Represents the intermediate termination of a request caused by actions such as query cancellation by a client or broken database connections
	Exception	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
Locks
	Lock:Deadlock	Flags the presence of a deadlock
	Lock:Deadlock Chain	Show a trace of the chain of queries creating the deadlock
	Lock:Timeout	Signifies that the lock has exceeded the timeout parameter, which is set by SET LOCK_TIMEOUT timeout_period(ms)
Stored Procedures	DP:Recomplie	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.
	SP:Starting	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.
Transactions
	SQLTransaction	Provides information about a database transaction, including information such as when a transaction started/completed, the duration of the transaction, and so on.