-모든 통계 데이터는 서비스 재시작 시 초기화 됩니다.
--프로시저 사용률
SELECT TOP(50) p.name AS 'SP명',
qs.execution_count AS 'SP실행수',
ISNULL(qs.execution_count/DATEDIFF(Second, qs.cached_time, GetDate()), 0) AS '[실행수/초]',
qs.total_worker_time/qs.execution_count AS '[작동시간/실행수]',
qs.total_worker_time AS '총작동시간',
qs.total_elapsed_time,
qs.total_elapsed_time/qs.execution_count AS [avg_elapsed_time],
qs.last_elapsed_time,
qs.cached_time
FROM sys.procedures AS p
INNER JOIN sys.dm_exec_procedure_stats AS qs
ON p.object_id = qs.object_id
ORDER BY qs.execution_count DESC
--사용률이 낮은 인덱스 찾기 SELECT 'DROP INDEX [' + B.NAME + '] ON [' + OBJECT_NAME(A.OBJECT_ID) + ']' AS [QUERY] , OBJECT_NAME(A.OBJECT_ID) AS [테이블 이름] , B.NAME AS [인덱스 이름], B.INDEX_ID AS [인덱스 ID] , A.USER_UPDATES AS [쓰기 횟수] , A.USER_SEEKS + A.USER_SCANS + A.USER_LOOKUPS AS [읽기 횟수] , A.USER_UPDATES - (A.USER_SEEKS + A.USER_SCANS + A.USER_LOOKUPS) AS [쓰기 - 읽기] FROM SYS.DM_DB_INDEX_USAGE_STATS AS A (NOLOCK) INNER JOIN SYS.indexes AS B (NOLOCK) ON A.OBJECT_ID = B.OBJECT_ID AND A.INDEX_ID = B.INDEX_ID WHERE OBJECTPROPERTY(A.OBJECT_ID, 'IsUserTable') = 1 --유저 생성 테이블만 AND A.DATABASE_ID = DB_ID() --현재 DB만 AND A.USER_UPDATES > (A.USER_SEEKS + A.USER_SCANS + A.USER_LOOKUPS) AND B.INDEX_ID > 1 AND (A.USER_SEEKS + A.USER_SCANS + A.USER_LOOKUPS) = 0 --0인 경우는 한번도 사용되지 않았을 경우. ORDER BY [쓰기 - 읽기] DESC, [쓰기 횟수] DESC, [읽기 횟수] ASC
--인덱스 생성 추천 --인덱스 생성에는 시간이 많이 소요될 수 있음. DB 사용률이 낮을때 할것(점심시간, 새벽 등) SELECT USER_SEEKS * AVG_TOTAL_USER_COST * (AVG_USER_IMPACT * 0.01) AS INDEX_ADVANTAGE , MIGS.LAST_USER_SEEK AS [마지막 사용] , MID.STATEMENT AS [테이블] , ROW_NUMBER() OVER (PARTITION BY MID.STATEMENT ORDER BY (USER_SEEKS * AVG_TOTAL_USER_COST * (AVG_USER_IMPACT * 0.01)) DESC) ADX , MID.EQUALITY_COLUMNS AS [= 연산 컬럼], MID.INEQUALITY_COLUMNS AS [= 외 연산 컬럼], MID.INCLUDED_COLUMNS AS [INCLUDED 컬럼] , MIGS.UNIQUE_COMPILES, MIGS.USER_SEEKS, MIGS.AVG_TOTAL_USER_COST, MIGS.AVG_USER_IMPACT INTO #TEMP_ADV FROM SYS.DM_DB_MISSING_INDEX_GROUP_STATS AS MIGS (NOLOCK) INNER JOIN SYS.DM_DB_MISSING_INDEX_GROUPS AS MIG (NOLOCK) ON MIGS.GROUP_HANDLE = MIG.INDEX_GROUP_HANDLE INNER JOIN SYS.DM_DB_MISSING_INDEX_DETAILS AS MID (NOLOCK) ON MIG.INDEX_HANDLE = MID.INDEX_HANDLE WHERE MID.STATEMENT LIKE '%' + DB_NAME() + '%' --조회할 DB AND USER_SEEKS * AVG_TOTAL_USER_COST * (AVG_USER_IMPACT * 0.01) > 100 ORDER BY INDEX_ADVANTAGE DESC;
SELECT 'CREATE INDEX IDX_'
- REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(A.테이블, DB_NAME(), ''), 'dbo', ''), '[', ''), ']', ''), '.', '')
- '' + CONVERT(NVARCHAR(8), GETDATE(), 112) + '' + CAST(A.ADX AS NVARCHAR)
- ' ON ' + REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(A.테이블, DB_NAME(), ''), 'dbo', ''), '[', ''), ']', ''), '.', '')
- '(' + ISNULL(A.[= 연산 컬럼], '')
- CASE WHEN A.[= 연산 컬럼] IS NULL OR A.[= 외 연산 컬럼] IS NULL THEN '' ELSE ',' END
- ISNULL(A.[= 외 연산 컬럼], '') + ')'
- CASE WHEN A.[INCLUDED 컬럼] IS NULL THEN '' ELSE ' INCLUDE (' + A.[INCLUDED 컬럼] + ')' END AS [QUERY] , A.* FROM #TEMP_ADV AS A
DROP TABLE #TEMP_ADV