We have this procedure - which inserts a single row on very regular basis - called from various other procedures.
The table is 93 million rows - however it being regularly caught and the instigator for many other blocking.
****************************************************************************/
ALTER PROCEDURE [taurus].[InsertAuditLog]
(
@auditActionId uniqueidentifier,
@paymentRequestId uniqueidentifier,
@event nvarchar(max),
@eventDate datetime,
@stateBefore varchar(max),
@stateAfter varchar(max),
@ipAddress varchar(50),
@result bit,
@updatedBy varchar(250)
)
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
BEGIN
BEGIN TRY
INSERT INTO dbo.AuditLog (
AuditActionId,
PaymentRequestId,
[Event],
EventDate,
StateBefore,
StateAfter,
IPAddress,
Result,
UpdatedBy
)
OUTPUT INSERTED.AuditLogId
VALUES (
@auditActionId,
@paymentRequestId,
@event,
@eventDate,
@stateBefore,
@stateAfter,
@ipAddress,
@result,
@updatedBy
)
END TRY
BEGIN CATCH
THROW;
END CATCH
END
END
GO
