iCore IT Service

SQL Server Checks

SQL

SQL srv: Checks if the SQL server process is running.

SQL srv Avg. Latch Wait Time: The wait time (in milliseconds) for latch requests that have to wait. Note here that this is a measurement for only those latches whose requests had to wait. In many cases, there is no wait. So keep in mind that this figure only applies for those latches that had to wait, not all latches.
A latch is in essence a "lightweight lock". From a technical perspective, a latch is a lightweight, short-term synchronization object (for those who like technical jargon). A latch acts like 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 (which is very quick indeed) to prevent the data in the row from being changed during this very short time period. This not only applies to rows of data, but to index information as well, as it is retrieved by SQL Server.
Just like a lock, a latch can prevent SQL Server from accessing rows in a database, which can hurt performance. Because of this, you want to minimize latch time.
SQL srv Batch Requests sec: This counter measures the number of batch requests that SQL Server receives per second, and generally follows in step to how busy your server's CPUs are. Generally speaking, over 1000 batch requests per second indicates a very busy SQL Server, and could mean that if you are not already experiencing a CPU bottleneck, that you may very well soon. Of course, this is a relative number, and the bigger your hardware, the more batch requests per second SQL Server can handle.

From a network bottleneck approach, a typical 100 Mbs network card is only able to handle about 3000 batch requests per second. If you have a server that is this busy, you may need to have two or more network cards, or go to a 1 Gbs network card.

SQL srv Buffer Cache Hit ratio: This shows the ratio of how many pages are going to memory versus disk. The percentage of pages that were found in the memory. The higher the value the better. Preferred around the 90% mark. This included the availability of the procedure and the data cache in the system. If you see this number as low, it may mean that SQL Server is not obtaining enough memory from the operating system.

SQL srv CPU usage: Indicates the processor usage by the SQL server. This number is calculated per processor. For a server with 4 CPUs means that the maximum number is: 4cpus * 2 cores * 100% = 800.

SQL srv DB Pages: This number shows the number of pages that constitute the SQL data cache. A large changes in this value indicates the database is swapping cache values from the cache. We need to either increase the memory of the system or the max server memory parameter.

SQL srv Deadlocks: (<1) The number of lock requests that resulted in a deadlock. If you see anything above 0, your users and applications will experience problems. Their queries will abort and the applications may fail.

SQL srv Latch Waits:(<10) Shows per second  the total number of latch requests that could not be immediately satisfied and had to wait

SQL srv Lock Wait Time ms:  This is the wait time in milliseconds to acquire a lock. Lower the value the better it is. For “Lock Wait Time” it is recommended to look beyond the Avg value.  Look for any peaks that are close (or exceeds) to a wait of 60 sec.   Though this counter counts how many total milliseconds SQL Server is  waiting on locks during the last second, but the counter actually records  at the end of locking event.  So most probably the peaks represent one huge locking event.  If those events exceeds more than 60seconds then they may have extended blocking and could be an issue. In such cases, thoroughly analyze the blocking script output. Some applications are written for timing out after 60 seconds and that’s not acceptable response for those applications.

SQL srv Lock Waits/sec: (0) Shows the number of locks per second that could not be satisfied immediately and had to wait for resources. This counter reports how many times users waited to acquire a lock over the past second.  Note that while you are actually waiting on the lock that this is not reflected in this counter—it gets incremented only when you “wake up” after waiting on the lock. If this value is nonzero then it is an indication that there is at least some level of blocking occurring.  If you combine this with the Lock Wait Time counter, you can get some idea of how long the blocking lasted.  A zero value for this counter can definitively prove out blocking as a potential cause; a nonzero value will require looking at other information to determine whether it is significant. 

SQL srv Log Growths: The number of times the log files have been extended. If there is lot of activity in this counter we need to allocate static and large enough space for our log files.

SQL srv Memory:  The Total Server Memory is the current amount of memory that SQL Server is using.  If this counter is still growing the server has not yet reached its steady-state, and it is still trying to populate the cache and get pages loaded into memory.  Performance will likely be somewhat slower during this time since more disk I/O is required at this stage.  This behavior is normal.  Eventually Total Server Memory should approximate Target Server Memory.

SQL srv Memory Grants Pending: Number of processes waiting on a workspace memory grant. If this counter has a high value SQL Server could benefit from additional memory. You should first examine the database design, queries and indexes however to ensure the system is properly tuned before purchasing additional RAM.

SQL srv Transactions sec: This number indicates how active our SQL Server system is. A higher value indicates more activity is occurring.

SQL srv User Connections: The number of users currently connected to the SQL Server.