March 20, 2007 at 11:00 am
We are showing a high number of waits on SQL Server on obtaining locks on the session state database for .net, ASPState. When we look, this is the query that is always running:
CREATE PROCEDURE TempGetStateItemExclusive2
@id tSessionId,
@itemShort tSessionItemShort OUTPUT,
@locked BIT OUTPUT,
@lockAge INT OUTPUT,
@lockCookie INT OUTPUT
AS
DECLARE @textptr AS tTextPtr
DECLARE @length AS INT
DECLARE @now as DATETIME
DECLARE @nowLocal as DATETIME
SET @now = GETUTCDATE()
SET @nowLocal = GETDATE()
UPDATE ASPState..ASPStateTempSessions
SET Expires = DATEADD(n, Timeout, @now),
LockDate = CASE Locked
WHEN 0 THEN @now
ELSE LockDate
END,
LockDateLocal = CASE Locked
WHEN 0 THEN @nowLocal
ELSE LockDateLocal
END,
@lockAge = CASE Locked
WHEN 0 THEN 0
ELSE DATEDIFF(second, LockDate, @now)
END,
@lockCookie = LockCookie = CASE Locked
WHEN 0 THEN LockCookie + 1
ELSE LockCookie
END,
@itemShort = CASE Locked
WHEN 0 THEN SessionItemShort
ELSE NULL
END,
@textptr = CASE Locked
WHEN 0 THEN TEXTPTR(SessionItemLong)
ELSE NULL
END,
@length = CASE Locked
WHEN 0 THEN DATALENGTH(SessionItemLong)
ELSE NULL
END,
@locked = Locked,
Locked = 1
WHERE SessionId = @id
IF @length IS NOT NULL BEGIN
READTEXT ASPState..ASPStateTempSessions.SessionItemLong @textptr 0 @length
END
RETURN 0
One thing I never knew about is the DeleteExpiredSessions proc that is apparently included with the create db statements for these. We had only been deleting data that was over 5 days old. However, whenever I try and run the DeleteExpiredSessions proc, it actually hangs up our web servers! The other day when I was trying to delete all 5 days' worth of old data, it hung it up for over two hours before it finally finished. I have heard some people say on websites that this job should be run every MINUTE... but I am afraid of it completely hanging up our system if we do that; but I am also concerned that us NOT running that job could be what is causing all the locks. Any ideas, anyone? Thanks for the help...
Amy
March 21, 2007 at 6:56 am
bit tricky as I don't have anything like this.. but .. if you're recording sessions, logging data etc. at a fairly high activity rate then attempting to delete ranges of data from the same table will likely cause exclusive table locks which will gum everything up. I have seen similar implementations attempting to retain current concurrent session stats on busy sites I ( it doesn't work very well )
Deleting in small batches will help, make sure the column(s) used to make the select for the delete are indexed - indexing for deletes is important. Dare I suggest that cursor deletes may work better as they maintain individual transactions whereas set based deletes tend to wrap the entire set into a transaction. ( be wary of while loops which whilst replacing a cursor will tend to actually work deletes as a set based solution )
Use table partitioning, I've used this solution successfully, this way you never delete from the tabel being inserting into.
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/
March 21, 2007 at 10:33 am
I guess I am wondering too what is "normal" for this situation - do most people delete expired sessions frequently? Or what SHOULD be done? I can't find any documentation on if they should be deleted and how often...
March 23, 2007 at 4:49 am
yeah, sorry can't help here - I just resolve problems from the SQL point of view.
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/
April 17, 2007 at 2:43 pm
I am having a similar issue...we installed Project Server 2007 last month and the 'DeleteExpiredSessions' job that came along with it runs every minute. We've noticed that our CPU utilization is higher now and I'm wondering if this is why and does anyone know if this job has to run every minute?
Thanks!
Thanks!
April 19, 2007 at 4:16 pm
FYI, we finally got some support from Microsoft on this. They had me reinstall the ASPState database using the scripts that were actually included with the updated .net driver. At some point, development team had upgraded to a higher version of .net (1.1 Sp1) and no one had recreated the ASpState database along with it. So I did that, and all the locks/waits went away - it was great.
Also, our web.config files have debug=true in them, which they say is a big no-no... we couldn't change that setting because we kept getting errors, but they say that most shops if possible should run with debug=false when using ASpState.
Hope that helps someone else out there...
March 13, 2008 at 8:04 am
We have a system suffering from the same issue. It's actually the only one of our systems suffering from it because all of the others are using StateServer (so I was told). Apparently this particular machine was suffering from errors when using StateServer (I have no details on these errors, it was before I started here) so they converted the SessionsStateMode to use SQLServer instead.
Here's some operational information on the system:
- SQL Server 2000 Standard Edition
- Approximately 300 connections to the server during peak usage
- I'm seeing about 175 trans/sec in tempdb and 175 trans/sec in ASPState during peak usage (total is about 500 trans/sec for the whole server)
I'm seeing retrieval times of over 7 minutes to get about 600+ records of data (SELECT) out of the ASPState database (keep in mind this is NOT happening for other databases in the same SQL Server instance). The procs that are being called (TempGetStateItemExclusive2 and TempUpdateStateItemLong) are taking between 5-15 seconds to complete each. The system is suffering horribly from this.
I did what Amy suggested above and re-installed the ASPState database based on the script from the most recent .Net 1.1 Framework directory on the server (v1.1.4322) but it doesn't seem to have any impact.
I was going to try this fix from Microsoft next http://support.microsoft.com/kb/843400/en-us, but I wanted to know if anyone had any more information on how to diagnose the problem and how to be sure that what the Microsoft KB article explains is really the problem I'm suffering from. I'm no expert in diagnosing locking problems, so any information to point me in the right direction would help.
Thanks!
PS - If you think it's definitely not worth spending the time fixing this and trying to figure out how to fix StateServer instead, we can take that route.
October 2, 2008 at 2:12 pm
The DeleteexpiredSessions job that runs every minute can be a problem. I would suggest going to every 5 minutes and running this one instead. It will avoid table locking and only delete at the row level. I'm not a cursor fan, but it makes sense here.
CREATE PROCEDURE [dbo].[DeleteExpiredSessionsNew]
AS
DECLARE @now datetime
SET @now = GETUTCDATE()
CREATE TABLE #ExpiredSessions
( SessionID nvarchar(88) NOT NULL
PRIMARY KEY
)
INSERT #ExpiredSessions (SessionID)
SELECT SessionID
FROM ASPState.dbo.ASPStateTempSessions
WHERE Expires < @now
DECLARE SessionCursor CURSOR LOCAL FORWARD_ONLY READ_ONLY
FOR SELECT SessionID FROM #ExpiredSessions ORDER BY CHECKSUM(NEWID())
DECLARE @SessionID nvarchar(88)
OPEN SessionCursor
FETCH NEXT FROM SessionCursor INTO @SessionID
WHILE @@FETCH_STATUS = 0 BEGIN
DELETE FROM ASPState.dbo.ASPStateTempSessions
WHERE SessionID = @SessionID
FETCH NEXT FROM SessionCursor INTO @SessionID
END
CLOSE SessionCursor
DEALLOCATE SessionCursor
DROP TABLE #ExpiredSessions
RETURN 0
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply