If your existing applications experience blocking and deadlock problems, follow the steps below to analyze the problem:
Check for deadlocks and lock contention:
Use sp_object_stats to determine the tables where blocking is a problem.
Identify the table(s) involved in the deadlock, either using sp_object_stats or by enabling the print deadlock information configuration parameter.
If the table uses allpages locking and has a clustered index, ensure that performance of the modified clustered index structure on data-only-locked tables will not hurt performance.
See “Tables where clustered index performance must remain high”.
If the table uses allpages locking, convert the locking scheme to datapages locking to determine whether it solves the concurrency problem.
Re-run your concurrency tests. If concurrency is still an issue, change the locking scheme to datarows locking.
