Solved

SQL Head Block not being released

Posted on 2010-08-13
1
1,428 Views
Last Modified: 2012-05-10
Experts,

First let me apologize if this has been asked before.

We have a database we are experiencing some major locking issues with.  We noticed some long wait times and a large number of waiting tasks.  Upon following the session IDs all the way back to the head blocking session ID that session ID is still marked as the head block, but has no SQL Command associated with it.  The only thing associated with the session is "SET TRANSACTION ISOLATION LEVEL READ COMMITTED"

Our web application is Cold Fusion Based hitting a SQL Server 2008 Server.

Please let me know where to look and what to look for.

Thanks in advance.
Chad
0
Comment
Question by:cpence
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
1 Comment
 
LVL 13

Accepted Solution

by:
dwkor earned 500 total points
ID: 33434924
In 99% of the cases concurrency and blocking issues related with non-optimized queries. Take a look at the sql statements system is executing and see is plans are not optimal (table/index scans instead of seek).

As the temporary solution you can enable read committed snapshot mode on the database (http://technet.microsoft.com/en-us/library/bb522682.aspx). In this mode writers do not block readers. It introduces extra load to tempdb so check that it's on the fast drive
0

Featured Post

Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.

738 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question