March 29, 2011 at 1:51 pm
I ran into an interesting situation the other day and thought I would share it. I'm assuming the issue has to do with the intenals of how SQL Server works.
I have a table that has 14,259,981 records that needed to be updated.
I had an update statement where I was doing a calculation and storing the valued in a decimal(6,2) field. Well in the end I found that 6 fields didn't fit into they where greater then 9999. Long story short there was bad data in the database. The following is the statement:
UPDATE dbo.a_table
SET AVALUE =
CASE
WHEN ((IsNull(FirstAmt,0) + IsNull(SecondAmt,0)) = 0 OR IsNull(Value,0) = 0) THEN
Cast(0 as decimal(6,2))
ELSE
Cast(((IsNull(FirstAmt,0) + IsNull(SecondAmt,0)) / Value) as decimal(6,2))
END
WHERE CID = 17
AND LID = 35;
Well when the statement failed it took over an hour to let me know that it failed. When it was successful took about 5 minutes.
What I noticed is when it failed it was doing lots of little Page Latches. So I'm assuming the issue is that when the transaction failed all the data pages needed to be updated to show the rollback.
That doesn't seem right to me though. I would had assumed that when the failure happened that the transaction log would be marked in such a way to show that this transaction was rolled back and that would be it.
I'm not sure if the recovery mode has any effect with this. The database was in simple recovery mode and it was on a SQL Server 2008 instance (10.0.2531).
I thought I would pass this along to get thoughts from the community. The interesting thing is I have a DBA for 5 years here and this is the first time I ran into this (at least that I was able to identify).
March 29, 2011 at 5:01 pm
Do you have multiple CPUs in your server? And if so, what is your server's MAXDOP setting?
If SQL Server decided to employ parallel worker threads to execute your UPDATE then you could be experiencing the issue described in this blog post.
It is a quite common concern for SQL Server admins and can cause some serious anxiety if it is first experienced in a production environment when a batch that is rolling back is blocking users or other critical operations from taking place.
Re: your post title...SQL Server sometimes punishes [and punches] you for writing bad queries 😉
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
March 30, 2011 at 12:23 am
Carlton B Ramsey (3/29/2011)
That doesn't seem right to me though. I would had assumed that when the failure happened that the transaction log would be marked in such a way to show that this transaction was rolled back and that would be it.
Rollbacks usually take longer than the actual operation. SQL has to read the log to see what was done, log the undo changes and modify each page that was affected by the initial operation.
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
March 30, 2011 at 8:08 am
@opc.three
MAXDOP is set to 0. It's running on a system with the 2 sockets and dual core processors.
I'll take a look at the article you attached.
It did teach me a very important lesson after 3 hours of fighting with the update. To always test your query before you execute it.
I used the following to test the query in the end and it took only a minute and a half to fail instead of the hour.
WITH testme as
(SELECT
AVALUE =
CASE
WHEN ((IsNull(FirstAmt,0) + IsNull(SecondAmt,0)) = 0 OR IsNull(Value,0) = 0) THEN
Cast(0 as decimal(6,2))
ELSE
Cast(((IsNull(FirstAmt,0) + IsNull(SecondAmt,0)) / Value) as decimal(6,2))
END
FROM dbo.a_table
WHERE CID = 17
AND LID = 35)
SELECT COUNT(*) FROM testme
WHERE IsNumeric(AVALUE) = 0 ;
Oh and thinks for catching the punshes you in the subject line on this. I ment punishes but it must have been a floydian slip.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply