What is the best way to track down/eliminate/prevent Deadlock Transaction issues with CF7 and SQL Server 2005?

While I consider myself fairly experienced with CF and SQL syntax, I am not terribly well-versed in SQL Server.  Therefore, I do not (have not) used stored procedures or triggers, etc.  I would prefer not to, at this point, until I have time to fully learn SQL Server.

My current project involves hospital patients (stored in one table) and their treatment events (stored in another table).  The treatment table contains 56 columns of varying type and, since it contains every unique patient treatment event, contains hundreds of thousands of records.  The table can be accessed from dozens of pages and reports and can be updated in a half-dozen other places.
Long story short, it's a big table that has multiple avenues of entry and, as a result, is constantly being viewed and altered by users.

Slowly, over time (years), I've been noticing more and more of the "Transaction (Process ID XX) was deadlocked on lock resources with another process and has been chosen as the deadlock victim." errors and they all seem to involve using a SELECT on the treatment table.
I've tried to consider and repair most "best practice" SQL issues (there are no "SELECT * FROM", for example) but it has not helped.

I've read in many places that I should consider using "SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED" before my SELECTS (and, presumably, my INSERTS and UPDATES?).  I'm not sure if this is the best solution to the issue or if it simply glosses over a bigger issue.

So, considering the following:

1) Pure SQL only (no triggers or stored procedures)
2) Large (and growing) table (indexed by SQL Query Analyzer)
3) Can be accessed from many places through the system
4) We have several installations -- all meet a minimum system requirement of CF7 and MS SQL 2005.

What would be the best solution or way to attack this?
kevinmkr1Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

_agx_Commented:
I'm going to let an MS SQL expert guide you on this but my $0.02 is ..

> The treatment table contains 56 columns of varying type and, since it contains
> every unique patient treatment event, ... contains hundreds of thousands of records.

I suspect that may be a big part of your problem.  Generally, having slimmer tables are better. When you have 1 or 2 big tables that are really wide, it's often a sign things need to be redesigned into smaller, more normalized, tables.  The problem with having only 1 or 2 big tables is that everything in the application uses them. So everybody (ie reports, scripts, etc..) is fighting for access to those resources at the same time. Most likely the same type of access. So there's a lot of contention (and as you've discovered) a much higher potential for blocking.  

>  consider using "SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED"
> before my SELECTS (and, presumably, my INSERTS and UPDATES?).

    I would strongly suggest you don't do that unless you really understand what it's doing (when to use it
    and when NOT to ..)  Now add in the fact that you're working with something as "trivial" as hospital patient
    information .. it's even more important you understand the consequences of using READ UNCOMMITTED   ;-)

0
kevinmkr1Author Commented:
That is something that I had considered (shrinking that table) but was concerned that it would end up being a whole lot of work recoding with the potential that it wouldn't fix the issue.  The reason being that 100% of the accesses includes the "DueDate", either for creating calendars, or running reports ("Who's coming in this week?", "Which patients are receiving this drug this month?", "Which patients are coming in today for Dr. Smith?").  So, while I *could* break out many of the lesser used columns (at a great time-expense), every request would be hitting that table, regardless.

So, I guess I should ask you -- would cutting the table down to 50% (or 25%) of its width, without reducing the quantity of accesses, speed the transaction execution time enough to make the effort worthwhile, based on your experience?
0
_agx_Commented:
From what you've described, it sounds like it could be a number of things. Or possibly even several _different_ deadlocks occurring for different reasons. So given that I'm _not_ an ms sql expert, I'd hesitate to say "doing x will definitely fix your problem".  The only way to really fix deadlock problems is to isolate the actual cause. Find the statements involved, and the chain of events leading up to it, by profiling and tracing.  Once you know the cause, you can determine what's the "right" fix for that specific case, and for your application.  Unfortunately, there's no one right answer.

http://stackoverflow.com/questions/20047/diagnosing-deadlocks-in-sql-server-2005
http://www.simple-talk.com/sql/learn-sql-server/how-to-track-down-deadlocks-using-sql-server-2005-profiler/

> would cutting the table down to 50% (or 25%) of its width

Maybe .. maybe not.  Table size isn't the only factor. In older versions I've gotten deadlocks on tables with only 5 columns, due to how the table was accessed from several pages.  But, since that table wasn't used by every page in the application, it was a lot easier to diagnose why the problem was occurring, and fix it with the right type of index.  

> That is something that I had considered (shrinking that table) but was concerned that it
> would end up being a whole lot of work recoding

It probably would.  I've never liked working with really wide tables (that contain many records), because they have problems of their own.  It usually means things aren't normalized, there's a lot of duplication, and you're doing a lot of slower string matching.  

Again, these are just my non-dba impressions. So take it all with a grain of salt ;-)  BTW: This is really more an MS SQL question than CF.  So perhaps some of MS SQL experts were put off by the CF in the title (ie thinking "I don't know anything about ColdFusion" ;-).

