특정 시점에 테이블의 데이터 값을 반환하는 테이블은 첫 번째 관계형 데이터베이스 이후로 우리와 함께 해왔지만 항상 특별한 쿼리와 제약 조건이 필요했고 제대로하기가 까다로울 수 있습니다. SQL Server 2016의 새로운 기능인 시스템 버전 임시 테이블은 이러한 테이블이 다른 테이블처럼 작동하도록합니다. 테이블을 생성하거나 기존 테이블을 수정하는 방법은 무엇입니까? 메모리 내 최적화 된 OLTP 테이블을 임시로 만들려면 어떻게해야합니까? Alex Grinberg가 방법을 보여줍니다.

임시 또는 시스템 버전 테이블은 SQL Server 2016의 데이터베이스 기능으로 도입되었습니다.이를 통해 현재 데이터가 아닌 지정된 시간에 저장된 데이터에 대한 정보를 제공 할 수있는 테이블 유형이 제공됩니다. ANSI SQL 2011은 먼저 임시 테이블을 데이터베이스 기능으로 지정했으며 이제 SQL Server에서 지원됩니다.

임시 테이블의 가장 일반적인 비즈니스 용도는 다음과 같습니다.

  • 천천히 변화하는 치수. 시간 테이블은 데이터웨어 하우징 데이터베이스에서 잘 알려진 문제인 시간 분할 데이터와 같이 지정된 기간 동안 최신 데이터를 쿼리하는 더 간단한 방법을 제공합니다.
  • 데이터 감사. 임시 테이블은 "상위"테이블에서 데이터가 수정 된시기를 결정하는 감사 추적을 제공합니다. 이를 통해 규정 준수 요구 사항을 충족하고 시간에 따른 데이터 변경 사항을 추적 및 감사하여 필요할 때 데이터 포렌식을 수행 하는 데 도움이됩니다 .
  • 레코드 수준 손상 복구 또는 복구 . 레코드가 실수로 삭제되거나 업데이트 된 경우 다운 타임없이 테이블 행의 데이터 변경을 '실행 취소'하는 방법을 설정합니다. 따라서 이전 버전의 데이터를 히스토리 테이블에서 검색하여 '상위'테이블에 다시 삽입 할 수 있습니다. – 이는 누군가 (또는 일부 애플리케이션 오류로 인해) 실수로 데이터를 삭제하고 사용자가 데이터를 되돌 리거나 복구하려고 할 때 도움이됩니다.
  • 문서 발행일에 대한 정확한 데이터로 재무 보고서, 송장 및 명세서 를 복제 합니다. 임시 테이블을 사용하면 특정 시점의 데이터를 쿼리하여 당시의 데이터 상태를 조사 할 수 있습니다.
  • 진행중인 비즈니스 활동에서 데이터가 시간에 따라 어떻게 변하는 지 이해하여 추세 를 분석하고 시간에 따라 데이터가 변하는 방식의 추세를 계산합니다.

SQL Server 2016이 도입되기 전 어두운 날에는 데이터 로깅 메커니즘이 트리거에서 명시 적으로 설정되어야했습니다. 간단한 예제를 제공하려면 Department 테이블 자체부터 시작하여 다음 구조로 Department 테이블 에 대한 기록 유지 관리를 자동화해야 합니다.

 

CREATE TABLE dbo.Department

    (

    DeptID INT NOT NULL,

    DeptName VARCHAR(50) NOT NULL,

    ManagerID INT NULL,

    ParentDeptID INT NULL,

    Created DATETIME NOT NULL

      CONSTRAINT DF_Department_Created DEFAULT GETDATE(),

    CONSTRAINT PK_Department_DeptID PRIMARY KEY CLUSTERED(DeptID ASC) ON [PRIMARY]

    ) ON [PRIMARY];

  GO

다음 단계는 변경 내역을 제공하는 두 개의 추가 열이 있는 Department_Log 테이블 을 만드는 것입니다.

  • LogDate
  • LogAction

 

CREATE TABLE dbo.Department_Log

    (

    DeptID INT NOT NULL,

    DeptName VARCHAR(50) NOT NULL,

    ManagerID INT NULL,

    ParentDeptID INT NULL,

    Created DATETIME NOT NULL,

    LogDate DATETIME NOT NULL,

    LogAction VARCHAR(10) NOT NULL

    ) ON [PRIMARY];

  GO

로깅 '히스토리'테이블이 준비되면 UPDATE 및 DELETE 작업에 대한 변경 사항을 기록하는 트리거를 만들 수 있습니다.

 

CREATE TRIGGER dbo.tr_Department_Log

  ON dbo.Department

  FOR UPDATE, DELETE

  AS

    BEGIN

    SET NOCOUNT ON;

    IF

      (SELECT COUNT(1)

         FROM inserted

           JOIN deleted

             ON Inserted.DeptID = Deleted.DeptID

      ) > 0

      BEGIN

      INSERT dbo.Department_Log

        (DeptID, DeptName, ManagerID, ParentDeptID, Created, LogDate, LogAction)

        SELECT Deleted.DeptID, Deleted.DeptName, Deleted.ManagerID,

          Deleted.ParentDeptID, Deleted.Created, GETDATE(), 'UPDATED'

        FROM deleted;

      END;

    ELSE

      BEGIN

      INSERT dbo.Department_Log

        (DeptID, DeptName, ManagerID, ParentDeptID, Created, LogDate, LogAction)

        SELECT Deleted.DeptID, Deleted.DeptName, Deleted.ManagerID,

          Deleted.ParentDeptID, Deleted.Created, GETDATE(), 'DELETED'

        FROM deleted;

      END;

    SET NOCOUNT OFF;

    END;

  GO

Department_Log 테이블이 트리거와 함께 작동 하는 방식을 보여주기 위해 DeptID = 1 인 행을 세 번 업데이트 한 다음이 행을 삭제하고 마지막으로 마지막 업데이트에서 DeptName 열을 원래 값으로 설정했습니다.

 

update dbo.Department

SET DeptName = ''

where DeptID = 1

 

update dbo.Department

SET DeptName = 'Engineering IT'

where DeptID = 1

 

update dbo.Department

SET DeptName = 'Engineering WEB'

where DeptID = 1

 

DELETE dbo.Department where DeptID = 1

 

INSERT dbo.Department(DeptID,  DeptName)

SELECT  DeptID,DeptName

FROM Department_Log WHERE DeptID = 1 and LogAction = 'DELETED'

 

update dbo.Department

SET DeptName = 'Engineering'

where DeptID = 1

 

select DeptID,  DeptName,Created,LogDate,LogAction from Department_Log

다음 그림에 표시된 Department_Log 테이블의 결과 : 

SQL Server 2016의 임시 테이블 기능은 로깅 메커니즘을 크게 단순화 할 수 있습니다. 이 문서에서는 시스템 버전 테이블을 작성하는 방법에 대한 단계별 지침을 제공합니다.

테이블을 임시 테이블로 마이그레이션하기 위해 기존 테이블에 임시 테이블 옵션을 설정할 수 있습니다. 새 임시 테이블을 생성하려면 임시 테이블 옵션을 ON으로 설정하기 만하면됩니다 (예 : SYSTEM_VERSIONING = ON). 임시 테이블 옵션이 활성화되면 SQL Server 2016은 "기록"테이블을 자동으로 생성하고 내부적으로 상위 및 기록 테이블을 모두 유지합니다. 하나는 실제 데이터를 저장하고 다른 하나는 기록 데이터를 저장합니다. 시간 테이블의 SYSTEM_TIME 기간 열 (예 : SysStartTime 및 SysEndTime)을 사용하면 메커니즘이 다른 시간 조각에 대한 데이터를보다 효율적으로 쿼리 할 수 ​​있습니다. 업데이트되거나 삭제 된 데이터는 "기록"테이블로 이동하는 반면 "상위"테이블은 업데이트 된 레코드에 대한 최신 행 버전을 유지합니다.

캐치는 무엇입니까?

임시 테이블의 가장 중요한 고려 사항, 제한 사항 및 제한 사항은 다음과 같습니다.

  • 임시 테이블과 히스토리 테이블 사이에 레코드를 연결하려면 임시 테이블에 기본 키가 있어야합니다. 그러나 히스토리 테이블은 기본 키를 가질 수 없습니다.
  • DATETIME2의 데이터 형식은 (예를 들어 SYSTEM_TIME 기간 열의 설정해야 SysStartTime 및 SysEndTime ).
  • 히스토리 테이블을 생성 할 때 항상 히스토리 테이블에서 임시 테이블의 스키마와 테이블 이름을 모두 지정해야합니다.
  • PAGE 압축은 히스토리 테이블의 기본 설정입니다.
  • 임시 테이블은 스토리지 비용에 영향을 미치고 성능 문제가있을 수있는 blob 데이터 유형 (nvarchar (max), varchar (max), varbinary (max), ntext, text 및 image)을 지원합니다.
  • 시간 테이블과 히스토리 테이블은 모두 동일한 데이터베이스에 생성되어야합니다. 링크 된 서버를 사용하여 임시 테이블을 제공 할 수 없습니다.
  • 히스토리 테이블에는 제약 조건, 기본 키, 외래 키 또는 열 제약 조건을 사용할 수 없습니다.
  • FOR SYSTEM_TIME 절을 사용하는 쿼리가있는 인덱싱 된 뷰에서 임시 테이블을 참조 할 수 없습니다.
  • SYSTEM_TIME 기간 열은 INSERT 및 UPDATE 문에서 직접 참조 할 수 없습니다.
  • SYSTEM_VERSIONING이 ON 인 동안에는 TRUNCATE TABLE을 사용할 수 없습니다.
  • 히스토리 테이블의 데이터를 직접 수정할 수 없습니다.

전체 고려 사항 및 제한 사항 목록은 온라인 설명서를 참조 하십시오.

임시 테이블 생성

Listing 1의 하나의 DDL 스크립트에서 임시 및 히스토리 테이블을 생성하는 방법을 설명했습니다. 앞서 언급했듯이, 두 열 모두에 대해 데이터 유형이 datetime2 인 SysStartTime 및 SysEndTime 열은 임시 테이블에 필요합니다. SysStartTime 컬럼 은 GENERATED ALWAYS AS ROW START NOT NULL 스펙 이어야 하며 SysEndTime 은 GENERATED ALWAYS AS ROW END NOT NULL 이어야 합니다 . 해당 열에 대한 기본값을 제공 할 의무는 없지만 권장합니다. SysStartTime 및 SysEndTime 열은 모두 PERIOD FOR SYSTEM_TIME 열에 지정되어야합니다 (MSDN이 PERIOD를 정의한대로 다른 발행물 PERIOD calls 절).

참고 : 시스템 버전 열의 이름을 SysStartTime 및 SysEndTime 으로 지정할 필요는 없지만 시간 캡처 기능을 반영하도록 열 이름을 선택해야합니다. GENERATED ALWAYS AS ROW START / END 및 PERIOD FOR SYSTEM_TIME (nameFrom, nameTo) 옵션은 임시 테이블 기능을 활성화합니다.

 

CREATE TABLE Department

    (

    DeptID INT NOT NULL PRIMARY KEY CLUSTERED,

    DeptName VARCHAR(50) NOT NULL,

    ManagerID INT NULL,

    ParentDeptID INT NULL,

    SysStartTime DATETIME2 GENERATED ALWAYS AS ROW START

      CONSTRAINT DF_Department_SysStartTime DEFAULT SYSUTCDATETIME() NOT NULL,

    SysEndTime DATETIME2 GENERATED ALWAYS AS ROW END

      CONSTRAINT DF_Department_SysEndTime

   DEFAULT CONVERT( DATETIME2, '9999-12-31 23:59:59' ) NOT NULL,

    PERIOD FOR SYSTEM_TIME(SysStartTime, SysEndTime)

    )

  WITH (SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.DepartmentHistory));

목록 1 : 시간 및 히스토리 테이블 작성

임시 테이블이 생성되면 밑줄이 그어진 히스토리 테이블이 자동으로 생성되고 (그림 1), 히스토리에 대해 SysStartTime 및 SysEndTime (또는 시스템 버전 관리를 정의하기 위해 선택한 이름) 열이 있는 CLUSTERED INDEX 가 생성됩니다. 표, 목록 2.

 

CREATE CLUSTERED INDEX ix_DepartmentHistory

  ON dbo.DepartmentHistory

    (SysStartTime ASC,

    SysEndTime ASC

    ) ON [PRIMARY];

목록 2 : 클러스터형 인덱스 생성

임시 테이블에 새 열을 추가해야하는 경우 ALTER TABLE… ADD 열 DDL을 허용해야하며 새 열은 히스토리 테이블에서 자동으로 미러링됩니다.

그림 1 : 개체 탐색기에서 새로 생성 된 시간 및 기록 테이블 표시.

그러나 임시 테이블에는 DROP TABLE DDL을 사용할 수 없습니다. 먼저 SYSTEM_VERSIONING을 꺼야합니다.

 

ALTER TABLE Department SET (SYSTEM_VERSIONING = OFF);

목록 3 : 부서 테이블에서 SYSTEM_VERSIONING 비활성화.

SYSTEM_VERSIONING이 OFF로 설정되면 임시 테이블과 히스토리 테이블이 모두 일반 테이블이됩니다. 그런 다음 DROP TABLE 명령을 해당 테이블에 사용할 수 있습니다.

기존 테이블을 시스템 버전 테이블로 설정

SQL Server를 사용하면 기존 테이블을 임시 테이블로 변환 할 수 있습니다. 이 작업을 수행하려면 테이블에 기본 키가 있는지 확인하고 아직 존재하지 않는 경우 새로 만들어야합니다. 그런 다음 테이블은 두 개의 datetime2 데이터 유형 열로 변경되어야하며 GENERATED ALWAYS AS ROW START / END 옵션도…
PERIOD FOR SYSTEM_TIME (nameFrom, nameTo) 과 함께 적용 되어야합니다.
두 옵션 모두 ALTER 명령으로 완료해야합니다. 두 번째 ALTER 명령은 SYSTEM_VERSIONING 속성을 활성화하고 선택적으로 (명시 적으로 제공하는 것이 좋습니다) HISTORY_TABLE 속성의 이름을 제공합니다 (Listing 4).

예를 들어 기존 테이블 Department_Exist 를 임시 테이블로 설정해 보겠습니다 . 목록 4를 실행 한 다음 목록 5를 실행하십시오. 그림 2와 같이 테이블 아이콘을 새로 고쳐 결과를 확인하십시오.

 

CREATE TABLE Department_Exist (    

       DeptID int NOT NULL PRIMARY KEY CLUSTERED  

     , DeptName varchar(50) NOT NULL  

     , ManagerID INT  NULL  

     , ParentDeptID int NULL )  

목록 4 : Department_Exist 테이블 생성.

 

ALTER TABLE dbo.Department_Exist

  ADD SysStartTime datetime2 GENERATED ALWAYS AS ROW START  

  CONSTRAINT DF_Department_Exist_SysStartTime DEFAULT SYSUTCDATETIME() NOT NULL,

  SysEndTime datetime2 GENERATED ALWAYS AS ROW END

  CONSTRAINT DF_Department_Exist_SysEndTime DEFAULT CONVERT (DATETIME2, '9999-12-31 23:59:59') NOT NULL,

         PERIOD FOR SYSTEM_TIME (SysStartTime, SysEndTime)

  GO

  

  ALTER TABLE dbo.Department_Exist

      SET (SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.Department_ExistHistory))

  GO

목록 5 : 시스템 버전 열을 추가하고 Department_Exist 테이블 에서 시스템 버전을 활성화 합니다.

기존 테이블

시간 테이블로 변환

그림 2 : Temporal 테이블로 변환 한 후 Department_Exist 를 나란히 비교 합니다.

임시 테이블의 메타 데이터를 확인하십시오.

 

-- List temporal tables, temporal_type = 2  

  SELECT tables.object_id, temporal_type, temporal_type_desc, history_table_id,

    tables.name

    FROM sys.tables

    WHERE temporal_type = 2 -- SYSTEM_VERSIONED_TEMPORAL_TABLE

  -- List temporal tables and history tables

  SELECT h.name temporal_name, h.temporal_type_desc, h.temporal_type,

    t.name AS history_table_name, t.temporal_type, t.temporal_type_desc

    FROM sys.tables t

      JOIN sys.tables h

        ON t.object_id = h.history_table_id

