SQL

이 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 문을 사용하여 데이터베이스 수준에서 사용하도록 설정해야합니다. 자동 정리 프로세스를 활성화하면 아래와 같이 보존 기간보다 오래된 내부 디스크 테이블의 데이터가 자동으로 삭제됩니다.

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

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

기본 키가 정의되지 않은 데이터베이스 테이블에서 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 스크립트를 사용하여 변경 정보를 검색 할 수 있습니다.

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

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

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

업데이트 감사

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

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

감사 삭제

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

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

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

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

저장된 Plan Cache 확인 및 활용 

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

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

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

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

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

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

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

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

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

전체 플랜캐시에 비해 단일 사용 계획에 대한 메모리 소비의 권장된 사항은 없지만 단일 실행 계획이 전체 플랜캐시의 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/ 

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

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

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

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

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

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

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

생성된 계획을 살펴보자. 

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

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

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

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

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

1. EXEC와 동적(adhoc) 쿼리

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

(내용 겁나 길다 ㅋ)

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

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

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

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

1. -- #1. 저장 프로시져 호출 예제

2. USE AdventureWorks2012

3. GO

4.  

5. -- uspGetExployeeManagers 라는 저장 프로시져에 인자 6을 넘기며 실행시킨다

6. -- usp (user stored procedure)

7. EXEC dbo.uspGetEmployeeManagers 6

8. GO

9.  

10. -- #2. 직접 문자열 쿼리 실행 예제

11. USE Son

12. GO

13.  

14. DECLARE @SQLStmt VARCHAR(80) =

15.         'SELECT * FROM UserTable WHERE ID = ''LHJ'''

16. 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의 제약 사항이다.

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

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

1. -- 다음의 일괄처리에서 EXEC sp_executesql은 로컬 변수에 접근하지 못한다.

2. DECLARE @CharVariable CHAR(3) = 'abc'

3. EXEC sp_executesql N'PRINT @CharVariable';

4. GO

5.  

6.  

7. USE master;

8. GO

9. -- sp_executesql이 끝나고 나면 Database context가 리셋된다

10. EXECUTE sp_executesql N'USE Son'

11. GO

12.  

13. -- 아래 구문은 실패하는데, 이미 Database context가 master로 리셋되었기 때문이다.

14. SELECT * FROM Son.UserTable;

15. GO

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

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

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

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

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

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

1. ------------------------------------------------------------

2. -- 테스트 테이블

3. ------------------------------------------------------------

4. CREATE TABLE TAB_A

5. (

6.     PK_COL      INT NOT NULL,

7.     COL1        INT NOT NULL,

8.     COL2        INT NOT NULL

9. );

10.  

11. ALTER TABLE TAB_A ADD CONSTRAINT PK_TAB_A PRIMARY KEY (PK_COL);

12. GO

13.  

14. CREATE TABLE TAB_B

15. (

16.     PK_COL      INT NOT NULL,

17.     COL1        INT NOT NULL,

18.     COL2        INT NOT NULL

19. );

20.  

21. -- 조인에 사용될 제약 조건

22. ALTER TABLE TAB_B ADD CONSTRAINT PK_TAB_B PRIMARY KEY (PK_COL);

23. ALTER TABLE TAB_B ADD CONSTRAINT FK_TAB_B FOREIGN KEY (PK_COL);

24. REFERENCES dbo.TAB_A(PK_COL);

25. GO

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

1. -- 측정하기 전에 플랜캐쉬를 초기화하자

2. DBCC FREEPROCCACHE;

3.  

4. DECLARE @SQLStmt NVARCHAR(MAX);

5. DECLARE @PARAM_DEF NVARCHAR(MAX);

6.  

7. SET @SQLStmt =

8.     N'SELECT A.COL1, A.COL2, B.COL1, B.COL2

9.      FROM dbo.TAB_A A

10.      INNER JOIN dbo.TAB_B B

11.      ON B.PK_COL = A.PK_COL

12.      WHERE A.COL1 = @VAL;';

13.          

14. SET @PARAM_DEF = N'@VAL INT';

15.  

16. DECLARE @I INT;

17. SET @I = 0;

18. -- 같은 쿼리가 캐싱되는지 확인하기 위해서 값만 바꿔가면서 반복 실행한다.

19. WHILE (@I < 10)

20. BEGIN

21.     -- EXEC sp_executesql

22.     EXEC sp_executesql @SQLStmt, @PARAM_DEF, @VAL = @I; -- 값만 바꿔서 넘겨준다.

23.        

24.     DECLARE @PARAM NVARCHAR(MAX);

25.     SET @PARAM = CAST(@I AS NVARCHAR(MAX));

26.        

