SoberCounsel (12/18/2012)
I'd investigate adding the "with(nolock)" hint to your query - it doesn't involve killing processes, and it might solve your problem if it's being caused by an errant lock - BUT, you will need to be aware of the issues that this particular hint can/might cause ("dirty reads").
The NOLOCK query hint has for worse implications than what most people consider "dirty reads". People hear that and think it only means uncommitted transactions. You can get duplicate or even missing data. Now I am not saying it should never be used but the implications are far greater than just a flippant "throw a NOLOCK on that query so it will run faster".
Here are just a couple of articles explaining this hint in further detail.
http://blogs.msdn.com/b/davidlean/archive/2009/04/06/sql-server-nolock-hint-other-poor-ideas.aspx
http://sqlblog.com/blogs/andrew_kelly/archive/2009/04/10/how-dirty-are-your-reads.aspx
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/