메모리 내 최적화 된 OLTP 테이블을 시스템 버전 테이블로 변환

In-Memory Optimized OLTP 테이블을 시스템 버전 테이블 로 변환하는 프로세스 는 비슷하지만이 섹션에서 다루고 설명해야 할 몇 가지 차이점이 있습니다.

인 메모리 최적화 테이블을 시스템 버전 테이블로 변환 할 때 몇 가지 특정 세부 정보를 알고 있어야합니다.

  • 인 메모리 최적화 테이블은 내구성이 있어야합니다 (DURABILITY = SCHEMA_AND_DATA).
  • In-Memory 최적화 된 히스토리 테이블은 디스크 기반으로 생성됩니다.
  • 부모 테이블에만 영향을 미치는 쿼리는 기본적으로 컴파일 된 T-SQL 모듈에서 사용할 수 있습니다. 고유하게 컴파일 된 모듈에서 FOR SYSTEM TIME 절을 사용하여 임시 쿼리를 사용할 수 없지만 임시 쿼리 및 비원시 모듈에서 메모리 내 최적화 된 테이블과 함께 FOR SYSTEM TIME 절을 사용할 수 있습니다.
  • SYSTEM_VERSIONING = ON 일 때 메모리 내 최적화 된 상위 테이블에 대한 변경 사항에 대한 가장 최근 변경 사항 (INSERT, DELETE)을 수락하기 위해 내부 메모리 최적화 준비 테이블 이 자동으로 생성됩니다 .
  • 내부 메모리 내 최적화 된 준비 테이블의 데이터는 비동기 데이터 플러시 작업에 의해 정기적으로 디스크 기반 기록 테이블로 이동됩니다. 이 데이터 플러시 메커니즘은 내부 메모리 버퍼를 상위 개체의 메모리 사용량의 10 % 미만으로 유지하는 것을 목표로합니다. DMV sys.dm_db_xtp_memory_consumers 는 총 메모리 사용량을 추적하는 데 도움이됩니다.
  • 데이터 플러시는 sys.sp_xtp_flush_temporal_history @schema_name, @object_name 저장 프로 시저를 호출하여 적용 할 수 있습니다.
  • SYSTEM_VERSIONING = OFF이거나 열을 추가, 삭제 또는 변경하여 시스템 버전 테이블의 스키마를 수정하면 내부 스테이징 버퍼의 전체 내용이 디스크 기반 기록 테이블로 이동됩니다.
  • 기록 데이터 쿼리는 효과적으로 SNAPSHOT 격리 수준에서 수행되며 항상 중복없이 메모리 내 스테이징 버퍼와 디스크 기반 테이블 간의 결합을 반환합니다.
  • 테이블 스키마를 내부적으로 변경하는 ALTER TABLE 작업은 데이터 플러시를 수행해야하므로 작업 속도가 느려질 수 있습니다.

시스템 버전 테이블 옵션이 활성화 된 상태에서 새 메모리 내 최적화 된 OLTP 생성

임시 테이블 옵션을 사용하여 새로운 인 메모리 최적화 테이블을 생성하기위한 DDL은 구문이 기존 디스크 기반 테이블과 매우 유사합니다. 메모리 내 최적화 된 테이블 구문에는 처음에 MEMORY_OPTIMIZED 및 DURABILITY 속성을 설정하는 WITH 블록이 있습니다. 따라서 SYSTEM_VERSIONING 속성은 목록 7과 같이 쉼표로 구분하여 추가해야합니다.

 

CREATE TABLE dbo.InMemory

  (

   UniqueName varchar(50) NOT NULL

   PRIMARY KEY NONCLUSTERED HASH WITH (BUCKET_COUNT = 131072),

   City varchar(32) NULL,

   State_Province varchar(32) NULL,

   LastModified datetime NOT NULL

      , SysStartTime datetime2 GENERATED ALWAYS AS ROW START

   CONSTRAINT DF_InMemory_SysStartTime DEFAULT GETDATE() NOT NULL

     , SysEndTime datetime2 GENERATED ALWAYS AS ROW END

   CONSTRAINT DF_InMemory_SysEndTime

   DEFAULT CONVERT (DATETIME2, '9999-12-31 23:59:59') NOT NULL

     , PERIOD FOR SYSTEM_TIME (SysStartTime,SysEndTime)

  )

  WITH

  (

      MEMORY_OPTIMIZED = ON , DURABILITY = SCHEMA_AND_DATA,

      SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.InMemory_History)

  )

목록 7 : 시스템 버전 테이블 옵션이 활성화 된 상태에서 새로운 인 메모리 최적화 OLTP 생성

기존 메모리 내 최적화 된 OLTP 테이블에 시스템 버전 테이블 옵션 추가.

기존의 메모리 내 최적화 된 OLTP 테이블을 시스템 버전 테이블로 변환하는 것이 더 어렵습니다 (Listing 8 참조).

이 메커니즘을 보여주기 위해 테이블을 만들어 보겠습니다.

 

CREATE TABLE dbo.InMemoryExist

  (

   UniqueName varchar(50) NOT NULL

   PRIMARY KEY NONCLUSTERED HASH WITH (BUCKET_COUNT = 131072),

   City varchar(32) NULL,

   State_Province varchar(32) NULL

  )

  WITH

  (

      MEMORY_OPTIMIZED = ON , DURABILITY = SCHEMA_AND_DATA

  )

목록 8 : 새로운 인 메모리 최적화 OLTP 테이블 생성

테이블이 생성되면 Listing 9와 같이 데이터를 테이블에 추가하기 전에 임시 테이블 옵션을 추가해야합니다.

 

ALTER TABLE dbo.InMemoryExist

  ADD SysStartTime datetime2 GENERATED ALWAYS AS ROW START  

  CONSTRAINT DF_InMemoryExist_SysStartTime DEFAULT SYSUTCDATETIME() NOT NULL,

  SysEndTime datetime2 GENERATED ALWAYS AS ROW END

  CONSTRAINT DF_InMemoryExist_SysEndTime DEFAULT CONVERT (DATETIME2, '9999-12-31 23:59:59') NOT NULL,

         PERIOD FOR SYSTEM_TIME (SysStartTime, SysEndTime)

  GO

  

  ALTER TABLE dbo.InMemoryExist

      SET (SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.InMemoryExist_History))

  GO

목록 9 : 임시 테이블 옵션 추가

테이블에 이미 데이터가있는 경우 테이블을 시스템 버전 테이블로 변환하는 프로세스가 더 복잡합니다.

경우 InMemoryExist이 시스템 버전이 옵션을 사용하여 생성 된, 우리는 드롭 할 필요가 InMemoryExist 및 InMemoryExist_History 테이블 :

 

ALTER TABLE InMemoryExist set (SYSTEM_VERSIONING = OFF)

  GO

  DROP TABLE InMemoryExist

  GO

  DROP TABLE InMemoryExist_History

  GO

테이블을 다시 생성합니다 (이 섹션의 위에 제공된 코드 샘플, 목록 8 사용). 그런 다음 InMemoryExist 테이블에 데이터를 삽입 합니다.

 

INSERT InMemoryExist

  select NEWID(),name, type_desc from sys.objects where is_ms_shipped = 0

코드를 실행하여 임시 테이블 옵션 (목록 9)을 추가하면 다음 오류가 발생합니다.

 

Msg 13575, Level 16, State 0, Line 21

  ADD PERIOD FOR SYSTEM_TIME failed because table 'database.dbo.InMemoryExist' contains records where end of period is not equal to MAX datetime.

  Msg 13510, Level 16, State 1, Line 29

SYSTEM_TIME 기간이 정의되지 않은 경우 SYSTEM_VERSIONING을 ON으로 설정할 수 없습니다.

이러한 오류를 방지하려면보다 자세한 단계에서 시스템 버전 관리 옵션을 추가해야합니다.

 

--Step 1. Adding nullable the columns

  ALTER TABLE dbo.InMemoryExist ADD SysStartTime datetime2 NULL  

  GO

  ALTER TABLE dbo.InMemoryExist ADD SysEndTime datetime2 NULL  

  GO

  --Step 2 Adding the default constraints

  ALTER TABLE InMemoryExist ADD CONSTRAINT DF_InMemoryExist_SysStartTime DEFAULT GETDATE() FOR SysStartTime;

  GO

  ALTER TABLE InMemoryExist ADD CONSTRAINT DF_InMemoryExist_SysEndTime DEFAULT CAST('9999-12-31 23:59:59.9999999' AS DATETIME2) FOR SysEndTime;

  --Step 3 Updating the column

  UPDATE dbo.InMemoryExist

   SET SysStartTime = '19000101 00:00:00.0000000'

   ,SysEndTime = '99991231 23:59:59.9999999'

  GO

  --Step 4 Setting NOT NULL to the columns

  ALTER TABLE dbo.InMemoryExist ALTER COLUMN SysStartTime datetime2 NOT NULL  

  GO

  ALTER TABLE dbo.InMemoryExist ALTER COLUMN SysEndTime datetime2 NOT NULL  

  GO

  --Step 5 Adding PERIOD FOR SYSTEM_TIME option

  ALTER TABLE InMemoryExist ADD PERIOD FOR SYSTEM_TIME (SysStartTime, SysEndTime)

  GO

  --Step 6 Setting SYSTEM_VERSIONING property

  ALTER TABLE InMemoryExist

      SET (SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.InMemoryExist_History))

  GO

이제 시스템 버전 관리 프로세스에 InMemoryExist 테이블이 활성화됩니다.

DATA_CONSISTENCY_CHECK 속성 지정

기존 데이터에 대한 데이터 일관성 검사를 시행하려면 DATA_CONSISTENCY_CHECK = ON으로 SYSTEM_VERSIONING을 설정해야합니다. 그러나 DATA_CONSISTENCY_CHECK 속성은 현재 . 임시 테이블에 대해 DATA_CONSISTENCY_CHECK를 활성화하기로 결정한 경우 인스턴스에 SQL Server 2016 용 누적 업데이트 1이 있는지 확인합니다.

다음은 기존 테이블에서 DATA_CONSISTENCY_CHECK 속성을 활성화하는 예입니다.

 

ALTER TABLE InMemoryExist

      SET (SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.TableName_History, DATA_CONSISTENCY_CHECK = ON))

  For a new table, DATA_CONSISTENCY_CHECK property enables after HISTORY_TABLE property separated by a comma.

  WITH (SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.DepartmentHistory, DATA_CONSISTENCY_CHECK = ON));

결론

임시 테이블은 행 버전 프로세스를 자동화하는 데 매우 유용한 SQL Server 2016 기능입니다. 데이터 보관 작업을 단순화하고 데이터웨어 하우스 데이터베이스에 대해 천천히 변화하는 차원을 활용하기위한 실질적인 해결책이 될 수도 있습니다. 새 테이블과 기존 테이블을 설정하는 것이 매우 쉽기 때문에 임시 테이블 기능은 SQL Server 데이터베이스와 함께 구현하기에 좋은 선택입니다.

'Database > SQL Server' 카테고리의 다른 글

free SSMS add-in for effective database performance tracing  (0) 2021.01.20
Tempdb 최적화 – tempdb 데이터파일 제거하기  (0) 2020.10.27
SQL Server 2016 - Temporal Table  (0) 2020.10.23
SQL Server 감사를위한 변경 데이터 캡처  (0) 2020.10.13
SQL Server 변경 추적을 사용하여 SQL Server 감사 생성  (0) 2020.10.13

QL Server 2016에서 Temporal Table이라는 기능이 있어서, 소개하고자 합니다.

Temporal은 다음과 같은 뜻을 가집니다.

- 1. 현세적인, 속세의   2. 시간의; 시간의 제약을 받는   3. 관자놀이께의 

 

Temporal 테이블은 제가 볼때 엄청난 기능은 아니고, Table의 내용이 변경(UPDATE, DELETE)이 될 경우 그 내용을 기록하는 History (?)성 테이블이라고 생각하면 됩니다.

글로 표현하는 것보다 그림으로 보는 것이 더욱 이해가 될 것 입니다.

제가 생각 할때 이 그림이 가장 맞는거 같습니다. 크게 별다른 기능이 아니고, 단순히 유저가 DELETE , UPDATE 라는 조작을 가하면 테이블에 기록을 남기는 것 입니다.

음... 일종에 트리거 같은 기능을 종속하여, 추가한 것으로 보여집니다. 

 

바로 이 기능을 테스트 해보겠습니다.

 

 

 

Step 1 - 테이블 생성 및 데이터 입력

 

우선 테이블을 생성하고, 데이터를 입력하는데 2개를 생성해 줍니다. 우리가 보기 위한 것은 기록을 검색하고, UPDATE , DELETE 시에 잘 남는지를 보는 것이므로,

생성과 동시에 데이터를 몇개 삽입 하도록 하겠습니다.

 

-- create history table CREATE TABLE dbo.PriceHistory (ID INT NOT NULL ,Product VARCHAR(50) NOT NULL ,Price NUMERIC(10,2) NOT NULL ,StartDate DATETIME2 NOT NULL ,EndDate DATETIME2 NOT NULL ) GO -- insert values for history INSERT INTO dbo.PriceHistory(ID,Product,Price,StartDate,EndDate) VALUES (1,'myProduct',1.15,'2015-07-01 00:00:00','2015-07-01 11:58:00') ,(1,'myProduct',1.16,'2015-07-01 11:58:00','2015-07-03 12:00:00') ,(1,'myProduct',1.18,'2015-07-03 12:00:00','2015-07-05 18:05:00') ,(1,'myProduct',1.21,'2015-07-05 18:05:00','2015-07-07 08:33:00') -- create current table to store prices CREATE TABLE dbo.Price (ID INT NOT NULL ,Product VARCHAR(50) NOT NULL ,Price NUMERIC(10,2) NOT NULL ,StartDate DATETIME2 NOT NULL ,EndDate DATETIME2 NOT NULL ,CONSTRAINT PK_Price PRIMARY KEY CLUSTERED (ID ASC) ) GO -- insert the current price (make sure start date is not in the future!) INSERT INTO dbo.Price(ID,Product,Price,StartDate,EndDate) VALUES (1,'myProduct',1.20,'2015-07-07 08:33:00','9999-12-31 23:59:59.9999999') GO

 

데이터가 잘 들어갔는지 확인해 보겠습니다.

첫번째가 Price 테이블 입니다. 그리고 아래가 PriceHistory 입니다.

 

 

 

 

Step 2 - Temporal Table 만들기 

 

이제 PriceHistory를 Price에 종속 시켜야합니다. 저는 종속이라는 표현을 쓰지만, Temporal 테이블을 생성한다고 하는게 맞겠네요.

아래를 보시면 StartDate 그리고 EndDate를 이용하여, Temporal 테이블의 기준 시간으로 삼습니다. 

-- enable system period columns ALTER TABLE dbo.Price ADD PERIOD FOR SYSTEM_TIME (StartDate,EndDate) GO -- turn on system versioning ALTER TABLE dbo.Price SET (SYSTEM_VERSIONING = ON (HISTORY_TABLE=dbo.PriceHistory,DATA_CONSISTENCY_CHECK=ON) ) GO

 

만약 Temporal 테이블이 정상적으로 만들어졌다면, 아래와 같은 모습을 확인 할 수 있습니다. Price 테이블을 클릭하면, 아래에 PriceHistory 테이블이 

나타나는 것을 확인 할 수 있습니다.

 

위의 2개의 테이블은 따로따로도 조회가 가능하며, Price 테이블의 과거 데이터를 조회 할 수도 있습니다.

 

SELECT * FROM dbo.Price FOR SYSTEM_TIME AS OF '2015-07-04' GO SELECT * FROM dbo.Price FOR SYSTEM_TIME AS OF '2015-07-03 12:00:00' GO SELECT * FROM dbo.Price FOR SYSTEM_TIME FROM '2015-07-06' TO '2015-07-06' GO

 

검색은 여러가지 가능하며, 부분적으로도 가능하며, BETWEEN 검색도 가능 합니다. 

이렇게 검색을 할 경우 현재 Price 테이블의 데이터를 조회하는 것이 아닌 PriceHistory 테이블을 조회합니다. 

 

