'Database' 카테고리의 글 목록 (4 Page)

특정 시점에 테이블의 데이터 값을 반환하는 테이블은 첫 번째 관계형 데이터베이스 이후로 우리와 함께 해왔지만 항상 특별한 쿼리와 제약 조건이 필요했고 제대로하기가 까다로울 수 있습니다. 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

-- borrowed from Erland Sommarskog

-- Link : http://www.sommarskog.se/query-plan-mysteries.html#dmvgettingplans

-- Remember that you are looking at the estimated plan so the actual no. of rows and actual executions wont be there ! <-- Important why a particular plan is bad.

DECLARE @dbname nvarchar(256),

@procname nvarchar(256)

SELECT @dbname = 'Northwind', -- Your DB name

@procname = 'dbo.List_orders_11' -- The SP that you want to get parameters for !

; WITH basedata AS (

SELECT qs.statement_start_offset/2 AS stmt_start,

qs.statement_end_offset/2 AS stmt_end,

est.encrypted AS isencrypted, est.text AS sqltext,

epa.value AS set_options, qp.query_plan,

charindex('<ParameterList>', qp.query_plan) + len('<ParameterList>')

AS paramstart,

charindex('</ParameterList>', qp.query_plan) AS paramend

FROM sys.dm_exec_query_stats qs

CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) est

CROSS APPLY sys.dm_exec_text_query_plan(qs.plan_handle,

qs.statement_start_offset,

qs.statement_end_offset) qp

CROSS APPLY sys.dm_exec_plan_attributes(qs.plan_handle) epa

WHERE est.objectid = object_id (@procname)

AND est.dbid = db_id(@dbname)

AND epa.attribute = 'set_options'

), next_level AS (

SELECT stmt_start, set_options, query_plan,

CASE WHEN isencrypted = 1 THEN '-- ENCRYPTED'

WHEN stmt_start >= 0

THEN substring(sqltext, stmt_start + 1,

CASE stmt_end

WHEN 0 THEN datalength(sqltext)

ELSE stmt_end - stmt_start + 1

END)

END AS Statement,

CASE WHEN paramend > paramstart

THEN CAST (substring(query_plan, paramstart,

paramend - paramstart) AS xml)

END AS params

FROM basedata

)

SELECT set_options AS [SET], n.stmt_start AS Pos, n.Statement,

CR.c.value('@Column', 'nvarchar(128)') AS Parameter,

CR.c.value('@ParameterCompiledValue', 'nvarchar(128)') AS [Sniffed Value],

CAST (query_plan AS xml) AS [Query plan]

FROM next_level n

CROSS APPLY n.params.nodes('ColumnReference') AS CR(c)

ORDER BY n.set_options, n.stmt_start, Parameter

'Database > Query' 카테고리의 다른 글

테이블 사이즈 (0)	2020.11.13
INDEX 상세 정보 조회 (0)	2020.11.02
DELETE 복원 프로시저 (0)	2020.10.20
EXEC와 동적(adhoc) 쿼리 (0)	2020.10.05
Find and Kill all the Blocked Process/Query (0)	2020.09.22

Create PROCEDURE Recover_Deleted_Data_Proc

@Database_Name NVARCHAR(MAX),

@SchemaName_n_TableName NVARCHAR(Max),

@Date_From DATETIME='1900/01/01',

@Date_To DATETIME ='9999/12/31'

AS

DECLARE @RowLogContents VARBINARY(8000)

DECLARE @TransactionID NVARCHAR(Max)

DECLARE @AllocUnitID BIGINT

DECLARE @AllocUnitName NVARCHAR(Max)

DECLARE @SQL NVARCHAR(Max)

DECLARE @Compatibility_Level INT

SELECT @Compatibility_Level=dtb.compatibility_level

FROM

master.sys.databases AS dtb WHERE dtb.name=@Database_Name

/*

IF ISNULL(@Compatibility_Level,0)<=80

BEGIN

RAISERROR('The compatibility level should be equal to or greater SQL SERVER 2005 (90)',16,1)

RETURN

END

*/

IF (SELECT COUNT(*) FROM INFORMATION_SCHEMA.TABLES WHERE [TABLE_SCHEMA]+'.'+[TABLE_NAME]=@SchemaName_n_TableName)=0

BEGIN

RAISERROR('Could not found the table in the defined database',16,1)

RETURN

END

DECLARE @bitTable TABLE

(

[ID] INT,

[Bitvalue] INT

)

--Create table to set the bit position of one byte.

INSERT INTO @bitTable

SELECT 0,2 UNION ALL

SELECT 1,2 UNION ALL

SELECT 2,4 UNION ALL

SELECT 3,8 UNION ALL

SELECT 4,16 UNION ALL

SELECT 5,32 UNION ALL

SELECT 6,64 UNION ALL

SELECT 7,128

--Create table to collect the row data.

DECLARE @DeletedRecords TABLE

(

[Row ID] INT IDENTITY(1,1),

[RowLogContents] VARBINARY(8000),

[AllocUnitID] BIGINT,

[Transaction ID] NVARCHAR(Max),

[FixedLengthData] SMALLINT,

[TotalNoOfCols] SMALLINT,

[NullBitMapLength] SMALLINT,

[NullBytes] VARBINARY(8000),

[TotalNoofVarCols] SMALLINT,

[ColumnOffsetArray] VARBINARY(8000),

[VarColumnStart] SMALLINT,

[Slot ID] INT,

[NullBitMap] VARCHAR(MAX)

)

--Create a common table expression to get all the row data plus how many bytes we have for each row.

