'SQL Server 2008'에 해당되는 글 8건

  1. 2010.11.11 Foreign Key와 Index 를 Drop & Create 하는 구문 생성하기 (3)
2010. 11. 11. 12:04
요즘은 게임 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 에서는 에러가 날 텐데요.
소스 찬찬히 보시고 수정해서 사용하시면 됩니다~ ^^


다운로드


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

 














Posted by 르매

댓글을 달아 주세요

  1. 최병훈 2010.11.25 13:08  댓글주소  수정/삭제  댓글쓰기

    안녕하세요~ 수고 하신 덕분에 좋은 글 많이 보고 있습니다.ㅎㅎㅎ
    질문이 있어서 마땅히 문의 할대도 없네요..

    Backup with password 옵션 문의 인데요~
    현재 DB backup file 보안을 구성 중인 별도의 비용 발생 없이 최소화 된 상태에서 보안을 생각 중입니다.

    TSQL 구문중 Backup database ~ with password 옵션이 있는데요.. mssql2008 이후 버젼에서는 제거가 된다고 합니다.
    1. 어떤 이유로 제거가 되는건지?
    2. 이후 버젼에 대처 옵션이 등장 하는건지?
    3. password를 알아 낼 수 있는 방법 - tool 등이 있는건지?
    혹 알 고 계신거 있으시면 조언 부탁드립니다.

    • BlogIcon 르매 2010.11.26 22:51 신고  댓글주소  수정/삭제

      1. BOL에 보시면 패스워드를 설정해도 다른 수단을 통해 백업 파일을 읽거나 패스워드를 바꾸는 것은 막을 수 없다는 내용이 있습니다.

      2. 이미 2008부터 제공하고 있는 기능이 있습니다. TDE라고 부르는데요.. 예전에 한번 포스팅한 적이 있는 내용입니다. http://purumae.tistory.com/98

      단, 이 기능은 엔터프라이즈 에디션에서 지원하기 때문에 스탠다드 에디션을 사용한다면 백업 파일을 보관하는 폴더의 ACL을 잘 관리하여 도난당하지 않도록 하는 방법 뿐일 듯 합니다.

      3. BOL의 내용으로보아 Tool이 있는 것으로 보입니다.
      실제 어떤 툴이 사용되는지는 잘 모르겠습니다. ^^;

  2. BlogIcon 최병훈 2010.11.29 16:01  댓글주소  수정/삭제  댓글쓰기

    좋은 정보 감사 합니다. ^^ ~ SQL Server Books Online 그냥 그렇거니 한 부분이였는데..뉴앙스상 tool이 있을 수 있다는것 같네요~ 혹 tool 발견 하시게 되면 연락 부탁 드려요~ 늘 감사 합니다. (pronet3@nate.com)