위의 실행계획을 보면, PriceHistory를 조회하고 있는 것이 보이실 겁니다. 이전 같은 경우 Log 테이블을 따로 만들고, Join해서 조회할 것을 줄였다? 뭐 이렇게

생각하면 좋을 듯 합니다.

 

 

 

 

Step 3 - 데이터 조작

이제 데이터를 조작해 보겠습니다. 각각 데이터를 조작 할 경우 어떻게 남는지 확인해 보겠습니다.

-- Update UPDATE dbo.Price SET Price = 1.24 -- Delete DELETE dbo.Price -- Insert INSERT INTO dbo.Price(ID,Product,Price,StartDate,EndDate) VALUES (2,'YouProduct',1.60,'2015-07-07 08:33:00','9999-12-31 23:59:59.9999999');

 

우선 UPDATE 작업을 진행 합니다. 

UPDATE를 진행 할 경우 영향을 2개의 Table이 받는 것을 볼 수 있습니다.

 

보시는 바와 같이 PriceHistory 테이블에 Insert 작업이 수행됩니다. 분명히 저는 Price 테이블에만 Update 하였는데, 자동으로 삽입됩니다.

 

결과를 확인하면 다음과 같습니다.

결과를 보면 위의 Price 테이블의 StartDate가 제가 수행 한 날짜로 Update가 되었습니다.

그리고, 아래의 PriceHistory 테이블에 원래 4개의 행이 있었는데 5개로 증가하였습니다. 기존의 Price 테이블의 데이터 입니다.

 

이번에는 Delete를 수행하겠습니다. 

역시 Delete도 2개의 행이 영향을 받습니다.  실행계획에서도 이를 확인 할 수 있습니다.

또한 PriceHistory 테이블에 1개의 행이 추가된 것을 볼 수 있습니다.

 

마지막으로 Insert를 수행 하겠습니다. Insert는 Price 테이블에 해보겠습니다.

응? 오류가 납니다.... 

 

위에서 우리는 Insert구문에 StartDate와 EndDate의 값도 삽입하도록 Insert 구문에 명시하였습니다. 하지만 이는 우리가 건들일 수 없는 부분 입니다.

이것은 자동으로 SYSTEM 시간에 의해서 찍히므로, 이것을 제외한 Insert 구문을 날려야 합니다.

INSERT INTO dbo.Price(ID,Product,Price) VALUES (2,'YouProduct',1.60);

 

아래와 같이 정상적으로 추가 된 것을 확인 할 수 있습니다.

 

감사합니다.

 

 

 

추가

 

Trigger가 걸리는지 테스트를 요청하셔서, 한번 테스트 해봤습니다.

단순히 테이블을 추가하여, DELETE 시에 Trigger가 정상으로 동작하는지 보겠습니다.

 

테스트를 위해서 몇개의 행을 집어넣고, 테스트 하였습니다. 

 

CREATE TRIGGER DBO.Trg_TEST ON DBO.Price AFTER DELETE AS BEGIN SET NOCOUNT ON INSERT INTO PriceTrigger SELECT ID,'Trigger Test' FROM deleted END GO

 

만들고, DELETE를 이용하여, 행을 한개 지웠습니다.

결과는 아래에 보시는 바와 같습니다. 트리거에도 잘 남았으며, PriceHistory에도 잘 남은 것을 볼 수 있습니다.

 

 

참고 

https://msdn.microsoft.com/en-us/library/dn935015.aspx

http://www.infoq.com/news/2015/06/SQL-Server-Temporal

https://www.mssqltips.com/sqlservertip/3680/introduction-to-sql-server-2016-temporal-tables/



출처: https://burning-dba.tistory.com/75 [데이터엔지니어 주형권]

'Database > SQL Server' 카테고리의 다른 글

Tempdb 최적화 – tempdb 데이터파일 제거하기  (0) 2020.10.27
SQL Server 임시 테이블 : 방법 레시피  (0) 2020.10.23
SQL Server 감사를위한 변경 데이터 캡처  (0) 2020.10.13
SQL Server 변경 추적을 사용하여 SQL Server 감사 생성  (0) 2020.10.13
저장된 Plan Cache 확인 및 활용  (0) 2020.10.05

개요

CDC라고도하는 변경 데이터 캡처는 추가 프로그래밍 작업없이 SQL Server 데이터베이스 테이블에서 수행되는 변경 사항을 추적하고 캡처하는 데 유용한 기능으로 SQL Server 2008 버전에 처음 도입되었습니다. SQL Server 2016 이전에는 SQL Server Enterprise 버전 에서만 SQL Server 데이터베이스에서 변경 데이터 캡처를 사용할 수 있었으며 SQL Server 2016부터는 필요하지 않았습니다.

변경 데이터 캡처는 데이터베이스 테이블에 대한 INSERT, UPDATE 및 DELETE 작업을 추적하고 원본 테이블의 동일한 열 구조와 이러한 변경 사항에 대한 설명을 기록하는 추가 열을 사용하여 미러링 된 테이블에 이러한 변경 사항에 대한 자세한 정보를 기록합니다. SQL Server는 삽입 된 값을 보여주는 각 INSERT 문에 대해 하나의 레코드, 삭제 된 데이터를 표시하는 각 DELETE 문에 대한 레코드와 각 UPDATE 문에 대해 두 개의 레코드를 기록합니다. 첫 번째는 변경 전 데이터를 표시하고 두 번째는 수행 후 데이터를 표시합니다. 변화.

추가 열에는 다음이 포함됩니다.

  • SQL Server 엔진에서 기록 된 변경 사항에 할당 한 커밋 로그 시퀀스 번호 (LSN)를 표시하는 __ $ start_lsn 및 __ $ end_lsn
  • 동일한 트랜잭션의 다른 변경과 관련된 변경 순서를 표시하는 __ $ seqval , 변경 작업 유형을 표시하는 __ $ operation ( 여기서 1 = 삭제, 2 = 삽입, 3 = 업데이트 (변경 전) 및 4) = 업데이트 (변경 후)
  • __ $ update_mask 는 캡처 된 각 열에 대해 정의 된 비트 마스크이며 업데이트 열을 식별합니다.

이 세부 정보를 통해 보안 또는 감사 목적으로 데이터베이스 변경 사항을 모니터링하거나 T-SQL 또는 ETL 방법을 사용하여 이러한 변경 사항을 OLTP 소스에서 대상 OLAP 데이터웨어 하우스로 점진적으로로드 할 수 있습니다.

설치 및 아키텍처

변경 데이터 캡처를 사용하려면 SQL Server 에이전트가 SQL Server 인스턴스에서 실행되고 있어야합니다. SQL Server 데이터베이스 테이블에서 기능이 활성화되면 해당 데이터베이스에 대해 두 개의 SQL Server 에이전트 작업이 생성됩니다. 첫 번째 작업은 데이터베이스 변경 테이블을 변경 정보로 채우고 두 번째 작업은 구성 가능한 보존 기간 인 3 일보다 오래된 레코드를 삭제하여 변경 테이블을 정리합니다.

변경 데이터 캡처는 데이터 변경의 소스 인 SQL Server 트랜잭션 로그에 의존합니다. 변경이 수행되면이 변경 사항이 트랜잭션 로그 파일에 기록됩니다.

해당 테이블에서 CDC 기능이 활성화 된 경우 CDC 기능에 대한 캡처 프로세스 역할을하는 트랜잭션 로그 복제 로그 판독기 에이전트는 트랜잭션 로그 파일에서 변경 로그를 읽고 이러한 변경 사항에 대한 메타 데이터 정보를 추가하고 아래와 같이 관련 CDC 변경 테이블 :

감사 솔루션으로서의 변경 데이터 캡처

변경 데이터 캡처는 비동기 SQL Server 감사 솔루션으로 사용되어 SELECT 문을 추적하는 옵션없이 INSERT, UPDATE 또는 DELETE 작업과 같은 테이블의 DML 변경을 추적하고 감사 할 수 있습니다.

Change Data Capture를 좋은 SQL Server Audit 솔루션으로 만드는 이유는 몇 가지 T-SQL 명령을 사용하여 쉽게 구성 할 수 있고 수정 프로세스 이전의 값에 대한 기록 정보를 제공하며 데이터 수정 프로세스에 대한 자세한 정보를 제공한다는 것입니다.

변경 데이터 캡처를 사용하여 SQL Server DML 변경 사항을 감사하는 방법을 살펴 보겠습니다.

CDC 활성화

감사를 위해 특정 테이블에서 변경 데이터 캡처를 사용하려면 먼저 SYSADMIN 고정 서버 역할의 구성원이 아래에 표시된대로 sys.sp_cdc_enable_db 시스템 저장 프로 시저를 사용하여 데이터베이스 수준에서 CDC를 먼저 사용하도록 설정해야 합니다. :

USE [CDCAudit]

GO

EXEC sys.sp_cdc_enable_db

GO

해당 데이터베이스에서 CDC가 활성화되었는지 확인하기 위해 다음과 같이 CDC가 활성화 된 데이터베이스 목록에 대해 sys.databases DMV를 쿼리합니다.

데이터베이스 수준에서 CDC를 활성화 한 후에는 sys.sp_cdc_enable_table 시스템 저장 프로 시저를 사용하여 db_owner 고정 데이터베이스 역할의 구성원이 데이터베이스 테이블의 DML 변경 사항을 추적하고 감사 할 수 있습니다. 아래에 표시된 것처럼 @captured_column_list 매개 변수로 지정된 열 목록의 변경 사항 과 @filegroup_name 매개 변수로 지정된 별도의 파일 그룹에 변경 테이블을 만듭니다 .

USE [CDCAudit]

GO  

EXEC sys.sp_cdc_enable_table  

@source_schema = N'dbo',  

@source_name   = N'Employee_Main',  

@role_name     = NULL,  

@filegroup_name = NULL,  

@supports_net_changes = 0

GO

해당 테이블에서 CDC가 활성화되었는지 확인하기 위해 아래와 같이 CDC가 활성화 된 현재 데이터베이스 아래의 모든 테이블에 대해 sys.tables DMV를 쿼리합니다.

테이블에서 CDC가 활성화되면 CDC 관련 정보를 저장하기 위해 데이터베이스의 CDC 스키마 아래에 여러 시스템 테이블이 생성됩니다. 이 테이블에는 다음이 포함됩니다.

  • 캡처 된 열 목록이 포함 된 CDC .captured_columns 테이블
  • 캡처에 사용할 수있는 테이블 목록이 포함 된 CDC .change_tables 테이블
  • 캡처 데이터가 활성화 된 이후의 모든 DDL 변경 내역을 기록하는 CDC .ddl_history 테이블
  • 변경 테이블과 연관된 모든 인덱스를 포함하는 CDC .index_columns 테이블
  • LSN 번호를 시간과 매핑하는 데 사용되는 CDC .lsn_time_mapping 테이블과 마지막으로 소스 테이블에서 DML 변경 사항을 캡처하는 데 사용되는 각 CDC 사용 테이블에 대한 하나의 변경 테이블이 아래와 같이 표시됩니다.

… 그리고 CDC 지원 테이블, 캡처 및 정리 작업과 관련된 SQL 에이전트 작업은 아래와 같이 생성됩니다.

CDC 비활성화

변경 데이터 캡처는 아래와 같이 sys.sp_cdc_disable_table 시스템 저장 프로 시저를 사용하여 특정 테이블에서 쉽게 비활성화 할 수 있습니다 .

… 또는 아래와 같이 sys.sp_cdc_disable_db 시스템 저장 프로 시저를 사용하여 CDC 활성화 테이블에서 하나씩 비활성화 할 필요없이 데이터베이스 수준에서 완전히 비활성화합니다 .

DML 변경 감사

데이터베이스 테이블에서 CDC를 활성화 한 후 해당 테이블에서 아래의 세 가지 데이터베이스 DML 변경 (INSERT, UPDATE, DELETE)을 수행하고 다음과 같이 CDC 기능을 사용하여 이러한 변경 사항을 감사하는 방법을 확인합니다.

앞서 언급했듯이 데이터 수정 사항은 CDC 사용 테이블과 관련된 변경 테이블에 기록됩니다 . 여기서는 [cdc]. [dbo_Employee_Main_CT] 테이블입니다. 소스 테이블에 최근 삽입 된 모든 레코드를보기 위해 유형 2의 모든 작업에 대한 변경 테이블을 쿼리 할 수 ​​있으며 삽입 된 값을 포함하여 INSERT 작업에 대한 전체 정보가 아래와 같이 표시됩니다.

유형 1의 모든 작업에 대한 변경 테이블을 쿼리하면 원본 테이블에서 최근에 삭제 된 모든 레코드가 반환되고 삭제 된 레코드의 값이 아래와 같이 표시됩니다.

마지막으로 유형 3 및 4의 작업에 대한 변경 테이블을 쿼리하여 UPDATE 문을 추적 할 수 있습니다. 그러면 업데이트 이전의 값이 작업 유형 3에, 변경 후 값이 작업 유형 4에 표시됩니다. 아래 그림과 같이:

변경 테이블 쿼리는 Microsoft에서 권장하지 않습니다. 대신 아래와 같이 CDC 사용 테이블과 관련된 CDC.fn_cdc_get_all_changes 시스템 함수를 쿼리 할 수 있습니다.

CDC.fn_cdc_get_all_changes 함수는 아래와 같이 모든 DML 변경 정보를 검색 하는 @from_lsn , @to_lsn , @row_filter_option 매개 변수 를 제공하여 쿼리 할 수 ​​있습니다 .

한계

변경 데이터 캡처를 사용 하면 데이터베이스 DML 변경 사항 만 감사 할 수 있지만 SELECT 문을 모니터링하는 옵션은 없지만 구성 작업은 무시할 수 있습니다. 반면 CDC를 SQL Server Audit 솔루션으로 고려하려면 상당한 유지 관리 및 관리 노력이 필요합니다. 여기에는 추적 데이터가 구성 가능한 일 수 동안 변경 테이블에 보관되고 동일하거나 다른 데이터 파일에 저장되기 때문에 보관 메커니즘 자동화가 포함됩니다.

또한 변경 테이블은 각 데이터베이스 아래에 저장되며 추적 된 각 테이블에 대해 함수가 생성됩니다. 이로 인해 번거롭고 동일한 데이터베이스의 모든 테이블, 동일한 인스턴스의 모든 데이터베이스 또는 여러 인스턴스의 DML 변경 정보를 읽는 통합 감사 보고서를 만드는 데 상당한 프로그래밍 노력이 필요합니다.

SQL Server Audit 솔루션으로서 CDC 기능에 대한 또 다른 제한은 CDC 사용 테이블에서 DDL 변경을 처리하는 데 필요한 어려운 프로세스입니다. 소스 테이블에서 변경 데이터 캡처를 사용하면 해당 테이블에서 DDL 변경을 수행하는 것을 방해하지 않기 때문입니다.

또한 SQL Server 에이전트 서비스가 실행되고 있지 않으면 CDC 캡처 작업이 작동하지 않으며 데이터베이스 복구 모델이 단순하더라도 CHECKPOINT가 실행 중이더라도 로그 잘림이 진행되지 않기 때문에 데이터베이스 로그 파일이 빠르게 증가합니다. 캡처를 기다리는 모든 변경 사항이 CDC 캡처 프로세스에 의해 수집 될 때까지 수행됩니다.

'Database > SQL Server' 카테고리의 다른 글

SQL Server 임시 테이블 : 방법 레시피  (0) 2020.10.23
SQL Server 2016 - Temporal Table  (0) 2020.10.23
SQL Server 변경 추적을 사용하여 SQL Server 감사 생성  (0) 2020.10.13
저장된 Plan Cache 확인 및 활용  (0) 2020.10.05
TempDB 물리적 파일 위치 변경[출처] TempDB 물리적 파일 위치 변경  (0) 2020.09.25

이 SQL Server 감사 시리즈는 SQL Server 변경 추적에 관한 내용이며 SQL 업데이트 및 삭제를 포함한 DML 변경 사항을 활성화, 비활성화, 감사하는 개요를 다룹니다.

이것이 SQL Server Audit 시리즈의 첫 번째 읽기 인 경우이 시리즈의 이전 기사 ( 하단 의 TOC 참조 )를 통해 SQL Server Audit의 개념에 대한 확실한 배경을 구축하는 것이 좋습니다. SQL Server 인스턴스 및 데이터베이스를 감사하는 이유와 SQL Server 데이터베이스를 감사하는 데 사용할 수있는 다양한 방법. 이 기사에서는 SQL Server 변경 추적을 사용하여 SQL Server 감사를 수행하는 방법에 대해 설명합니다.

