!!!参考サイト

!Tips for Using SQL Server Performance Monitor Counters / sql-server-performance.com
*http://www.sql-server-performance.com/2005/sql-server-performance-monitor-coutners/

,説明,項目名
,large number of page splits, \SQLServer:Access Methods\Page Splits/sec
,physical RAM is devoted to SQL Server’s data cache,\SQLServer:Buffer Manager\Total pages
,how busy SQL Server,\SQLServer:SQL Statistics\Batch Requests/sec
,To find out how many compilations SQL Server is doing,\SQLServer:SQL Statistics\SQL Compilations/sec
, the number of log flushes per second, \SQLServer:Databases(_Total)\Log Flushes/sec
, the number of users using SQL Server affects,\SQLServer:General Statistics\User Connections
,deadlocks,\SQLServer:Locks(_Total)\Number of Deadlocks/sec
,have to wait for their transactions to complete,\SQLServer:Locks(_Total)\Average Wait Time (ms)
,better to have fewer table scans,\SQLServer:Access Methods\Full Scans/sec
,backup or restore operations are running at sub-optimal speeds,\SQLServer:Backup Device(*)\Device Throughput Bytes/sec + \PhysicalDisk(_Total)\Avg. Disk Queue Length
,using transactional replication,\SQLServer:Replication Logreader(*)\Logreader:Delivery Latency + \SQLServer:Replication Dist.(*)\Dist:Delivery Latency
,key counter to watch is the SQL Server Buffer Manager,\SQLServer:Buffer Manager\Buffer cache hit ratio
,enough memory to run efficiently,\SQLServer:Memory Manager\Target Server Memory (KB) + \SQLServer:Memory Manager\Total Server Memory (KB)
,your SQL Server could use more memory in order to boost performance,\SQLServer:Buffer Manager\Page life expectancy
,moving dirty pages from the buffer to disk in order to free up buffer space,\SQLServer:Buffer Manager\Lazy writes/sec
,When a checkpoint occurs all dirty pages are written to disk,\SQLServer:Buffer Manager\Checkpoint pages/sec
,latch requests that have to wait,\SQLServer:Latches\Average Latch Wait Time (ms)
,latch requests that could not be granted immediately,\SQLServer:Latches\Latch Waits/sec
,total latch wait time (in milliseconds) for latch requests,\SQLServer:Latches\Total Latch Wait Time (ms)


*「SQLServer: Databases: Transaction/Sec: _Total」より「Batch Requests /sec」 の方がいい。 transaction 内の処理はごく一部。
*Compilation が 100 /秒 を超えていたら対応
*Buffer cache hit ratio は 90-95% が目標
*Page life expectancy が300秒以下ならもっとメモリ効率を高められる
*Lazy Write は 毎秒20以下が目標。高いならメモリ追加が必要

!Understanding SQL Performance Counters
*http://www.extremeexperts.com/sql/articles/sqlcounters.aspx
*28項目