July 1, 2012 at 1:13 pm
Thanks for this. I can not open the plan on my machine.I will try from my work machine tomorrow.
GulliMeel
Finding top n Worst Performing queries[/url]
Improve the performance of Merge Join(special case)
How to Post Performance Problem -Gail Shaw[/url]
July 1, 2012 at 1:41 pm
Les Cardwell (7/1/2012)
>>Please define 'chokes'. You get an error message?Msg 102, Level 15, State 1, Procedure UpdEPCollectorAssociation, Line 15
Incorrect syntax near 'sp_getapplock'.
That's incredibly odd... I wonder if it's whining (badly) about your lack of transaction wrapper, which I flopped which side you had to call it on. This works just fine:
BEGIN TRAN
EXEC sp_getapplock @DbPrincipal = 'dbo', @Resource = 'Form1',
@LockMode = 'Shared';
EXEC sp_releaseapplock @DbPrincipal = 'dbo', @Resource = 'Form1';
COMMIT TRAN
GO
which is basically just a copy/paste of your code in SQL 2k5 Express. *scratches head*.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
July 1, 2012 at 6:57 pm
Pffttt...in my overwhelmed state, and attempts at trying various row locking mechanisms, I didn't add EXEC (doh!).
I'll give it a go tomorrow after I see how this latest set of alterations to Isolation Levels runs overnight...
Thx,
~Les
Dr. Les Cardwell, DCS-DSS
Enterprise Data Architect
Central Lincoln PUD
July 2, 2012 at 1:15 am
Still no luck with plan opening.I am opening it in text pad and data is kidn of scrambled.
G...
Looks like the indexes are issues here.
The delete statement is using clustered index but all index keys are not part of it.Thus all the rows which has dailystartdate =@startdate will have the locks while only the one for a particular endpoint will be deleted.Thus for the same @startdate will have to wait...
I added two indexes to the CollectorTransitionHistory table, based on the predicates. The first statement is against the Endpoints table, and the EndpointID is the PK (clustered), so it's probably not the culprit.
I am not sure I got your point here. But one correction from my side. I read the order of the key wrongly.For delete statement you have endpoint and dailystartdate as the sarg and your clustered index's first two keys are also these columns.Earlier i thought that collectorid is the second column and thus thought it might scan on first column and then lock unnecessary rows. But now that is not the case it wil lock the rows which wil be deleted actually. So it was my bad..
I will try to get plan work for me somehow..
GulliMeel
Finding top n Worst Performing queries[/url]
Improve the performance of Merge Join(special case)
How to Post Performance Problem -Gail Shaw[/url]
July 2, 2012 at 9:21 am
Not sure why no one recommended trace flag 1222, nor this series of blog posts: http://blogs.msdn.com/bartd/archive/2006/09/09/Deadlock-Troubleshooting_2C00_-Part-1.aspx
I note that deadlock investigations and fixing can be a VERY complex undertaking. Consider getting a professional on board for some assistance.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
July 2, 2012 at 9:32 am
Not sure why no one recommended trace flag 1222, nor this series of blog posts: http://blogs.msdn.com/bartd/archive/2006/09/09/Deadlock-Troubleshooting_2C00_-Part-1.aspx
I asked for deadlock graph 😉
But all of above can be validated based on the actual plan for these statements,if possible deadlock graph as well?
GulliMeel
GulliMeel
Finding top n Worst Performing queries[/url]
Improve the performance of Merge Join(special case)
How to Post Performance Problem -Gail Shaw[/url]
July 2, 2012 at 10:11 am
Just a hunch Les, but have you tried replacing the delete & update with MERGE?
-------------------------------------------------------------------------------------
-- Delete any previous record from today
-- Close out the previous record
MERGE CollectorTransitionHistory AS [target]
USING (SELECT @endpointId, @collectorId, @startDate, @startTime) AS source
(endpointId, collectorId, startDate, startTime)
ON ([target].endpointId = source.endpointId)
WHEN MATCHED AND [target].dailyReadStartDate = @startDate THEN DELETE
WHEN MATCHED AND [target].dailyReadEndDate IS NULL THEN
UPDATE SET dailyReadEndDate = source.startDate, endDate = source.startTime;
-------------------------------------------------------------------------------------
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
July 2, 2012 at 10:50 am
TheSQLGuru (7/2/2012)
Not sure why no one recommended trace flag 1222, nor this series of blog posts: http://blogs.msdn.com/bartd/archive/2006/09/09/Deadlock-Troubleshooting_2C00_-Part-1.aspxI note that deadlock investigations and fixing can be a VERY complex undertaking. Consider getting a professional on board for some assistance.
LOL...well, I am that :alien:...but this is a potentially pernicous problem, and we're just trying to mitigate the issue until the vendor refactors the logic. IOW, I don't have the freedom to alter the fundamental aspects of the code, but can alter locking behavior, or effect a wrapper that doesn't affect the logic. Ironically, the vendor didn't know they had a problem until we reported the behavior. Who knows how much incorrect data has been propogated at other sites.
Dr. Les Cardwell, DCS-DSS
Enterprise Data Architect
Central Lincoln PUD
July 2, 2012 at 10:58 am
Hi Chris,
>>Just a hunch Les, but have you tried replacing the delete & update with MERGE?
Unfortunately, I can't alter the code. The utility has been replacing in-house developed apps with COTS in an effort to reduce their risk...but it's been a bit of an oxymoron. I spend most of my time as EA getting these things to run with some level of efficiency <sheesh>
I did solve the problem by changing the locking behavior... SET READ_COMMITTED_SNAPSHOT ON; ...then affecting the SP behavior by SET TRANSACTION ISOLATION LEVEL READ COMMITTED. Since making the change yesterday, the SP hasn't deadlocked. I cut my teeth on Novell and record locking optimization, but rarely have to explicitly change it these days, at least not on anything we write.
Best of Breed COTS apps...not what they're cracked up to be. :rolleyes:
Thx,
~Les
Dr. Les Cardwell, DCS-DSS
Enterprise Data Architect
Central Lincoln PUD
July 2, 2012 at 11:01 am
Gullimeel (7/2/2012)
Still no luck with plan opening.I am opening it in text pad and data is kidn of scrambled.
Gullimeel...the record locking solved it for now. Thanks again for your insight.
Les
Dr. Les Cardwell, DCS-DSS
Enterprise Data Architect
Central Lincoln PUD
July 2, 2012 at 11:18 am
Les Cardwell (7/2/2012)
Hi Chris,>>Just a hunch Les, but have you tried replacing the delete & update with MERGE?
Unfortunately, I can't alter the code. The utility has been replacing in-house developed apps with COTS in an effort to reduce their risk...but it's been a bit of an oxymoron. I spend most of my time as EA getting these things to run with some level of efficiency <sheesh>
I did solve the problem by changing the locking behavior... SET READ_COMMITTED_SNAPSHOT ON; ...then affecting the SP behavior by SET TRANSACTION ISOLATION LEVEL READ COMMITTED. Since making the change yesterday, the SP hasn't deadlocked. I cut my teeth on Novell and record locking optimization, but rarely have to explicitly change it these days, at least not on anything we write.
Best of Breed COTS apps...not what they're cracked up to be. :rolleyes:
Thx,
~Les
Often deadlocks are "properly" resolved with "proper" indexing, although as you know code can definitely be a factor. As long as you don't get hit by some of the issues associated with RCSI seems like you are good to go for now.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
Viewing 11 posts - 16 through 25 (of 25 total)
You must be logged in to reply to this topic. Login to reply