SQL Server 2005: Slow running queries - Blocking and Index problems

As a DBA you must be aware that slow or long running queries can contribute to excessive resource consumption and be the consequence of blocked queries. No database application is exception to this behaviour, so the queries that also run slowly because of wait states for logical lockas and system resources that happens due to the blocking. As per the heading above queries will run slow due to the blocking and missing indexes, not only these 2, there may be indexes that are not used.

On a performance perspective creating  a index on a table is not sufficient, it has to be maintained to reduce the fragmentation, this does not impact the performance of a query, but does impact the DML queries. By experience main root cause of the blocking can be a poor application design, bad query plans, the lack of useful indexes, and an SQL Server instance that is improperly configured for the workload. So when a new query is executed the optimizer will be checking for a plan, few times it might appear to be taking longer than expected. The default configuration of SQL optimizer engine any changes in the statistical information can lead to a poort query planning, missing indexes that forces table scans and slow down with a heavy blocking.

Though the main causes of these symptoms are not necessarily independent of each other. The poor choice of a query plan can tax system resources and cause an overall slowdown of the workload. So, if a large table is missing a useful index, or the query optimizer decides not to use it, this not only causes the query to slow down but it also puts heavy pressure on the I/O subsystem to read the unnecessary data pages and on the memory (buffer pool) to store these pages in the cache. Similarly, excessive recompilation of a frequently running query can put pressure on the CPU.

You can refer to this Get a blocking list for the objects & indexes using DMV blog post for the blocking list in addition to the following SP-TSQL query whenever a blocking is persistent within the SQL instance:

create proc dbo.sp_block (@spid bigint=NULL)
as
select 
    tl.resource_type,
    'database'=db_name(resource_database_id),
    'blk object' = tl.resource_associated_entity_id,
    tl.request_mode,
    tl.request_session_id,
    wt.blocking_session_id     
from 
    sys.dm_tran_locks as tl, 
    sys.dm_os_waiting_tasks as wt
where 
    tl.lock_owner_address = wt.resource_address and
    tl.request_session_id = isnull(@spid,tl.request_session_id)