Table fragmentation, Index contention and locking issues to resolve

Yet another important factors that every DBA needs to concerned about table & index fragmentation within their SQL Server environment. Refer to the article about DetectTableFragmentation  in both 2000 and 2005 version.

Fragmentation occurs due to updates and delets on the table and the golden rule is that to have relevant indexes for better performance. As you may aware that indexes involve both a maintenance cost and a read benefit, the overall index cost benefit can be determined by comparing reads and writes.   Not always feasible for the optimizer to get the performance due to the fragmentation as the referring an index within a query with hints allows to avoid table scans however they do require maintenance to be kept up-to-date. 

To see the index contention on a table DMV sys.dm_db_index_operational_stats helps you a lot as it also reports the waits for locks, in this scenario refer to the blog to identify the TranasctionLocks that are offending and may be causing performance loss. Within this specified DMV you have to refer to the columns such as row_lock_count, row_lock_wait_count, page_lock_wait_count, page_latch_wait_count, page_latch_wait_in_ms, pageio_latch_wait_count, pageio_latch_wait_in_ms detail lock and latch contention and with these it will be easy to term the waits that are causing the performance loss.

Further to this using Server side trace (profiler) will help to track down the offending queries, don't forget to use Profiler Blocked Process Report.  SQLCAT's recommendation is to set the Blocked Process Threshold to 15 using sp_configure ‘Blocked Process Threshold’,15.  Then proceed to run a trace to capture blocks over 15 seconds. Using this trace you will be able to identify the blocked and blocking processes that are long blocks with details can be saved in a file for further analysis once you have killed or taken action to resolve the blocking.

Don't forget the BlackBox feature as referred in that post.