Sql-server – TempInsertStateItemShort procedure on ASPState is taking more than 1 second to execute


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  
            DECLARE @now AS datetime  
            DECLARE @nowLocal AS datetime  

            SET @now = GETUTCDATE()  
            SET @nowLocal = GETDATE()  

            INSERT [tempdb].dbo.ASPStateTempSessions   
                 DATEADD(n, @timeout, @now),   
            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!!!!

Best Answer

As stated by Max Vernon:

800,000 rows seems to indicate the site has 800,000 sessions active (or at least active in the last 20 minutes - which is the default session timeout). Is that in line with the actual traffic you have? If not, I'd say the website does not have correct rights to the ASPState database.

The fragmentation would be "normal" for a table where the PK is a randomly generated GUID, as in this case.

I've note that ASPStateTempSessions was not being clean up expired sessions.

There is a job to clean up expired sessions ASPState_Job_DeleteExpiredSessions that was disable. I just enabled it and things are back to normal.

one more thing, just a good link I just found that helps to optimize ASPState. link