[Last Call] Learn about multicloud storage options and how to improve your company's cloud strategy. Register Now

x
?
Solved

SQL Head Block not being released

Posted on 2010-08-13
1
Medium Priority
?
1,457 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 1500 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

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

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

It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
This month, Experts Exchange sat down with resident SQL expert, Jim Horn, for an in-depth look into the makings of a successful career in SQL.
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

650 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