Microsoft® SQL Server 2012 Unleashed

Monitoring Other SQL Server Performance Items

In addition to the main system items that affect performance which have been covered already, there are other items in SQL Server that affect system performance that you should include in your performance monitoring approach.

Monitoring Locks

One of the often-overlooked areas of performance degradation is locking. You need to ensure that the correct types of locks are issued and that the worst kind of lock, a blocking lock, is kept to a minimum. A blocking lock, as its name implies, prevents other users from continuing their own work. An easy way to identify the level of blocking locks is to use the counter SQLServer:Memory Manager:Lock Blocks. If this counter frequently indicates a value greater ...