개요

CT라고도하는 SQL Server 변경 추적은 SQL Server 2008에 처음 도입 된 경량 추적 메커니즘으로, SQL Server 데이터베이스 테이블에서 수행 된 DML 변경을 추적하는 데 사용할 수 있습니다. SQL 변경 내용 추적은 무료 Express 버전을 포함한 모든 SQL Server 버전에서 구성 할 수 있습니다.

SQL Server 변경 추적은 동기 추적 메커니즘으로, 변경 데이터 캡처 비동기 메커니즘과 같은 트랜잭션 로그 파일에서 변경 내용을 읽는 동안 지연없이 DML 변경이 커밋되면 변경 정보를 직접 사용할 수 있습니다. . 즉, SQL 변경 내용 추적은 DML 변경 내용을 캡처하거나 쓸 때 SQL 에이전트 작업에 대한 종속성이 없기 때문에 SQL Server 에이전트 서비스를 가동하고 실행할 필요가 없습니다.

SQL Server 변경 내용 추적이 데이터베이스 테이블에서 활성화되면 SQL Server 엔진은 Change_Tracking_ <Object_ID> 라는 이름으로 내부 테이블을 만들어 추적 된 사용자 테이블의 INSERT, UPDATE 및 DELETE 문을 추적합니다. SQL 변경 추적을 변경 데이터 캡처 기능 보다 가볍게 만드는 이유는 추적 된 테이블 내의이 행에 대해 수행 된 DML 변경이 기본 키를 제공함을 언급하여 데이터베이스 테이블 변경을 추적한다는 것입니다.수정 된 행의 열 값, 변경된 열 및 수정 유형, 최소 저장 요구 사항 및 오버 헤드로 삽입 또는 삭제 된 값 또는 업데이트 프로세스 전후의 값을 쓰는 등 변경된 데이터에 대한 자세한 정보를 쓰지 않습니다. 이 키 값은 추적 된 테이블에서 수정 된 행을 식별하는 데 사용되므로 해당 테이블에서 SQL 변경 내용 추적을 활성화하기 위해 데이터베이스 테이블에 기본 키가 있어야하는 이유입니다.

감사 솔루션으로서의 SQL Server 변경 추적

SQL Server 변경 추적은 레거시 SQL Server 감사 솔루션으로 간주되며, 변경된 행, 해당 행의 기본 키 및 무엇과 같은 간단한 감사 질문에 대답하여 데이터베이스 테이블 DML 변경을 추적하고 감사하는 데 사용할 수 있습니다. 해당 행에서 수행 된 변경 유형.

SQL 변경 추적이 SQL Server 데이터베이스 감사 솔루션으로서 덜 바람직하게 만드는 이유는 업데이트 프로세스 전후에 삽입 된 데이터, 삭제 된 데이터 또는 데이터에 대한 정보를 기록하지 않는다는 것입니다. 또한 기본 키 제약 조건이 정의되지 않은 테이블은 기본 키 제약 조건이있는 데이터베이스 테이블에 대해서만 제한되므로 SQL 변경 추적을 사용하여 감사 할 수 없습니다.

반면에 SQL Server 변경 내용 추적은 데이터베이스 테이블에서 수행 된 변경 사항에 대한 기록을 기록하지 않으며 버전 기록을 유지하지 않고 해당 행에서 수행 된 마지막 변경 사항을 기록합니다. 예를 들어 행이 삽입 된 다음 여러 번 업데이트되고 마지막으로 삭제되면 SQL Server Audit 솔루션 인 SQL 변경 추적은 해당 행에서 수행 된 이전 작업을 고려하지 않고 마지막 삭제 문만 기록합니다.

SQL Server Chang Tracking을 사용하여 유용한 SQL Server Audit 솔루션을 구축하려면 다음에 저장된 변경된 행의 기본 키 값을 기반으로 SQL 변경 내용 추적의 내부 테이블을 추적 된 원본 테이블과 조인하는 데 추가적인 코딩 작업이 필요합니다. 변경된 데이터에 대한 완전한 정보를 얻기위한 내부 테이블. 내부 테이블이 표시되지 않고 직접 쿼리 할 수 ​​없기 때문에 나중에 설명 할 이러한 내부 테이블을 기반으로 구축 된 온 디스크 테이블을 사용하는 SQL 변경 추적 기능의 이점을 얻을 수 있습니다. 이 기사.

여기서 고려해야 할 또 다른 사항은 SQL 변경 내용 추적 내부 테이블이 시간이 지남에 따라 점진적으로 증가한다는 것입니다. 제거 프로세스는 자동 정리 스레드에 의해 제어되지만 사전 정의 된 보존 기간 (기본값 2 일)을 기반으로 내부 디스크 테이블에서 오래된 데이터를 제거하는 역할을하지만 적절한 보존 기간을 다음으로 설정해야합니다. SQL Server Audit 솔루션에 사용할 수있는 변경 데이터를 유지합니다. 또한 SQL Server 2016에 추가 된 새로운 저장 프로 시저 를 활용하여 내부 SQL Server 변경 추적 테이블에 대한 수동 정리를 수행 할 수 있습니다.

SQL 변경 추적을 사용하여 SQL Server DML 변경 사항을 감사하는 방법을 살펴 보겠습니다.

SQL 변경 추적 활성화

감사 목적으로 데이터베이스 테이블에서 SQL Server 변경 추적을 사용하려면 내부 디스크 테이블에 대한 보존 기간을 제공하여 ALTER DATABASE T-SQL 문을 사용하여 데이터베이스 수준에서 사용하도록 설정해야합니다. 자동 정리 프로세스를 활성화하면 아래와 같이 보존 기간보다 오래된 내부 디스크 테이블의 데이터가 자동으로 삭제됩니다.

USE master

GO

ALTER DATABASE [CTAudit]

SET CHANGE_TRACKING = ON

(CHANGE_RETENTION = 2 DAYS, AUTO_CLEANUP = ON)

또한 아래와 같이 내부 디스크 테이블의 보존 기간을 지정하고 자동 정리 프로세스를 활성화 할 수있는 데이터베이스 속성 창의 변경 내용 추적 탭 에서 SQL Server Management Studio 도구를 사용하여 활성화 할 수도 있습니다.

데이터베이스 수준에서 SQL Server 변경 추적을 활성화 한 후에는 DML 변경 사항을 추적하고 감사 할 각 테이블에서이를 활성화해야합니다. 이는 아래의 ALTER TABLE T-SQL 문을 사용하여 수행 할 수 있습니다.

USE CTAudit

GO

ALTER TABLE Employee_Main

ENABLE CHANGE_TRACKING

WITH (TRACK_COLUMNS_UPDATED = ON)

기본 키가 정의되지 않은 데이터베이스 테이블에서 SQL 변경 내용 추적을 활성화하려고하면 ALTER TABLE 문이 실패하여 아래 오류 메시지와 같이 SQL 변경 내용 추적을 활성화하기 전에 테이블에 기본 키를 만들어야 함을 보여줍니다. :

테이블에 Primary Key 제약 조건을 추가하면 ALTER TABLE 문이 성공적으로 실행됩니다. 또한 아래와 같이 테이블 속성 창의 SQL 변경 내용 추적 탭에서 SQL Server Management Studio를 사용하여 SQL 변경 내용 추적을 활성화 할 수도 있습니다.

SQL 변경 내용 추적 비활성화

데이터베이스 테이블에서 SQL Server 변경 추적을 활성화해도 해당 테이블에서 CT가 비활성화되지 않는 한 실패 할 기본 키의 변경을 제외하고는 해당 테이블에서 DDL 변경을 수행 할 수 없습니다.

아래의 ALTER TABLE T-SQL 문을 사용하여 테이블 수준에서 변경 내용 추적을 비활성화 할 수 있습니다.

테이블 수준에서 비활성화 한 후에는 아래의 ALTER DATABASE T-SQL 문을 사용하여 데이터베이스 수준에서 변경 추적을 쉽게 비활성화 할 수 있습니다.

DML 변경 감사

INSERT 감사

SQL Server 데이터베이스 감사 목적으로 데이터베이스 테이블에서 SQL Server 변경 내용 추적을 사용하면 테이블 행에서 수행되는 모든 DML 변경 내용이 CT 내부 테이블에 기록됩니다. Employe_Main 테스트 테이블에 아래 INSERT 문을 수행한다고 가정합니다.

INSERT 문 이후 내부 테이블에 기록 된 변경 내용 추적 데이터를 가져 오려면 CHANGETABLE 시스템 함수 와 같은 변경 내용 추적 기능을 사용할 수 있습니다 . CHANGETABLE 함수는 지정된 버전 번호 이후에 추적 된 테이블에서 수행 된 모든 변경 사항을 반환합니다. 버전 번호 카운터는 변경된 각 행과 연결되며 추적 된 테이블에 변경 사항이있을 때마다 증가합니다. 아래 T-SQL 스크립트를 사용하여 변경 정보를 검색 할 수 있습니다.

SELECT * FROM CHANGETABLE

(CHANGES [Employee_Main],0) as CT ORDER BY SYS_CHANGE_VERSION

INSERT 문을 수행 한 후 CT 내부 테이블에서 반환 된 데이터에는 수행 된 DML 변경의 버전, DML 작업 유형 (이 경우 INSERT의 경우 I)이 표시됩니다. INSERT의 경우와 마지막으로 SQL Server 감사 테이블에 삽입 된 행의 기본 키 값은 아래와 같습니다.

삽입 된 전체 레코드를 얻으려면 아래 T-SQL 스크립트 에서처럼 기본 키 값을 기반으로 추적 된 소스 테이블과 CHANGETABLE 함수를 쉽게 조인 할 수 있습니다.

SELECT CT.SYS_CHANGE_VERSION,

  CT.SYS_CHANGE_OPERATION, EM.*

  FROM CHANGETABLE

(CHANGES [Employee_Main],0) as CT

JOIN [dbo].[Employee_Main] EM

ON CT.Emp_ID = EM.Emp_ID

ORDER BY SYS_CHANGE_VERSION

CT 함수와 추적 된 소스 테이블을 결합하여 반환 된 결과는 삽입 된 데이터에 대한 완전한 정보를 표시하며, 이는 아래와 같이 데이터베이스 테이블을 감사하는 데 유용 할 수 있습니다.

업데이트 감사

동일한 추적 테이블에서 아래 UPDATE 문이 수행되는 경우 :

그런 다음 추적 된 소스 테이블과 CHANGETABLE 함수를 결합하는 이전 쿼리를 실행하면 Emp_ID 값이 2 인 행의 변경 버전 번호가 증가하는 것을 볼 수 있습니다. 또한 해당 행에서 수행 된 변경의 마지막 버전 만 기록되고 이전 INSERT 문이 누락되고 마지막 업데이트 문이 유지됩니다.이 문은 내부적으로 이전 레코드를 삭제 한 다음 새 값으로 레코드를 삽입합니다. 아래 그림과 같이:

감사 삭제

아래 DELETE 문을 실행하여 추적 된 테이블에서 세 번째 행을 삭제한다고 가정합니다.

그런 다음 CHANGETABLE 함수를 추적 된 소스 테이블과 결합하는 동일한 쿼리를 실행하여 삭제 된 레코드 정보를 확인합니다. 아래에 표시된 것처럼 삭제 된 레코드 데이터가 추적 된 소스 테이블에 없기 때문에 표시되지 않습니다.

이전 쿼리에서 JOIN 유형을 LEFT OUTER JOIN으로 변경하면 CHANGETABLE 함수에서 삭제 된 레코드 정보가 검색되는 것을 볼 수 있습니다. 이 정보에는 변경 버전 번호와 변경 유형 (DELETE의 경우 D) 만 포함되며 아래에 표시된 것처럼 삭제 된 레코드에 대한 정보는 없습니다.

이전 결과에서 SQL Server 변경 추적을 제한된 SQL Server 데이터베이스 감사 솔루션으로 사용하여 CT 지원 테이블에서 DML 변경을 추적 할 수 있다는 것이 분명합니다. 이는 업데이트 또는 삭제 작업 이전의 값에 대한 기록 정보없이 수정 된 레코드에 대해 수행 된 마지막 변경 만 반환하기 때문입니다.

'Database > SQL Server' 카테고리의 다른 글

SQL Server 2016 - Temporal Table  (0) 2020.10.23
SQL Server 감사를위한 변경 데이터 캡처  (0) 2020.10.13
저장된 Plan Cache 확인 및 활용  (0) 2020.10.05
TempDB 물리적 파일 위치 변경[출처] TempDB 물리적 파일 위치 변경  (0) 2020.09.25
SQL Server TempDB 데이터베이스 및 래치 경합  (0) 2020.09.24

저장된 Plan Cache 확인 및 활용 

 

l  Version : SQL Server 2005, 2008, 2008R2, 2012 

 

SQL Server에서 요청된 쿼리는 컴파일 과정을 거쳐 실행 계획을 생성한다. 이때 요청된 쿼리에 따라 수 많은 계획이 생성될 수도 있고 기존의 계획이 재사용 될 수도 있다. 이번 포스트에서는 저장된 캐시 계획에 대한 활용방안을 살펴 보자. 

 

비슷한 방식으로 실행 계획을 저장하는 SQL Server의 내부 메모리 영역(플랜캐시 또는 프로시저 캐시라고도 한다)이 있다. SQL Server는 기존 계획이 내부에 존재하는지 먼저 확인한다. 그리고 기존의 계획을 발견하게 되면 요청된 쿼리에 대해 새로운 계획에 대한 컴파일 시간을 할애 하지 않아도 된다. 이렇게 기존의 계획을 재사용하면 일반적인 성능을 높일 수 있다. 

 

SQL Server에 저장되어 있는 플랜캐시를 확인하여 자주 사용하는 플랜 및 사용하지 않는 플랜을 분석 할 수가 있다. 플랜캐시는 시간이 지남에 따라 실행되는 모든 쿼리에 대한 자세한 내용을 저장한다. 우리는 주기적으로 캐시의 내용을 검사 할 수 있다. 또한 성능 문제를 조사하기 위해 플랜캐시를 확인 할 수 있다. 

 

[캐시 내부의 단일 실행 계획을 찾는 방법] 

한 번만 실행되는 쿼리문에 대해 저장된 쿼리 계획을 갖는 것은 큰 문제가 되지 않는다. 하지만 많은 단일 실행 계획이 있을 때 문제가 발생 한다. 이는 플랜캐시의 사용량 증가와 함께 CPU 리소스 사용의증가로 전체적인 성능저하가 발생한다.  

다음 스크립트는 플랜캐시에서 한번 사용된 계획을 나타낸다. 

SELECT 

        text, cp.objtype, cp.size_in_bytes 

FROM sys.dm_exec_cached_plans AS cp 

        CROSS APPLY sys.dm_exec_sql_text(cp.plan_handle) st 

WHERE cp.cacheobjtype = N'Compiled Plan' 

        AND cp.objtype IN(N'Adhoc', N'Prepared') 

        AND cp.usecounts = 1 

ORDER BY cp.size_in_bytes DESC 

OPTION (RECOMPILE); 

 

 

SQL Server2008은 플랜 캐시 사이즈의 증가를 최소화 하기 위해 임시워크로드(ad-hoc workload)에 대한 구성 옵션 최적화를 도입했다. 이 옵션은 첫 번째 실행에 대해서는 전체 계획을 저장하지 않는다. 단지 계획 스텁은 플랜캐시 내부의 적은 공간을 차지하도록 생성된다. 계획이 두 번 사용하면 다음에는 완전히 컴파일된 계획을 저장한다. 임시워크로드 최적화는 기본적으로 활성화 되어 있지 않다.  

 

위의 스크립트는 한 번만 사용되는 명령문을 결정하는데 매우 큰 도움이 된다. 그러나 이러한 단일 사용계획이 정말 문제가 있는 경우는 어떻게 알 수 있을까? 다음 스크립트를 사용하여 단일 사용 계획에 사용되는 메모리 양을 측정하고 전체 플랜캐시의 크기를 비교할 수 있다. 

