Monitor Your Servers around the World with ManageEngine Applications Manager

In Database PagesConnection Statistics you can find the number of connections that have been established. If this has largely higher values then you need to address it by distributing the load. After distributing the load you can verify the distribution. Logins/Min also can be used for the same issue.

Cache Details also is important when measuring SQL Server performance. The Cache Hit Ratio indicates how often SQL Server goes to the buffer. The higher the ratio, the less often SQL Server has to go to the hard disk to fetch data. In an OLTP application this ratio should exceed 90%. The Cache Pages number generally should be close to the total amount of RAM in the server. If the amount of RAM devoted to the data cache is much smaller then you need to so some further investigation to find out why.

Latch Details and Access Method Details are also helpful to DBAs.

A latch acts as a lock in that its purpose is to prevent data from changing unexpectedly. For example, when a row of data is being moved from the buffer to the SQL Server storage engine, a latch is used by SQL Server during this move to prevent the data in the row from being changed during this very short time period. A latch can prevent SQL Server from accessing rows in a database, which can hurt performance. Because of this, you need to minimize latch time. Average Latch Wait Time and Latch Waits/Sec are the available parameters within Applications Manager.

The Average Latch Wait Time (Min) is a measurement for only those latches for which requests had to wait. In many cases, there is no wait.

The Latch Waits/Min is the number of latch requests in a one-second period that could not be granted immediately. This is measured by Average Latch Wait Time (Min).

Applications Manager doesn’t provide a Total Latch Wait Time. However, this can be derived from the previous two counters.

For Access Method Details, Full Scans Per Min tells you how many full scans have occurred per minute. Generally, it is better to have fewer table scans for a healthier database.

Lock Details are also very important to DBAs. You track deadlocks by using these counters. The Deadlocks/Min counter should be at a low value. You can use the Average Lock Wait Time counter to measure the average wait time of a variety of locks, including database, extent, Key, Page, RID, and table locks.

To get a feel for how busy SQL Server is, monitor the Batch Requests/Min counter under SQL Statistics. Generally, a count over 50,000 indicates a very busy SQL Server. A High SQL Compilations/Min value indicates that the server is going for unnecessary compilations.

Memory plays a vital role in the smooth operation of SQL Server. Total Memory tells you how much the mssqlserver service currently is using. SQL Cache Memory should be close to the Total Memory for good performance. If it is not close, the server is probably not a dedicated SQL Server.


Leave a comment

Your email address will not be published.