Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase «««12345»»

Using SQL Profiler to Resolve Deadlocks in SQL Server Expand / Collapse
Author
Message
Posted Wednesday, March 10, 2010 3:23 PM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Wednesday, September 25, 2013 6:23 AM
Points: 392, Visits: 405
Jonathan Kehayias (2/1/2009)
Comments posted to this topic are about the item <A HREF="/articles/deadlocks/65614/">Using SQL Profiler to Resolve Deadlocks in SQL Server</A>


well explained and very helpful. Thanks for sharing


===========================================
Better try and fail than not to try at all...

Database Best Practices

SQL Server Best Practices
Post #880564
Posted Friday, June 04, 2010 12:14 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, September 01, 2010 6:45 AM
Points: 2, Visits: 11
Hello everybody.

Please explain to me one thing. In the article said that I have to run the script to get execution plan, but in BOL I don`t see any information about procedure "BookMarkLookUpSelect". I have SQL 2005 with SP2 on all of my servers. So I whant to understand when and where I have to run this script.

Thanks.
Post #932597
Posted Friday, June 04, 2010 1:31 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Yesterday @ 10:49 PM
Points: 488, Visits: 1,286
@maxibeck,

check the attachements given at the end of the article.


Regards,
Raj

Strictlysql.blogspot.com
Post #932626
Posted Friday, June 04, 2010 7:41 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Tuesday, May 14, 2013 11:05 AM
Points: 1,222, Visits: 140
Great article Jonathon! I look forward to the next SQL Saturday in Tampa so i can sit in our your presentations.

Think great, be great!
Post #932810
Posted Friday, June 04, 2010 8:19 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, April 03, 2014 12:46 PM
Points: 1,413, Visits: 4,531
sanokistoka (10/22/2009)
Have you tried Oracle, which doesn't exhibit, say SELECT/UPDATE deadlocks because of row versioning? Thankfully, there is now row versioning in MSS. The point being is that, yes an application must be coded to handled deadlocks it expects *where* it expects them, but MSS is causing an egregious number of unexpected deadlocks, and I wonder if that's due to page locking and/or lock escalation policies. What's your experience with Oracle and how does it compare with MSS when it comes to unexpected deadlocks?


i forgot the issues with row versioning, but in my experience deadlocks are caused by management issues or poor schema.

few years ago our devs changed an app that hit a 200 million row table. at the same time we saw a huge increase in data. they went from single thread to multi-thread. next thing you know we're seeing different threads of the app block each other. i had an idea to change the clustered index so as not be in in accordance with best practices. reason being that the main query of the app selected up to 7 million rows in some cases. with the current index structure it locked every page of the table. we made this change and it fixed all our blocking issues.

management is a lot of times people will access data with MS Access and lock the entire table. instead of using replicated data people think they are special and want to access data that is always being modified.


https://plus.google.com/100125998302068852885/posts?hl=en
http://twitter.com/alent1234
x-box live gamertag: i am null
[url=http://live.xbox.com/en-US/MyXbox/Profile[/url]
Post #932844
Posted Friday, June 04, 2010 11:53 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Tuesday, March 11, 2014 2:30 PM
Points: 94, Visits: 561
Nice article. Just wanted to point out that you can generate an XDL file from the Profile trace window just by right-clicking the Deadlock Graph event. Choose the Extract Event Data option from the context menu, and a Save As... dialog box pops up allowing you to save the .xdl file for this single event. I use XML Notepad to crack the XML open. (not that I have lots of experience doing this )
Post #932991
Posted Friday, June 04, 2010 3:57 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 6:50 PM
Points: 20,455, Visits: 14,074
It's nice to see good articles like this re-published from time to time.



Jason AKA CirqueDeSQLeil
I have given a name to my pain...
MCM SQL Server


SQL RNNR

Posting Performance Based Questions - Gail Shaw
Posting Data Etiquette - Jeff Moden
Hidden RBAR - Jeff Moden
VLFs and the Tran Log - Kimberly Tripp
Post #933088
Posted Saturday, June 05, 2010 4:33 PM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Tuesday, February 25, 2014 12:31 PM
Points: 1,732, Visits: 1,059
This is my first time reading the article and although you do state,
The deadlock in this type of scenario is completely timing based, and it can be difficult to reproduce without a looping execution as used in the attached demo.
The example you give is very simple and I think a very common scenario. Unfortunately I can't agree with the solution. No I'm not talking about the application handling the deadlock exception and retrying. That's been argued enough already and I actually agree that the application should handled the exception (what it wants to do with it is another story).

I'm questioning the covered index solution. I know with most things computers, the answer is "it depends". So I'd be curious to get some feedback on the following scenario.

We don't have a very busy database in my opinion, but we do experience a lot of deadlocks. Unfortunately the client application uses NHibernate, which I feel is a major part of the problem. But all that aside, let's say we have the following table and requirements:
CREATE TABLE Trip(
id int IDENTITY(1,1) NOT NULL,
userID int NOT NULL,
name varchar(200) NOT NULL CONSTRAINT DF_Trip_name DEFAULT(''),
descriptionText varchar(max) NOT NULL CONSTRAINT DF_Trip_summary DEFAULT(''),
ulLat float NULL,
ulLon float NULL,
brLat float NULL,
brLon float NULL,
created datetime NOT NULL CONSTRAINT DF_Trip_created DEFAULT(getdate()),
lastUpdated datetime NOT NULL,
startDate datetime NOT NULL,
viewCount int NOT NULL CONSTRAINT DF_Trip_viewCount DEFAULT((0)),
sourceDevice varchar(50) NOT NULL CONSTRAINT DF_Trip_sourceDevice DEFAULT(''),
sourceCarrier varchar(50) NOT NULL CONSTRAINT DF_Trip_sourceCarrier DEFAULT (''),
sourceManufacturer varchar(50) NOT NULL CONSTRAINT DF_Trip_sourceManufacturer DEFAULT(''),
poiCount int NOT NULL CONSTRAINT DF_Trip_poiCount DEFAULT((0)),
mediaCount int NOT NULL CONSTRAINT DF_Trip_mediaCount DEFAULT((0)),
startLocationLatitude float NULL,
startLocationLongitude float NULL,
startLocationQuadKey varchar(50) NOT NULL,
CONSTRAINT PK_Trip PRIMARY KEY CLUSTERED
(
id ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON PRIMARY
) ON PRIMARY

GO
CREATE NONCLUSTERED INDEX IX_Trip_startDate_userID ON Trip
(
startDate ASC,
userID ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON PRIMARY

Many inserts happen on this table and although the 'descriptionText' field is defined as varchar(max), the application limits it to 64KB.

Now, let's say that the only SELECT statement that goes against this table is to return a particular user's most recent trips.
SELECT * FROM Trip WHERE startDate > getdate() - 1 AND userID = @userID

The execution plan for that query is, I think, the best it can be. It properly does an Index Seek on the IX_Trip_startDate_userID index to satisfy the WHERE clause, and then does a Key Lookup using the clustered primary key index PK_Trip to get all the data.

So given your example, all that has to happen for a deadlock is the SELECT starts and grabs a lock on IX_Trip_startDate_userID, but before it can get a lock for the key lookup, an INSERT starts and grabs the lock on PK_Trip.

It seems to me that the database should be smart enough to analyze the SELECT query and recognize that it cannot acquire all the necessary data using the IX_Trip_startDate_userID index alone and should know and therefore acquire a lock on PK_Trip at the same time.

I think the solution of creating a covering index in this scenario isn't a very good one since you'd be duplicating all that data, particularly the 'descriptionText' field.

Does anyone have any suggestions or comments? Either on my example scenario or why the database isn't smart enough to acquire the locks that it needs all at the same time. I understand that in order to have a highly performant, highly concurrent system, you need to minimize the use of locks, but your article just surprised me on how seemingly simple it is to generate deadlock in SQL Server by doing something so common and what the database is supposed to be designed to handle, that is, concurrent INSERTs and SELECTs.



Post #933182
Posted Monday, June 07, 2010 6:18 AM


Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Today @ 1:46 PM
Points: 794, Visits: 2,079
kevin77 (6/5/2010)
Does anyone have any suggestions or comments? Either on my example scenario or why the database isn't smart enough to acquire the locks that it needs all at the same time. I understand that in order to have a highly performant, highly concurrent system, you need to minimize the use of locks, but your article just surprised me on how seemingly simple it is to generate deadlock in SQL Server by doing something so common and what the database is supposed to be designed to handle, that is, concurrent INSERTs and SELECTs.

While I'm not the most experienced when it comes to deadlocks (although reading this article has caused to be become smarter in the subject for some reason), I do support a database that gets them frequently. Now most of them are short timed deadlocks but every now and then one occurs that will bring the database to a hault. Some instances (well most) are hardware related, which it is on a VM and shouldn't be (which you can check Jonathan's blog on plenty of articles to support this for me)
However, this application does 10 times more reads than it does writes (updates/inserts/etc), which the major tables have millions of rows of data. So to prevent heavy deadlocking they simply add (NO LOCK) on their SELECT queries since they are reading from a few tables that are consistently being updated or inserted into.
Which the software is a BMC product, I leave it to the peanut gallery to guess which one.



Shawn Melton
PS C:\>(Find-Me).TwitterUri
@wshawnmelton
PS C:\>(Find-Me).BlogUri
meltondba.wordpress.com
Post #933466
Posted Monday, June 07, 2010 11:19 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Tuesday, February 25, 2014 12:31 PM
Points: 1,732, Visits: 1,059
MeltonDBA (6/7/2010)
So to prevent heavy deadlocking they simply add (NO LOCK) on their SELECT queries since they are reading from a few tables that are consistently being updated or inserted into.


Cool, I'd be fine with that solution in my scenario. Unfortunately, I don't think you can tell NHibernate to add that hint. (Yet another reason not to use ORM crap.) But some day we'll hopefully get rid of NHibernate.

Thanks.



Post #933611
« Prev Topic | Next Topic »

Add to briefcase «««12345»»

Permissions Expand / Collapse