SELECT 

        objtype AS [CacheType] 

        , count_big(*) AS [Total Plans] 

        , sum(cast(size_in_bytes as decimal(18,2)))/1024/1024 AS [Total MBs] 

        , avg(usecounts) AS [Avg Use Count] 

        , sum(cast((CASE WHEN usecounts = 1 THEN size_in_bytes ELSE 0 END) as decimal(18,2)))/1024/1024 AS [Total MBs - USE Count 1] 

        , sum(CASE WHEN usecounts = 1 THEN 1 ELSE 0 END) AS [Total Plans - USE Count 1] 

 FROM sys.dm_exec_cached_plans 

 GROUP BY objtype 

 ORDER BY [Total MBs - USE Count 1] DESC 

 

 

 

전체 플랜캐시에 비해 단일 사용 계획에 대한 메모리 소비의 권장된 사항은 없지만 단일 실행 계획이 전체 플랜캐시의 50%이상 사용할 경우 임시워크로드(ad-hoc workload) 옵션에 대한 최적화 사용을 검토해 보는 것이 좋다.  

 

 

[한 번 사용된 캐시 계획 삭제] 

한 번 사용된 플랜 캐시를 삭제하고 싶을 때에는 다음 스크립트를 사용한다. (Kimberly Tripp) 

l  http://www.sqlskills.com/blogs/kimberly/plan-cache-adhoc-workloads-and-clearing-the-single-use-plan-cache-bloat/ 

 

DECLARE @MB decimal(19,3) , @Count bigint, @StrMB nvarchar(20)  

 

 

SELECT 

         @MB = sum(cast((CASE WHEN usecounts = 1 AND objtype IN ('Adhoc', 'Prepared') THEN size_in_bytes ELSE 0 END) as decimal(12,2)))/1024/1024  

        ,@Count = sum(CASE WHEN usecounts = 1 AND objtype IN ('Adhoc', 'Prepared') THEN 1 ELSE 0 END) 

    ,@StrMB = convert(nvarchar(20), @MB) 

FROM sys.dm_exec_cached_plans  

 

 

IF @MB > 10 

BEGIN 

    DBCC FREESYSTEMCACHE('SQL Plans')  

    RAISERROR ('%s MB was allocated to single-use plan cache. Single-use plans have been cleared.', 10, 1, @StrMB) 

END 

ELSE 

BEGIN 

    RAISERROR ('Only %s MB is allocated to single-use plan cache – no need to clear cache now.', 10, 1, @StrMB) 

END 

go  

 

 

[최적화가 필요한 플랜캐시 찾기] 

다음 스크립트는 인덱스가 누락된 현재의 모든 계획을 찾을 수 있는 쿼리이다. 

SELECT 

        dec.usecounts, dec.refcounts, dec.objtype 

    ,dec.cacheobjtype, des.dbid, des.text      

    ,deq.query_plan 

FROM sys.dm_exec_cached_plans AS dec 

    CROSS APPLY sys.dm_exec_sql_text(dec.plan_handle) AS des 

    CROSS APPLY sys.dm_exec_query_plan(dec.plan_handle) AS deq 

WHERE 

deq.query_plan.exist(N'/ShowPlanXML/BatchSequence/Batch/Statements/StmtSimple/QueryPlan/MissingIndexes/MissingIndexGroup') <> 0 

ORDER BY dec.usecounts DESC 

 

 

다음 스크립트는 암시적 변환 경고를 이용한 플랜캐시를 찾는 방법이다. 암시적 변환은 데이터베이스에 정의된 데이터 유형과 쿼리에 사용된 유형의 데이터 타입 불일치를 나타낸다. 

;WITH XMLNAMESPACES(DEFAULT N'http://schemas.microsoft.com/sqlserver/2004/07/showplan') 

SELECT 

        cp.query_hash,cp.query_plan_hash, 

        ConvertIssue= operators.value('@ConvertIssue', 'nvarchar(250)'), 

        Expression= operators.value('@Expression', 'nvarchar(250)'), qp.query_plan 

FROM sys.dm_exec_query_stats cp 

        CROSS APPLY sys.dm_exec_query_plan(cp.plan_handle) qp 

        CROSS APPLY query_plan.nodes('//Warnings/PlanAffectingConvert') rel(operators) 

 

 

 

다음 스크립트는 캐시에 있는 각 계획의 내부 계획에 대한 결과를 반환 한다.  

;WITH XMLNAMESPACES(DEFAULT N'http://schemas.microsoft.com/sqlserver/2004/07/showplan') 

SELECT 

        cp.query_hash,cp.query_plan_hash, 

        PhysicalOperator= operators.value('@PhysicalOp','nvarchar(50)'), 

        LogicalOp= operators.value('@LogicalOp','nvarchar(50)'), 

        AvgRowSize= operators.value('@AvgRowSize','nvarchar(50)'), 

        EstimateCPU= operators.value('@EstimateCPU','nvarchar(50)'), 

        EstimateIO= operators.value('@EstimateIO','nvarchar(50)'), 

        EstimateRebinds= operators.value('@EstimateRebinds','nvarchar(50)'), 

        EstimateRewinds= operators.value('@EstimateRewinds','nvarchar(50)'), 

        EstimateRows= operators.value('@EstimateRows','nvarchar(50)'), 

        Parallel= operators.value('@Parallel','nvarchar(50)'), 

        NodeId= operators.value('@NodeId','nvarchar(50)'), 

        EstimatedTotalSubtreeCost= operators.value('@EstimatedTotalSubtreeCost','nvarchar(50)') 

FROM sys.dm_exec_query_stats cp 

        CROSS APPLY sys.dm_exec_query_plan(cp.plan_handle) qp 

        CROSS APPLY query_plan.nodes('//RelOp') rel(operators) 

 

 

 

 

[비슷한 계획을 찾는 방법] 

쿼리의 해시 함수 값은 비슷하지만 문자 값이 다를 경우 다른 계획이 생성 된다. 

 

다음 예시를 통해 살펴보자. Where 조건만 다르게 하여 2개의 쿼리를 실행 한다. 

SELECT P.FirstName, P.LastName 

 FROM Person.Person AS P 

 WHERE P.FirstName = 'Amanda' 

 GO 

 

SELECT P.FirstName, P.LastName 

 FROM Person.Person AS P 

 WHERE P.FirstName = 'Logan' 

 GO 

 

 

생성된 계획을 살펴보자. 

SELECT 

        st.text, qs.query_hash 

FROM sys.dm_exec_query_stats qs 

        CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) st 

 

 

 

쿼리 해시는 동일하나 서로 다른 문자 값인 것을 확인 할 수 있다. 

 

다음 스크립트를 이용하면 동일한 계획을 확인 할 수 있다. 

SELECT COUNT(*) AS [Count], query_stats.query_hash, 

    query_stats.statement_text AS [Text] 

FROM 

    (SELECT QS.*, 

    SUBSTRING(ST.text,(QS.statement_start_offset/2) + 1, 

    ((CASE statement_end_offset 

        WHEN -1 THEN DATALENGTH(ST.text) 

        ELSE QS.statement_end_offset END 

            - QS.statement_start_offset)/2) + 1) AS statement_text 

    FROM sys.dm_exec_query_stats AS QS 

    CROSS APPLY sys.dm_exec_sql_text(QS.sql_handle) as ST) as query_stats 

GROUP BY query_stats.query_hash, query_stats.statement_text 

ORDER BY 1 DESC 

 

 

 

위 결과를 바탕으로 유사한 쿼리 해시 값을 많이 가진 경우 대신에 사용되는 하나의 매개 변수가 있는 문을 작성하는 것이 좋다. 이것은 많은 계획을 하나의 계획으로 저장할 수 있다. 

 

하나의 매개 변수가 있는 쿼리를 작성하는 코드를 변경할 수 없는 경우에는 실행계획 재사용의 극대화를 위해 플랜 가이드를 사용하는 것이 좋다. 

 

 

SQL Server 캐싱된 계획은 SQL Server에서 실행되는 쿼리에 대한 전반적인 정보를 가지고 있다. 시스템의 속도가 저하될 경우 캐시된 데이터를 확인 할 수 있다. DBA는 자신이 운용하는 서버의 캐시를 주기적으로 확인하여 변경된 사항은 없는지 정상적으로 플랜을 사용하고 있는지 등을 파악하여 시스템 최적화를 이끌어 낼 수 있을 듯 하다. 

 

[참고자료] 

l  SQL Server Plan Cache: The Junk Drawer for Your Queries 

http://sqlmag.com/database-performance-tuning/sql-server-plan-cache-junk-drawer-your-queries 

 

l  Plan cache, adhoc workloads and clearing the single-use plan cache bloat 

http://www.sqlskills.com/blogs/kimberly/plan-cache-adhoc-workloads-and-clearing-the-single-use-plan-cache-bloat/ 

[출처] 저장된 Plan Cache 확인 및 활용 |작성자 SungWook Kang

'Database > SQL Server' 카테고리의 다른 글

SQL Server 감사를위한 변경 데이터 캡처  (0) 2020.10.13
SQL Server 변경 추적을 사용하여 SQL Server 감사 생성  (0) 2020.10.13
TempDB 물리적 파일 위치 변경[출처] TempDB 물리적 파일 위치 변경  (0) 2020.09.25
SQL Server TempDB 데이터베이스 및 래치 경합  (0) 2020.09.24
SQL Server의 Shrink TempDB 데이터베이스 개요  (0) 2020.09.24

--TEMPDB

--데이터베이스의 크기와 물리적인 배치가 시스템의 성능에 영향을 미칠 수 있다.

--따라서 성능을 위하여  tempdb를 확장하거나 이동하는 작업을 수행하는경우가종종발생한다.

--이러한 작업은 서비스 재시작을 필요로 하므로 점검시 진행하도록 한다. 

  

USE master

GO

 

--1. tempdb의논리파일이름확인

SELECT name, physical_name, state_desc

FROM sys.master_files

WHERE database_id = DB_ID(N'tempdb')

GO

 

--2. ALTER DATABASE 사용하여파일위치변경

ALTER DATABASE tempdb

MODIFY FILE(NAME = tempdev, FILENAME = 'd:\mssql\tempdb.mdf')

ALTER DATABASE tempdb

MODIFY FILE(NAME = templog, FILENAME = 'e:\mssql\templog.ldf')

GO

 

--3. Processor만큼파일분할및사이즈변경및파일사이즈, 증가옵션설정

ALTER DATABASE [tempdb] MODIFY FILE ( NAME = N'tempdev', SIZE = 20480KB , FILEGROWTH = 10240KB )

GO

ALTER DATABASE [tempdb] ADD FILE ( NAME = N'tempdev2', FILENAME = N'd:\mssql\tempdev2.ndf' , SIZE = 20480KB , FILEGROWTH = 10240KB )

GO

ALTER DATABASE [tempdb] ADD FILE ( NAME = N'tempdev3', FILENAME = N'd:\mssql\tempdev3.ndf' , SIZE = 20480KB , FILEGROWTH = 10240KB )

GO

ALTER DATABASE [tempdb] ADD FILE ( NAME = N'tempdev4', FILENAME = N'd:\mssql\tempdev4.ndf' , SIZE = 20480KB , FILEGROWTH = 10240KB )

GO

ALTER DATABASE [tempdb] MODIFY FILE ( NAME = N'templog', SIZE = 163840KB )

GO

 

--4.SQL Server 서비스 재시작.

 

--5.SQL Server 서비스가 시작된것을확인후 정사 이동 확인 

SELECT name, physical_name, state_desc 

FROM sys.master_files

WHERE database_id = DB_ID(N'tempdb')

GO

[출처] TempDB 물리적 파일 위치 변경|작성자 SungWook Kang

'Database > SQL Server' 카테고리의 다른 글

SQL Server 변경 추적을 사용하여 SQL Server 감사 생성  (0) 2020.10.13
저장된 Plan Cache 확인 및 활용  (0) 2020.10.05
SQL Server TempDB 데이터베이스 및 래치 경합  (0) 2020.09.24
SQL Server의 Shrink TempDB 데이터베이스 개요  (0) 2020.09.24
SQL Server 2019 메모리 최적화 TempDB 메타 데이터  (0) 2020.09.24

SQL Server에서 사용되는 TempDB 데이터베이스는 무엇입니까?

TempDB 데이터베이스는 SQL Server의 시스템 데이터베이스 중 하나이지만 다른 시스템 데이터베이스와 구별되는 다양한 고유 기능을 가지고 있습니다. 이 SQL Server TempDB 데이터베이스에 전역 및 로컬 임시 테이블이 만들어지고 이러한 테이블의 데이터는이 데이터베이스에 저장됩니다. 동시에이 데이터베이스 리소스에서 테이블 변수, 임시 저장 프로 시저 및 커서가 사용됩니다. 또한 TempdDB 리소스는 다음 기능에서도 사용됩니다.

  • 스냅 샷 격리 및 읽기 커밋 된 스냅 샷 격리
  • 온라인 인덱스 작업
  • MARS – (다중 활성 결과 집합)

SQL 엔진을 다시 시작하면 TempdDB 데이터베이스가 삭제되고 다시 생성됩니다. 우리는이 데이터베이스를 백업 할 수 없으며 복구 모델을 단순에서 다른 것으로 변경할 수 없습니다. 이 모든 것을 고려할 때 TempDB 데이터베이스 설정이 쿼리 성능에 직접적인 영향을 미친다고 말할 수 있습니다.

SQL Server의 래치는 무엇입니까?

SQL 버퍼 풀은 SQL Server 용으로 운영 체제에서 예약 한 메모리 공간이며 SQL 버퍼 캐시라고도합니다. SQL Server는 데이터 페이지를 읽거나 조작하기 위해 디스크에서 메모리로 데이터 페이지를 전송하고 특수 논리 에 따라 디스크로 다시 보냅니다 . 이 메커니즘의 주요 목적은 메모리가 항상 스토리지 시스템보다 빠르기 때문에 클라이언트에 더 빠른 성능을 제공하려는 것입니다. 이러한 맥락에서 우리는 버퍼 풀에서 데이터 페이지 일관성을 보장하는 메커니즘이 필요합니다. 래치는 SQL Server가 메모리에서 데이터 페이지의 일관성을 보장 할 수 있도록 메모리에 저장된 데이터 구조를 불일치 및 손상으로부터 보호하는 데 사용되는 동기화 개체입니다. 이 동기화 작업은 SQL Server에서 내부적으로 관리합니다.

TempDB 데이터베이스 메타 데이터 경합

TempDB 메타 데이터 경합은 임시 테이블을 생성하는 동안 많은 세션이 SQL Server TempDB의 시스템 테이블에 동시에 액세스하려고 할 때 발생합니다. 이러한 과중한 워크로드는 이러한 이유로 인해 이러한 시스템 테이블에서 대기 시간을 유발하고 쿼리 성능이 저하됩니다.

이제이 문제를 시뮬레이션하기 위해 TempDB에 가짜 워크로드를 생성합니다. SQLQueryStress 라는 구식이지만 좋은 도구 를 사용하여 TempDB 데이터베이스에 가짜 워크로드를 생성합니다.

먼저 다음 절차를 생성합니다. 이 저장 프로시 저는 임시 테이블을 만들고 sys.all_columns 테이블에서 임의의 20 개 행을 삽입합니다.

CREATE PROC ProcTest

AS

BEGIN

CREATE TABLE #t1

(

  c1 INT,

  c2 INT);

INSERT INTO #t1

       SELECT TOP 20 column_id,

                     system_type_id

       FROM sys.all_columns WITH(NOLOCK);

END

SQLQueryStress를 시작하고 다음 쿼리를 쿼리 패널에 붙여 넣습니다. 이 쿼리는 WHILE 루프에서 ProcTest 저장 프로 시저를 100 번 실행합니다.

DECLARE @i INT;

SET @i = 1;

WHILE @i <= 100

    BEGIN

        EXEC ProcTest;

        SET @i = @i + 1;

    END

우리는 설정합니다 반복의 수 100을 설정할 것 스레드 수 저장 프로 시저가 2500 번 실행되도록 25을.

데이터베이스 버튼 을 클릭하고 데이터베이스 연결 및 자격 증명 설정을 지정합니다.

GO 버튼을 클릭 하여 쿼리 실행을 시작합니다.