HTH
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Cloud Class® Course: Python 3 Fundamentals

This course will teach participants about installing and configuring Python, syntax, importing, statements, types, strings, booleans, files, lists, tuples, comprehensions, functions, and classes.

kevinmkr1Author Commented:
That's a really great point -- I'm sure you're right (regarding the CF vs SQL tagging).  My thought process was, I am not prepared for any extremely heavy SQL at this point and so an answer like, "move all of your SQL inserts to Stored Procedures and then set a trigger here...etc" would be far too much for me at this point.  I was rather hoping somebody with CF experience would have some magical solution.  Considering that I've been coding for 10 years in CF and this is my first time posting on Experts-Exchange, I should have realized that I would not be receiving a simple answer -- otherwise I would have been able to find it by my traditional avenues.  :)

Do you have any experience with 'READ_COMMITTED_SNAPSHOT'?  That came up a few times as a solution, as well.

As for finishing out this question, I'm not sure what the common Experts Exchange protocol is, but I would like to leave this open for another few days just in case a true dba shows up.  I intend to award you partial (or complete) points, in all cases.  I would even take your recommendation as to how to distribute them (if you do not end up receiving them all).

Thanks for your assistance.
0
_agx_Commented:
> otherwise I would have been able to find it by my traditional avenues

   Exactly.  Unfortunately, the answer is often "it depends".  

> Do you have any experience with 'READ_COMMITTED_SNAPSHOT'?  
 
    Unfortunately, no.  What I know about it suggests it's supposed to help with blocking/deadlocks due
    to read/write locking conflicts (ie select vs update, insert,etc..).  But as with everything there are trade-offs.
    It wasn't the right fit for one of our applications where we were considering it. The app already made heavy
    use of tempdb and had lots of SET TRANSACTION statements littered everywhere (legacy apps... ugh) . But
    that would be a good question to ask advice on in one of the sql zones.  Ask what experiences people have
    had with it pros/cons..

> regarding the CF vs SQL tagging

   As far as the tagging, I think everything was fine except for the "CF" reference in the title and first line.
   Selecting the "SQL Server 2005" zone will ensure the right experts see the question. But a lot of people
   pick which questions to review based on the title or first sentence alone. So unless the issue really
   involves CF, you're often better off describing the db side of things only.

   BTW: If you can't edit the question, you can always use the "request attention" feature and ask
   a moderator to re-categorize the question into different zones.

> I would like to leave this open for another few days

   In cases like this, definitely. You really don't have an answer yet ;-) So leaving it open for a few more
   days is totally fine IMO.

>  how to distribute them

    As far as points, I wouldn't worry about it yet.  Though, I appreciate you're asking.  If you're relatively
    new to EE - there's the official grading guidelines in the FAQ's.  But in my experience everyone handles
    the distribution of points a little differently.  Ultimately it should come down to which response(s)/expert(s)
    _you_ feel answered the question at hand, or provided info. you found useful in solving the problem.  

    But again, I wouldn't worry about it yet.  Frankly, I'd REALLY like to see an MS SQL dba type chime in here ...  
    points or no points ;)  
0
_agx_Commented:
> READ_COMMITTED_SNAPSHOT
>  Ask what experiences people have had with it pros/cons..

One more thing. You may have seen these already, but definitely look into some of the pros/cons.  As usual, there are conflicting opinions:

http://www.sql-server-performance.com/articles/per/new_isolation_levels_p2.aspx
http://social.msdn.microsoft.com/Forums/en-US/sqldatabaseengine/thread/d1b3d46e-2642-4bc7-a68a-0e4b8da1ca1b
http://www.jimmcleod.net/blog/index.php/2009/08/27/the-potential-dangers-of-the-read-committed-snapshot-isolation-level/

(Okay, I'm done now...)
0
kevinmkr1Author Commented:
It doesn't look like I'm going to get any other action here so I think I'll close this up.

Thanks again for the links and advice.  It should get me well on my way towards diagnosing these issues.
0
kevinmkr1Author Commented:
As this doesn't seem to be a "one size fits all" situation, I'm not sure how the answer provided could have been improved upon.
0
_agx_Commented:
It might be worth it to open a new question - only in the MS SQL-type zones - about the READ_COMMITTED_SNAPSHOT issue.  As for some opinions/experiences of people who've used it.  Then at least you'll have a better sense of how viable an option it is (or isn't) for your particular setup.  

Best of Luck
0
_agx_Commented:
> As for some

Correction:  ASK for some ..
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
ColdFusion Language

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.