;WITH RowData AS (

SELECT

[RowLog Contents 0] AS [RowLogContents]

,[AllocUnitID] AS [AllocUnitID]

,[Transaction ID] AS [Transaction ID]

--[Fixed Length Data] = Substring (RowLog content 0, Status Bit A+ Status Bit B + 1,2 bytes)

,CONVERT(SMALLINT, CONVERT(BINARY(2), REVERSE(SUBSTRING([RowLog Contents 0], 2 + 1, 2)))) AS [FixedLengthData] --@FixedLengthData

-- [TotalnoOfCols] = Substring (RowLog content 0, [Fixed Length Data] + 1,2 bytes)

,CONVERT(INT, CONVERT(BINARY(2), REVERSE(SUBSTRING([RowLog Contents 0], CONVERT(SMALLINT, CONVERT(BINARY(2)

,REVERSE(SUBSTRING([RowLog Contents 0], 2 + 1, 2)))) + 1, 2)))) as [TotalNoOfCols]

--[NullBitMapLength]=ceiling([Total No of Columns] /8.0)

,CONVERT(INT, ceiling(CONVERT(INT, CONVERT(BINARY(2), REVERSE(SUBSTRING([RowLog Contents 0], CONVERT(SMALLINT, CONVERT(BINARY(2)

,REVERSE(SUBSTRING([RowLog Contents 0], 2 + 1, 2)))) + 1, 2))))/8.0)) as [NullBitMapLength]

--[Null Bytes] = Substring (RowLog content 0, Status Bit A+ Status Bit B + [Fixed Length Data] +1, [NullBitMapLength] )

,SUBSTRING([RowLog Contents 0], CONVERT(SMALLINT, CONVERT(BINARY(2), REVERSE(SUBSTRING([RowLog Contents 0], 2 + 1, 2)))) + 3,

CONVERT(INT, ceiling(CONVERT(INT, CONVERT(BINARY(2), REVERSE(SUBSTRING([RowLog Contents 0], CONVERT(SMALLINT, CONVERT(BINARY(2)

,REVERSE(SUBSTRING([RowLog Contents 0], 2 + 1, 2)))) + 1, 2))))/8.0))) as [NullBytes]

--[TotalNoofVarCols] = Substring (RowLog content 0, Status Bit A+ Status Bit B + [Fixed Length Data] +1, [Null Bitmap length] + 2 )

,(CASE WHEN SUBSTRING([RowLog Contents 0], 1, 1) In (0x10,0x30,0x70) THEN

CONVERT(INT, CONVERT(BINARY(2), REVERSE(SUBSTRING([RowLog Contents 0],

CONVERT(SMALLINT, CONVERT(BINARY(2), REVERSE(SUBSTRING([RowLog Contents 0], 2 + 1, 2)))) + 3

+ CONVERT(INT, ceiling(CONVERT(INT, CONVERT(BINARY(2), REVERSE(SUBSTRING([RowLog Contents 0], CONVERT(SMALLINT, CONVERT(BINARY(2)

,REVERSE(SUBSTRING([RowLog Contents 0], 2 + 1, 2)))) + 1, 2))))/8.0)), 2)))) ELSE null END) AS [TotalNoofVarCols]

--[ColumnOffsetArray]= Substring (RowLog content 0, Status Bit A+ Status Bit B + [Fixed Length Data] +1, [Null Bitmap length] + 2 , [TotalNoofVarCols]*2 )

,(CASE WHEN SUBSTRING([RowLog Contents 0], 1, 1) In (0x10,0x30,0x70) THEN

SUBSTRING([RowLog Contents 0]

, CONVERT(SMALLINT, CONVERT(BINARY(2), REVERSE(SUBSTRING([RowLog Contents 0], 2 + 1, 2)))) + 3

+ CONVERT(INT, ceiling(CONVERT(INT, CONVERT(BINARY(2), REVERSE(SUBSTRING([RowLog Contents 0], CONVERT(SMALLINT, CONVERT(BINARY(2)

,REVERSE(SUBSTRING([RowLog Contents 0], 2 + 1, 2)))) + 1, 2))))/8.0)) + 2

, (CASE WHEN SUBSTRING([RowLog Contents 0], 1, 1) In (0x10,0x30,0x70) THEN

CONVERT(INT, CONVERT(BINARY(2), REVERSE(SUBSTRING([RowLog Contents 0],

CONVERT(SMALLINT, CONVERT(BINARY(2), REVERSE(SUBSTRING([RowLog Contents 0], 2 + 1, 2)))) + 3

+ CONVERT(INT, ceiling(CONVERT(INT, CONVERT(BINARY(2), REVERSE(SUBSTRING([RowLog Contents 0], CONVERT(SMALLINT, CONVERT(BINARY(2)

,REVERSE(SUBSTRING([RowLog Contents 0], 2 + 1, 2)))) + 1, 2))))/8.0)), 2)))) ELSE null END)

* 2) ELSE null END) AS [ColumnOffsetArray]

-- Variable column Start = Status Bit A+ Status Bit B + [Fixed Length Data] + [Null Bitmap length] + 2+([TotalNoofVarCols]*2)

,CASE WHEN SUBSTRING([RowLog Contents 0], 1, 1)In (0x10,0x30,0x70)

THEN (

CONVERT(SMALLINT, CONVERT(BINARY(2), REVERSE(SUBSTRING([RowLog Contents 0], 2 + 1, 2)))) + 4

+ CONVERT(INT, ceiling(CONVERT(INT, CONVERT(BINARY(2), REVERSE(SUBSTRING([RowLog Contents 0], CONVERT(SMALLINT, CONVERT(BINARY(2)

,REVERSE(SUBSTRING([RowLog Contents 0], 2 + 1, 2)))) + 1, 2))))/8.0))

+ ((CASE WHEN SUBSTRING([RowLog Contents 0], 1, 1) In (0x10,0x30,0x70) THEN

CONVERT(INT, CONVERT(BINARY(2), REVERSE(SUBSTRING([RowLog Contents 0],

CONVERT(SMALLINT, CONVERT(BINARY(2), REVERSE(SUBSTRING([RowLog Contents 0], 2 + 1, 2)))) + 3

+ CONVERT(INT, ceiling(CONVERT(INT, CONVERT(BINARY(2), REVERSE(SUBSTRING([RowLog Contents 0], CONVERT(SMALLINT, CONVERT(BINARY(2)

,REVERSE(SUBSTRING([RowLog Contents 0], 2 + 1, 2)))) + 1, 2))))/8.0)), 2)))) ELSE null END) * 2))

ELSE null End AS [VarColumnStart]

,[Slot ID]

FROM sys.fn_dblog(NULL, NULL)

WHERE

AllocUnitId IN

(SELECT [Allocation_unit_id] FROM sys.allocation_units allocunits

INNER JOIN sys.partitions partitions ON (allocunits.type IN (1, 3)

AND partitions.hobt_id = allocunits.container_id) OR (allocunits.type = 2

AND partitions.partition_id = allocunits.container_id)

WHERE object_id=object_ID('' + @SchemaName_n_TableName + ''))

AND Context IN ('LCX_MARK_AS_GHOST', 'LCX_HEAP') AND Operation in ('LOP_DELETE_ROWS')

And SUBSTRING([RowLog Contents 0], 1, 1)In (0x10,0x30,0x70)

/*Use this subquery to filter the date*/

AND [TRANSACTION ID] IN (SELECT DISTINCT [TRANSACTION ID] FROM sys.fn_dblog(NULL, NULL)

WHERE Context IN ('LCX_NULL') AND Operation in ('LOP_BEGIN_XACT')

And [Transaction Name] In ('DELETE','user_transaction')

And CONVERT(NVARCHAR(11),[Begin Time]) BETWEEN @Date_From AND @Date_To)),

--Use this technique to repeate the row till the no of bytes of the row.

N1 (n) AS (SELECT 1 UNION ALL SELECT 1),

N2 (n) AS (SELECT 1 FROM N1 AS X, N1 AS Y),

N3 (n) AS (SELECT 1 FROM N2 AS X, N2 AS Y),

N4 (n) AS (SELECT ROW_NUMBER() OVER(ORDER BY X.n)

FROM N3 AS X, N3 AS Y)

INSERT INTO @DeletedRecords

SELECT RowLogContents

,[AllocUnitID]

,[Transaction ID]

,[FixedLengthData]

,[TotalNoOfCols]

,[NullBitMapLength]

,[NullBytes]

,[TotalNoofVarCols]

,[ColumnOffsetArray]

,[VarColumnStart]

,[Slot ID]

---Get the Null value against each column (1 means null zero means not null)

,[NullBitMap]=(REPLACE(STUFF((SELECT ',' +

(CASE WHEN [ID]=0 THEN CONVERT(NVARCHAR(1),(SUBSTRING(NullBytes, n, 1) % 2)) ELSE CONVERT(NVARCHAR(1),((SUBSTRING(NullBytes, n, 1) / [Bitvalue]) % 2)) END) --as [nullBitMap]

FROM

N4 AS Nums

Join RowData AS C ON n<=NullBitMapLength

Cross Join @bitTable WHERE C.[RowLogContents]=D.[RowLogContents] ORDER BY [RowLogContents],n ASC FOR XML PATH('')),1,1,''),',',''))

FROM RowData D

IF (SELECT COUNT(*) FROM @DeletedRecords)=0

BEGIN

RAISERROR('There is no data in the log as per the search criteria',16,1)

RETURN

END

DECLARE @ColumnNameAndData TABLE

(

[Row ID] int,

[Rowlogcontents] varbinary(Max),

[NAME] sysname,

[nullbit] smallint,

[leaf_offset] smallint,

[length] smallint,

[system_type_id] tinyint,

[bitpos] tinyint,

[xprec] tinyint,

[xscale] tinyint,

[is_null] int,

[Column value Size]int,

[Column Length] int,

[hex_Value] varbinary(max),

[Slot ID] int,

[Update] int

)

--Create common table expression and join it with the rowdata table

-- to get each column details

/*This part is for variable data columns*/

--@RowLogContents,

--(col.columnOffValue - col.columnLength) + 1,

--col.columnLength

--)

INSERT INTO @ColumnNameAndData

SELECT

[Row ID],

Rowlogcontents,

NAME ,

cols.leaf_null_bit AS nullbit,

leaf_offset,

ISNULL(syscolumns.length, cols.max_length) AS [length],

cols.system_type_id,

cols.leaf_bit_position AS bitpos,

ISNULL(syscolumns.xprec, cols.precision) AS xprec,

ISNULL(syscolumns.xscale, cols.scale) AS xscale,

SUBSTRING([nullBitMap], cols.leaf_null_bit, 1) AS is_null,

(CASE WHEN leaf_offset<1 and SUBSTRING([nullBitMap], cols.leaf_null_bit, 1)=0

THEN

(Case When CONVERT(INT, CONVERT(BINARY(2), REVERSE (SUBSTRING ([ColumnOffsetArray], (2 * leaf_offset*-1) - 1, 2)))) >30000

THEN

CONVERT(INT, CONVERT(BINARY(2), REVERSE (SUBSTRING ([ColumnOffsetArray], (2 * leaf_offset*-1) - 1, 2)))) - POWER(2, 15)

ELSE

CONVERT(INT, CONVERT(BINARY(2), REVERSE (SUBSTRING ([ColumnOffsetArray], (2 * leaf_offset*-1) - 1, 2))))

END)

END) AS [Column value Size],

