Foreign Key와 Index 를 Drop & Create 하는 구문 생성하기

요즘은 게임 DB 통합을 준비하고 있답니다.

게임의 장르에 따라 다르겠지만 월드 개념이 있는 경우, 유저의 접속 트래픽에 따라 신규 월드를 오픈하거나 기존의 월드를 합치는 이슈가 발생하는데요. 신규 월드를 오픈하는 것과는 달리 월드를 통합하는 일은 DBA의 역할이 매우 큽니다.

작업의 내용을 한마디로 정의한다면, "완전히 동일한 레이아웃을 가진 두개의 DB를 하나로 합치는 일" 이라고 할 수 있겠군요.

말은 간단하지만 고려해야 할 요소는 조금 있는 편입니다.

- PK, UK의 중복 문제
- 데이터 merge 성능 (서비스 다운타임을 줄이기 위한 싸움)
- 통합 진행 상황을 모니터할 수 있게끔 단계 별로 로깅
- 각 단계 별 에러 핸들링

오늘 포스팅할 내용은 데이터 merge 성능을 향상 시키기 위해 미리 작업해 두어야하는 것들에 대한 이야기입니다.

아시겠지만 대량의 데이터를 INSERT하기 위해 미리 해 두어야할 작업이란..
foreign key와 non-clustered 인덱스를 제거하는 일입니다.

그렇지 않으면 데이터가 INSERT될 때마다 참조 무결성의 체크와 인덱스 업데이트 작업이 병행되기 때문에 극악의 성능을 경험하게 될테니까요.

데이터 INSERT가 끝나면 clustered 인덱스를 rebuild하고 non-clustered index를 생성하고 마지막으로 foreign key를 생성하면 됩니다. (기타 제약 조건이나 트리거도 일괄 disable 후 다시 enable 해줘야겠죠)

정리하면..

1. drop foreign keys
2. drop non-clustered indexes
3. disable constraints
4. disable triggers
5. insert mass data
6. rebuild clustered indexes
7. create non-clustered indexes
8. create foreign keys
9. enable constraints
10. enable triggers

이제 소개하는 스크립트는 위 작업에 필요한 구문을 자동으로 생성해 줍니다.

단, SQL 2008 기준으로 작성했습니다.

filtered index 부분 때문에 sql 2005 에서는 에러가 날 텐데요.
소스 찬찬히 보시고 수정해서 사용하시면 됩니다~ ^^


다운로드

script.txt

SET NOCOUNT ON;

GO

 

IF OBJECT_ID(N'dbo.P_Print') IS NOT NULL

  DROP PROCEDURE dbo.P_Print;

GO

 

CREATE PROCEDURE dbo.P_Print

  @nvcString nvarchar(max)

AS

 

SET NOCOUNT ON;

 

DECLARE @intReturnValue int, @nvcStringBuffer nvarchar(max), @i int, @j int;

 

IF LEN(@nvcString) <= 4000 OR @nvcString IS NULL

  PRINT @nvcString;

 

ELSE

BEGIN

  WHILE 1 = 1

  BEGIN

    SET @nvcStringBuffer = LEFT(@nvcString, 4000);

 

    SET @i = CHARINDEX(CHAR(10) + CHAR(13), REVERSE(@nvcStringBuffer));

    SET @j = (DATALENGTH(@nvcStringBuffer) / 2) - CASE @i WHEN 0 THEN 0 ELSE @i + 1 END;

 

    SET @nvcStringBuffer = LEFT(@nvcStringBuffer, @j);

    PRINT @nvcStringBuffer;

 

    SET @nvcString = CASE @i WHEN 0 THEN @nvcString ELSE STUFF(@nvcString, @j + 1, 2, N'') END;

    SET @nvcString = SUBSTRING(@nvcString, @j + 1, (DATALENGTH(@nvcString) / 2) - @j);

 

    IF LEN(@nvcString) <= 4000

    BEGIN

      PRINT @nvcString;

      BREAK;

    END

  END

END

 

RETURN 0;

GO

 

DECLARE @nvcStmt nvarchar(max) = N'';

DECLARE @i int = 1, @j int, @object_id int;

DECLARE @Result table (TableName sysname NOT NULL, IndexName sysname NOT NULL);

 

IF OBJECT_ID(N'dbo.MGR_ForeignKeys', N'U') IS NOT NULL

  DROP TABLE dbo.MGR_ForeignKeys;

 

CREATE TABLE dbo.MGR_ForeignKeys (

  Seq int NOT NULL IDENTITY(1, 1) PRIMARY KEY,

  ForeignKeyName sysname NOT NULL,

  ParentTableName sysname NOT NULL,

  ChildTableName sysname NOT NULL,

  ParentColumns nvarchar(1000) NOT NULL,

  ChildColumns nvarchar(1000) NOT NULL

);

 

INSERT dbo.MGR_ForeignKeys

