데이터베이스 개발자 Tip & 강좌

SQLER의 개발자들이 만들어가는 데이터베이스 사용자 Tip & 강좌 게시판입니다. SQL서버, Oracle, MySQL 등 여러 클라우드/오픈소스 기반 데이터베이스 개발 및 운영 관련 팁과 쿼리 노하우를 이곳에서 가장 먼저 접하실 수 있습니다. 많은 도움 되시길 바랍니다.

중복 인덱스와 성능(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/

 

 


강성욱 / jevida@naver.com
Microsoft SQL Server MVP
Blog : http://sqlmvp.kr
Facebook : http://facebook.com/sqlmvp

No. Subject Author Date Views
1810 DMV를 이용한 CPU 사용량 높은 쿼리 찾기 jevida(강성욱) 2016.09.29 4395
1809 DMV를 이용한 인덱스 크기 및 조각화 정보 반환 jevida(강성욱) 2016.09.29 1156
1808 Checkpoint 추적하기 jevida(강성욱) 2016.09.29 1285
» 중복 인덱스와 성능(Duplicate Indexes with Performance) jevida(강성욱) 2016.09.29 2235
1806 823, 824, 825, 832 오류 (DISK IO 오류) jevida(강성욱) 2016.09.29 2138
1805 DISK I/O 병목 확인 jevida(강성욱) 2016.09.29 3718
1804 SQL Server 2012에서 비상계정 생성하기 - 비밀번호를 잊어 버렸을 경우 대처하기 jevida(강성욱) 2016.09.29 1251
1803 SQL Server 차단 최소화 jevida(강성욱) 2016.09.29 1129
1802 자주 사용되는 System 함수 jevida(강성욱) 2016.09.29 1070
1801 프로시저와 임시테이블, 그리고 리컴파일 jevida(강성욱) 2016.09.29 2398
1800 access check cache 크기에 따른 성능 문제 jevida(강성욱) 2016.09.29 1049
1799 Hot Add CPU jevida(강성욱) 2016.09.29 849
1798 스레드 및 파이버 실행 jevida(강성욱) 2016.09.29 1000
1797 CPU에 스레드 할당 및 lightweight pooling 옵션 사용 jevida(강성욱) 2016.09.29 1693
1796 스레드 및 태스크 아키텍처 jevida(강성욱) 2016.09.29 1392
1795 메모리 관리 아키텍처 – NUMA 버퍼 풀 증가 및 축소 jevida(강성욱) 2016.09.29 1231
1794 메모리 관리 아키텍처 – NUMA 지원 방법 jevida(강성욱) 2016.09.29 1545
1793 메모리 관리 아키텍처 – NUMA(Non-Uniform Memory Access)이해 jevida(강성욱) 2016.09.29 1415
1792 메모리 관리 아키텍처 – Hot Add 메모리 jevida(강성욱) 2016.09.28 929
1791 메모리 관리 아키텍처 – 버퍼 관리_페이지 쓰기 jevida(강성욱) 2016.09.28 1053





XE Login