September 29, 2011 at 8:04 am
Hi Guys,
There is a particular statement in one of my stored procedures that is causing me a headache. As far as I can see it's a very straight forward INSERT. That looks a bit like this:
INSERT INTO dbo.TransactionDuplicates (ImportTransactionRef, TransactionId, Value1, Value2, Value3)
SELECT DISTINCT i.ImportTransactionRef, i.TransactionID, i.Value1, i.Value2, i.Value3
FROM dbo.Import_Stage2_Transaction i
INNER JOIN dbo.AccountTransaction t ON i.TransactionID = t.TransactionID
INNER JOIN Account a ON t.AccountRef = a.AccountRef AND i.CompanyRef = a.CompanyRef
WHERE ImportRef = @ImportRef
There are no schema-bound views on the table, or triggers or anything like that. The AccountTransaction table is fairly big (2000000+ rows), but nothing ridiculous.
The problem is that sometimes this statement does not finish. By backing up the database and then restoring and running this one stored procedure over and over it fails to finish approximately 1 out of 3 times. When it does complete it completes in a couple of seconds. When it is continually running I can see the process in Activity Monitor, the task state switches between SUSPENDED with wait type TRACEWRITE, and RUNNING. It does not show as blocked in activity monitor. Running the SQL Server Profiler shows thousands of EventClass Lock:Acquired/Lock:Released every second, and nothing else. The Mode, BinaryData and TextData in the profiler repeats every 3 events, which is why I think it seems to be in some sort of infinite loop.
Any ideas? I've tried running some queries to find blocked processes, that I've stolen from various articles/blogs, but none of them have shown any rows.
September 29, 2011 at 9:14 am
waxingsatirical (9/29/2011)
Hi Guys,There is a particular statement in one of my stored procedures that is causing me a headache. As far as I can see it's a very straight forward INSERT. That looks a bit like this:
INSERT INTO dbo.TransactionDuplicates (ImportTransactionRef, TransactionId, Value1, Value2, Value3)SELECT DISTINCT i.ImportTransactionRef, i.TransactionID, i.Value1, i.Value2, i.Value3
FROM dbo.Import_Stage2_Transaction i
INNER JOIN dbo.AccountTransaction t ON i.TransactionID = t.TransactionID
INNER JOIN Account a ON t.AccountRef = a.AccountRef AND i.CompanyRef = a.CompanyRef
WHERE ImportRef = @ImportRef
There are no schema-bound views on the table, or triggers or anything like that. The AccountTransaction table is fairly big (2000000+ rows), but nothing ridiculous.
The problem is that sometimes this statement does not finish. By backing up the database and then restoring and running this one stored procedure over and over it fails to finish approximately 1 out of 3 times. When it does complete it completes in a couple of seconds. When it is continually running I can see the process in Activity Monitor, the task state switches between SUSPENDED with wait type TRACEWRITE, and RUNNING. It does not show as blocked in activity monitor. Running the SQL Server Profiler shows thousands of EventClass Lock:Acquired/Lock:Released every second, and nothing else. The Mode, BinaryData and TextData in the profiler repeats every 3 events, which is why I think it seems to be in some sort of infinite loop.
Any ideas? I've tried running some queries to find blocked processes, that I've stolen from various articles/blogs, but none of them have shown any rows.
If this insert if failing you should get some sort of error. I would guess it has to be a deadlock. You could turn on deadlock tracing and then inspect the sql log to see if it has in fact deadlocked with another process.
I would also consider using a (nolock) hint on non transactional tables. For instance I am guessing your Account table probably doesn't change that much and it might be worth the risk of doing dirty reads.
September 29, 2011 at 9:22 am
Nope, no errors, just never finishes executing.
Although, you are right I could safely add WITH (NOLOCK) on all of these joins.
September 29, 2011 at 10:14 am
waxingsatirical (9/29/2011)
Nope, no errors, just never finishes executing.Although, you are right I could safely add WITH (NOLOCK) on all of these joins.
I would be very reluctant to just toss a nolock hint on transaction tables. At the very least you should understand very thoroughly what you are doing before making that decision. http://sqlblog.com/blogs/andrew_kelly/archive/2009/04/10/how-dirty-are-your-reads.aspx. nolock is NOT a magical make this go faster pill, it can have some VERY serious side affects.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
September 29, 2011 at 10:24 am
waxingsatirical (9/29/2011)
Hi Guys,There is a particular statement in one of my stored procedures that is causing me a headache. As far as I can see it's a very straight forward INSERT. That looks a bit like this:
INSERT INTO dbo.TransactionDuplicates (ImportTransactionRef, TransactionId, Value1, Value2, Value3)SELECT DISTINCT i.ImportTransactionRef, i.TransactionID, i.Value1, i.Value2, i.Value3
FROM dbo.Import_Stage2_Transaction i
INNER JOIN dbo.AccountTransaction t ON i.TransactionID = t.TransactionID
INNER JOIN Account a ON t.AccountRef = a.AccountRef AND i.CompanyRef = a.CompanyRef
WHERE ImportRef = @ImportRef
There are no schema-bound views on the table, or triggers or anything like that. The AccountTransaction table is fairly big (2000000+ rows), but nothing ridiculous.
Have you tried updating statistics on those tables? With full scan preferably. From the lock behaviour, sounds like an inappropriate plan, probably with a nice little nested loop join that's fine for small row counts and totally inappropriate for larger ones.
Do you need the distinct?
Can you get an estimated execution plan of a time when it doesn't finish, and an actual execution plan of a time when it does?
I would strongly not suggest messing with the locking. My gut feel is that's a symptom, not the problem, and to fix this we need to find and fix the root cause, not treat symptoms.
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
September 30, 2011 at 10:23 am
I wasn't able to get an actual execution plan, as I could only get this behaviour when the statement is run as part of a stored procedure. I think the statement needs to complete to get an execution plan anyway?
I've changed the logic further up to be able get rid of the DISTINCT, and I haven't seen the behaviour again. However, this might have just been because the stored procedure was run in again and therefore recompiled.
I don't know. Just keeping my fingers crossed at the moment.
Thanks for your help though.
September 30, 2011 at 10:29 am
waxingsatirical (9/30/2011)
I wasn't able to get an actual execution plan, as I could only get this behaviour when the statement is run as part of a stored procedure. I think the statement needs to complete to get an execution plan anyway?
Actual plan, yes, estimated no. That's why I was asking for the actual of one that completes and an estimated of one that doesn't.
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
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply