SQL 调优

 

内存: OS相关计数器
Memory :Available Mbytes >100MB
Memory :Free System Page Table Entries >7000
Memory :Pages Input/Sec <10
Paging File :Paging File: %Usage < 70%

内存: SQL Server相关计数器
Target Server Memory (KB)
Total Server Memory (KB)

CPU:OS相关计数器
Processor: Processor Time < 80%
Processor: Privileged Time < 30% of Total %Processor Time
System: Context Switches/sec< 3000 *
System: Processor Queue Length < 4 *

* per CPU

CPU: SQL Server相关计数器
Process(sqlservr) :% Processor Time < 80%
Process(sqlservr): % Privileged Time < < 30% of %Processor Time (sqlservr)

Physical Disk 读相关计数器
Avg. Disk sec/Read
Excellent <  08 MS
Good  <  12 MS
Fair  <  20 MS
Poor  >  20 MS

Physical Disk 写相关计数器
Non cached Writes
Excellent <  08 MS    
Good  <  12 MS    
Fair  <  20 MS    
Poor  >  20 MS    

Cached Writes Only相关计数器
Excellent <  01 MS    
Good  <  02 MS    
Fair  <  04 MS    
Poor  >  04 MS   

SQL Server, Access Methods相关计数器
Forwarded Records/sec: < batch /10
FreeSpace Scans/sec <batch /10
Index Searches/sec:Full Scans/sec > 1000
Page Splits/sec < batch /5
Worktables Created/sec < 200

SQL Server, Buffer Manager相关计数器
Buffer cache hit ratio > 97%
Checkpoint pages/sec
Lazy writes/sec < 20
Page life expectancy > 300
Page lookups/sec < batch /sec x 100
Page reads/sec, Page writes/sec < 90

SQL Server, General Statistics
Logins/sec, Logouts/sec <2
Processes Blocked non zero

SQL Server, Latches
(Total Latch Wait Time) / (Latch Waits/Sec) < 10

SQL Server, Locks
(Lock Request/sec)/(Batch Requests/sec) < 500
Number of Deadlocks/sec < 1

SQL Server, SQL Statistics
Batch Requests/sec
SQL Compilations/sec
< Batch Requests/sec x 10% (SQL2k)
< Batch Requests/sec x 20% (SQL2k5)
SQL Re-Compilations/sec
< SQL Compilations/sec x 10% (SQL2k)
< SQL Compilations/sec x 20% (SQL2k5)

Inside SQL Server 系列 (2005)
SQL Server Internal (2008)