Many DeadLocks

Question

Many DeadLocks

Mani-584606

SSChampion

Points: 11788
More actions
July 25, 2010 at 1:42 am

#387420

Hi,
We have SQL Server 2005 EE x64 with SP3 instance having Share point 2007 databases.
We are getting many Dead locks when Crawl job runs.
Generally, Crawl job only does indexing of documents in Share point right? But I'm seeing lot of DELETE statements in the Dead lock graphs!! please see the attachment for the Dead lock graph
and please advice what should be done to avoid these dead locks?
Thanks

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 1

Mani-584606 (7/25/2010)
Hi,
We have SQL Server 2005 EE x64 with SP3 instance having Share point 2007 databases.
We are getting many Dead locks when Crawl job runs.
Generally, Crawl job only does indexing of documents in Share point right? But I'm seeing lot of DELETE statements in the Dead lock graphs!! please see the attachment for the Dead lock graph
and please advice what should be done to avoid these dead locks?
Thanks

You have two choices here. One is to review the goodness in this link (and the associated 2 posts) and work this problem yourself. http://blogs.msdn.com/bartd/archive/2006/09/09/Deadlock-Troubleshooting_2C00_-Part-1.aspx

The other choice is to engage a professional to help you with this problem.

Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 2

From what I've heard, deadlocks are 'expected' on sharepoint when running the search crawl. They can be ignored as the search will retry the queries. (this is per the Sharepoint support team)

NB. You cannot make any changes to the sharepoint database (including index changes) without completely losing all support from Microsoft.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Henry Treftz SSCarpal Tunnel Points: 4207 More actions · Answer 3

I think SharePoint is designed so that it can deal with the crawl being the victim in a deadlock. So in this case it might be annoying but it is not a huge issue.

jeff.mason SSCrazy Eights Points: 9585 More actions · Answer 4

Because of the support issues that Gail refers to, we find that it is a waste of time to monitor SQL Servers running SharePoint. Best to make them reside on their own servers in their own little world and not do more than the barest monitoring of them (to ensure that servers and jobs are running and disks aren't full). Too many false positives, and even if there were issues for tuning, we couldn't take tuning action anyway since MS restricts DB changes against SharePoint. If you "ignore" it you won't need to worry about deadlocks, and if performance becomes a major issue on the DB end you don't have a lot you can do without MS anyway.

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 5

GilaMonster (7/26/2010)
From what I've heard, deadlocks are 'expected' on sharepoint when running the search crawl. They can be ignored as the search will retry the queries. (this is per the Sharepoint support team)
NB. You cannot make any changes to the sharepoint database (including index changes) without completely losing all support from Microsoft.

BWAA-HAA!!!! Gotta love that... MS "supports" deadlocks. :-):-D:-P;-):hehe:

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 6

Jeff Moden (7/26/2010)
BWAA-HAA!!!! Gotta love that... MS "supports" deadlocks. :-):-D:-P;-):hehe:

The Sharepoint team only.

Someone I know took this problem to customer support. The SQL support team looked over the logs, did the analysis and identified a single index that, if created, would completely stop the deadlocks. The Sharepoint support team then told him that if he added that index he would lose any support from MS for that installation.

Both teams were right.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 7

GilaMonster (7/26/2010)
Jeff Moden (7/26/2010)
BWAA-HAA!!!! Gotta love that... MS "supports" deadlocks. :-):-D:-P;-):hehe:
The Sharepoint team only.
Someone I know took this problem to customer support. The SQL support team looked over the logs, did the analysis and identified a single index that, if created, would completely stop the deadlocks. The Sharepoint support team then told him that if he added that index he would lose any support from MS for that installation.
Both teams were right.

We had a similar deadlock problem with "Dynamics" (peviously known as "Great Plains"). My systems DBA and I engineered a fix, got the support team to buy into it, and got them to "bless" our fix so we could install it. Guess we caught them on a good day.

As a side bar, they also said they'll add the fix to the "fix list" for a future release. We got pretty lucky all the way around.

Based on all that, I'm surprised that the Sharepoint team isn't just a little more flexible.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

baxterr SSC Veteran Points: 245 More actions · Answer 8

We had a similar deadlock problem with "Dynamics" (peviously known as "Great Plains"). My systems DBA and I engineered a fix, got the support team to buy into it, and got them to "bless" our fix so we could install it. Guess we caught them on a good day.
As a side bar, they also said they'll add the fix to the "fix list" for a future release. We got pretty lucky all the way around.
Based on all that, I'm surprised that the Sharepoint team isn't just a little more flexible.

This is a little off topic now. But, I'm interested in what actions you took to remedy the deadlocks in Dynamics GP? I'm experiencing a lot of them and would be interested in what steps you chose to fix it. Thanks a ton.

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 9

It was a while back so I don't remember any specific details like the name of the proc or anything like that. I do remember the problem though. They started a transaction, did an UPDATE, did a SELECT to store what they just UPDATEd in a variable, and then did a COMMIT. We removed the transaction and used the 3 part update in SQL Server to do the same thing in an implicit transaction. Like this...

UPDATE sometable

SET @variable = column = some expression

WHERE some criteria

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

baxterr SSC Veteran Points: 245 More actions · Answer 10

Thanks Jeff for replying to such an old post. I'm new to supporting Dynamics GP, but it seems they are still having issues in the latest 2010 release as well. I'll post here if I find a specific fix or find more information on the topic. Seems odd that it would have this issue for more than one iteration of the software, but you never know. Thanks again.