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]
CREATE CLUSTERED INDEX [IDX_auditLog_SessionID] ON [dbo].[auditLog] (SessionID)
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:
( SELECT SessionID
WHERE SessionID = @SessionID )
BEGIN -- If we have an exsitng record with the session id
SET LastAction = @MsgTime
WHERE SessionID = @SessionID
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?