Session 상태가 SQL Server인 경우 DB가 느려지고 사이트가 Time Out 되는 문제

Session 상태가 SQL Server인 경우 DB가 느려지고 사이트가 Time Out 되는 문제

 

1. 증상

 

- 사이트가 불규칙적으로 "제한시간만료" 에러 페이지 리턴

 

- w3wp.exe 의 메모리와 상관없이 사이트가 멈춤, w3wp.exe 서비스 프로세스를 끝내면 바로 돌아옴

 

- DB 직접 SSMS 연결 상태의 경우도 간혹 멈추거나 먹통이 되는 경우

 

- ASPStateTempSessions 테이블의 데이터가 약 3000 이상이 될 때 발생.

 

 

 

2. 문제 발생 환경

 

- IIS 서버

 

- Session 상태 관리를 MS SQL Server 에 두는 경우

 

- SQL Server 에이전트의 작업 중 "ASPState_Job_DeleteExpiredSessions" 의 일정이 매일 1분마다 호출 

 

 

 

3. 내용

 

1) "ASPState_Job_DeleteExpiredSessions" 작업안에는 SP인 "DeleteExpiredSessions" ( DB: ASPState ) 가 단계로 포함되어 있음.

   "DeleteExpiredSessions" 의 역활은 만기된 세션 ID 의 레코드를 삭제하는 역활

 

2) "DeleteExpiredSessions"은 SQL에서 자동으로 만들어 주는 SP로 기본적으로 다음과 같은 구문이로 이뤄짐

CREATE PROCEDURE [dbo].[DeleteExpiredSessions] AS DECLARE @now datetime SET @now = GETUTCDATE() DELETE [tempdb].dbo.ASPStateTempSessions WHERE Expires < @now RETURN 0

 

3) 테이블 "ASPStateTempSessions" 에서 만기일자가 현제일자 보다 적은 경우 삭제하라는 문구

  이는 테이블 X Lock 을 발생시킴.

  

4) 웹 사이트에서 많은 브라우져 세션들이 만기일자를 업데이트 하기 위해 접근, 이것은 SP "DeleteExpiredSessions" 작업과 경합 및 충돌을 일으킬수 있음

 

 

4. 해결책

 

1) "ASPState_Job_DeleteExpiredSessions" 의 작업 일정을 매일 30분마다 호출 

 

2)  SP "DeleteExpiredSessions"의 구문을 바꿔줌

   테이블 Lock 이 아닌 커서를 이용해 Row Lock 이 걸리도록 유도

USE [ASPState] GO CREATE PROCEDURE [dbo].[DeleteExpiredSessions] AS --------------------------------------------------- -- -- 기존 구문 ( MS 자동 생성 ) -- Table 단위 Lcok -- --------------------------------------------------- --DECLARE @now datetime --SET @now = GETUTCDATE() --DELETE [tempdb].dbo.ASPStateTempSessions --WHERE Expires < @now --RETURN 0 --------------------------------------------------- -- -- 기존 구문 ( MS 자동 생성 ) 끝 -- --------------------------------------------------- --------------------------------------------------- -- -- 변경 구문 ( 각 레코드 단위로 커서를 이용해 삭제 ) -- Row Lock -- --------------------------------------------------- DECLARE @now DATETIME SET @now = GETUTCDATE() CREATE TABLE #ExpiredSessions ( SessionID NVARCHAR(88) NOT NULL PRIMARY KEY ) INSERT #ExpiredSessions (SessionID) SELECT SessionID FROM [tempdb].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 [tempdb].dbo.ASPStateTempSessions WHERE SessionID = @SessionID FETCH NEXT FROM SessionCursor INTO @SessionID END CLOSE SessionCursor DEALLOCATE SessionCursor DROP TABLE #ExpiredSessions RETURN 0 --------------------------------------------------- -- -- 변경 구문 ( 각 레코드 단위로 커서를 이용해 삭제 ) 끝 -- Row Lock -- ---------------------------------------------------