(CASE WHEN leaf_offset<1 and SUBSTRING([nullBitMap], cols.leaf_null_bit, 1)=0 THEN

(Case

When CONVERT(INT, CONVERT(BINARY(2), REVERSE (SUBSTRING ([ColumnOffsetArray], (2 * leaf_offset*-1) - 1, 2)))) >30000 And

ISNULL(NULLIF(CONVERT(INT, CONVERT(BINARY(2), REVERSE (SUBSTRING ([ColumnOffsetArray], (2 * ((leaf_offset*-1) - 1)) - 1, 2)))), 0), [varColumnStart])<30000

THEN (Case When [System_type_id]In (35,34,99) Then 16 else 24 end)

When CONVERT(INT, CONVERT(BINARY(2), REVERSE (SUBSTRING ([ColumnOffsetArray], (2 * leaf_offset*-1) - 1, 2)))) >30000 And

ISNULL(NULLIF(CONVERT(INT, CONVERT(BINARY(2), REVERSE (SUBSTRING ([ColumnOffsetArray], (2 * ((leaf_offset*-1) - 1)) - 1, 2)))), 0), [varColumnStart])>30000

THEN (Case When [System_type_id]In (35,34,99) Then 16 else 24 end) --24

When CONVERT(INT, CONVERT(BINARY(2), REVERSE (SUBSTRING ([ColumnOffsetArray], (2 * leaf_offset*-1) - 1, 2)))) <30000 And

ISNULL(NULLIF(CONVERT(INT, CONVERT(BINARY(2), REVERSE (SUBSTRING ([ColumnOffsetArray], (2 * ((leaf_offset*-1) - 1)) - 1, 2)))), 0), [varColumnStart])<30000

THEN (CONVERT(INT, CONVERT(BINARY(2), REVERSE (SUBSTRING ([ColumnOffsetArray], (2 * leaf_offset*-1) - 1, 2))))

- ISNULL(NULLIF(CONVERT(INT, CONVERT(BINARY(2), REVERSE (SUBSTRING ([ColumnOffsetArray], (2 * ((leaf_offset*-1) - 1)) - 1, 2)))), 0), [varColumnStart]))

When CONVERT(INT, CONVERT(BINARY(2), REVERSE (SUBSTRING ([ColumnOffsetArray], (2 * leaf_offset*-1) - 1, 2)))) <30000 And

ISNULL(NULLIF(CONVERT(INT, CONVERT(BINARY(2), REVERSE (SUBSTRING ([ColumnOffsetArray], (2 * ((leaf_offset*-1) - 1)) - 1, 2)))), 0), [varColumnStart])>30000

THEN POWER(2, 15) +CONVERT(INT, CONVERT(BINARY(2), REVERSE (SUBSTRING ([ColumnOffsetArray], (2 * leaf_offset*-1) - 1, 2))))

- ISNULL(NULLIF(CONVERT(INT, CONVERT(BINARY(2), REVERSE (SUBSTRING ([ColumnOffsetArray], (2 * ((leaf_offset*-1) - 1)) - 1, 2)))), 0), [varColumnStart])

END)

END) AS [Column Length]

,(CASE WHEN SUBSTRING([nullBitMap], cols.leaf_null_bit, 1)=1 THEN NULL ELSE

SUBSTRING

(

Rowlogcontents,

(

(Case When CONVERT(INT, CONVERT(BINARY(2), REVERSE (SUBSTRING ([ColumnOffsetArray], (2 * leaf_offset*-1) - 1, 2)))) >30000

THEN

CONVERT(INT, CONVERT(BINARY(2), REVERSE (SUBSTRING ([ColumnOffsetArray], (2 * leaf_offset*-1) - 1, 2)))) - POWER(2, 15)

ELSE

CONVERT(INT, CONVERT(BINARY(2), REVERSE (SUBSTRING ([ColumnOffsetArray], (2 * leaf_offset*-1) - 1, 2))))

END)

-

(Case When CONVERT(INT, CONVERT(BINARY(2), REVERSE (SUBSTRING ([ColumnOffsetArray], (2 * leaf_offset*-1) - 1, 2)))) >30000 And

ISNULL(NULLIF(CONVERT(INT, CONVERT(BINARY(2), REVERSE (SUBSTRING ([ColumnOffsetArray], (2 * ((leaf_offset*-1) - 1)) - 1, 2)))), 0), [varColumnStart])<30000

THEN (Case When [System_type_id]In (35,34,99) Then 16 else 24 end) --24

When CONVERT(INT, CONVERT(BINARY(2), REVERSE (SUBSTRING ([ColumnOffsetArray], (2 * leaf_offset*-1) - 1, 2)))) >30000 And

ISNULL(NULLIF(CONVERT(INT, CONVERT(BINARY(2), REVERSE (SUBSTRING ([ColumnOffsetArray], (2 * ((leaf_offset*-1) - 1)) - 1, 2)))), 0), [varColumnStart])>30000

THEN (Case When [System_type_id]In (35,34,99) Then 16 else 24 end) --24

When CONVERT(INT, CONVERT(BINARY(2), REVERSE (SUBSTRING ([ColumnOffsetArray], (2 * leaf_offset*-1) - 1, 2)))) <30000 And

ISNULL(NULLIF(CONVERT(INT, CONVERT(BINARY(2), REVERSE (SUBSTRING ([ColumnOffsetArray], (2 * ((leaf_offset*-1) - 1)) - 1, 2)))), 0), [varColumnStart])<30000

THEN CONVERT(INT, CONVERT(BINARY(2), REVERSE (SUBSTRING ([ColumnOffsetArray], (2 * leaf_offset*-1) - 1, 2))))

- ISNULL(NULLIF(CONVERT(INT, CONVERT(BINARY(2), REVERSE (SUBSTRING ([ColumnOffsetArray], (2 * ((leaf_offset*-1) - 1)) - 1, 2)))), 0), [varColumnStart])

When CONVERT(INT, CONVERT(BINARY(2), REVERSE (SUBSTRING ([ColumnOffsetArray], (2 * leaf_offset*-1) - 1, 2)))) <30000 And

ISNULL(NULLIF(CONVERT(INT, CONVERT(BINARY(2), REVERSE (SUBSTRING ([ColumnOffsetArray], (2 * ((leaf_offset*-1) - 1)) - 1, 2)))), 0), [varColumnStart])>30000

THEN POWER(2, 15) +CONVERT(INT, CONVERT(BINARY(2), REVERSE (SUBSTRING ([ColumnOffsetArray], (2 * leaf_offset*-1) - 1, 2))))

- ISNULL(NULLIF(CONVERT(INT, CONVERT(BINARY(2), REVERSE (SUBSTRING ([ColumnOffsetArray], (2 * ((leaf_offset*-1) - 1)) - 1, 2)))), 0), [varColumnStart])

END)

) + 1,

(Case When CONVERT(INT, CONVERT(BINARY(2), REVERSE (SUBSTRING ([ColumnOffsetArray], (2 * leaf_offset*-1) - 1, 2)))) >30000 And

ISNULL(NULLIF(CONVERT(INT, CONVERT(BINARY(2), REVERSE (SUBSTRING ([ColumnOffsetArray], (2 * ((leaf_offset*-1) - 1)) - 1, 2)))), 0), [varColumnStart])<30000

THEN (Case When [System_type_id] In (35,34,99) Then 16 else 24 end) --24

When CONVERT(INT, CONVERT(BINARY(2), REVERSE (SUBSTRING ([ColumnOffsetArray], (2 * leaf_offset*-1) - 1, 2)))) >30000 And

ISNULL(NULLIF(CONVERT(INT, CONVERT(BINARY(2), REVERSE (SUBSTRING ([ColumnOffsetArray], (2 * ((leaf_offset*-1) - 1)) - 1, 2)))), 0), [varColumnStart])>30000

THEN (Case When [System_type_id] In (35,34,99) Then 16 else 24 end) --24

When CONVERT(INT, CONVERT(BINARY(2), REVERSE (SUBSTRING ([ColumnOffsetArray], (2 * leaf_offset*-1) - 1, 2)))) <30000 And

ISNULL(NULLIF(CONVERT(INT, CONVERT(BINARY(2), REVERSE (SUBSTRING ([ColumnOffsetArray], (2 * ((leaf_offset*-1) - 1)) - 1, 2)))), 0), [varColumnStart])<30000

THEN ABS(CONVERT(INT, CONVERT(BINARY(2), REVERSE (SUBSTRING ([ColumnOffsetArray], (2 * leaf_offset*-1) - 1, 2))))

- ISNULL(NULLIF(CONVERT(INT, CONVERT(BINARY(2), REVERSE (SUBSTRING ([ColumnOffsetArray], (2 * ((leaf_offset*-1) - 1)) - 1, 2)))), 0), [varColumnStart]))

When CONVERT(INT, CONVERT(BINARY(2), REVERSE (SUBSTRING ([ColumnOffsetArray], (2 * leaf_offset*-1) - 1, 2)))) <30000 And

ISNULL(NULLIF(CONVERT(INT, CONVERT(BINARY(2), REVERSE (SUBSTRING ([ColumnOffsetArray], (2 * ((leaf_offset*-1) - 1)) - 1, 2)))), 0), [varColumnStart])>30000

THEN POWER(2, 15) +CONVERT(INT, CONVERT(BINARY(2), REVERSE (SUBSTRING ([ColumnOffsetArray], (2 * leaf_offset*-1) - 1, 2))))

- ISNULL(NULLIF(CONVERT(INT, CONVERT(BINARY(2), REVERSE (SUBSTRING ([ColumnOffsetArray], (2 * ((leaf_offset*-1) - 1)) - 1, 2)))), 0), [varColumnStart])

END)

)

END) AS hex_Value