SQLQueryStress가 쿼리를 수행하는 동안 sp_WhoisActive를 실행 하고 결과를 분석합니다.

보시다시피 PAGELATCH_EX 대기 유형은 TempDB 데이터베이스의 wait_info 열에서 볼 수 있습니다. TempDB 데이터베이스의 경우 SQL Server 2019의 새로운 기능을 사용하여이 대기 시간을 극복 할 수 있습니다. 다음 섹션에서는이 기능에 대해 알아 봅니다.

메모리 최적화 TempDB 메타 데이터

메모리 최적화 TempDB 메타 데이터 기능을 활성화하면 일부 SQL Server TempDB 시스템 테이블을 비 지속적 메모리 최적화 테이블로 변환하여 TempDB의 시스템 테이블에 대한 대기 시간을 최소화합니다. 메모리 최적화 테이블은 짧은 지연 시간, 높은 처리량 및 가속화 된 응답 시간을 제공하므로이 기능은 이러한 성능 향상을 활용합니다.

다음 쿼리를 통해이 기능을 활성화 할 수 있습니다.

ALTER SERVER CONFIGURATION SET MEMORY_OPTIMIZED TEMPDB_METADATA = ON;

또는 다음 쿼리를 사용하여이 옵션을 활성화 할 수 있습니다.

EXEC sys.sp_configure N' show advanced options', 1;

RECONFIGURE;

EXEC sys.sp_configure N'tempdb metadata memory-optimized', 1;

RECONFIGURE;

다음 쿼리는이 기능의 상태를 감지하는 데 도움이됩니다.

SELECT

CASE  SERVERPROPERTY('IsTempdbMetadataMemoryOptimized')

WHEN 1 THEN 'Enable'

WHEN 0 THEN 'Disable'

END

AS 'Memory-Optimized TempDB Metadata Status'

메모리 최적화 TempDB 메타 데이터 기능을 활성화 한 후 SQL Server 엔진을 다시 시작해야합니다. SQL Server를 다시 시작한 후 다음 쿼리를 통해 메모리 최적화 테이블로 변환 된 테이블 목록을 볼 수 있습니다.

SELECT mem_table.[object_id], obj.name

  FROM tempdb.sys.all_objects AS obj

  INNER JOIN tempdb.sys.memory_optimized_tables_internal_attributes AS mem_table

  ON obj.[object_id] = mem_table.[object_id]

동일한 매개 변수를 사용하여 동일한 쿼리에 대해 SQLQueryStress를 다시 실행할 때. sp_WhoIsActive 출력이 변경되고 PAGELATCH_EX 대기 유형이 표시되지 않습니다 .

메모리 최적화 TempDB 메타 데이터 기능에는 몇 가지 제한 사항이 있으며 사용을 결정하기 전에 다음 제한 사항을 고려해야합니다.

  • 메모리 최적화 TempDB 메타 데이터가 활성화 된 경우 임시 테이블에 대해 열 저장소 인덱스를 만들 수 없습니다.

    CREATE TABLE #temp1 (val1 INT, val2 NVARCHAR(50));

    CREATE COLUMNSTORE INDEX indexcol1 ON #temp1(val2);

처음에 우리는 이름이 # temp1 인 로컬 임시 테이블을 만들었고 columnstore 인덱스를 만들려고 할 때 메모리 최적화 메타 데이터 기능이 활성화되어 있기 때문에 성공하지 못했습니다.

  • sp_estimate_data_compression_savings 기본 제공 프로시 저는 메모리 최적화 TempDB 메타 데이터가 활성화 된 경우 columnstore 인덱스를 포함하는 테이블에 대해 실행되지 않습니다.

    CREATE TABLE t1 (val1 INT, val2 NVARCHAR(50));

    CREATE COLUMNSTORE INDEX indexcol1 ON t1(val2);

    GO

    EXEC sp_estimate_data_compression_savings 'dbo', 't1', NULL, NULL, 'ROW' ;  

저장 프로 시저 인 sp_estimate_data_compression_savings 는 작업을 압축하기 전에 테이블에 대해 예상되는 압축 이득을 계산합니다. 그러나 메모리 최적화 TempDB 메타 데이터 옵션을 활성화 한 경우이 절차는 columnstore 인덱스를 포함하므로 t1 테이블에 대해 작동하지 않습니다.

TempDB 데이터베이스 할당 페이지 경합

데이터 페이지 는 데이터 를 저장하는 SQL Server의 기본 단위이며 데이터 페이지의 크기는 8KB입니다. 물리적으로 연속 된 8 개의 데이터 페이지는 익스텐트 라고 합니다. 할당 된 익스텐트에 대한 정보는 GAM (Global Allocation Map)에 의해 기록됩니다 . 혼합으로 사용되는 익스텐트에 대한 정보는 SGAM (Shared Global Allocation Map)에 의해 기록됩니다 . 페이지 여유 공간 (PFS) 은 익스텐트의 어느 페이지에서 사용 가능한 여유 공간을 기록합니다.

세션은 임시 테이블을 만들고 삭제할 때 SQL Server TempDB 할당 페이지를 업데이트해야합니다. 이 동시 연결 수가 증가하기 시작하면 한 번에 하나의 스레드 만 이러한 페이지를 변경할 수 있으므로 다른 스레드가이 페이지가 할당 된 리소스를 해제 할 때까지 기다려야하기 때문에 이러한 페이지 할당에 액세스하는 것이 더 어려워집니다. 이제이 시나리오를 시뮬레이션합니다.

  • SQLQueryStress를 시작하고 다음 쿼리를 쿼리 패널에 붙여 넣습니다.

    SELECT TOP 2500 *

    INTO #t1

    FROM sys.all_objects WITH(NOLOCK);

  • 우리는 설정합니다 반복의 수를 100 세트로 스레드 수 200 :

  • 데이터베이스 버튼 을 클릭하고 데이터베이스 자격 증명 및 기타 설정을 지정합니다.

  • GO 버튼을 클릭 하여 쿼리 실행을 시작합니다.

SQLQueryStress가 쿼리를 수행하는 동안 sp_WhoisActive를 실행하고 wait_info 열의 결과를 분석합니다.

EXEC sp_WhoIsActive

보시다시피 PAGELATCH_UP 대기 유형은 wait_info 열에서 볼 수 있습니다. TempDb 데이터베이스에 더 많은 데이터 파일을 추가하면이 문제가 최소화되며 Microsoft는 필요한 파일 수에 대한 공식을 권장합니다.

“논리 프로세서 수가 8 개 이하이면 논리 프로세서와 동일한 수의 데이터 파일을 사용하십시오. 논리 프로세서 수가 8 개보다 크면 8 개의 데이터 파일을 사용합니다. 경합이 계속되면 경합이 허용 가능한 수준으로 줄어들 때까지 논리 프로세서 수까지 4의 배수로 데이터 파일 수를 늘립니다.”

이 공식에 따르면 TempDB 데이터베이스의 파일 번호를 늘려이 문제를 최소화 할 수 있습니다.

'Database > SQL Server' 카테고리의 다른 글

저장된 Plan Cache 확인 및 활용  (0) 2020.10.05
TempDB 물리적 파일 위치 변경[출처] TempDB 물리적 파일 위치 변경  (0) 2020.09.25
SQL Server의 Shrink TempDB 데이터베이스 개요  (0) 2020.09.24
SQL Server 2019 메모리 최적화 TempDB 메타 데이터  (0) 2020.09.24
SQL Server 메모리 내 데이터베이스 내부 메모리 구조 모니터링  (0) 2020.09.24

TempDB에 대한 몇 가지 중요한 사항

  • TempDB는 글로벌 리소스 (연결된 모든 사용자가 사용할 수 있음) 시스템 데이터베이스입니다.
  • SQL Server는 SQL 서비스가 다시 시작될 때마다 TempDB 데이터베이스를 다시 만듭니다. 다시 시작하는 동안 모델 데이터베이스에서 MDF 및 LDF 사본을 가져옵니다. MDF 및 LDF 파일의 크기와 수는 미리 구성된 크기로 재설정됩니다.
  • SQL Server가 TempDB에서 복구를 수행하지 않고 이전 데이터가 손실 됨
  • TempDB 데이터베이스는 항상 단순 복구 모델이며 데이터베이스 백업을 수행 할 수 없습니다.
  • TempDB는 트랜잭션을 최소한으로 기록하기 때문에 트랜잭션을 롤백 할 수 없습니다.