SELECT FK.name, PT.name, CT.name

  , STUFF((

    SELECT N',' + PC.name

    FROM sys.foreign_key_columns FC

      INNER JOIN sys.columns PC ON FC.referenced_object_id = PC.object_id AND FC.referenced_column_id = PC.column_id

    WHERE FK.object_id = FC.constraint_object_id

    ORDER BY FC.constraint_column_id

    FOR XML PATH(N'')

  ), 1, 1, N'')

  , STUFF((

    SELECT N',' + CC.name

    FROM sys.foreign_key_columns FC

      INNER JOIN sys.columns CC ON FC.parent_object_id = CC.object_id AND FC.parent_column_id = CC.column_id

    WHERE FK.object_id = FC.constraint_object_id

    ORDER BY FC.constraint_column_id

    FOR XML PATH(N'')

  ), 1, 1, N'')

FROM sys.foreign_keys FK

  INNER JOIN sys.tables PT ON FK.referenced_object_id = PT.object_id

  INNER JOIN sys.tables CT ON FK.parent_object_id = CT.object_id;

 

IF OBJECT_ID(N'dbo.MGR_Indexes', N'U') IS NOT NULL

  DROP TABLE dbo.MGR_Indexes;

 

CREATE TABLE dbo.MGR_Indexes (

  Seq int NOT NULL IDENTITY(1, 1) PRIMARY KEY,

  IndexName sysname NOT NULL,

  TableName sysname NOT NULL,

  UniqueFlag bit NOT NULL,

  FilterFlag bit NOT NULL,

  FilterDefinition nvarchar(1000) NULL,

  PrimaryKeyFlag bit NOT NULL,

  IgnoreDupKeyFlag bit NOT NULL,

  IndexColumns nvarchar(1000) NOT NULL,

  IncludedColumns nvarchar(1000) NULL,

  FileGroupName sysname NOT NULL,

  IndexType int NOT NULL

);

 

INSERT dbo.MGR_Indexes

SELECT I.name, T.name, I.is_unique, I.has_filter, I.filter_definition, I.is_primary_key, I.[ignore_dup_key]

  , STUFF((

    SELECT ',' + C.name

    FROM sys.index_columns IC

      INNER JOIN sys.columns C ON IC.object_id = C.object_id AND IC.column_id = C.column_id

    WHERE IC.is_included_column = 0 AND IC.object_id = I.object_id AND IC.index_id = I.index_id

    ORDER BY IC.key_ordinal

    FOR XML PATH(N'')

  ), 1, 1, N'')

  , STUFF((

    SELECT ',' + C.name

    FROM sys.index_columns IC

      INNER JOIN sys.columns C ON IC.object_id = C.object_id AND IC.column_id = C.column_id

    WHERE IC.is_included_column = 1 AND IC.object_id = I.object_id AND IC.index_id = I.index_id

    ORDER BY IC.key_ordinal

    FOR XML PATH(N'')

  ), 1, 1, N''), FG.name, I.[type]

FROM sys.tables T

  INNER JOIN sys.indexes I ON T.object_id = I.object_id

  INNER JOIN sys.filegroups FG ON I.data_space_id = FG.data_space_id

WHERE I.[type] = 2

UNION ALL

SELECT I.name, T.name, I.is_unique, I.has_filter, I.filter_definition, I.is_primary_key, I.[ignore_dup_key]

  , STUFF((

    SELECT ',' + C.name

    FROM sys.index_columns IC

      INNER JOIN sys.columns C ON IC.object_id = C.object_id AND IC.column_id = C.column_id

    WHERE IC.is_included_column = 0 AND IC.object_id = I.object_id AND IC.index_id = I.index_id

    ORDER BY IC.key_ordinal

    FOR XML PATH(N'')

  ), 1, 1, N'')

  , STUFF((

    SELECT ',' + C.name

    FROM sys.index_columns IC

      INNER JOIN sys.columns C ON IC.object_id = C.object_id AND IC.column_id = C.column_id

    WHERE IC.is_included_column = 1 AND IC.object_id = I.object_id AND IC.index_id = I.index_id

    ORDER BY IC.key_ordinal

    FOR XML PATH(N'')

  ), 1, 1, N''), FG.name, I.[type]

FROM sys.views T

  INNER JOIN sys.indexes I ON T.object_id = I.object_id

  INNER JOIN sys.filegroups FG ON I.data_space_id = FG.data_space_id;

 

SELECT @nvcStmt += N'ALTER TABLE dbo.' + ChildTableName + N' DROP CONSTRAINT ' + ForeignKeyName + N';' + CHAR(13) + CHAR(10)

FROM dbo.MGR_ForeignKeys;

 

PRINT N'-- 1. Drop foreign keys';

EXEC dbo.P_Print @nvcStmt;

PRINT REPLICATE(CHAR(13) + CHAR(10), 3);

 

SET @nvcStmt = N'';

 

SELECT @nvcStmt += N'DROP INDEX ' + IndexName + N' ON dbo.' + TableName + N';' + CHAR(13) + CHAR(10)

FROM dbo.MGR_Indexes

WHERE PrimaryKeyFlag = 0;

 

SELECT @nvcStmt += N'ALTER TABLE dbo.' + TableName + N' DROP CONSTRAINT ' + IndexName + N';' + CHAR(13) + CHAR(10)

