#22360957 V1 - Sql Server Performance Monitoring Counters

SQL Server Performance Monitoring

SQL Server is typically affected by the following bottlenecks: 1.

CPU

2.

Memory

3.

File I/O

4.

Locking, blocking and deadlocking Monitoring server I/O subsystem : PhysicalDisk Object: Avg. Disk Queue Length counter to determine the average number of system requests waiting for disk access PhysicalDisk Object: Avg. Disk Transfer/sec counter to determine the rate of read and write operations on the disk. SQL Server Memory Tuning Memory: Pages/sec This counter indicates the rate at which pages are read from or written to disk to resolve hard page faults SQL Server: Buffer Manager: Buffer Cache Hit Ratio: counter indicates the Percentage of pages that were found in the buffer pool without having to incur a read from disk. Buffer Cache Hit Ratio should be consistently greater than 90. This indicates that the data cache supplied 90 per cent of the requests for data. If this value is consistently low, it is a very good indicator that more memory is needed by SQL Server. SQL Server: Memory Manager: Total Server Memory (KB) counter indicates the total amount of dynamic memory the server is currently consuming If Total Server Memory for SQL Server is consistently higher than the overall server memory, it indicates that there is not enough RAM.

CPU Performance Monitoring Processor: Percent Processor Time is the percentage of elapsed time that the processor spends to execute a non-Idle thread Processor: Percent User Time measures the amount of processor time consumed by non-kernel level applications. SQL is such an application. If this is high and you have multiple processes running on a server, you may want to delve further by looking at specific process instances through the instances of the counter Process: Percent User Time. SQL Server: Access Methods - Page Splits/sec Number of page splits per second that occur as a result of overflowing index pages.

Locking, blocking and deadlocking SQL Server Locks Object: Number of Deadlocks/sec. This counter indicates the number of lock requests that resulted in a deadlock. But unless this number is relatively high, one cannot see much here because the measure is by second, and it takes quite a few deadlocks to be noticeable. SQL Server Locks Object: Average Wait Time (ms). This counter indicates the average wait time of a variety of locks, including: database, extent, Key, Page, RID, and table If users are complaining that they have to wait for their transactions to complete, one can find out which object locking on the server is contributing to this problem. SQL Server General Statistics: User Connections. This counter indicates the Number of users connected to the system.

2

3