Using Perfmon in SQL SERVER

Hi Guys,

In SQL SERVER,every tool is very important to monitor the certain tasks executing on the sql server.In on that track performance monitor also plays very key role in the sense of monitoring the sql server.

performance monitor is the windows level tool to monitor the hardware and software utilization in the system.In perfmon counters are objects which will show the utility of the resources using by certain software nothing but sql server.

I wanna share some of the basic counters in perfmon.

SQL Server Access Methods object: Page Splits/sec

To monitor number of page splits w.r.t SQL Server. If the number of page splits is high, we need to increase the fill factor of our indexes there by providing more room in data pages and subsequently reducing the chances for Page Splits.

SQL Server Buffer Manager Object: Cache Size (pages)

To monitor the amount of physical RAM used by SQL Server's data cache. This number is presented in pages. We should see that no more than 60 %

SQLServer: SQL Statistics: Batch Requests/Sec counter

This counter measures the number of batch requests that SQL Server receives per second. Value greater than 1000 batch requests per second indicate a busy Server, and could lead to CPU bottleneck.

SQLServer: SQL Statistics: SQL Compilations/Sec counters

To monitor compilations on SQL Server requests per second. This counter should not exceed 150.

SQLServer: Databases: Log Flushes/sec

To monitor the number of log flushes per second. This can be measured on a per database level, or for all databases on a SQL Server. If the counter is more, we need to increase RAM allocated for SQL Server.

SQL Server General Statistics Object: User Connections

To monitor the number of user connections, not the number of users, those are currently connected to SQL Server.

Server Locks Object: Number of Deadlocks/sec

To minotor the number of deadlocks per second. Since the monitoring is done on per second basis, we may always see some number for this counter. For average, we need to average this value to seconds.

SQL Server Locks Object: Average Wait Time (ms)

To monitor the average wait time for locks. This counter is very useful if the users experience waits for their transactions to complete.

SQL Server Access Methods Object: Full Scans/sec

To monitor the number of table scans SQL Server instance is performing. This counter is for an entire server, not pertaining to a specific database.

SQL Server Buffer Manager Object: Buffer Cache Hit Ratio

To monitor how often SQL Server access buffer and not the hard disk, to retreive data. The higher this ratio, the less often SQL Server has to go to the hard disk to fetch data, and performance overall is boosted.

SQLServer:Memory Manager: Total Server Memory (KB)

To monitor how much total RAM the instance is using.

SQLServer:Memory Manager: Target Server Memory (KB)

Specifies how much memory SQL Server would like to have in order to operate efficiently. This is actually based on the number of buffers reserved by SQL Server when it is first started up.

ADVANCED COUNTERS:

There are some SQL Server counters that you can watch to help determine if your SQL Server is experiencing any problems.

* SQL Server Buffer Mgr: Page Life Expectancy: To specify how long data pages are staying in the buffer. If this counter gets below 300 seconds, this is an indication that SQL Server could use more memory in order to boost performance.

* SQL Server Buffer Mgr: Lazy Writes/Sec: This counter tells us how many times a second that the Lazy Writer process is moving dirty pages from the buffer to disk in order to free up buffer space. Ideally, it should be close to zero which indicates that your SQL Server's buffer cache is plenty big and SQL Server doesn't have to free up dirty pages, instead waiting for this to occur during regular checkpoints. If this value is high, then a need for more memory is indicated.

* SQL Server Buffer Mgr: Checkpoint Pages/Sec: Higher Value of this counter indicates that the checkpoint process is running more often than it should, which more server resources. In those cases, we need to increase RAM to reduce how often the checkpoint occurs or decrease the "recovery interval" SQL Server.

* SQL Server Buffer Mgr: Page Life Expectancy: To monitor how long data pages are staying in the buffer. If the value is less than 300 seconds means, an indication that the SQL Server could use more memory to boost overall performance.