TempInsertStateItemShort procedure on ASPState is taking more than 1 second to execute
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