중복 인덱스와 성능(Duplicate Indexes with Performance)

중복 인덱스와 성능(Duplicate Indexes with Performance)

 

• Version : SQL Server 2000, 20005, 2008, 2008R2, 2012

 

데이터베이스를 운영하는데 있어서 인덱스와 성능 문제는 떼어 놓을 수 없는 관계이다. 인덱스가 설정 되어 있지 않으면 원하는 데이터를 찾을 때 전체 테이블을 읽어야 하는 문제가 있기 때문에 I/O 측면에 많은 불리한 점이 있기 때문이다. 그래서 대부분의 데이터베이스를 운용할 때는 인덱스를 설정하여 사용한다.

 

그렇다면 인덱스를 만들 때 어떻게 만들면 좋을까? 온라인 공간에 찾아보면 인덱스를 생성할 때의 주의점 및 인덱스 키를 정하기 위한 다양한 가이드 들이 있다. 그 중에 오늘 다룰 내용은 중복 인덱스와 성능의 관계 이다.

 

SQL Server에서 한 컬럼 (또는 특정 컬럼)에 대해서 인덱스를 생성할 때 다양한 동일한 인덱스(ASC, DESC, 복합인덱스 등)를 생성할 수 있다. 그렇다면 실제 요청되는 쿼리들은 동일한 인덱스의 여러 복사본을 사용하여 검색 할까?

 

SQL Server에서는 실제 옵티마이저가 판단하여 가장 비용이 적게 들어가는 인덱스를 선택한다. 대부분의 검색작업에서는 동일 인덱스에 대해서 큰 문제가 발생하지 않지만 데이터의 입력, 수정, 삭제가 일어나는 경우 모든 인덱스에 변경된 내용을 유지해 주어야 하기 때문에 비용이 발생 한다.

 

단순히 인덱스의 변경 비용뿐만 아니라 이를 위한 추가 트랜잭션 로그, 디스크 여유공간, 인덱스 유지 관리에 필요한 페이지, 메모리 공간 등 많은 비용이 요구 된다.

 

또한 중복 인덱스가 단편화될 가능성도 있다. 정기적으로 조각모음을 하더라도 추가 리소스가 필요하다. 또한 유지보수 측면에서 고가용성이 구성되어 있을 경우 전체적인 성능 저하가 발생 할 수도 있다.

 

[중복 인덱스 999개 생성하기]

SET NOCOUNT ON GO   CREATE TABLE TestTable ( col1 int identity ); GO   DECLARE @IndexID smallint, @ExecStr nvarchar(500);   SELECT @IndexID = 1;   WHILE @IndexID <= 999 BEGIN SELECT @ExecStr = 'CREATE INDEX [Test' + right('00' + convert(varchar(5), @IndexID), 3) + '] ON TestTable (col1)';   EXEC(@ExecStr);   SELECT @IndexID = @IndexID + 1; END GO   SELECT count(*) FROM sys.indexes WHERE object_id = object_id('TestTable'); GO   --DROP TABLE TestTable GO

 

 

 

[중복 인덱스 확인]