,[Slot ID]

,0

FROM @DeletedRecords A

Inner Join sys.allocation_units allocunits On A.[AllocUnitId]=allocunits.[Allocation_Unit_Id]

INNER JOIN sys.partitions partitions ON (allocunits.type IN (1, 3)

AND partitions.hobt_id = allocunits.container_id) OR (allocunits.type = 2 AND partitions.partition_id = allocunits.container_id)

INNER JOIN sys.system_internals_partition_columns cols ON cols.partition_id = partitions.partition_id

LEFT OUTER JOIN syscolumns ON syscolumns.id = partitions.object_id AND syscolumns.colid = cols.partition_column_id

WHERE leaf_offset<0

UNION

/*This part is for fixed data columns*/

SELECT

[Row ID],

Rowlogcontents,

NAME ,

cols.leaf_null_bit AS nullbit,

leaf_offset,

ISNULL(syscolumns.length, cols.max_length) AS [length],

cols.system_type_id,

cols.leaf_bit_position AS bitpos,

ISNULL(syscolumns.xprec, cols.precision) AS xprec,

ISNULL(syscolumns.xscale, cols.scale) AS xscale,

SUBSTRING([nullBitMap], cols.leaf_null_bit, 1) AS is_null,

(SELECT TOP 1 ISNULL(SUM(CASE WHEN C.leaf_offset >1 THEN max_length ELSE 0 END),0) FROM

sys.system_internals_partition_columns C WHERE cols.partition_id =C.partition_id And C.leaf_null_bit<cols.leaf_null_bit)+5 AS [Column value Size],

syscolumns.length AS [Column Length]

,CASE WHEN SUBSTRING([nullBitMap], cols.leaf_null_bit, 1)=1 THEN NULL ELSE

SUBSTRING

(

Rowlogcontents,(SELECT TOP 1 ISNULL(SUM(CASE WHEN C.leaf_offset >1 And C.leaf_bit_position=0 THEN max_length ELSE 0 END),0) FROM

sys.system_internals_partition_columns C where cols.partition_id =C.partition_id And C.leaf_null_bit<cols.leaf_null_bit)+5

,syscolumns.length) END AS hex_Value

,[Slot ID]

,0

FROM @DeletedRecords A

Inner Join sys.allocation_units allocunits ON A.[AllocUnitId]=allocunits.[Allocation_Unit_Id]

INNER JOIN sys.partitions partitions ON (allocunits.type IN (1, 3)

AND partitions.hobt_id = allocunits.container_id) OR (allocunits.type = 2 AND partitions.partition_id = allocunits.container_id)

INNER JOIN sys.system_internals_partition_columns cols ON cols.partition_id = partitions.partition_id

LEFT OUTER JOIN syscolumns ON syscolumns.id = partitions.object_id AND syscolumns.colid = cols.partition_column_id

WHERE leaf_offset>0

Order By nullbit

Declare @BitColumnByte as int

Select @BitColumnByte=CONVERT(INT, ceiling( Count(*)/8.0)) from @ColumnNameAndData Where [System_Type_id]=104

;With N1 (n) AS (SELECT 1 UNION ALL SELECT 1),

N2 (n) AS (SELECT 1 FROM N1 AS X, N1 AS Y),

N3 (n) AS (SELECT 1 FROM N2 AS X, N2 AS Y),

N4 (n) AS (SELECT ROW_NUMBER() OVER(ORDER BY X.n)

FROM N3 AS X, N3 AS Y),

CTE As(

Select RowLogContents,[nullbit]

,[BitMap]=Convert(varbinary(1),Convert(int,Substring((REPLACE(STUFF((SELECT ',' +

(CASE WHEN [ID]=0 THEN CONVERT(NVARCHAR(1),(SUBSTRING(hex_Value, n, 1) % 2)) ELSE CONVERT(NVARCHAR(1),((SUBSTRING(hex_Value, n, 1) / [Bitvalue]) % 2)) END) --as [nullBitMap]

from N4 AS Nums

Join @ColumnNameAndData AS C ON n<=@BitColumnByte And [System_Type_id]=104 And bitpos=0

Cross Join @bitTable WHERE C.[RowLogContents]=D.[RowLogContents] ORDER BY [RowLogContents],n ASC FOR XML PATH('')),1,1,''),',','')),bitpos+1,1)))

FROM @ColumnNameAndData D Where [System_Type_id]=104)

Update A Set [hex_Value]=[BitMap]

from @ColumnNameAndData A

Inner Join CTE B On A.[RowLogContents]=B.[RowLogContents]

And A.[nullbit]=B.[nullbit]

/**************Check for BLOB DATA TYPES******************************/

DECLARE @Fileid INT

DECLARE @Pageid INT

DECLARE @Slotid INT

DECLARE @CurrentLSN INT

DECLARE @LinkID INT

DECLARE @Context VARCHAR(50)

DECLARE @ConsolidatedPageID VARCHAR(MAX)

DECLARE @LCX_TEXT_MIX VARBINARY(MAX)

declare @temppagedata table

(

[ParentObject] sysname,

[Object] sysname,

[Field] sysname,

[Value] sysname)

declare @pagedata table

(

[Page ID] sysname,

[File IDS] int,

[Page IDS] int,

[AllocUnitId] bigint,

[ParentObject] sysname,

[Object] sysname,

[Field] sysname,

[Value] sysname)

DECLARE @ModifiedRawData TABLE

(

[ID] INT IDENTITY(1,1),

[PAGE ID] VARCHAR(MAX),

[FILE IDS] INT,

[PAGE IDS] INT,

[Slot ID] INT,

[AllocUnitId] BIGINT,

[RowLog Contents 0_var] VARCHAR(Max),

[RowLog Length] VARCHAR(50),

[RowLog Len] INT,

[RowLog Contents 0] VARBINARY(Max),

[Link ID] INT default (0),

[Update] INT

)

DECLARE Page_Data_Cursor CURSOR FOR

/*We need to filter LOP_MODIFY_ROW,LOP_MODIFY_COLUMNS from log for deleted records of BLOB data type& Get its Slot No, Page ID & AllocUnit ID*/

SELECT LTRIM(RTRIM(Replace([Description],'Deallocated',''))) AS [PAGE ID]

,[Slot ID],[AllocUnitId],NULL AS [RowLog Contents 0],NULL AS [RowLog Contents 0],Context

FROM sys.fn_dblog(NULL, NULL)

WHERE

AllocUnitId IN

(SELECT [Allocation_unit_id] FROM sys.allocation_units allocunits

INNER JOIN sys.partitions partitions ON (allocunits.type IN (1, 3)

AND partitions.hobt_id = allocunits.container_id) OR (allocunits.type = 2

AND partitions.partition_id = allocunits.container_id)

WHERE object_id=object_ID('' + @SchemaName_n_TableName + ''))

AND Operation IN ('LOP_MODIFY_ROW') AND [Context] IN ('LCX_PFS')

AND Description Like '%Deallocated%'

/*Use this subquery to filter the date*/

AND [TRANSACTION ID] IN (SELECT DISTINCT [TRANSACTION ID] FROM sys.fn_dblog(NULL, NULL)

WHERE Context IN ('LCX_NULL') AND Operation in ('LOP_BEGIN_XACT')

AND [Transaction Name]='DELETE'

AND CONVERT(NVARCHAR(11),[Begin Time]) BETWEEN @Date_From AND @Date_To)

GROUP BY [Description],[Slot ID],[AllocUnitId],Context

UNION

