Selects showing in deadlock as primary issue, why

Hi,

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.

Thanks
Bob
remenardAsked:
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.

Dale BurrellDirectorCommented:
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.
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
David ToddSenior DBACommented:
Hi,

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)

HTH
  David

PS I use Ola Hallengren's free script at http://ola.hallengren.com/ for most of my database maintenance tasks.
0
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.
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
Microsoft SQL Server

From novice to tech pro — start learning today.