What Performance counters will you use to set up SQL Server baseline ?

Leave a comment (1) Go to comments

What Performance counters will you use to set up SQL Server baseline ?

Setting up SQL Server baseline is primary objective for every DBA. This baseline will be key during troubleshooting performance issues. A DBA should setup up a server base by using the following performance counters.

S. No Counter Name Description
1 Memory\Available Bytes Indicates how much physical memory is remaining after the working sets of running processes and the cache have been served
2 Memory\Page Faults/sec Page faults / sec is the sum of hard and soft page faults. Soft page faults are where the data is found elsewhere in RAM. For example, Word has opened the spellchecker, and now Outlook wishes to use it, there is no need for another call to the disk as the spellchecker is already in memory.Hard page faults are generated when ever data has to be fetched from the pagefile on the disk.
3 Memory\Pages/sec The less paging the better your server’s performance. This counter measures ‘hard’ page faults, in other words the page in no where in memory, so has to be fetched from the disk (pagefile).
4 PhysicalDisk(_Total)\% Disk Time The PhysicalDisk: Percent Disk Time counter monitors the percentage of time that the disk is working or Disk Time is the percentage of elapsed time that the selected disk drive is busy servicing read or write requests. Check the PhysicalDisk: Current Disk Queue Length counter to see the number of requests that are queued up waiting for disk access.
PhysicalDisk(_Total)\% Disk Read Time This counter will monitors the percentage of time that the disk is working in read operations
5 PhysicalDisk(_Total)\% Disk Write Time This counter will monitors the percentage of time that the disk is working in Writeoperations
6 PhysicalDisk(_Total)\Avg. Disk Read Queue Length Avg. Disk Read Queue Length is the average number of read requests that were queued for the selected disk during the sample interval.
7 PhysicalDisk(_Total)\Avg. Disk Write Queue Length Avg. Disk Write Queue Length is the average number of write requests that were queued for the selected disk during the sample interval.
8 Processor(_Total)\% Privileged Time this counter measures is what percent of the System: % Total Processor Time counter is used for running Kernel Mode (sometimes also referred to as privileged mode) code
9 Processor(_Total)\% Processor Time This Counter will measures the total % utilization of your processors by all running processes
10 Processor(_Total)\% User Time this counter measures is what percent of the System: % Total Processor Time counter is used for running User Mode code
11 Processor(_Total)\Interrupts/sec The average rate per second at which the processor handles interrupts from applications or hardware devices. High activity rates can indicate hardware problems.
12 Processor(<Individual_Processor>)\% Processor Time This Counter will measures the total % utilization of specific processor by all running processes, which are running on that processor
13 SQLServer:Access Methods\Full Scans/sec indicates that we are having table / Index page scans
14 SQLServer:Access Methods\Page Splits/sec Number of page splits per second that occur as a result of overflowing index pages.Having indexes with very high fill factors on volatile tables will cause more page splits. This value needs to be low as possible.
15 SQLServer:Access Methods\Table Lock Escalations/sec This gives us the number of times a table lock was asked for in a second. A high number needs a revisit to the query and the indexes on the table.
16 SQLServer:Buffer Manager\Buffer cache hit ratio The percentage of pages that were found in the memory. This included the availability of the procedure and the data cache in the system.
17 SQLServer:Buffer Manager\Database pages Number of pages in the buffer pool with database content. Number of pages in the buffer pool with database content.
18 SQLServer:Buffer Manager\Stolen pages This is the number of pages that were stolen from the buffer cache to satisfy other memory requests.
19 SQLServer:Databases(_Total)\Active Transactions The number of currently active transactions in the system.
20 SQLServer:Databases(_Total)\Log Growths The number of times the log files have been extended. Should be least in numbers
21 SQLServer:Databases(_Total)\Transactions/sec This number indicates how active our SQL Server system is.
22 SQLServer:General Statistics\Logins/sec Total number of logins started per second.
23 SQLServer:General Statistics\Logouts/sec Total number of logout operations started per second.
24 SQLServer:General Statistics\User Connections The number of users currently connected to the SQL Server.
25 SQLServer:Latches\Average Latch Wait Time (ms) Shows the average time for a latch to wait before the request is met
26 SQLServer:Locks(_Total)\Lock Requests/sec Number of requests for a type of lock per second.
27 SQLServer:Locks(_Total)\Lock Timeouts/sec This counter shows the number of locks per second that timed out. It should be Zero
28 SQLServer:Locks(_Total)\Lock Wait Time (ms) Total wait time (milliseconds) for locks in the last second.
29 SQLServer:Locks(_Total)\Lock Waits/sec Shows the number of locks per second that could not be satisfied immediately and had to wait for resources
30 SQLServer:Locks(_Total)\Number of Deadlocks/sec This counter shows the number of deadlocks on the SQL Server per second
31 SQLServer:Memory Manager\Connection Memory (KB) Amount of memory in KB used to maintain the connections.
32 SQLServer:Memory Manager\Optimizer Memory (KB) The amount of memory in KB that the server is using for query optimization. This value shoud be constant kind of thing
33 SQLServer:Plan Cache(_Total)\Cache Hit Ratio The ratio between the cache hits and misses
34 SQLServer:Plan Cache(_Total)\Cache Object Counts Number of cache objects in the cache
35 SQLServer:Plan Cache(_Total)\Cache Objects in use Number of cache objects in use
36 SQLServer:Plan Cache(_Total)\Cache Pages Number of 8k pages used by cache objects
37 SQLServer:SQL Statistics\SQL Compilations/sec The number of times per second that SQL Server compilations have occurred
38 SQLServer:SQL Statistics\SQL Re-Compilations/sec This needs to be nil in our system as much as possible. A recompile can cause deadlocks and compile locks that are not compatible with any locking type
39 TCP\Connection Failures The number of connections that have failed since the service was started (regardless of when PerfMon was started). TCP counts a connection as having failed when it goes directly from sending (SYNC-SENT) or receiving (SYNC-RCVD) to CLOSED or from receiving (SYNC-RCVD) to listening (LISTEN).
40 TCP\Connections Active Connections Active is the number of times TCP connections have made a direct transition to the SYN-SENT state from the CLOSED state.
41 TCP\Connections Established The number of simultaneous connections supported by TCP (at last observation). This counter displays the number of connections last observed to be in the ESTABLISHED or CLOSE-WAIT state. It displays the last observed value only; its value is not an average.
Other Counter, should also be added




1 System Object\Processor Queue Length
2 Memory\Available Mbytes
3 PhysicalDisk \ Disk Queue Length
4 PhysicalDisk \ % Idle Time
5 Network Interface \ Bytes Total/Sec
6 SQL Server Database \ Transactions/Sec
7 SQL Server Locks \ Average Wait Time
8 SQL Statistics: Batch Requests/sec
9 SQL Statistics: SQL Recompilations/sec
10 SQL Statistics: SQL Compilations/sec
11 \Process(*)\Private Bytes
12 System\Context Switches/sec


EOF - What Performance counters will you use to set up SQL Server baseline ?, SQL Server 2012 , 2008 R2 , 2008 , 2005 , 2000
Leave a comment

1 Comments.

  1. Good list . Also, if baselining a VM\ESX consider baselining the Hypervisor Host : http://www.sqlserver-dba.com/2011/04/virtualization-and-database-servers.html

Leave a Reply

Your email address will not be published. Required fields are marked *


*


You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>

Notify me of followup comments via e-mail. You can also subscribe without commenting.