SELECT [PAGE ID],[Slot ID],[AllocUnitId]

,Substring([RowLog Contents 0],15,LEN([RowLog Contents 0])) AS [RowLog Contents 0]

,CONVERT(INT,Substring([RowLog Contents 0],7,2)),Context --,CAST(RIGHT([Current LSN],4) AS INT) AS [Current LSN]

FROM sys.fn_dblog(NULL, NULL)

WHERE

AllocUnitId IN

(SELECT [Allocation_unit_id] FROM sys.allocation_units allocunits

INNER JOIN sys.partitions partitions ON (allocunits.type IN (1, 3)

AND partitions.hobt_id = allocunits.container_id) OR (allocunits.type = 2

AND partitions.partition_id = allocunits.container_id)

WHERE object_id=object_ID('' + @SchemaName_n_TableName + ''))

AND Context IN ('LCX_TEXT_MIX') AND Operation in ('LOP_DELETE_ROWS')

/*Use this subquery to filter the date*/

AND [TRANSACTION ID] IN (SELECT DISTINCT [TRANSACTION ID] FROM sys.fn_dblog(NULL, NULL)

WHERE Context IN ('LCX_NULL') AND Operation in ('LOP_BEGIN_XACT')

And [Transaction Name]='DELETE'

And CONVERT(NVARCHAR(11),[Begin Time]) BETWEEN @Date_From AND @Date_To)

/****************************************/

OPEN Page_Data_Cursor

FETCH NEXT FROM Page_Data_Cursor INTO @ConsolidatedPageID, @Slotid,@AllocUnitID,@LCX_TEXT_MIX,@LinkID,@Context

WHILE @@FETCH_STATUS = 0

BEGIN

DECLARE @hex_pageid AS VARCHAR(Max)

/*Page ID contains File Number and page number It looks like 0001:00000130.

In this example 0001 is file Number & 00000130 is Page Number & These numbers are in Hex format*/

SET @Fileid=SUBSTRING(@ConsolidatedPageID,0,CHARINDEX(':',@ConsolidatedPageID)) -- Seperate File ID from Page ID

SET @hex_pageid ='0x'+ SUBSTRING(@ConsolidatedPageID,CHARINDEX(':',@ConsolidatedPageID)+1,Len(@ConsolidatedPageID)) ---Seperate the page ID

SELECT @Pageid=Convert(INT,cast('' AS XML).value('xs:hexBinary(substring(sql:variable("@hex_pageid"),sql:column("t.pos")) )', 'varbinary(max)')) -- Convert Page ID from hex to integer

FROM (SELECT CASE substring(@hex_pageid, 1, 2) WHEN '0x' THEN 3 ELSE 0 END) AS t(pos)

IF @Context='LCX_PFS'

BEGIN

DELETE @temppagedata

INSERT INTO @temppagedata EXEC( 'DBCC PAGE(' + @DataBase_Name + ', ' + @fileid + ', ' + @pageid + ', 1) with tableresults,no_infomsgs;');

INSERT INTO @pagedata SELECT @ConsolidatedPageID,@fileid,@pageid,@AllocUnitID,[ParentObject],[Object],[Field] ,[Value] FROM @temppagedata

END

ELSE IF @Context='LCX_TEXT_MIX'

BEGIN

INSERT INTO @ModifiedRawData SELECT @ConsolidatedPageID,@fileid,@pageid,@Slotid,@AllocUnitID,NULL,0,CONVERT(INT,CONVERT(VARBINARY,REVERSE(SUBSTRING(@LCX_TEXT_MIX,11,2)))),@LCX_TEXT_MIX,@LinkID,0

END

FETCH NEXT FROM Page_Data_Cursor INTO @ConsolidatedPageID, @Slotid,@AllocUnitID,@LCX_TEXT_MIX,@LinkID,@Context

END

CLOSE Page_Data_Cursor

DEALLOCATE Page_Data_Cursor

DECLARE @Newhexstring VARCHAR(MAX);

--The data is in multiple rows in the page, so we need to convert it into one row as a single hex value.

--This hex value is in string format

INSERT INTO @ModifiedRawData ([PAGE ID],[FILE IDS],[PAGE IDS],[Slot ID],[AllocUnitId]

,[RowLog Contents 0_var]

, [RowLog Length])

SELECT [Page ID],[FILE IDS],[PAGE IDS],Substring([ParentObject],CHARINDEX('Slot', [ParentObject])+4, (CHARINDEX('Offset', [ParentObject])-(CHARINDEX('Slot', [ParentObject])+4))-2 ) as [Slot ID]

,[AllocUnitId]

,Substring((

SELECT

REPLACE(STUFF((SELECT REPLACE(SUBSTRING([Value],CHARINDEX(':',[Value])+1,CHARINDEX('†',[Value])-CHARINDEX(':',[Value])),'†','')

FROM @pagedata C WHERE B.[Page ID]= C.[Page ID] And Substring(B.[ParentObject],CHARINDEX('Slot', B.[ParentObject])+4, (CHARINDEX('Offset', B.[ParentObject])-(CHARINDEX('Slot', B.[ParentObject])+4)) )=Substring(C.[ParentObject],CHARINDEX('Slot', C.[ParentObject])+4, (CHARINDEX('Offset', C.[ParentObject])-(CHARINDEX('Slot', C.[ParentObject])+4)) ) And

[Object] Like '%Memory Dump%' Order By '0x'+ LEFT([Value],CHARINDEX(':',[Value])-1)

FOR XML PATH('') ),1,1,'') ,' ','')

),1,20000) AS [Value]

,

Substring((

SELECT '0x' +REPLACE(STUFF((SELECT REPLACE(SUBSTRING([Value],CHARINDEX(':',[Value])+1,CHARINDEX('†',[Value])-CHARINDEX(':',[Value])),'†','')

FROM @pagedata C WHERE B.[Page ID]= C.[Page ID] And Substring(B.[ParentObject],CHARINDEX('Slot', B.[ParentObject])+4, (CHARINDEX('Offset', B.[ParentObject])-(CHARINDEX('Slot', B.[ParentObject])+4)) )=Substring(C.[ParentObject],CHARINDEX('Slot', C.[ParentObject])+4, (CHARINDEX('Offset', C.[ParentObject])-(CHARINDEX('Slot', C.[ParentObject])+4)) ) And

[Object] Like '%Memory Dump%' Order By '0x'+ LEFT([Value],CHARINDEX(':',[Value])-1)

FOR XML PATH('') ),1,1,'') ,' ','')

),7,4) AS [Length]

From @pagedata B

Where [Object] Like '%Memory Dump%'

Group By [Page ID],[FILE IDS],[PAGE IDS],[ParentObject],[AllocUnitId]--,[Current LSN]

Order By [Slot ID]

UPDATE @ModifiedRawData SET [RowLog Len] = CONVERT(VARBINARY(8000),REVERSE(cast('' AS XML).value('xs:hexBinary(substring(sql:column("[RowLog Length]"),0))', 'varbinary(Max)')))

FROM @ModifiedRawData Where [LINK ID]=0

UPDATE @ModifiedRawData SET [RowLog Contents 0] =cast('' AS XML).value('xs:hexBinary(substring(sql:column("[RowLog Contents 0_var]"),0))', 'varbinary(Max)')

FROM @ModifiedRawData Where [LINK ID]=0

Update B Set B.[RowLog Contents 0] =

(CASE WHEN A.[RowLog Contents 0] IS NOT NULL AND C.[RowLog Contents 0] IS NOT NULL THEN A.[RowLog Contents 0]+C.[RowLog Contents 0]

WHEN A.[RowLog Contents 0] IS NULL AND C.[RowLog Contents 0] IS NOT NULL THEN C.[RowLog Contents 0]

WHEN A.[RowLog Contents 0] IS NOT NULL AND C.[RowLog Contents 0] IS NULL THEN A.[RowLog Contents 0]

END)

,B.[Update]=ISNULL(B.[Update],0)+1

from @ModifiedRawData B

LEFT Join @ModifiedRawData A On A.[Page IDS]=Convert(int,Convert(Varbinary(Max),Reverse(Substring(B.[RowLog Contents 0],15+14,2))))

And A.[File IDS]=Convert(int,Convert(Varbinary(Max),Reverse(Substring(B.[RowLog Contents 0],19+14,2))))

And A.[Link ID]=B.[Link ID]

LEFT Join @ModifiedRawData C On C.[Page IDS]=Convert(int,Convert(Varbinary(Max),Reverse(Substring(B.[RowLog Contents 0],27+14,2))))