FROM dbo.MGR_Indexes

WHERE PrimaryKeyFlag = 1;

 

PRINT N'-- 2. Drop non-clustered indexes';

EXEC dbo.P_Print @nvcStmt;

PRINT REPLICATE(CHAR(13) + CHAR(10), 3);

 

PRINT N'-- 3. Disable constraints';

PRINT N'EXEC sp_MSForEachTable ''ALTER TABLE ? NOCHECK CONSTRAINT ALL'';';

PRINT REPLICATE(CHAR(13) + CHAR(10), 3);

 

PRINT N'-- 4. Disable triggers';

PRINT N'EXEC sp_MSForEachTable ''ALTER TABLE ? DISABLE TRIGGER ALL'';';

PRINT REPLICATE(CHAR(13) + CHAR(10), 3);

 

SET @nvcStmt  = N'';

 

SELECT IDENTITY(int, 1, 1) AS Seq, [object_id]

INTO #tmp

FROM sys.tables;

 

SET @j = @@ROWCOUNT;

 

WHILE @i <= @j

BEGIN

  SELECT @object_id = [object_id] FROM #tmp WHERE Seq = @i;

 

  INSERT @Result (TableName, IndexName)

  SELECT OBJECT_NAME(@object_id), name

  FROM sys.dm_db_index_physical_stats (DB_ID(), @object_id, NULL, NULL, NULL) AS a

    JOIN sys.indexes AS b ON a.object_id = b.object_id AND a.index_id = b.index_id

  WHERE name IS NOT NULL AND [type] = 1;

 

  SET @i += 1;

END

 

SELECT @nvcStmt += N'ALTER INDEX ' + IndexName + N' ON dbo.' + TableName + N' REBUILD' + CHAR(13) + CHAR(10)

FROM @Result;

 

PRINT N'-- 6. Rebuild clustered indexes';

EXEC dbo.P_Print @nvcStmt;

PRINT REPLICATE(CHAR(13) + CHAR(10), 3);

 

DROP TABLE #tmp;

 

SET @nvcStmt = N'';

 

SELECT @nvcStmt += N'CREATE '

  + CASE UniqueFlag WHEN 1 THEN N'UNIQUE ' ELSE N'' END

  + CASE IndexType WHEN 1 THEN N'CLUSTERED ' ELSE N'' END

  + N'INDEX ' + IndexName + N' ON dbo.' + TableName + N' (' + IndexColumns + N') '

  + CASE WHEN IncludedColumns IS NOT NULL THEN N'INCLUDE (' + IncludedColumns + N') ' ELSE N'' END

  + CASE FilterFlag WHEN 1 THEN N'WHERE ' + FilterDefinition + N' ' ELSE N'' END

  + N'ON [' + FileGroupName + N'];' + CHAR(13) + CHAR(10)

FROM dbo.MGR_Indexes

WHERE PrimaryKeyFlag = 0

ORDER BY IndexType;

 

SELECT @nvcStmt += N'ALTER TABLE dbo.' + TableName + N' ADD CONSTRAINT ' + IndexName + N' PRIMARY KEY NONCLUSTERED (' + IndexColumns + N') ON [' + FileGroupName + N'];' + CHAR(13) + CHAR(10)

FROM dbo.MGR_Indexes

WHERE PrimaryKeyFlag = 1;

 

PRINT N'-- 7. Create non-clustered indexes';

EXEC dbo.P_Print @nvcStmt;

PRINT REPLICATE(CHAR(13) + CHAR(10), 3);

 

SET @nvcStmt = N'';

 

SELECT @nvcStmt += N'ALTER TABLE dbo.' + ChildTableName + N' ADD CONSTRAINT ' + ForeignKeyName + N' FOREIGN KEY (' + ChildColumns + N') REFERENCES dbo.' + ParentTableName + N' (' + ParentColumns + N');' + CHAR(13) + CHAR(10)

FROM dbo.MGR_ForeignKeys;

 

PRINT N'-- 8. Create foreign keys';

EXEC P_Print @nvcStmt;

PRINT REPLICATE(CHAR(13) + CHAR(10), 3);

 

PRINT N'-- 9. Enable Constraint';

PRINT N'EXEC sp_MSForEachTable ''ALTER TABLE ? WITH CHECK CHECK CONSTRAINT ALL'';';

PRINT REPLICATE(CHAR(13) + CHAR(10), 3);

 

PRINT N'-- 10. Enable Trigger';

PRINT N'EXEC sp_MSForEachTable ''ALTER TABLE ? ENABLE TRIGGER ALL'';';

PRINT REPLICATE(CHAR(13) + CHAR(10), 3);

 

 

IF OBJECT_ID(N'dbo.P_Print') IS NOT NULL

  DROP PROCEDURE dbo.P_Print;

 

IF OBJECT_ID(N'dbo.MGR_ForeignKeys', N'U') IS NOT NULL

  DROP TABLE dbo.MGR_ForeignKeys;

 

IF OBJECT_ID(N'dbo.MGR_Indexes', N'U') IS NOT NULL

  DROP TABLE dbo.MGR_Indexes;

GO