Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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!!!!

share|improve this question
    
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. –  Max Vernon Jun 24 '13 at 17:01
    
The fragmentation would be "normal" for a table where the PK is a randomly generated GUID, as in this case. –  Max Vernon Jun 24 '13 at 17:02
    
Not sure if it means 800,000 sessions active. I see sessions from 2012-09-25 01:55:08.023 which expired at 2012-09-25 02:49:25.267. I believe this table keeps old sessions there. –  nozari Jun 24 '13 at 17:16
1  
OK, I got it here. There is a job to clean up expired sessions ASPState_Job_DeleteExpiredSessions that was disable. I'm just enabling it and will keep monitoring it :D –  nozari Jun 24 '13 at 18:18
1  
one more thing, just a good link I just found that helps to optimize ASPState. link –  nozari Jun 24 '13 at 18:32

1 Answer 1

up vote 1 down vote accepted

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

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.