Selects showing in deadlock as primary issue, why


MS 2008 R1 Standard.

We have a large database that has a high number of users and access.
We are getting a number of deadlocks.
Installed is SQL Power Tools that monitors and captures alot of performance info.
The deadlocks it had been capturing are on large complex selects.
I know they say it has to be an update on there some where, but it shows none.
Some of the large selects have two or three alias for a table it is re-using another way in the join.  The info in the dead lock points to the table not an index and the locks are all IS.

We are trying to get a handle on what is going on but it does not make a lot of sense.
Any suggestions or insight would be greatly appreciated.

Who is Participating?
Dale BurrellConnect With a Mentor DirectorCommented:
Firstly deadlocks can and do happen on pure selects, well not pure ones, they need to be selects that require multiple locks, but there certainly doesn't need to be an update involved.

You are almost certainly missing an index and forcing the query to lock the table early while it does a search. You're joins would be a good place to look to see that you have the correct indexes in place for the joins to happen quickly.

However there are so many things that can cause and affect deadlocks that you really need an entire book.

One thing you can try is running the query optimiser on your query, because often locks that cause deadlocks also result in slower queries.

The next step is to start looking at the execution plan and seeing where its having to do table searches because indexes are missing.

Thats the tip of the deadlock iceberg. I've seen so many questions about that on EE that I would highly recommend you go search for them and read up about them.
David ToddSenior DBACommented:

Is there regular index maintenance happening on these tables/database? Rebuilding the index also updates the statistics. It is a good practice to update the stats when reorganising the index.

All that to say, if the query plan is gone to custard because the tables (more properly the indexes) haven't been maintained, you could be getting table scans (bad, takes time, could lock the entire table) compared to index seeks (good, quick, only touches a few rows)


PS I use Ola Hallengren's free script at for most of my database maintenance tasks.
remenardAuthor Commented:
Thanks for the info. one of the things we finially figured out is that the montioring tool is using a feature that is not supported for production use by MS.  Under load it can fail to keep all the information you need.
They are changing how that aspect of monitoring is done, so I should get better info about deadlocks.
Thanks for your help.
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.

All Courses

From novice to tech pro — start learning today.