usecounts 1인 ad-hoc plan 제거
아래 스크립트는 비 효율적인 프로그램 디자인으로 인해 플랜이 무수히 생기는 과정을 시뮬레이션 하고
해당 플랜만 서버에서 제거 하는 방법입니다.
1. 쿼리에서 처음부터 바인드 변수 처리
2. OPTION (RECOMPILE) 을 이용해 플랜이 생기지 않도록 처리
3. 생긴플랜 지우기 등을 생각해 볼 수 있을듯 합니다.
일단 sql 모니터링 프로그램이 죽어도 프로그램을 고쳐주지 않으므로 제거하는 방법을 생각해 보았습니다.
-- 테스트 테이블 만들기
SET NOCOUNT ON
IF OBJECT_ID('TBLX') IS NOT NULL
DROP TABLE TBLX
GO
CREATE TABLE TBLX
(IDX INT IDENTITY(1,1)
,C1 INT
)
GO
-- 데이터 삽입
INSERT INTO TBLX VALUES(1)
GO 10000
CREATE UNIQUE CLUSTERED INDEX UCL_TBLX ON TBLX (IDX)
GO
-- 비 효율적인 어플리케이션 시뮬레이션 (플랜 메모리 과도하게 사용 하도록)
DECLARE @MAX INT, @SQL_TEXT VARCHAR(8000)
SELECT @MAX = MAX(IDX) FROM TBLX
WHILE (@MAX > 0) BEGIN
SET @SQL_TEXT = 'SELECT TOP 10 * FROM TBLX WHERE IDX ='+CAST(@MAX AS VARCHAR(100)) --+ 'OPTION (RECOMPILE)' -- 파라메터 바인딩이 되지 않도록 TOP 사용
--PRINT @SQL_TEXT
EXEC (@SQL_TEXT)
SET @MAX -= 1
END
GO
-- 쓸모 없는 플랜을 제거
DECLARE @T_PLAN_HANDLE TABLE
(IDX INT IDENTITY(1,1)
,PLAN_HANDLE VARBINARY (1000))
INSERT INTO @T_PLAN_HANDLE
SELECT PLAN_HANDLE--, *
FROM SYS.DM_EXEC_CACHED_PLANS
CROSS APPLY SYS.DM_EXEC_SQL_TEXT(PLAN_HANDLE)
WHERE USECOUNTS = 1
AND OBJTYPE = 'ADHOC'
-- AND 기타조건 I모사 SQL 모니터링 쿼리?
DECLARE @MAX INT, @PLAN_HANDLE VARCHAR (1000), @SQL_TEXT VARCHAR(8000)
SELECT @MAX = MAX(IDX) FROM @T_PLAN_HANDLE
WHILE (@MAX > 0) BEGIN
SELECT @PLAN_HANDLE = SYS.FN_SQLVARBASETOSTR( PLAN_HANDLE) FROM @T_PLAN_HANDLE WHERE IDX = @MAX
SET @SQL_TEXT = 'DBCC FREEPROCCACHE ('+CAST(@PLAN_HANDLE AS VARCHAR(8000))+')'
--PRINT @SQL_TEXT
EXEC (@SQL_TEXT)
SET @MAX -= 1
END
--DBCC MEMORYSTATUS
--******************************************************************************
--* AD-HOC 쿼리로 인해 PLAN MEMORY 영역이 비 효율적으로 사용되고 있는 상태
--******************************************************************************
CACHESTORE_SQLCP (NODE 0) KB
---------------------------------------- -----------
VM RESERVED 0
VM COMMITTED 0
AWE ALLOCATED 0
SM RESERVED 0
SM COMMITTED 0
SINGLEPAGE ALLOCATOR 119232 -- 대충 넣다가 정지해서 이것보다 많은 메모리가 올라 갈 수 있습니다.
MULTIPAGE ALLOCATOR 248
--******************************************************************************
--* 필요 없다고 판단되는 쿼리를 PLAN 에서 삭제하고 난 후의 상태
--******************************************************************************
CACHESTORE_SQLCP (NODE 0) KB
---------------------------------------- -----------
VM RESERVED 0
VM COMMITTED 0
AWE ALLOCATED 0
SM RESERVED 0
SM COMMITTED 0
SINGLEPAGE ALLOCATOR 8240
MULTIPAGE ALLOCATOR 248