MSSQL_AUTO_UPDATE_STATISTICS_ASYNC(비동기업데이트)

제가 알고 있기로 통계정보는 업데이트 하는 동안 테이블에 Lock이 걸려서 부하가 있으므로, 통계의 업데이트는 점검시간에 하는 것으로 알고 있었습니다. 

 

하지만 비동기 옵션을 통해서 이러한 현상을 극복 할 수 있습니다.

 

위의 글이 잘 보이지 않는 다면 아래의 링크를 클릭하면 됩니다.

http://technet.microsoft.com/ko-kr/library/bb522682(v=sql.105).aspx 

 

비동기 업데이트 옵션을 이용한다면, 성능이 영향을 주지 않는지 테스트 하기 위해서 간단한 테스트를 진행 했습니다. 

비동기 옵션을 OFF로 해두고 테스트를 진행하고, ON으로 한 뒤 진행 해보았습니다.

 

 

위의 통계옵션은 알고 있을 것이라고 생각 됩니다. 

통계 정보가 업데이트를 되고 있는지 확인 하기 위해서는 다음의 쿼리를 이용 합니다.

DBCC SHOW_STATISTICS (TBLX,[NCL__TBLX__01])

 

아래 예시를 통해 테스트를 진행 합니다.

 

 

SELECT TOP 50000 ROW_NUMBER() OVER(ORDER BY(SELECT 1)) AS IDX ,      DATEADD(hour,0,0) AS DATE INTO   dbo.TBLX FROM   master..spt_values A1 ,      master..spt_values A2 GO   CREATE NONCLUSTERED INDEX [NCL__TBLX__01] ON [dbo].[TBLX]([DATE]) GO   UPDATE TOP (1) PERCENT dbo.TBLX SET DATE='1900-01-01 00:00:001' GO

 

고정 된 값을 SELECT 함으로써 어떠한 변화가 있는지 알아 보도록 하겠습니다.

 

프로파일러를 통해서 그 성능 차이를 알아 보도록 하겠습니다.

실행 순서는 /*동기*/ -> 업데이트 -> 동기 업데이트 이후 입니다.

 

 

/*동기*/SELECT * FROM dbo.TBLX WHERE DATE='1900-01-01 00:00:001' GO   UPDATE dbo.TBLX SET    DATE=getdate() WHERE  DATE<>'1900-01-01 00:00:001' GO   /*동기업데이트이후*/SELECT * FROM dbo.TBLX WHERE DATE='1900-01-01 00:00:001' GO

 

 

순서데로 프로파일을 잡으면 다음과 같습니다.

 

 

확인해 보면 Duration이 확실히 증가 했습니다. 물론 큰 차이는 없지만 (DATA가 얼마 없으므로) 분명히 오래 걸렸습니다.

다시 실행해 보면 그 차이를 분명히 알 수 있습니다.

 

 

처음 2ms에서 통계 업데이트 직후에 했을때 22ms였는데 다시 해보니 5ms였습니다. 분명히 이것은 쿼리 문제가 아닌 것 입니다.

흔히 알고 있는 통계 업데이트로 인한 것 입니다.

 

그렇다면 비동기업데이트를 ON하고 실행 해 보겠습니다.

 

 

순서는 똑같습니다. 대신 확실한 차이를 보기 위해 같은 방법으로 테스트 데이터를 다시 만듭니다.

 

 

Duration이 2ms -> 3ms 입니다. 전혀 영향이 없습니다.

 

중간에 살짝 튄 것 이외엔 변경되지 않았습니다. (살짝 튄 것은 백단 작업으로 인할 수도 있음)

 

다시 한번 UPDATE 이후에 조회 하였으나, 큰 변화는 없습니다.

 

 

결론적으로 비동기 업데이트를 사용한다면 통계 업데이트를 ON으로 해두어도 성능상 부하가 없을 수 있습니다.