Advertisements
Home > Performance Tuning > Bench Mark Logging

Bench Mark Logging

Bench marking also system administrators to easily see what changed between the time system went live and the current performance issue being experienced. So I was setting this up on the new SQL 2005 Cluster running on Windows 2008; I have setup following counters. Everyone’s needs will be different; if there are counters anyone think I should consider adding please email me at gupta.mohit.k@gmail.com or leave me comment here 🙂 Thanks!

I am tracking following counters and my reasoning for it …

System/OS Level:

  • Logical Disk, I know many people recommend physical disk just in case you are running multiple partitions on one disk. In my case each logical disk does not get shared with anything else so I am only tracking Logical performace.
  • Memory, I am running Active-Active configuration so I need to know how well that will work with memory (I got Min/Max, Lock pages in memory all configured).
  • Page file, Again testing memory pressure.
  • Network, Network I/O
  • Processor, Processor % Time, Queue Length etc.

SQL Level:

  • Buffer Manager, Memory pressure.
  • Database Statistics, Size, Log usage, # of transactions.
  • General Server Statistics, Application in question uses a lot of temp tables so I need to know when the usage of temp tables increases in case I have to adjust tempdb.
  • Latch & Lock Information, Blocking information, if any.
  • Transaction Information, Space in tempdb, batch requests, recomplies, plan cache, etc.
  • Wait Statistics, any waits taking place? If so what, network, disk, memory, or cpu?

Counter List:
\LogicalDisk(*)\% Disk Read Time
\LogicalDisk(*)\% Disk Time
\LogicalDisk(*)\% Disk Write Time
\LogicalDisk(*)\% Free Space
\LogicalDisk(*)\% Idle Time
\LogicalDisk(*)\Avg. Disk Bytes/Read
\LogicalDisk(*)\Avg. Disk Bytes/Write
\LogicalDisk(*)\Avg. Disk Read Queue Length
\LogicalDisk(*)\Avg. Disk sec/Read
\LogicalDisk(*)\Avg. Disk sec/Write
\LogicalDisk(*)\Avg. Disk Write Queue Length
\Memory\Available MBytes
\Memory\Page Faults/sec
\Memory\Pages/sec
\MSSQL$SQLInstanceName:Buffer Manager\Buffer cache hit ratio
\MSSQL$SQLInstanceName:Buffer Manager\Checkpoint pages/sec
\MSSQL$SQLInstanceName:Buffer Manager\Page life expectancy
\MSSQL$SQLInstanceName:Databases(*)\Active Transactions
\MSSQL$SQLInstanceName:Databases(*)\Backup/Restore Throughput/sec
\MSSQL$SQLInstanceName:Databases(*)\Data File(s) Size (KB)
\MSSQL$SQLInstanceName:Databases(*)\Log File(s) Size (KB)
\MSSQL$SQLInstanceName:Databases(*)\Log Flushes/sec
\MSSQL$SQLInstanceName:Databases(*)\Percent Log Used
\MSSQL$SQLInstanceName:Databases(*)\Transactions/sec
\MSSQL$SQLInstanceName:General Statistics\Active Temp Tables
\MSSQL$SQLInstanceName:General Statistics\Logins/sec
\MSSQL$SQLInstanceName:General Statistics\Transactions
\MSSQL$SQLInstanceName:Latches\Average Latch Wait Time (ms)
\MSSQL$SQLInstanceName:Latches\Latch Waits/sec
\MSSQL$SQLInstanceName:Latches\Total Latch Wait Time (ms)
\MSSQL$SQLInstanceName:Locks(*)\Average Wait Time (ms)
\MSSQL$SQLInstanceName:Locks(*)\Lock Requests/sec
\MSSQL$SQLInstanceName:Locks(*)\Lock Timeouts (timeout > 0)/sec
\MSSQL$SQLInstanceName:Locks(*)\Lock Waits/sec
\MSSQL$SQLInstanceName:Locks(*)\Number of Deadlocks/sec
\MSSQL$SQLInstanceName:Memory Manager\Connection Memory (KB)
\MSSQL$SQLInstanceName:Memory Manager\Target Server Memory (KB)
\MSSQL$SQLInstanceName:Memory Manager\Total Server Memory (KB)
\MSSQL$SQLInstanceName:Plan Cache(SQL Plans)\*
\MSSQL$SQLInstanceName:Plan Cache(Temporary Tables & Table Variables)\*
\MSSQL$SQLInstanceName:SQL Errors(*)\*
\MSSQL$SQLInstanceName:SQL Statistics\Batch Requests/sec
\MSSQL$SQLInstanceName:SQL Statistics\SQL Compilations/sec
\MSSQL$SQLInstanceName:SQL Statistics\SQL Re-Compilations/sec
\MSSQL$SQLInstanceName:Transactions\Free Space in tempdb (KB)
\MSSQL$SQLInstanceName:Transactions\Longest Transaction Running Time
\MSSQL$SQLInstanceName:Transactions\Transactions
\MSSQL$SQLInstanceName:Wait Statistics(*)\Lock waits
\MSSQL$SQLInstanceName:Wait Statistics(*)\Memory grant queue waits
\MSSQL$SQLInstanceName:Wait Statistics(*)\Network IO waits
\MSSQL$SQLInstanceName:Wait Statistics(*)\Page IO latch waits
\MSSQL$SQLInstanceName:Wait Statistics(*)\Page latch waits
\MSSQL$SQLInstanceName:Wait Statistics(*)\Wait for the worker
\Network Interface(*)\Bytes Received/sec
\Network Interface(*)\Bytes Sent/sec
\Network Interface(*)\Bytes Total/sec
\Network Interface(*)\Packets/sec
\Paging File(*)\*
\Processor(*)\% Privileged Time
\Processor(*)\% Processor Time
\Processor(*)\Interrupts/sec
\System\Context Switches/sec
\System\Processes
\System\Processor Queue Length
\System\Threads
\Terminal Services\*

Advertisements
Categories: Performance Tuning
  1. No comments yet.
  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: