How to decrease the cumulative wait time for locks in sql server 2008 R2

_{Thursday, July 19, 2012 3:58 PM}



_{Tuesday, July 24, 2012 8:33 AM ✅Answered}

Hi Shri,

Cumulative Wait Time (sec): The total amount of time in seconds that tasks have waited for one or more resources in the wait category since SQL Server was last started on the instance, or since DBCC SQLPERF was ran on the instance.

A blocking lock occurs when one lock causes another process to wait in a holding queue until the current process is entirely done with the resources. As soon as the first process is complete, the blocked process resumes operation. In a normal server environment, infrequent blocking locks are acceptable. But if blocking locks are common (rather than infrequent), there is probably some kind of design or query implementation problem. Some of the common design problems that cause blocking locks are very wide tables, insufficient indexes, and tables that are not completely normalized.

How to reduce the occurrence of blocking locks and deadlocks: http://msdn.microsoft.com/en-us/library/aa266504(v=VS.60).aspx.

Thanks,
Maggie

Please remember to mark the replies as answers if they help and unmark them if they provide no help. This can be beneficial to other community members reading the thread.