And C.[File IDS]=Convert(int,Convert(Varbinary(Max),Reverse(Substring(B.[RowLog Contents 0],31+14,2))))

And C.[Link ID]=B.[Link ID]

Where (A.[RowLog Contents 0] IS NOT NULL OR C.[RowLog Contents 0] IS NOT NULL)

Update B Set B.[RowLog Contents 0] =

(CASE WHEN A.[RowLog Contents 0] IS NOT NULL AND C.[RowLog Contents 0] IS NOT NULL THEN A.[RowLog Contents 0]+C.[RowLog Contents 0]

WHEN A.[RowLog Contents 0] IS NULL AND C.[RowLog Contents 0] IS NOT NULL THEN C.[RowLog Contents 0]

WHEN A.[RowLog Contents 0] IS NOT NULL AND C.[RowLog Contents 0] IS NULL THEN A.[RowLog Contents 0]

END)

--,B.[Update]=ISNULL(B.[Update],0)+1

from @ModifiedRawData B

LEFT Join @ModifiedRawData A On A.[Page IDS]=Convert(int,Convert(Varbinary(Max),Reverse(Substring(B.[RowLog Contents 0],15+14,2))))

And A.[File IDS]=Convert(int,Convert(Varbinary(Max),Reverse(Substring(B.[RowLog Contents 0],19+14,2))))

And A.[Link ID]<>B.[Link ID] And B.[Update]=0

LEFT Join @ModifiedRawData C On C.[Page IDS]=Convert(int,Convert(Varbinary(Max),Reverse(Substring(B.[RowLog Contents 0],27+14,2))))

And C.[File IDS]=Convert(int,Convert(Varbinary(Max),Reverse(Substring(B.[RowLog Contents 0],31+14,2))))

And C.[Link ID]<>B.[Link ID] And B.[Update]=0

Where (A.[RowLog Contents 0] IS NOT NULL OR C.[RowLog Contents 0] IS NOT NULL)

UPDATE @ModifiedRawData SET [RowLog Contents 0] =

(Case When [RowLog Len]>=8000 Then

Substring([RowLog Contents 0] ,15,[RowLog Len])

When [RowLog Len]<8000 Then

SUBSTRING([RowLog Contents 0],15+6,Convert(int,Convert(varbinary(max),REVERSE(Substring([RowLog Contents 0],15,6)))))

End)

FROM @ModifiedRawData Where [LINK ID]=0

UPDATE @ColumnNameAndData SET [hex_Value]=[RowLog Contents 0]

--,A.[Update]=A.[Update]+1

FROM @ColumnNameAndData A

INNER JOIN @ModifiedRawData B ON

Convert(int,Convert(Varbinary(Max),Reverse(Substring([hex_value],17,4))))=[PAGE IDS]

AND Convert(int,Substring([hex_value],9,2)) =B.[Link ID]

Where [System_Type_Id] In (99,167,175,231,239,241,165,98) And [Link ID] <>0

UPDATE @ColumnNameAndData SET [hex_Value]=

(CASE WHEN B.[RowLog Contents 0] IS NOT NULL AND C.[RowLog Contents 0] IS NOT NULL THEN B.[RowLog Contents 0]+C.[RowLog Contents 0]

WHEN B.[RowLog Contents 0] IS NULL AND C.[RowLog Contents 0] IS NOT NULL THEN C.[RowLog Contents 0]

WHEN B.[RowLog Contents 0] IS NOT NULL AND C.[RowLog Contents 0] IS NULL THEN B.[RowLog Contents 0]

END)

--,A.[Update]=A.[Update]+1

FROM @ColumnNameAndData A

LEFT JOIN @ModifiedRawData B ON

Convert(int,Convert(Varbinary(Max),Reverse(Substring([hex_value],5,4))))=B.[PAGE IDS] And B.[Link ID] =0

LEFT JOIN @ModifiedRawData C ON

Convert(int,Convert(Varbinary(Max),Reverse(Substring([hex_value],17,4))))=C.[PAGE IDS] And C.[Link ID] =0

Where [System_Type_Id] In (99,167,175,231,239,241,165,98) And (B.[RowLog Contents 0] IS NOT NULL OR C.[RowLog Contents 0] IS NOT NULL)

UPDATE @ColumnNameAndData SET [hex_Value]=[RowLog Contents 0]

--,A.[Update]=A.[Update]+1

FROM @ColumnNameAndData A

INNER JOIN @ModifiedRawData B ON

Convert(int,Convert(Varbinary(Max),Reverse(Substring([hex_value],9,4))))=[PAGE IDS]

And Convert(int,Substring([hex_value],3,2))=[Link ID]

Where [System_Type_Id] In (35,34,99) And [Link ID] <>0

UPDATE @ColumnNameAndData SET [hex_Value]=[RowLog Contents 0]

--,A.[Update]=A.[Update]+10

FROM @ColumnNameAndData A

INNER JOIN @ModifiedRawData B ON

Convert(int,Convert(Varbinary(Max),Reverse(Substring([hex_value],9,4))))=[PAGE IDS]

Where [System_Type_Id] In (35,34,99) And [Link ID] =0

UPDATE @ColumnNameAndData SET [hex_Value]=[RowLog Contents 0]

--,A.[Update]=A.[Update]+1

FROM @ColumnNameAndData A

INNER JOIN @ModifiedRawData B ON

Convert(int,Convert(Varbinary(Max),Reverse(Substring([hex_value],15,4))))=[PAGE IDS]

Where [System_Type_Id] In (35,34,99) And [Link ID] =0

Update @ColumnNameAndData set [hex_value]= 0xFFFE + Substring([hex_value],9,LEN([hex_value]))

--,[Update]=[Update]+1

Where [system_type_id]=241