27.     EXEC (N'SELECT A.COL1, A.COL2, B.COL1, B.COL2

28.            FROM dbo.TAB_A A

29.            INNER JOIN dbo.TAB_B B

30.            ON B.PK_COL = A.PK_COL

31.            WHERE A.COL1 = ' + @PARAM + N';'); -- 값이 바뀌면서 쿼리문 전체가 바뀐다.

32.  

33.     SET @I = @I + 1;

34. END

35.  

36. -- 아래는 실행 계획 캐쉬의 정보를 보여준다.

37. SELECT cp.objtype, cp.cacheobjtype, cp.size_in_bytes, cp.refcounts, cp.usecounts, st.text

38. FROM sys.dm_exec_cached_plans cp

39. CROSS APPLY sys.dm_exec_sql_text(cp.plan_handle) st

40. WHERE cp.objtype IN ('Proc', 'Prepared', 'Adhoc')

41.       AND st.text NOT LIKE '%SELECT cp.objtype%'

42.       AND st.text NOT LIKE '%@BatchID%'

43.       AND st.text NOT LIKE '%@_msparam%'

44.       AND st.text NOT LIKE '%database%'

45.       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 문은 가급적 재사용 하자.

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

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 개 행을 삽입합니다.

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

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

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

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

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

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

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

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

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

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

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

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

동일한 매개 변수를 사용하여 동일한 쿼리에 대해 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 열의 결과를 분석합니다.

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

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

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

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 데이터베이스에 대한 자동 증가를 비활성화합니다.

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

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

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

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

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

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

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

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

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

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

DBCC SHRINKFILE을 사용하여 TEMPDB 축소

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

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

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

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

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

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

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

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

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

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

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

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

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

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

이 설정을 적용하려면 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임을 알 수 있습니다.

  

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 :

스크립트 2 :

OStress 유틸리티

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

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

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

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

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

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

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

다음 출력에서 ​​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 세부 정보를 보는 데 사용되는 새로운 동적 관리 함수의 사용도 볼 수 있습니다.

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

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

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

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

결론

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

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

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

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

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

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

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;

    

   

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

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

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

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

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

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

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

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

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

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

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

• 저가형 컴퓨터에서 초기화 된 메모리 내 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를 사용합니다.

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'가 저장 프로 시저에 추가되었습니다.

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

1. 고정 드라이브 및 볼륨 정보 목록 만들기
2. XTP 시스템 저장 프로 시저를 사용하여 SQL Server 메모리 내 컨테이너 세부 정보 검토
3. SQL Server In-Memory 데이터베이스 내에서 사용 / 여유 공간 확인
4.  SQL Server 및 XML로 HTML 이메일 작성
5. XTP 성능 카운터 확인
6.   그리고 더…

시작하기

SQL Server 메모리 내 OLTP 솔루션은 간단히 말해서 데이터를 메모리로 이동하여 시스템 성능을 향상시킵니다. 쿼리 실행 성능을 향상시키는 데 도움이됩니다. 이 가이드에서는 이러한 내부 데이터 구조에 대해 더 자세히 살펴 보겠습니다.

고정 드라이브 및 볼륨 정보

운영 체제에 표시되는 모든 드라이브에 대한 정보와 SQL Server 데이터베이스 파일이있는 모든 드라이브에 대한 정보를 가져 오는 몇 가지 유용한 쿼리가 있습니다. 따라서 운영 체제에 표시되는 모든 드라이브에 대한 정보를 얻고 SQL Server 데이터베이스 파일이 있는지 여부에 관계없이 운영 체제에서 사용할 수있는 각 드라이브에 사용할 수있는 공간 만 표시합니다.

또한 SQL Server 데이터베이스 파일이있는 모든 논리 드라이브의 크기와 여유 공간을 제공하는 별도의 쿼리가 있습니다. 이는 공간 부족을 원하지 않기 때문에 유용한 정보입니다. 공간이 부족 해지면 시스템 성능이 저하 될 수 있습니다. IO 작업에 직접적인 영향을줍니다. 따라서 더 나아가 고정 드라이브는 운영 체제에서 사용할 수있는 모든 논리 드라이브에서 사용 가능한 공간을 보여 주지만 DMF sys.dm_os_volume_stats는 전체 크기와 모든 논리 드라이브에 대해 사용 가능한 공간을 포함하는 볼륨 정보를 제공합니다. SQL Server 데이터베이스 파일은 알아두면 매우 유용한 정보이며 디스크 공간이 부족할 때이를 감지하는 데 도움이됩니다.

SQL의 출력은 다음과 같습니다.

참고 : 디스크 공간이 부족하면 마그네틱 스토리지와 플래시 스토리지 모두에서 성능에 영향을 미칠 수 있습니다.

참고 : 이전 버전의 SQL Server는 xp_fixeddrives 확장 저장 프로 시저를 사용하여 SQL Server의 디스크 사용량 정보를 열거합니다. 그런 다음 OLE 사용 개체가 드라이브의 전체 크기를 가져 오도록 설정하여 출력을 조작합니다. 그런 다음 필요한 결과를 얻기 위해 데이터가 변환됩니다. 이 프로세스는 약간 번거롭고 다양한 보안상의 이유로 권장되지 않습니다. SQL Server 2008 SP1에서 DMF sys.dm_os_volume_stats ()를 사용하여 SQL 인스턴스에서 공간 관련 메트릭을 파생 할 수 있습니다.

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'가 저장 프로 시저에 추가되었습니다.

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

메모리 내 데이터베이스 내에서 사용 / 사용 가능한 공간을 확인하는 방법

SQL Server In memory 데이터베이스는 다양한 구성 요소로 구성됩니다. 데이터베이스에는 MEMORY_OPTIMIZED_DATA 파일 그룹이 있어야합니다. 이 파일 그룹에는 하나 이상의 컨테이너가 포함될 수 있습니다. 모든 컨테이너는 데이터 및 / 또는 델타 파일을 저장합니다.

파일 / 컨테이너, 각 파일 / 컨테이너 정보의 자동 증가 설정을 얻으려면 아래 SQL을 사용하십시오.