TempDB 사용 요약

  • 일반적으로 중간 테이블을 준비하기 위해 로컬 임시 테이블 (# 명명 규칙) 및 전역 임시 테이블 (## 명명 규칙)을 만듭니다. SQL Server는 TempDB 데이터베이스에 이러한 임시 테이블을 만듭니다.
  • SORT_IN_TEMPDB = ON 절을 사용하여 TempDB에서 인덱스를 만들거나 다시 작성할 수 있습니다. SQL Server는 개체가 속한 데이터베이스 대신 TempDB에서 모든 정렬 계산을 수행합니다.
  • SQL Server는 COMMITTED SNAPSHOT 읽기 격리 수준에 TempDB를 사용합니다. SQL Server는 각 레코드에 대해 행 버전 관리를 사용합니다. 이전 버전은 행 버전 관리를 추적하기 위해 TempDB에서 추가로 14 바이트를 얻습니다.
  • 커서 작업 테이블, 스풀 작업, GROUP BY, ORDER BY, UNION, DBCC CHECKDB와 같은 중간 정렬 작업과 같은 내부 개체, 임시 대형 개체 저장소, 서비스 브로커 이벤트 알림
  • 다중 활성 결과 집합 (MultipleActiveResultSets = True 사용)에서 SQL Server는 버전 관리를 사용하고 TempDB에 저장합니다.

당신은이 기사를 통해 갈 수있는 구성, 운영하고, 임시 데이터베이스 SQL Server 시스템 데이터베이스의 제한 과 는 SQL 서버 임시 데이터베이스 데이터베이스를 모니터링하는 방법 임시 데이터베이스 데이터베이스에 대한 자세한 정보를 얻을 수 있습니다.

Shrink TempDB 활동 개요

SQL Server 2016부터 설치 마법사는 CPU 코어를 자동으로 감지하고 TempDB에 필요한 수의 데이터베이스 파일을 만듭니다. 또한 개별 파일의 최대 증가량을 64MB로 설정합니다.

다음 스크린 샷에서 데모 SQL 인스턴스에 대한 TempDB 구성을 볼 수 있습니다.

이 데모에서는 TempDB 데이터베이스에 대한 자동 증가를 비활성화합니다.

  • 참고 : 프로덕션 인스턴스에서는이 작업을 수행하지 마십시오. 데모 목적으로 만 테스트 인스턴스에서 수행하고 있습니다.

다음 쿼리를 실행하여 로컬 임시 테이블을 만들고 여기에 데이터를 삽입합니다.

CREATE TABLE #TempTable (col1 char(1000), col2 char(1000))

 

    SET NOCOUNT ON;

    DECLARE @i INT = 1

    

    BEGIN TRAN

     WHILE @i <= 150000

     BEGIN

      INSERT INTO #TempTable values ('A','B')

      SET @i += 1

     END

    

    COMMIT TRAN

    

    DROP TABLE #TempTable

다음과 같은 오류 메시지가 표시됩니다. SQL Server는 TempDB 로그 파일의 크기를 늘릴 수 없으며 트랜잭션을 수행 할 수 없습니다. 자동 증가를 비활성화하고 문제를 해결하기 위해 자동 증가를 활성화했다고 생각할 수 있습니다. 디스크 크기로 확장 된 TempDB 로그 파일과 같은 상황을 고려하고 로그 파일을 확장 할 수있는 디스크 여유 공간이 충분하지 않습니다.

각 데이터 파일에 대해 최대 크기가 20MB 인 데이터 파일에 대해 자동 증가를 활성화 해 보겠습니다. TempDB에 대한 로그 파일 증가가 활성화되어 있고 최대 파일 크기가 없음을 알 수 있습니다.

쿼리를 다시 실행하여 TempDB를 채우고 오류 메시지를 확인하겠습니다. SQL Server 주 파일 그룹에 여유 공간이 없습니다.

이 시점에서 SQL 인스턴스도 새로 고치려고하면 유사한 오류 메시지가 표시됩니다.

TempDB 데이터베이스를 축소하는 다양한 방법

상위 테이블 보고서 별 디스크 사용량을보고 TempDB에서 공간을 사용하는 개체가 무엇인지 확인할 수 있습니다. TempDB를 마우스 오른쪽 버튼으로 클릭하고 보고서-> 표준 보고서-> 상위 테이블 별 디스크 사용량으로 이동합니다.

이 스크린 샷에서 #TempTable이 TempDB의 공간을 사용하고 있음을 알 수 있습니다.

TempDB에있는 기존 개체를 삭제할 수 있으며 공간을 해제해야합니다. 특히 프로덕션 인스턴스에서는 매번 개체를 드롭 할 수 없습니다. 이로 인해 이전 작업을 모두 잃을 수 있습니다.

DBCC SHRINKFILE을 사용하여 TEMPDB 축소

DBCC SHRINKFILE 명령을 사용하여 TempDB의 데이터 또는 로그 파일을 축소 할 수 있습니다. 이 경우 SQL 서비스를 다시 시작할 필요가 없습니다.

DBCC SHRINKFILE(logical_filename, size_in_MB)

다음 쿼리를 실행하여 TempDB에서 개별 파일 크기를 가져옵니다.

SELECT name,

    file_id,

    type_desc,

    size * 8 / 1024 [TempdbSizeInMB]

FROM tempdb.sys.database_files

ORDER BY type_desc DESC,

      file_id;

DBCC SHRINKFILE 명령을 사용하여 TempDev 데이터 파일을 축소 해 보겠습니다.

DBCC SHRINKFILE(tempdev,10)

축소를 수행하고 다음 출력을 얻습니다.

추가로 파일 축소를 시도 할 수 있습니다.

DBCC SHRINKFILE(tempdev,0)

이런 식으로 개별 데이터 또는 로그 파일을 축소해야합니다.

DBCC SHRINKDATABASE 명령을 사용하여 TEMPDB 축소

DBCC SHRINKDATABASE 명령을 사용하여 TempDB 데이터베이스를 축소 할 수도 있습니다. 명령 구문은 다음과 같습니다.

DBCC SHRINKDATABASE(TempDB, ‘target_percentage_of_free_space’);

이 명령을 사용하여 TempDB를 축소하고 10 %의 여유 공간을 남겨 보겠습니다.

DBCC SHRINKDATABASE(tempdb, 10);

데이터베이스 수준 축소를 수행하고 다음 출력을 얻습니다.

tempdb.sys.database_files를 사용하여 데이터베이스의 데이터 및 로그 파일 크기를 확인할 수 있습니다.

ALTER DATABASE 명령을 사용하여 TempDB 크기 조정

Alter 명령을 사용하여 tempdb 파일의 크기를 조정할 수 있습니다. 데이터 및 로그의 초기 크기가 1GB라고 가정하면이 명령을 사용하여 더 낮은 수준으로 설정할 수 있습니다.

다음 명령은 TempDEV 및 TempLog 파일의 초기 크기를 100MB로 조정합니다.

USE master;

    GO

    ALTER DATABASE tempdb

    MODIFY FILE (NAME = tempdev, SIZE=100Mb);

    GO

    ALTER DATABASE tempdb

    MODIFY FILE (NAME = templog, SIZE=100Mb);

    GO

이 설정을 적용하려면 SQL 서비스를 다시 시작해야합니다.

SSMS를 사용하여 TempDB 축소

SSMS GUI 방법을 사용하여 TempDB도 축소 할 수 있습니다. TempDB를 마우스 오른쪽 버튼으로 클릭하고 작업으로 이동합니다. 작업 목록에서 축소를 클릭하고 데이터베이스 또는 파일을 선택할 수 있습니다.

데이터베이스 및 파일 옵션은 모두 앞에서 설명한 DBCC SHRINKDATABASE 및 DBCC SHRINKFILE 명령과 유사합니다.

SQL 서비스를 다시 시작하여 TempDB 데이터베이스 축소

TempDB 데이터베이스를 재활용하고 TempDB 데이터 또는 로그 파일 공간으로 인한 디스크 공간 관련 문제를 해결하는 마지막 옵션이어야합니다. 프로덕션 인스턴스에서는 SQL 서비스를 다시 시작하기위한 다운 타임을 찾기가 어렵습니다. 따라서 다른 옵션을 먼저 고려하고 마지막 옵션으로 수행해야합니다.

Tempdb를 축소하려고하는데 공간이 해제되지 않으면 어떻게됩니까?

때때로 TempDB 데이터베이스를 축소하려고하면 명령이 성공한 것으로 표시되지만 데이터베이스에 여유 공간이 표시되지 않습니다. 현재 워크로드에 따라 TempDB의 SQL Server에 필요한 활성 트랜잭션, 버전 관리 또는 개체 때문일 수 있습니다.

Microsoft 기사를 참조 하면 SQL Server가 유휴 모드 또는 단일 사용자 모드에있을 때 TempDB를 축소해야합니다.

다음 방법을 사용하여 TempDB를 축소 할 수 있습니다.

  • DBCC DROPCLEANBUFFERS 명령을 실행하여 캐시 된 인덱스 및 데이터 페이지를 플러시합니다.

    CHECKPOINT;

                GO

                DBCC DROPCLEANBUFFERS;

                GO

  • DBCC FREEPROCCACHE 명령을 실행하여 절차 캐시를 지 웁니다.

    DBCC FREEPROCCACHE;

                GO

이제 이전 방법을 사용하여 데이터베이스를 축소 해보십시오. DBCC SHRINKDATABASE 명령 대신 DBCC SHRINKFILE을 선호해야합니다.

단일 사용자 모드에서 TempDB 축소

TempDB 크기로 인해 SQL 서비스를 시작할 수없는 경우가 있으며 alter database 명령을 사용하여 초기 크기를 재설정해야합니다. alter database 명령을 실행하여 tempdb 데이터 파일의 크기를 조정했지만 실수로 디스크 여유 공간에 따라 실행 불가능한 파일의 초기 크기를 지정했다고 가정합니다. SQL 서비스를 다시 시작하려고했지만 드라이브에 TempDB 파일을 만들 수있는 충분한 여유 공간이 없기 때문에 시작할 수 없습니다.

최소 구성 모드에서 SQL 서비스를 시작하고 TempDB 파일의 크기를 조정해야합니다.

최소 구성 모드를 사용하려면 SQL 서비스가 중지 된 상태 여야합니다.

  • 관리자 권한으로 명령 프롬프트 열기

  • SQL Server Binary 폴더로 이동합니다. 내 SQL 인스턴스에서 BINN 폴더의 경로는 다음과 같습니다.

    C : \ Program Files \ Microsoft SQL Server \ MSSQL15.SQL2019CTP \ MSSQL \ Binn

    경로가 확실하지 않은 경우 SQL Server 구성 관리자 및 속성 페이지에서 SQL 서비스를 마우스 오른쪽 단추로 클릭하면 이진 경로를 볼 수 있습니다.

  • 관리 프롬프트에서 다음 명령을 실행합니다 (내 SQL2019CTP 인스턴스의 경우).

    sqlservr.exe -s SQL2019CTP -c -f

    최소 구성 모드에서 SQL Services를 시작하며 경고 메시지로도 볼 수 있습니다.

  • 다른 명령 프롬프트를 열고 SQLCMD를 사용하여 SQL 서버에 연결합니다.

    • 참고 : 현재 한 명의 관리자 만 SQL Server에 연결할 수 있습니다. 연결을 시도하면 다음 오류 메시지가 표시됩니다.

    이제 alter database 명령을 실행하여 TempDB의 크기를 조정할 수 있습니다. 각 tempdb 파일에 대해 alter database 명령을 실행해야합니다.

    USE master;

                    GO

                    ALTER DATABASE tempdb

                    MODIFY FILE (NAME = tempdev, SIZE=100Mb);

                    GO

                    ALTER DATABASE tempdb

                    MODIFY FILE (NAME = templog, SIZE=100Mb);

                    GO

  • 최소 구성 모드에서 SQL 서비스를 시작한 관리 명령 프롬프트를 반환하고 CTRL + C를 눌러 종료합니다.

  • SQL Server 구성 관리자를 사용하여 SQL 서비스를 시작하고 다음 쿼리를 사용하여 변경 사항을 확인합니다.

    SELECT

                    name, file_id, type_desc, size * 8 / 1024 [TempdbSizeInMB]

                    FROM sys.master_files

                    WHERE DB_NAME(database_id) = 'tempdb'

                    ORDER BY type_desc DESC, file_id

                    GO

    스크린 샷에서 TempDev 및 TempLog 파일의 초기 크기가 100MB임을 알 수 있습니다.

'Database > SQL Server' 카테고리의 다른 글

TempDB 물리적 파일 위치 변경[출처] TempDB 물리적 파일 위치 변경  (0) 2020.09.25
SQL Server TempDB 데이터베이스 및 래치 경합  (0) 2020.09.24
SQL Server 2019 메모리 최적화 TempDB 메타 데이터  (0) 2020.09.24
SQL Server 메모리 내 데이터베이스 내부 메모리 구조 모니터링  (0) 2020.09.24
SQL Server 메모리 내 데이터베이스 개체의 내부 데이터 구조를 모니터링하는 방법  (0) 2020.09.24
  1. 메모리 내 최적화 된 TempDB 메타 데이터의 세부 정보
  2. 메모리 내 최적화 된 TempDB 메타 데이터를 보여주기 위해 T-SQL 샘플 준비
  3. 메모리 최적화 TempDB 메타 데이터 기능을 활성화하는 방법을 논의합니다.
  4. OStress 유틸리티의 사용 설명
  5. OStress 명령을 사용하여 워크로드 구현
  6. tempdb 카탈로그 뷰에서 성능 및 리소스 경합 측정
  7. 한계에 대해 논의
  8. 그리고 더…

소개

인 메모리 최적화 기술은 TempDB 데이터베이스의 가장 많이 기다려온 리소스 경합 관리 중 하나에 그 공간을 활용했습니다. 메모리 최적화 TempDB 메타 데이터 경합은 이전 SQL Server 버전에서 실행되는 과중한 워크로드를 확장하는 데 병목 현상이 나타 났던 많은 과중한 워크로드에 대한 솔루션입니다. SQL Server 2019는 메모리에 최적화 된 데이터베이스  기능 세트에 새로운 기능인 "메모리 최적화 TempDB 메타 데이터"를 도입했습니다 . 리소스 경합을 대폭 단순화하고 효과적으로 관리하고 과도한 TempDB 워크로드를 처리하고 확장 할 수있는 유연성을 제공합니다.

시작하다

이 기사는 두 단계로 설명됩니다. 먼저 TempDB의 작동 방식에 대한 일반적인 시나리오를 살펴 보겠습니다. 모두 알고 있듯이 SQL Server에서 가장 일반적인 성능 문제는 TempDB 경합으로 간주됩니다. 임시 테이블을 생성하는 시나리오를 시뮬레이션하고 매번 임시 테이블이 생성되는 방식과 SQL Server가 메타 데이터 관리와 함께 리소스 할당을 유지하기위한 내부 압력을 구축하는 방법을 살펴볼 것입니다.

둘째, 임시 테이블 메타 데이터를 관리 할 때 시스템 테이블이 유지되는 방식을 보여주고 래치가없는 비 지속적 테이블로 이동하는 방법을 알아보기 위해 메모리 최적화 TempDB 의 새로운 기능을 살펴 보겠습니다.

T-SQL 스크립트 준비

이 섹션에서는 중첩 스크립트를 만드는 방법, OSTRESS를 사용하여 스트레스 테스트를 수행하는 방법, 페이지 래치 세부 정보를 찾는 방법, 경합을 측정하는 방법에 대해 알아 봅니다.

TempDB 데이터베이스에서 경합 및 워크로드를 생성하는 간단한 스크립트입니다. sys.sysobjects 시스템 뷰 에서 삽입 된 행을 사용하여 각 호출에서 #dummytable이 생성되는 것을 볼 수 있습니다 . OStress 유틸리티를 사용하여 SQL Server를 강조하는 방법 에 대한 Nikilesh Patel의 기사를 참조 할 수 있습니다 .

T-SQL 스크립트

SSMS를 시작하고 새 쿼리 창을 연 후 다음 T-SQL 스크립트를 실행하여 두 개의 프로 시저를 만듭니다.

스크립트 1 :

CREATE OR ALTER PROCEDURE dbo.SPROC_tempdbMemOptimzedtest_1 AS

BEGIN

   SET

      NOCOUNT

      ON;

CREATE TABLE #DummyTable ( ID BIGINT NOT NULL );

INSERT INTO

   #DummyTable

   SELECT

      T.RowNum

   FROM

      (

         SELECT

            TOP (1) ROW_NUMBER() OVER (

         ORDER BY

(

            SELECT

               NULL)) RowNum

            FROM

               sys.sysobjects

      )

      T;

END

스크립트 2 :

CREATE

OR ALTER PROCEDURE dbo.SPROC_tempdbMemOptimzedtest_2 AS

BEGIN

   DECLARE @id int = 0;

WHILE( @id < 10 )

BEGIN

   EXECUTE dbo.SPROC_tempdbMemOptimzedtest_1;

   SET

      @id = @id + 1;

END

END

OStress 유틸리티

OStress는 RML 유틸리티 패키지와 함께 번들로 제공되는 무료 Microsoft입니다. 데이터베이스에서 T-SQL 쿼리를 강조하거나 재생하는 데 사용할 수있는 확장 가능한 명령 줄 도구입니다. 여러 반복으로 스크립트를 실행하여 부하 증가 워크로드를 시뮬레이션하는 데 사용할 수있는 다양한 스위치를 나열합니다.

TempDB 데이터베이스에 대한 스트레스를 시뮬레이션하기 위해 OStress 유틸리티를로드하겠습니다. 이 섹션에서는 OStress 명령을 사용하여 배치 파일을 만드는 방법을 살펴 봅니다. 이렇게하면 호출을 매우 간단하게 유지할 수 있으며 배치 파일을 쉽게 변경할 수 있습니다.

이 예에서는 다음 내용 으로 stresstest.bat 파일을 만듭니다 .

cd C:\Program Files\Microsoft Corporation\RMLUtils\

  ostress.exe -Slocalhost -E -dMongoDB -Q'EXECUTE dbo.SPROC_tempdbMemOptimzedtest_2;' –n100 –r30

이제 배치 파일을 실행할 준비가되었습니다.

ostress.exe, '-S'에 사용되는 매개 변수는 서버 (이 경우 localhost)입니다. '–d'는 데이터베이스 용입니다. MongoDB는 저장 프로 시저를 생성 한 데이터베이스이고 '–Q'는 독립 실행 형 SQL 쿼리를 실행하기위한 것입니다.이 경우에는 30 회 반복을 동시에 실행하는 100 개의 세션으로 실행되는 반복 저장 프로 시저입니다. .

배치 파일을 실행하고 워크로드를 측정 해 보겠습니다.

다음 SQL을 실행하여 TempDB에서 경합을 확인하십시오.

SELECT

   req.session_id,

   req.wait_type,

   req.wait_resource,

   OBJECT_NAME(inf.[object_id], inf.database_id) as [object_name],

   req.blocking_session_id,

   req.command,

   SUBSTRING(txt.text,

   (

      req.statement_start_offset / 2

   )

    + 1,

   (

(

      CASE

         req.statement_end_offset

         WHEN

            - 1

         THEN

            DATALENGTH(txt.text)

         ELSE

            req.statement_end_offset

      END

      - req.statement_start_offset) / 2

   )

   + 1) AS statement_text, inf.database_id, inf.[file_id], inf.page_id, inf.[object_id], inf.index_id, inf.page_type_desc

FROM

   sys.dm_exec_requests AS req CROSS APPLY sys.dm_exec_sql_text(req.sql_handle) AS txt

   CROSS APPLY sys.fn_PageResCracker (req.page_resource) AS pgc

   CROSS APPLY sys.dm_db_page_info(pgc.[db_id], pgc.[file_id], pgc.page_id, 'DETAILED') AS inf

WHERE

   req.wait_type like '%page%'

다음 출력에서 ​​wait_type 'PAGELATCH_EX'를 볼 수 있으며 TempDB 데이터베이스의 EXECUTE 재귀 저장 프로시 저도 볼 수 있습니다.

다음 스 니펫에서 100 개의 사용자 연결 시뮬레이션으로 재귀 프로 시저 호출이 실행되고 30 개의 동시 실행이 67 초 소요되었음을 알 수 있습니다.

이 섹션에서는 메모리 최적화 TempDB 메타 데이터의 의미를 연구하고 이해합니다.

메모리 최적화 TempDB 메타 데이터 활성화

SQL Server에서 TempDB 기능을 사용하려면 두 단계가 필요하고 세 번째 단계는 선택 사항입니다.

  1. 서버 구성을 변경하려면 다음 T-SQL을 실행하십시오.

    ALTER SERVER CONFIGURATION SET MEMORY_OPTIMIZED TEMPDB_METADATA=ON;

  2. SQL Server 인스턴스에 반영 할 변경 사항에 참여하려면 SQL 서비스를 다시 시작하십시오.

    sp_configure 를 사용하여 다음 T-SQL을 사용하여 메모리 최적화 TempDB 메타 데이터 매개 변수를 설정할 수도 있습니다 .

    EXEC sys.sp_configure N'show advanced options', 1;

    RECONFIGURE WITH OVERRIDE;

    EXEC sys.sp_configure N'tempdb metadata memory-optimized', 1;

    RECONFIGURE WITH OVERRIDE;

    다음 스 니펫에서 TempDB 메타 데이터 메모리 최적화 매개 변수가 이미 활성화되어 있음을 확인할 수 있습니다.

  3. 서버 구성을 확인하려면 다음 T-SQL을 실행하십시오.

    SELECT SERVERPROPERTY('IsTempDBMetadataMemoryOptimized') AS IsTempDBMetadataMemoryOptimized;

    출력 "1"은 메모리 최적화 TempDB가 SQL Server 인스턴스에서 활성화되었음을 나타냅니다.

    sp_configure 및 sys.configuration 시스템보기를 사용하여 구성을 확인할 수도 있습니다.

    EXEC sp_configure 'tempdb metadata memory-optimized'

     

    select * From sys.configurations where name like '%tempdb%'

OStress 유틸리티를 사용하여 SQL Server를 강조하겠습니다. 이 섹션에서는 OStress 명령을 사용하여 적용된 과중한 워크로드를 볼 수 있습니다.

이 경우 앞서 언급 한 동일한 OStress 명령 유틸리티가 실행되고 (배치 파일이 다시 실행 됨) 출력이 아래 스 니펫에 캡처됩니다.

아래 T-SQL 스크립트는 현재 SQL Server 인스턴스에서 실행중인 쿼리를 찾는 데 사용됩니다. T-SQL에서는 페이지 할당 및 페이지 리소스 wait_types 세부 정보를 보는 데 사용되는 새로운 동적 관리 함수의 사용도 볼 수 있습니다.

SELECT

   req.session_id,

   req.wait_type,

   req.wait_resource,

   OBJECT_NAME(inf.[object_id], inf.database_id) as [object_name],

   req.blocking_session_id,

   req.command,

   SUBSTRING(txt.text,

   (

      req.statement_start_offset / 2

   )

    + 1,

   (

(

      CASE

         req.statement_end_offset

         WHEN

            - 1

         THEN

            DATALENGTH(txt.text)

         ELSE

            req.statement_end_offset

      END

      - req.statement_start_offset) / 2

   )

   + 1) AS statement_text, inf.database_id, inf.[file_id], inf.page_id, inf.[object_id], inf.index_id, inf.page_type_desc

FROM

   sys.dm_exec_requests AS req

   CROSS APPLY sys.dm_exec_sql_text(req.sql_handle) AS txt

   CROSS APPLY sys.fn_PageResCracker (req.page_resource) AS pgc

   CROSS APPLY sys.dm_db_page_info(pgc.[db_id], pgc.[file_id], pgc.page_id, 'DETAILED') AS inf

WHERE

   req.wait_type like '%page%'

다음 스 니펫에서 TempDB 테이블 관리에 경합이 없음을 알 수 있습니다.

다음 스 니펫에서 100 명의 사용자 연결과 30 개의 동시 실행으로 SP 실행을 완료하는 데 31 초 밖에 걸리지 않음을 알 수 있습니다.

MEMORY_OPTIMIZED TEMPDB_METADATA를 비활성화하려면 다음 T-SQL을 실행합니다.

ALTER SERVER CONFIGURATION SET MEMORY_OPTIMIZED TEMPDB_METADATA=OFF;

변경 사항을 적용하려면 SQL 서비스를 다시 시작해야합니다.

결론

이 기사에서는 새로운 메모리 최적화 TempDB 메타 데이터에 대해 설명했습니다. 또한 시스템 카탈로그가 래치없는 임시 테이블 메타 데이터 관리에 어떻게 관여하는지 살펴 보았습니다. SQL Server 2019는 인 메모리 최적화 기술과 함께 또 다른 확장 성 기능을 도입했습니다. TempDB 메타 데이터 경합은 오랫동안 병목 현상이었습니다. tempdb 메타 데이터 시스템 테이블을 관리하는 기존 방법은 래치가없는 비 지속성 메모리 최적화 테이블 사용으로 이동되었습니다.

이 확장 가능한 옵션을 통해 SQL Server는 tempdb 작업을 훨씬 더 나은 방식으로 처리 할 수 ​​있습니다. 이 기능을 활성화하기위한 제한 사항도 이해하고 있기를 바랍니다. 메모리 최적화 메타 데이터를 켜고 끄려면 SQL 서비스를 다시 시작해야합니다.

메모리 최적화 테이블에서 실행되는 SQL 쿼리는 SQL 힌트를 지원하지 않습니다. 잠금 및 격리 SQL 힌트를 사용하는 경우 TempDB 메타 데이터 카탈로그 뷰에 대해 실행되는 쿼리가 작동하지 않습니다.

메모리 최적화 tempdb 메타 데이터 테이블의 다른 제한은 격리입니다. 이는 단일 트랜잭션이 둘 이상의 데이터베이스에있는 메모리 최적화 테이블에 액세스 할 수 없음을 의미합니다. 트랜잭션 내에서 자체적으로 포함됩니다.

이 경우 동일한 트랜잭션에서 TempDB 시스템보기에 액세스하면 다음 오류가 발생합니다.

"메모리 최적화 테이블 또는 고유하게 컴파일 된 모듈에 액세스하는 사용자 트랜잭션은 둘 이상의 사용자 데이터베이스 또는 데이터베이스 모델 및 msdb에 액세스 할 수 없으며 마스터에 쓸 수 없습니다."

'Database > SQL Server' 카테고리의 다른 글

SQL Server TempDB 데이터베이스 및 래치 경합  (0) 2020.09.24
SQL Server의 Shrink TempDB 데이터베이스 개요  (0) 2020.09.24
SQL Server 메모리 내 데이터베이스 내부 메모리 구조 모니터링  (0) 2020.09.24
SQL Server 메모리 내 데이터베이스 개체의 내부 데이터 구조를 모니터링하는 방법  (0) 2020.09.24
SQL Server의 메모리 내 OLTP에 대한 간략한 개요  (0) 2020.09.24
  1. SMSS 표준 보고서를 사용하여 사용 가능한 리소스를 빠르게 확인
  2. T-SQL을 사용하여 SQL Server 메모리 내 데이터베이스를 모니터링하는 방법
  3. 메모리 소비자의 내부 구조를 자세히 설명합니다.
  4. DMF를 사용하여 SQL Server 내에서 공간 관리 추진
  5. SQL Server 내에서 메모리 내 컨테이너 모니터링
  6. 메모리 내 체크 포인트 모드의 세부 정보를 가져 오는 방법
  7. In-Memory 데이터베이스 내 사용 / 여유 공간 결정
  8. 그리고 더…

소개

기술이 발전하고 환경이 관계형 데이터베이스 트랜잭션을 비 휘발성 공간에서 휘발성 공간으로 전환함에 따라 데이터베이스 모니터링 및 유지 관리의 복잡성이 더욱 심해집니다 (때로는 지루함). 따라서 데이터베이스 모니터링 및 데이터베이스 메트릭 평가가 중요합니다. 모니터링 및 평가의 목적은 구현 및 출력을 체계적으로 추적하고 표준의 효과를 측정하여 변경이 필요한시기와시기를 정확히 결정하는 것입니다.

시작하기

SSMS 표준 보고서를 사용하여 SQL Server 메모리 내 개체에 대한 높은 수준의 세부 정보를 얻는 방법을 살펴 보겠습니다. 표준 SSMS 보고서 인 “메모리 최적화 개체 별 메모리 사용량”은 SQL Server 메모리 내 최적화 개체의 메모리 사용량 세부 정보를 보여줍니다.

메모리 최적화 객체 별 메모리 사용량 보고서를 보려면 다음과 같이하십시오.

  1. 에서  개체 탐색기 에서 SQL Server 데이터베이스 엔진의 인스턴스에 연결 한 다음 해당 인스턴스를 확장합니다.
  2. 데이터베이스를 마우스 오른쪽 단추로 클릭  한 다음 보고서 를 클릭합니다. 
  3. 메모리 최적화 개체 별 메모리 사용량 선택

메모리 최적화 개체 별 메모리 사용량의 세 섹션은 다음과 같습니다.

  1. 메모리 최적화 개체에 할당 된 총 메모리

    DMV sys.dm_db_xtp_table_memory_stats를 사용하여 메모리 최적화 개체에 할당 된 총 메모리를 파생 할 수 있습니다.

    아래 SQL은 SSMS 보고서에서도 생성 할 수있는 출력을 생성합니다.

    IF

    (

        SELECT COUNT(1)

        FROM sys.data_spaces

        WHERE type = 'FX'

    ) > 0

        BEGIN

            SELECT SUM([Total used Memory MB] + [Total Unused Memory MB] + [Index used Memory MB] + [Index Unused Memory MB]) [Total memory allocated to memory optimized objects MB]

            FROM

            (

                SELECT OBJECT_NAME(object_id) AS tblName,

                       CAST(memory_used_by_table_kb / 1024.00 AS DECIMAL(10, 2)) [Total used Memory MB],

                       CAST(memory_allocated_for_table_kb / 1024.00 AS DECIMAL(10, 2)) - CAST(memory_used_by_table_kb / 1024.00 AS DECIMAL(10, 2)) [Total Unused Memory MB],

                       CAST(memory_used_by_indexes_kb / 1024.00 AS DECIMAL(10, 2)) [Index used Memory MB],

                       CAST(memory_allocated_for_indexes_kb / 1024.00 AS DECIMAL(10, 2)) - CAST(memory_used_by_indexes_kb / 1024.00 AS DECIMAL(10, 2)) [Index Unused Memory MB]

                FROM sys.dm_db_xtp_table_memory_stats

            ) T;

        END;
     
  2. 메모리 최적화 개체 별 총 메모리 사용량, 테이블, 인덱스 및 시스템의 메모리 사용량을 보여주는 원형 차트.

  3. SQL Server 메모리 내 테이블의 메모리 사용량 세부 정보

    관련 DMV sys.dm_db_xtp_table_memory_stats 를 사용하여 메모리 최적화 테이블의 크기 조정 세부 정보를 그릴 수도 있습니다 .

    IF

    (

        SELECT COUNT(1)

        FROM sys.data_spaces

        WHERE type = 'FX'

    ) > 0

        BEGIN

            SELECT OBJECT_NAME(object_id) AS tblName,

                   CAST(memory_used_by_table_kb / 1024.00 AS DECIMAL(10, 2)) [Total used Memory MB],

                   CAST(memory_allocated_for_table_kb / 1024.00 AS DECIMAL(10, 2)) - CAST(memory_used_by_table_kb / 1024.00 AS DECIMAL(10, 2)) [Total Unused Memory MB],

                   CAST(memory_used_by_indexes_kb / 1024.00 AS DECIMAL(10, 2)) [Index used Memory MB],

                   CAST(memory_allocated_for_indexes_kb / 1024.00 AS DECIMAL(10, 2)) - CAST(memory_used_by_indexes_kb / 1024.00 AS DECIMAL(10, 2)) [Index Unused Memory MB]

            FROM sys.dm_db_xtp_table_memory_stats;

        END;
     

내부 시스템 구조 구성 요소에 의한 메모리 소비

시스템 할당자가 사용한 총 메모리를 보려면 다음 쿼리를 사용하십시오.

SELECT SUM(allocated_bytes) / (1024 * 1024) AS total_allocated_MB,

       SUM(used_bytes) / (1024 * 1024) AS total_used_MB

FROM sys.dm_xtp_system_memory_consumers;

 

다음으로 다양한 메모리 소비자 유형을 살펴 보겠습니다.

SELECT memory_consumer_type,

       memory_consumer_type_desc,

       allocated_bytes / 1024 [allocated_bytes_kb],

       used_bytes / 1024 [used_bytes_kb],

       allocation_count

FROM sys.dm_xtp_system_memory_consumers;

다음 출력에서 ​​몇 가지 메모리 LOOKASIDE 소비자가 있음을 알 수 있습니다.

참고 : lookaside 소비자는 MMU (메모리 관리 장치)의 일부이며 각 트랜잭션의 메모리 위치에 액세스하는 데 걸리는 시간을 줄이는 데 사용되는 버퍼 캐시 유형입니다.

SQL Server 메모리 내 최적화 된 테이블에 액세스 할 때 런타임에 메모리 사용량을 확인하려면 다음 쿼리를 사용하십시오.

SELECT type clerk_type,

       name,

       memory_node_id,

       pages_kb / 1024 pages_mb

FROM sys.dm_os_memory_clerks

WHERE type LIKE '%xtp%';

 

특성

데이터 형식

기술

이름

nvarchar (256)

이 메모리 담당자의 내부적으로 할당 된 이름을 지정합니다. 구성 요소에는 특정 유형의 여러 메모리 담당자가있을 수 있습니다. 구성 요소는 특정 이름을 사용하여 동일한 유형의 메모리 담당자를 식별하도록 선택할 수 있습니다. nullable이 아닌 열입니다.

memory_node_id

smallint

메모리 노드의 ID를 반환합니다. 또한 nullable이 아닌 필드입니다.

pages_kb

Bigint

적용 대상 : SQL Server 2012 ~ SQL Server 2017.

이 메모리 클러 크에 할당 된 페이지 메모리의 양 (KB)을 지정합니다. nullable 열이 아닙니다.

출력은 데이터베이스 ID 13이 메모리에서 173KB를 예약 함을 보여줍니다.

이제 sys.dm_os_memory_clerks를 사용하여 내부적으로 메모리를 사용하는 SQL Server의 다른 구성 요소가 무엇인지 살펴 보겠습니다. 그러면 SQL Server 내에서 메모리 클러 크의 활성 구성 요소에 대한 개요가 제공됩니다.

SELECT TOP (10) type MemoryClerkType,

                SUM(pages_kb) / 1024 AS SizeMB

FROM sys.dm_os_memory_clerks

GROUP BY type

ORDER BY 2 DESC;

 

SQL Server 메모리 내 최적화 된 검사 점 모드

오류 로그에서 SQL 인스턴스의 SQL Server 메모리 내 검사 점 모드에 대한 세부 정보를 얻으려면 SQL 인스턴스의 메모리 최적화 검사 점 모드를 나열하는 다음 쿼리를 실행합니다.

EXEC sys.xp_readerrorlog 0, 1, N'In-Memory OLTP initialized on';

출력은 세 가지 가능한 값 중 하나를 나열 할 수 있습니다.

  • 저가형 컴퓨터에서 초기화 된 메모리 내 OLTP
  • 표준 컴퓨터에서 초기화 된 메모리 내 OLTP
  • 고급 시스템에서 초기화 된 메모리 내 OLTP (대형 체크 포인트 모드)

앞서 언급 한 xp_readerrorlog 쿼리 는 SQL Server 오류 로그 파일을 읽습니다. 시작하는 동안 SQL Server는 시스템이 대형 체크 포인트를 사용하고 있는지 여부를 파악합니다. 다시 시작할 때마다 SQL Server가 시작될 때마다 시스템 구성 및 사양을 확인하고 SQL Server 메모리 내 OLTP 검사 점 파일을 읽을 수있는 방법도 빠르게 측정합니다. 따라서 쿼리를 실행하면 저가형 컴퓨터에서 초기화되었다는 텍스트 메시지가 표시됩니다. 그 이유는 시스템이 매우 적은 리소스로 구성되어 있기 때문입니다.

참고 : 대형 체크 포인트 모드를 사용하도록 시스템을 구성하기 위해 추적 플래그 9912가 활성화되었습니다. 어떤 경우 든 워크로드에 더 적합한 성능을 측정하는 것이 좋습니다.

출력에 고급 컴퓨터에서 초기화 된 SQL Server In-Memory OLTP가 표시되면 다음 필수 사항을 충족해야합니다.

  1. 서버에는 16 개 이상의 논리 프로세서가 있어야합니다.
  2. 128GB 이상의 메모리로 구성해야합니다.
  3. > 200MB / 초 IO 인 높은 트랜잭션 속도 시스템.

노트 :

  1. SQL Server 2016 CU1 이상에서는 대규모 검사 점 모드를 사용하려면 전역 추적 지연 9912를 사용하도록 설정해야합니다.
  2. 대형 체크 포인트는 높은 처리량과 높은 트랜잭션 속도 시스템을 위해 설계되었습니다.
  3. 앞서 언급 한 문 중 하나 또는 두 개가 참이면 출력에 "표준 컴퓨터에서 초기화 된 메모리 내 OLTP"가 표시됩니다.
  4. 세 가지 문이 모두 참이면 SQL 인스턴스는 "고급 컴퓨터에서 초기화 된 메모리 내 OLTP"로 구성됩니다.
  5. 세 문 중 어느 것도 양호한 상태가 아니면 "저사양 시스템에서 초기화 된 메모리 내 OLTP"출력을 반환합니다.

인스턴스 수준에서 XTPSupported를 확인하는 방법

다음 쿼리를 실행하여 인스턴스에 대한 유용한 정보를 볼 수 있습니다. 속성을 나열하는 내장 함수 인 SERVERPROPERTY를 사용합니다.

SELECT SERVERPROPERTY('IsXTPSupported') AS [IsXTPSupported]

 

SQL Server 메모리 내 컨테이너 모니터링

DMV sys.dm_db_xtp_checkpoint_files 는 데이터 파일과 델타 파일이라는 두 가지 유형의 파일을 사용하여 데이터베이스의 체크 포인트 파일 쌍에 대한 정보를 제공합니다. 데이터 파일은 삽입 된 레코드를 저장하고 델타는 제거 된 레코드를 저장합니다. 이러한 레코드는 가비지 수집 원칙을 사용하여 시간이 지남에 따라 병합됩니다. 세부 사항이있는 각 파일을 보려면 sys.dm_db_xtp_checkpoint_files 확장 저장소 프로 시저를 사용하십시오 .

sp_spaceused  – sp_spaceused는 이제 SQL Server 메모리 내 OLTP와 관련된 저장소 공간을 이해하는 데 사용할 수 있습니다. 관련 데이터를 가져 오기 위해 새 매개 변수 '@include_total_xtp_storage'가 저장 프로 시저에 추가되었습니다.

USE SQLShackInMemDB;

GO

EXEC sp_spaceused

     @updateusage = 'FALSE',

     @mode = 'ALL',

     @oneresultset = '1',

     @include_total_xtp_storage = '1';

GO

 

xtp_precreated, xtp_used 및 xtp_pending_truncation 은 체크 포인트 파일 쌍의 상태를 기반으로합니다. 이 포인터는 데이터베이스 백업의 시작 여부에 대한 정보를 제공합니다. 이것이 sp_spaceused 출력에서 ​​XTP 열이 파생되는 방식입니다.

'Database > SQL Server' 카테고리의 다른 글

SQL Server의 Shrink TempDB 데이터베이스 개요  (0) 2020.09.24
SQL Server 2019 메모리 최적화 TempDB 메타 데이터  (0) 2020.09.24
SQL Server 메모리 내 데이터베이스 개체의 내부 데이터 구조를 모니터링하는 방법  (0) 2020.09.24
SQL Server의 메모리 내 OLTP에 대한 간략한 개요  (0) 2020.09.24
SQL Server 접속 네트워크 프로토콜  (0) 2020.09.24

+ Recent posts

티스토리툴바