Frequent Blocking on UPDATE

Last Post 22 Apr 2013 10:25 AM by gunneyk. 1 Replies.
AddThis - Bookmarking and Sharing Button Printer Friendly
  •  
  •  
  •  
  •  
  •  
Sort:
PrevPrev NextNext
You are not authorized to post a reply.
Author Messages Not Resolved
rerichards
New Member
New Member

--
22 Apr 2013 08:27 AM
I have got a table with the following schema:

CREATE TABLE dbo.auditLog(
[SessionID] [uniqueidentifier] NOT NULL,
[LoginDateTime] [datetime] NOT NULL,
[LastAction] [datetime] NOT NULL,
[LogoutDateTime] [datetime] NULL,
[LoginUser] [varchar](50) NOT NULL,
[Reason] [varchar](20) NOT NULL,
[Notes] [varchar](50) NOT NULL,
[ItemKey] int NOT NULL,
[LoginInfo] [varchar](255) NOT NULL
) ON [PRIMARY]

GO

CREATE CLUSTERED INDEX [IDX_auditLog_SessionID] ON [dbo].[auditLog] (SessionID)
GO

While profiling activities on the database I can see there are numerous blocking events when this stored procedure is called simultaneously by various clients, which stored procedure executes the following code, specifically when the UPDATE is executed:

IF EXISTS
( SELECT SessionID
FROM dbo.auditLog
WHERE SessionID = @SessionID )
BEGIN -- If we have an exsitng record with the session id
UPDATE dbo.auditLog
SET LastAction = @MsgTime
WHERE SessionID = @SessionID
END

When the Blocking occurs the UPDATE statement is the blocking process and the blocked process is also the UPDATE statement.

I have read where uniqueidentifier may not be the best data type on which to build a Clustered Index, but in this case I do not see where the Clustered Index on a uniqueidentifier would have anything to do with the blocking that is taking place.

Or, is the Clustered Index built on a uniqueidentifier data type possibly contributing to the blocking?
gunneyk
New Member
New Member

--
22 Apr 2013 10:25 AM
OK a couple off things here. Since a clustered Index (CI) sorts the data physically by the CI key expression if other sessions are inserting data into the table it can be causing page splits. If your session ID is on that same page it can certainly block. GUID's by default are somewhat random in value so they may need to go on pages that have other GUID's and if full will split the page. If you use sequential guids then you are less likely to get page splits since they will append to the last page. But my guess would be that the @SessionID variable is NOT the same datatype as the SessionID column and thus scanning the CI instead of seeking. You might also consider putting the CI on a different column (maybe LoginDateTime) and making the SessionID a NCI. Then if you have spits which would be far less since it's only the guid in the index it would be much more lightweight.
Another thing I would do is skip the EXISTS. Just issue the UPDATE and check the @@ROWCOUNT. If it is > 0 then the row existed and you can move on. If it is 0 then do the INSERT. Usually more rows exist than not so this can save a SELECT for the EXISTS.
You are not authorized to post a reply.

Acceptable Use Policy