SQL deadlock prediction tools?

After a couple of projects where deadlock issues were only caught late, I came up with this idea for a tool.  Based on some initial poking it looks possible, but it seems obvious enough that someone should have written it already.  I can't find it, however.

Problem: It's hard to prevent deadlocks in SQL Server, even if you use the standard academic practices for concurrency in design.  For example, I've seen SQL server deadlocks due to:

1.  SELECT parallelization.  Did you that on a multiple processor box the two table/index selects in "SELECT a.col1, b.col2 from a join b on a.c3 = b.c3" could execute in any order, including overlapping?  I sure didn't.
2.  Index range locking.

These are fixable with various query hints, or upping the transaction level, but no one ever wants to up the transaction level from the default, for performance reasons.  More importantly, this stuff is never caught during design.

However, it certainly *looks* like it should be possible to write a static code analysis tool that will find all this.  Given a database of tables & stored procedures, the tool could tell you whether or not it was internally consistent.

Does this actually exist out there?  Or do I need to write it?

Jason McCullough
Wednesday, May 26, 2004

While I think the idea has merit, I can see that it would be susceptible to changes in the underlying database engine, especially the optimiser.

You would have to spend a *lot* of time working out exactly how the optimiser works.

Also, the strategies used by the optimiser will change over time, so it is not sufficient to simply "scan" the design and make suggestions.

For example, the optimiser will react differently as tables get more (or less) rows, as the index distribution changes, etc, etc.

Steve Jones (UK)
Thursday, May 27, 2004

Not exactly what you are looking but...

Microsoft has put out a pretty good design analyzer for best practices for SQL Server. It generates a nice web report that tells you where possible problems will occur and when you are falling out of borg compliance. I'm currently using it to make sure that my designs are kosher and it has given me some great 'uh' moments...it will also generate a bunch of subjective items that are more judgement calls than actually incorrect. Oh, and the best part is it's free!

http://www.microsoft.com/downloads/details.aspx?FamilyId=B352EB1F-D3CA-44EE-893E-9E07339C1F22&displaylang=en

One more anon
Friday, May 28, 2004

  Recent Topics

  Fog Creek Home