I'm working on a .NET website who runs in load balance within more than one server and because of that I need to have my session on SQL Server.
In the last 2 weeks I've notice that the procedure TempInsertStateItemShort is taking more than 1 second to run.
Basically this procedure inserts into tempdb.dbo.ASPStateTempSessions
CREATE PROCEDURE dbo.TempInsertStateItemShort @id tSessionId, @itemShort tSessionItemShort, @timeout int AS DECLARE @now AS datetime DECLARE @nowLocal AS datetime SET @now = GETUTCDATE() SET @nowLocal = GETDATE() INSERT [tempdb].dbo.ASPStateTempSessions (SessionId, SessionItemShort, Timeout, Expires, Locked, LockDate, LockDateLocal, LockCookie) VALUES (@id, @itemShort, @timeout, DATEADD(n, @timeout, @now), 0, @now, @nowLocal, 1) RETURN 0
tempdb.dbo.ASPStateTempSessions table has 800k rows and the PK index is about 98% fragmented.
Does the fragmentation be the reason for insert take more than 1 second? Does anyone had experience that? How can I improve performance on this table?
Thanks in advance!!!!