/*============================================================================ File: sp_SQLskills_SQL2008_finddupes.sql   Summary: Run against a single database this procedure will list ALL duplicate indexes and the needed TSQL to drop them!                      Date: July 2011   SQL Server 2008 Version ------------------------------------------------------------------------------ Written by Kimberly L. Tripp, SYSolutions, Inc.   For more scripts and sample code, check out http://www.SQLskills.com   This script is intended only as a supplement to demos and lectures given by SQLskills instructors.   THIS CODE AND INFORMATION ARE PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND, EITHER EXPRESSED OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE IMPLIED WARRANTIES OF MERCHANTABILITY AND/OR FITNESS FOR A PARTICULAR PURPOSE. ============================================================================*/   USE master go   if OBJECTPROPERTY(OBJECT_ID('sp_SQLskills_SQL2008_finddupes'), 'IsProcedure') = 1     drop procedure sp_SQLskills_SQL2008_finddupes go   SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO   CREATE PROCEDURE [dbo].[sp_SQLskills_SQL2008_finddupes] ( @ObjName nvarchar(776) = NULL        -- the table to check for duplicates -- when NULL it will check ALL tables ) AS   -- Jul 2011: V1 to find duplicate indexes.   -- See my blog for updates and/or additional information -- http://www.SQLskills.com/blogs/Kimberly (Kimberly L. Tripp)   SET NOCOUNT ON   DECLARE @ObjID int,            -- the object id of the table         @DBName    sysname,         @SchemaName sysname,         @TableName sysname,         @ExecStr nvarchar(4000)   -- Check to see that the object names are local to the current database. SELECT @DBName = PARSENAME(@ObjName,3)   IF @DBName IS NULL SELECT @DBName = db_name() ELSE IF @DBName <> db_name() BEGIN      RAISERROR(15250,-1,-1)      -- select * from sys.messages where message_id = 15250      RETURN (1) END   IF @DBName = N'tempdb' BEGIN      RAISERROR('WARNING: This procedure cannot be run against tempdb. Skipping tempdb.', 10, 0)      RETURN (1) END   -- Check to see the the table exists and initialize @ObjID. SELECT @SchemaName = PARSENAME(@ObjName, 2)   IF @SchemaName IS NULL SELECT @SchemaName = SCHEMA_NAME()   -- Check to see the the table exists and initialize @ObjID. IF @ObjName IS NOT NULL BEGIN SELECT @ObjID = object_id(@ObjName)        IF @ObjID is NULL BEGIN RAISERROR(15009,-1,-1,@ObjName,@DBName) -- select * from sys.messages where message_id = 15009 RETURN (1) END END     CREATE TABLE #DropIndexes ( DatabaseName sysname, SchemaName sysname, TableName sysname, IndexName sysname, DropStatement nvarchar(2000) )   CREATE TABLE #FindDupes ( index_id int,     is_disabled bit,     index_name sysname,     index_description varchar(210),     index_keys nvarchar(2126), included_columns nvarchar(max),     filter_definition nvarchar(max),     columns_in_tree nvarchar(2126),     columns_in_leaf nvarchar(max) )   -- OPEN CURSOR OVER TABLE(S) IF @ObjName IS NOT NULL DECLARE TableCursor CURSOR LOCAL STATIC FOR SELECT @SchemaName, PARSENAME(@ObjName, 1) ELSE DECLARE TableCursor CURSOR LOCAL STATIC FOR          SELECT schema_name(uid), name FROM sysobjects WHERE type = 'U' --AND name ORDER BY schema_name(uid), name      OPEN TableCursor   FETCH TableCursor INTO @SchemaName, @TableName   -- For each table, list the add the duplicate indexes and save -- the info in a temporary table that we'll print out at the end.   WHILE @@fetch_status >= 0 BEGIN TRUNCATE TABLE #FindDupes   SELECT @ExecStr = 'EXEC sp_SQLskills_SQL2008_finddupes_helpindex ''' + QUOTENAME(@SchemaName) + N'.' + QUOTENAME(@TableName) + N''''   --SELECT @ExecStr   INSERT #FindDupes EXEC (@ExecStr)       --SELECT * FROM #FindDupes        INSERT #DropIndexes SELECT DISTINCT @DBName, @SchemaName, @TableName, t1.index_name, N'DROP INDEX ' + QUOTENAME(@SchemaName, N']') + N'.' + QUOTENAME(@TableName, N']') + N'.' + t1.index_name FROM #FindDupes AS t1 JOIN #FindDupes AS t2 ON t1.columns_in_tree = t2.columns_in_tree AND t1.columns_in_leaf = t2.columns_in_leaf AND ISNULL(t1.filter_definition, 1) = ISNULL(t2.filter_definition, 1) AND PATINDEX('%unique%', t1.index_description) = PATINDEX('%unique%', t2.index_description) AND t1.index_id > t2.index_id   FETCH TableCursor INTO @SchemaName, @TableName END      DEALLOCATE TableCursor   -- DISPLAY THE RESULTS   IF (SELECT count(*) FROM #DropIndexes) = 0      RAISERROR('Database: %s has NO duplicate indexes.', 10, 0, @DBName) ELSE SELECT * FROM #DropIndexes ORDER BY SchemaName, TableName   return (0) -- sp_SQLskills_SQL2008_finddupes go   exec sys.sp_MS_marksystemobject 'sp_SQLskills_SQL2008_finddupes' go   EXECUTE sp_msforeachdb 'USE ?; exec sp_SQLskills_SQL2008_finddupes'; GO

 

 

 

비즈니스를 잘 파악하여 효율적인 인덱스 설계 및 관리로 성능을 높일 수 있도록 하자.

 

[참고자료]    

http://technet.microsoft.com/ko-kr/magazine/hh873103(en-us).aspx

http://www.sqlskills.com/blogs/kimberly/removing-duplicate-indexes/

 

 

2013-07-02 / 강성욱 / http://sqlmvp.kr