If existing applications experience blocking and deadlock problems, 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 tables 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 that solves the concurrency problem.
Re-run the concurrency tests. If concurrency is still an issue, change the locking scheme to datarows locking.