CREATE TABLE [#temp_Data]

(

[FieldName] VARCHAR(MAX),

[FieldValue] NVARCHAR(MAX),

[Rowlogcontents] VARBINARY(8000),

[Row ID] int

)

INSERT INTO #temp_Data

SELECT NAME,

CASE

WHEN system_type_id IN (231, 239) THEN LTRIM(RTRIM(CONVERT(NVARCHAR(max),hex_Value))) --NVARCHAR ,NCHAR

WHEN system_type_id IN (167,175) THEN LTRIM(RTRIM(CONVERT(VARCHAR(max),hex_Value))) --VARCHAR,CHAR

WHEN system_type_id IN (35) THEN LTRIM(RTRIM(CONVERT(VARCHAR(max),hex_Value))) --Text

WHEN system_type_id IN (99) THEN LTRIM(RTRIM(CONVERT(NVARCHAR(max),hex_Value))) --nText

WHEN system_type_id = 48 THEN CONVERT(VARCHAR(MAX), CONVERT(TINYINT, CONVERT(BINARY(1), REVERSE (hex_Value)))) --TINY INTEGER

WHEN system_type_id = 52 THEN CONVERT(VARCHAR(MAX), CONVERT(SMALLINT, CONVERT(BINARY(2), REVERSE (hex_Value)))) --SMALL INTEGER

WHEN system_type_id = 56 THEN CONVERT(VARCHAR(MAX), CONVERT(INT, CONVERT(BINARY(4), REVERSE(hex_Value)))) -- INTEGER

WHEN system_type_id = 127 THEN CONVERT(VARCHAR(MAX), CONVERT(BIGINT, CONVERT(BINARY(8), REVERSE(hex_Value))))-- BIG INTEGER

WHEN system_type_id = 61 Then CONVERT(VARCHAR(MAX),CONVERT(DATETIME,CONVERT(VARBINARY(8000),REVERSE (hex_Value))),100) --DATETIME

WHEN system_type_id =58 Then CONVERT(VARCHAR(MAX),CONVERT(SMALLDATETIME,CONVERT(VARBINARY(8000),REVERSE(hex_Value))),100) --SMALL DATETIME

WHEN system_type_id = 108 THEN CONVERT(VARCHAR(MAX),CONVERT(NUMERIC(38,20), CONVERT(VARBINARY,CONVERT(VARBINARY(1),xprec)+CONVERT(VARBINARY(1),xscale))+CONVERT(VARBINARY(1),0) + hex_Value)) --- NUMERIC

WHEN system_type_id =106 THEN CONVERT(VARCHAR(MAX), CONVERT(DECIMAL(38,20), CONVERT(VARBINARY,Convert(VARBINARY(1),xprec)+CONVERT(VARBINARY(1),xscale))+CONVERT(VARBINARY(1),0) + hex_Value)) --- DECIMAL

WHEN system_type_id In(60,122) THEN CONVERT(VARCHAR(MAX),Convert(MONEY,Convert(VARBINARY(8000),Reverse(hex_Value))),2) --MONEY,SMALLMONEY

WHEN system_type_id = 104 THEN CONVERT(VARCHAR(MAX),CONVERT (BIT,CONVERT(BINARY(1), hex_Value)%2)) -- BIT

WHEN system_type_id =62 THEN RTRIM(LTRIM(STR(CONVERT(FLOAT,SIGN(CAST(CONVERT(VARBINARY(8000),Reverse(hex_Value)) AS BIGINT)) * (1.0 + (CAST(CONVERT(VARBINARY(8000),Reverse(hex_Value)) AS BIGINT) & 0x000FFFFFFFFFFFFF) * POWER(CAST(2 AS FLOAT), -52)) * POWER(CAST(2 AS FLOAT),((CAST(CONVERT(VARBINARY(8000),Reverse(hex_Value)) AS BIGINT) & 0x7ff0000000000000) / EXP(52 * LOG(2))-1023))),53,LEN(hex_Value)))) --- FLOAT

When system_type_id =59 THEN Left(LTRIM(STR(CAST(SIGN(CAST(Convert(VARBINARY(8000),REVERSE(hex_Value)) AS BIGINT))* (1.0 + (CAST(CONVERT(VARBINARY(8000),Reverse(hex_Value)) AS BIGINT) & 0x007FFFFF) * POWER(CAST(2 AS Real), -23)) * POWER(CAST(2 AS Real),(((CAST(CONVERT(VARBINARY(8000),Reverse(hex_Value)) AS INT) )& 0x7f800000)/ EXP(23 * LOG(2))-127))AS REAL),23,23)),8) --Real

WHEN system_type_id In (165,173) THEN (CASE WHEN CHARINDEX(0x,cast('' AS XML).value('xs:hexBinary(sql:column("hex_Value"))', 'VARBINARY(8000)')) = 0 THEN '0x' ELSE '' END) +cast('' AS XML).value('xs:hexBinary(sql:column("hex_Value"))', 'varchar(max)') -- BINARY,VARBINARY

WHEN system_type_id =34 THEN (CASE WHEN CHARINDEX(0x,cast('' AS XML).value('xs:hexBinary(sql:column("hex_Value"))', 'VARBINARY(8000)')) = 0 THEN '0x' ELSE '' END) +cast('' AS XML).value('xs:hexBinary(sql:column("hex_Value"))', 'varchar(max)') --IMAGE

WHEN system_type_id =36 THEN CONVERT(VARCHAR(MAX),CONVERT(UNIQUEIDENTIFIER,hex_Value)) --UNIQUEIDENTIFIER

WHEN system_type_id =231 THEN CONVERT(VARCHAR(MAX),CONVERT(sysname,hex_Value)) --SYSNAME

WHEN system_type_id =241 THEN CONVERT(VARCHAR(MAX),CONVERT(xml,hex_Value)) --XML

WHEN system_type_id =189 THEN (CASE WHEN CHARINDEX(0x,cast('' AS XML).value('xs:hexBinary(sql:column("hex_Value"))', 'VARBINARY(8000)')) = 0 THEN '0x' ELSE '' END) +cast('' AS XML).value('xs:hexBinary(sql:column("hex_Value"))', 'varchar(max)') --TIMESTAMP

WHEN system_type_id=98 THEN (CASE

WHEN CONVERT(INT,SUBSTRING(hex_Value,1,1))=56 THEN CONVERT(VARCHAR(MAX), CONVERT(INT, CONVERT(BINARY(4), REVERSE(Substring(hex_Value,3,Len(hex_Value)))))) -- INTEGER

WHEN CONVERT(INT,SUBSTRING(hex_Value,1,1))=108 THEN CONVERT(VARCHAR(MAX),CONVERT(numeric(38,20),CONVERT(VARBINARY(1),Substring(hex_Value,3,1)) +CONVERT(VARBINARY(1),Substring(hex_Value,4,1))+CONVERT(VARBINARY(1),0) + Substring(hex_Value,5,Len(hex_Value)))) --- NUMERIC

WHEN CONVERT(INT,SUBSTRING(hex_Value,1,1))=167 THEN LTRIM(RTRIM(CONVERT(VARCHAR(max),Substring(hex_Value,9,Len(hex_Value))))) --VARCHAR,CHAR

WHEN CONVERT(INT,SUBSTRING(hex_Value,1,1))=36 THEN CONVERT(VARCHAR(MAX),CONVERT(UNIQUEIDENTIFIER,Substring((hex_Value),3,20))) --UNIQUEIDENTIFIER

WHEN CONVERT(INT,SUBSTRING(hex_Value,1,1))=61 THEN CONVERT(VARCHAR(MAX),CONVERT(DATETIME,CONVERT(VARBINARY(8000),REVERSE (Substring(hex_Value,3,LEN(hex_Value)) ))),100) --DATETIME

WHEN CONVERT(INT,SUBSTRING(hex_Value,1,1))=165 THEN '0x'+ SUBSTRING((CASE WHEN CHARINDEX(0x,cast('' AS XML).value('xs:hexBinary(sql:column("hex_Value"))', 'VARBINARY(8000)')) = 0 THEN '0x' ELSE '' END) +cast('' AS XML).value('xs:hexBinary(sql:column("hex_Value"))', 'varchar(max)'),11,LEN(hex_Value)) -- BINARY,VARBINARY

END)

END AS FieldValue

,[Rowlogcontents]

,[Row ID]

FROM @ColumnNameAndData ORDER BY nullbit

--Create the column name in the same order to do pivot table.

DECLARE @FieldName VARCHAR(max)

SET @FieldName = STUFF(

(

SELECT ',' + CAST(QUOTENAME([Name]) AS VARCHAR(MAX)) FROM syscolumns WHERE id=object_id('' + @SchemaName_n_TableName + '')

FOR XML PATH('')), 1, 1, '')

--Finally did pivot table and get the data back in the same format.

SET @sql = 'SELECT ' + @FieldName + ' FROM #temp_Data PIVOT (Min([FieldValue]) FOR FieldName IN (' + @FieldName + ')) AS pvt'

EXEC sp_executesql @sql

'Database > Query' 카테고리의 다른 글

INDEX 상세 정보 조회 (0)	2020.11.02
프로시저 파리미터 (0)	2020.10.20
EXEC와 동적(adhoc) 쿼리 (0)	2020.10.05
Find and Kill all the Blocked Process/Query (0)	2020.09.22
오래된 커서 (0)	2020.09.11

개요

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 문에 대해 두 개의 레코드를 기록합니다. 첫 번째는 변경 전 데이터를 표시하고 두 번째는 수행 후 데이터를 표시합니다. 변화.

참고 : CDC에 대한 자세한 내용은 변경 데이터 캡처 를 사용하여 SQL Server의 변경 내용 모니터링을 참조하세요 .

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

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 트랜잭션 로그에 의존합니다. 변경이 수행되면이 변경 사항이 트랜잭션 로그 파일에 기록됩니다.

확인 SQL 서버 트랜잭션 로그 아키텍처 자세한 내용은.

해당 테이블에서 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

1. EXEC와 동적(adhoc) 쿼리

자세한 내용은 MSDN의 EXECUTE 페이지를 참고하자.

(내용 겁나 길다 ㅋ)

EXECUTE 문은 SQL 문장을 실행시키는 역할을 한다.

EXEC와 동일하기에, 보통 EXEC로 많이들 사용한다.

(시스템/유저정의/CLR) 저장 프로시져를 직접 호출할 수 있으며, 문자열 쿼리를 실행시킬 수 있다.

다음 두 개의 예제는 차례대로 저장 프로시져와 문자열 쿼리를 EXEC로 실행하는 내용을 포함하고 있다.

-- #1. 저장 프로시져 호출 예제
USE AdventureWorks2012
GO
-- uspGetExployeeManagers 라는 저장 프로시져에 인자 6을 넘기며 실행시킨다
-- usp (user stored procedure)
EXEC dbo.uspGetEmployeeManagers 6
GO
-- #2. 직접 문자열 쿼리 실행 예제
USE Son
GO
DECLARE @SQLStmt VARCHAR(80) =
'SELECT * FROM UserTable WHERE ID = ''LHJ'''
EXEC(@SQLStmt)

이와 같이 EXEC 문을 사용하여, 쿼리문 내에서 다른 쿼리문을 실행시킬 수 있다.

이렇게 EXEC()를 이용해서 쿼리문을 실행하는 것을 동적(adhoc) 쿼리라고 한다.

2. 동적 쿼리를 가급적 쓰지 말아야 하는 이유

저장 프로시져는 실행 계획이 플랜 캐쉬에 캐싱된다.

즉, 한번 컴파일되면 이것이 캐쉬에 저장되고, 재사용이 되면 될수록 재컴파일이 필요없기에 효율이 올라간다.

그렇다면 저장 프로시져는 언제까지 실행 계획이 캐시에 유지될까?

저장 프로시져가 수정되거나
인덱스 통계가 재빌드되기 전까지

하지만, 동적 쿼리는 시스템에 캐싱되지 않아 호출할 때마다 컴파일을 해야하고,

이것이 결국 서버의 성능을 떨어트리게 된다.

동적 쿼리라고 해서 무조건 실행 계획에 캐슁되지 않는 것은 아니지만,

매개 변수가 하나라도 바뀌면 이것은 새로 컴파일되고 새로 캐슁된다. (EXEC의 경우 그렇다)

즉, 조금이라도 쿼리문이 바뀌면 이것은 새로 컴파일 -> 기존 캐시 미사용 -> 성능 하락이 발생하는 것이다.

동적 쿼리를 쓰는 방법은 다음 두 가지가 있다.

EXEC
EXEC sp_executesql

EXEC()는 위에서 얘기한 매개변수가 하나라도 바뀌면 재컴파일됨이 확실하다.

하지만, EXEC sp_executesql은 EXEC()와는 다르며, 조금 더 융통성 있고 효율적이라고 할 수 있다.

(자세한 내용은 MSDN의 sp_executesql 사용 페이지를 참고하자)

우선 EXEC에 비해 EXEC sp_executesql이 가지는 장점은 아래와 같다.

시스템 프로시져가 아니라 확장형 프로시져이다.
쿼리 문에 매개변수(입력/출력)를 정의할 수 있다.
매개변수 사용으로 인하여 쿼리 최적화 프로그램이 컴파일된 실행플랜을 재사용할 확률이 높아진다.

EXEC sp_executesql은 매개변수를 정의할 수 있고 이를 파라미터화 할 수 있기 때문에

쿼리문이 고정된 채 매개변수만 바뀌는 경우라면, 첫번째 실행에서 실행 계획을 캐싱, 재사용하게 된다.

이로 인해 EXEC()에 비해 많은 성능상 이득을 취할 수 있게 된다.

따라서, 어쩔 수 없이 동적 쿼리를 사용해야 한다면, EXEC() 보다 EXEC sp_executesql을 사용하도록 하자.

그리고 다음은 EXEC sp_executesql의 제약 사항이다.

현재 데이터베이스가 변경되지 않는다.
로컬 변수에 접근할 수 없다.

위 내용에 대해 아래 예제가 이해하는 데 도움을 줄 것이다.

-- 다음의 일괄처리에서 EXEC sp_executesql은 로컬 변수에 접근하지 못한다.
DECLARE @CharVariable CHAR(3) = 'abc'
EXEC sp_executesql N'PRINT @CharVariable';
GO
USE master;
GO
-- sp_executesql이 끝나고 나면 Database context가 리셋된다
EXECUTE sp_executesql N'USE Son'
GO
-- 아래 구문은 실패하는데, 이미 Database context가 master로 리셋되었기 때문이다.
SELECT * FROM Son.UserTable;
GO

3. EXEC()와 EXEC sp_executesql의 성능 비교

(다음 예제는 "http://sungedb.tistory.com/99" 에서 퍼왔습니다)

위에서 EXEC()는 매개변수만 바뀌어도 새로 컴파일되서 재사용이 불가능하다고 하였고,

EXEC sp_executesql은 매개변수가 바뀌어도 구문이 변하지 않으면, 재사용이 가능하다고 하였다.

이제 아래 예제를 통해 그 차이를 확실히 느껴보자.

1. 우선 테스트에 사용될 DB를 생성하자.

------------------------------------------------------------
-- 테스트 테이블
------------------------------------------------------------
CREATE TABLE TAB_A
(
PK_COL INT NOT NULL,
COL1 INT NOT NULL,
COL2 INT NOT NULL
);
ALTER TABLE TAB_A ADD CONSTRAINT PK_TAB_A PRIMARY KEY (PK_COL);
GO
CREATE TABLE TAB_B
(
PK_COL INT NOT NULL,
COL1 INT NOT NULL,
COL2 INT NOT NULL
);
-- 조인에 사용될 제약 조건
ALTER TABLE TAB_B ADD CONSTRAINT PK_TAB_B PRIMARY KEY (PK_COL);
ALTER TABLE TAB_B ADD CONSTRAINT FK_TAB_B FOREIGN KEY (PK_COL);
REFERENCES dbo.TAB_A(PK_COL);
GO

2. 이제 테스트 쿼리를 작성해 보자.

-- 측정하기 전에 플랜캐쉬를 초기화하자
DBCC FREEPROCCACHE;
DECLARE @SQLStmt NVARCHAR(MAX);
DECLARE @PARAM_DEF NVARCHAR(MAX);
SET @SQLStmt =
N'SELECT A.COL1, A.COL2, B.COL1, B.COL2
FROM dbo.TAB_A A
INNER JOIN dbo.TAB_B B
ON B.PK_COL = A.PK_COL
WHERE A.COL1 = @VAL;';
SET @PARAM_DEF = N'@VAL INT';
DECLARE @I INT;
SET @I = 0;
-- 같은 쿼리가 캐싱되는지 확인하기 위해서 값만 바꿔가면서 반복 실행한다.
WHILE (@I < 10)
BEGIN
-- EXEC sp_executesql
EXEC sp_executesql @SQLStmt, @PARAM_DEF, @VAL = @I; -- 값만 바꿔서 넘겨준다.
DECLARE @PARAM NVARCHAR(MAX);
SET @PARAM = CAST(@I AS NVARCHAR(MAX));
EXEC (N'SELECT A.COL1, A.COL2, B.COL1, B.COL2
FROM dbo.TAB_A A
INNER JOIN dbo.TAB_B B
ON B.PK_COL = A.PK_COL
WHERE A.COL1 = ' + @PARAM + N';'); -- 값이 바뀌면서 쿼리문 전체가 바뀐다.
SET @I = @I + 1;
END
-- 아래는 실행 계획 캐쉬의 정보를 보여준다.
SELECT cp.objtype, cp.cacheobjtype, cp.size_in_bytes, cp.refcounts, cp.usecounts, st.text
FROM sys.dm_exec_cached_plans cp
CROSS APPLY sys.dm_exec_sql_text(cp.plan_handle) st
WHERE cp.objtype IN ('Proc', 'Prepared', 'Adhoc')
AND st.text NOT LIKE '%SELECT cp.objtype%'
AND st.text NOT LIKE '%@BatchID%'
AND st.text NOT LIKE '%@_msparam%'
AND st.text NOT LIKE '%database%'
AND st.text NOT LIKE '%objtype%'

위 예제에서 실행 계획 캐쉬 정보는 아래와 같다.

EXEC()는 매개변수가 바뀔 때마다 새로 컴파일, 재사용이 되지 않아 use counts가 1 인 것을 확인할 수 있고,

EXEC sp_executesql은 모두 재사용되어, use_counts가 10 인 것을 확인할 수 있다.

참고 1)

DBCC는 DataBase Console Command의 약자로 DB를 매니지먼트하는데 사용된다.

자세한 내용은 MSDN의 DBCC 페이지를 살펴보자. 공부할 게 많은 페이지이다.

참고 2)

마지막 예제의 실행 계획 캐쉬의 정보를 보는 SELECT 문은 가급적 재사용 하자.

'Database > Query' 카테고리의 다른 글

프로시저 파리미터 (0)	2020.10.20
DELETE 복원 프로시저 (0)	2020.10.20
Find and Kill all the Blocked Process/Query (0)	2020.09.22
오래된 커서 (0)	2020.09.11
테이블 명세서 (0)	2020.08.29

--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

Database

캐치는 무엇입니까?

임시 테이블 생성

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

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

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

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

DATA_CONSISTENCY_CHECK 속성 지정

결론

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

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

'Database > Query' 카테고리의 다른 글

'Database > Query' 카테고리의 다른 글

개요

설치 및 아키텍처

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

CDC 활성화

CDC 비활성화

DML 변경 감사

한계

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

개요

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

SQL 변경 추적 활성화

SQL 변경 내용 추적 비활성화

DML 변경 감사

INSERT 감사

업데이트 감사

감사 삭제

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

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

'Database > Query' 카테고리의 다른 글

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

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

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

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

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

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

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

티스토리툴바