Technology Notebook

Performance::MSSQL

Quote from: Buck WoodyThese objects and counters serve as a demonstration of the types of things I monitor for a SQL Server application I use:

Object	Counter	Meaning	Notes
Cache	Data Maps Hits %	NTFS Processing Efficiency	Should be better than 90% or may need to defrag Hard Drive
Cache	MDL Read Hits %	Cache Hits for IIS	Should be better than 90%
Memory	% Committed Bytes	Shows memory use	Should be under 70
Memory	Available MBytes	How much unallocated RAM is left on the server	Should be greater than 50MB
Memory	Cache Faults/Sec	Physical Disk Read I/O Operation	The fewer the better
Network Interface	Bytes Total/sec	Shows network activity	Network Monitor Driver must be installed for accurate measurement of networkitems - Measure against network bandwidth availability
Network Segment	% Network Utilization	Total Network segment use - not from this server alone	Depends on many networking variables, but can be useful to a Networkspecialist
PhysicalDisk	Current Disk Queue Length:_Total	Shows physical drive activity	Less than 3 per physical disk is acceptable
PhysicalDisk	% Disk Read Time	How much time spent doing reads	Combine with Writes to see if Index usage is correct - may need to adjustfillfactor
PhysicalDisk	% Disk Time -- _Total	Shows drive activity	diskperf -yv is required for proper logical disk counters to operate. Shouldbe less than 55% - watch for increase
PhysicalDisk	% Disk Write Time	How much time spent doing writes	Combine with Reads to see if Index usage is correct - may need to adjustfillfactor
Process	% Processor Time	Pick Specific Object	Will explain how much that object is taking on the processor
Processor	% Total Processor Time	Shows the CPU activity being taken by all processes	Should not exceed 80% for continuous periods with high Proc Queue Length.NOTE: W2K measures non-busy time and subtracts it from 100%
Server	Bytes Received/Sec	Data received by the server NIC
Server	Bytes Transmitted/Sec	Data sent by the server NIC
SQL Server:Access Methods	Full Scans/Sec	Table scans	For the entire server - not just one database
SQL Server:Access Methods	Page Splits/Sec	Splits happen when data or indexes span a page (8k)	Fillfactors may not be correct if this is high
SQL Server:Buffer Manager	Cache Size (pages)		Multiply x 8192 for RAM amount, should be close to the RAM in yoursystem.
SQL Server:Locks	Average Wait Time (ms)	Time processes spend waiting on a lock release	Should not be high
SQL Server:Locks	Number of Deadlocks	Number of processes deadlocking each other	Measurement is by the second
SQL Server:Memory Manager	Target Server Memory	How much RAM SQL wants
SQL Server:Memory Manager	Total Server Memory	How much RAM SQL is using
SQL Server:SQL Statistics	Batch Requests/Sec	Bow many batches sent to SQL Server	Over 1000 indicates a busy SQL Server - May indicate CPU bottleneck. A 100Mbnetwork card can handle 3000 per second.
SQL Server:SQl Statistics	SQL Compilations/Sec	How many compiles SQL has to do	Over 100 may indicate a SQL problem
SQLServer:Buffer Manager	Buffer Cache Hit Ratio	Shows how much data is found in the Buffer	Should be 99% or greater
SQLServer:General Statistics	User Connections	Shows the number of connections taken on the SQL Server	Varies
System	% Total Privileged Time	Kernel mode operations	Should be less than 20% or may be IO bound. Pair with %Disk time counter tosee if greater than 50%. Can also indicate driver or NIC.
System	Context Switches/Sec	Server thread switches	Should not exceed 8000 per second, per processor
System	Processor Queue Length	Shows the amount of processes waiting for CPU time	Pairs with % Processor Time counter - Should not be greater than 2 perproc.
Web Service	GET Requests/Sec	Number of GET requests	Each GET connection attempt
Web Service	POST Requests/Sec	Number of POST requests	Each POST connection attempt
Web Service	Total Method Requests/Sec	Hits on the Web Server	Service Request rate