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

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

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

TempDB 사용 요약

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

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

Shrink TempDB 활동 개요

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

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

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

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

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

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

SET NOCOUNT ON;

DECLARE @i INT = 1

BEGIN TRAN

WHILE @i <= 150000

BEGIN

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

SET @i += 1

END

COMMIT TRAN

DROP TABLE #TempTable

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

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

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

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

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

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

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

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

DBCC SHRINKFILE을 사용하여 TEMPDB 축소

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

DBCC SHRINKFILE(logical_filename, size_in_MB)

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

SELECT name,

file_id,

type_desc,

size * 8 / 1024 [TempdbSizeInMB]

FROM tempdb.sys.database_files

ORDER BY type_desc DESC,

file_id;

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

DBCC SHRINKFILE(tempdev,10)

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

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

DBCC SHRINKFILE(tempdev,0)

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

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

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

DBCC SHRINKDATABASE(TempDB, ‘target_percentage_of_free_space’);

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

DBCC SHRINKDATABASE(tempdb, 10);

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

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

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

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

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

USE master;

GO

ALTER DATABASE tempdb

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

GO

ALTER DATABASE tempdb

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

GO

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

SSMS를 사용하여 TempDB 축소

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

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

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

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

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

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

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

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

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

CHECKPOINT;

            GO

            DBCC DROPCLEANBUFFERS;

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

DBCC FREEPROCCACHE;

GO

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

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

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

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

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

관리자 권한으로 명령 프롬프트 열기
SQL Server Binary 폴더로 이동합니다. 내 SQL 인스턴스에서 BINN 폴더의 경로는 다음과 같습니다.

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

경로가 확실하지 않은 경우 SQL Server 구성 관리자 및 속성 페이지에서 SQL 서비스를 마우스 오른쪽 단추로 클릭하면 이진 경로를 볼 수 있습니다.
관리 프롬프트에서 다음 명령을 실행합니다 (내 SQL2019CTP 인스턴스의 경우).

sqlservr.exe -s SQL2019CTP -c -f

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

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

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

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

USE master;

GO

ALTER DATABASE tempdb

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

GO

ALTER DATABASE tempdb

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

GO

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

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

SELECT

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

FROM sys.master_files

WHERE DB_NAME(database_id) = 'tempdb'

ORDER BY type_desc DESC, file_id

GO

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

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

TempDB 물리적 파일 위치 변경[출처] TempDB 물리적 파일 위치 변경 (0)	2020.09.25
SQL Server TempDB 데이터베이스 및 래치 경합 (0)	2020.09.24
SQL Server 2019 메모리 최적화 TempDB 메타 데이터 (0)	2020.09.24
SQL Server 메모리 내 데이터베이스 내부 메모리 구조 모니터링 (0)	2020.09.24
SQL Server 메모리 내 데이터베이스 개체의 내부 데이터 구조를 모니터링하는 방법 (0)	2020.09.24

메모리 내 최적화 된 TempDB 메타 데이터의 세부 정보
메모리 내 최적화 된 TempDB 메타 데이터를 보여주기 위해 T-SQL 샘플 준비
메모리 최적화 TempDB 메타 데이터 기능을 활성화하는 방법을 논의합니다.
OStress 유틸리티의 사용 설명
OStress 명령을 사용하여 워크로드 구현
tempdb 카탈로그 뷰에서 성능 및 리소스 경합 측정
한계에 대해 논의
그리고 더…

소개

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

시작하다

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

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

T-SQL 스크립트 준비

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

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

T-SQL 스크립트

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

스크립트 1 :

CREATE OR ALTER PROCEDURE dbo.SPROC_tempdbMemOptimzedtest_1 AS

BEGIN

SET

NOCOUNT

ON;

CREATE TABLE #DummyTable ( ID BIGINT NOT NULL );

INSERT INTO

#DummyTable

SELECT

T.RowNum

FROM

(

SELECT

TOP (1) ROW_NUMBER() OVER (

ORDER BY

(

SELECT

NULL)) RowNum

FROM

sys.sysobjects

)

T;

END

스크립트 2 :

CREATE

OR ALTER PROCEDURE dbo.SPROC_tempdbMemOptimzedtest_2 AS

BEGIN

DECLARE @id int = 0;

WHILE( @id < 10 )

BEGIN

EXECUTE dbo.SPROC_tempdbMemOptimzedtest_1;

SET

@id = @id + 1;

END

OStress 유틸리티

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

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

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

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

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

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

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

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

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

SELECT

req.session_id,

req.wait_type,

req.wait_resource,

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

req.blocking_session_id,

req.command,

SUBSTRING(txt.text,

(

req.statement_start_offset / 2

)

+ 1,

(

CASE

req.statement_end_offset

WHEN

- 1

THEN

DATALENGTH(txt.text)

ELSE

req.statement_end_offset

END

- req.statement_start_offset) / 2

)

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

FROM

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

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

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

WHERE

req.wait_type like '%page%'

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

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

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

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

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

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

ALTER SERVER CONFIGURATION SET MEMORY_OPTIMIZED TEMPDB_METADATA=ON;
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 메타 데이터 메모리 최적화 매개 변수가 이미 활성화되어 있음을 확인할 수 있습니다.
서버 구성을 확인하려면 다음 T-SQL을 실행하십시오.

SELECT SERVERPROPERTY('IsTempDBMetadataMemoryOptimized') AS IsTempDBMetadataMemoryOptimized;

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

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

EXEC sp_configure 'tempdb metadata memory-optimized'

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

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

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

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

SELECT

req.session_id,

req.wait_type,

req.wait_resource,

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

req.blocking_session_id,

req.command,

SUBSTRING(txt.text,

(

req.statement_start_offset / 2

)

+ 1,

(

CASE

req.statement_end_offset

WHEN

- 1

THEN

DATALENGTH(txt.text)

ELSE

req.statement_end_offset

END

- req.statement_start_offset) / 2

)

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

FROM

sys.dm_exec_requests AS req

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

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

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

WHERE

req.wait_type like '%page%'

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

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

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

ALTER SERVER CONFIGURATION SET MEMORY_OPTIMIZED TEMPDB_METADATA=OFF;

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

결론

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

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

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

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

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

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

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

SQL Server TempDB 데이터베이스 및 래치 경합 (0)	2020.09.24
SQL Server의 Shrink TempDB 데이터베이스 개요 (0)	2020.09.24
SQL Server 메모리 내 데이터베이스 내부 메모리 구조 모니터링 (0)	2020.09.24
SQL Server 메모리 내 데이터베이스 개체의 내부 데이터 구조를 모니터링하는 방법 (0)	2020.09.24
SQL Server의 메모리 내 OLTP에 대한 간략한 개요 (0)	2020.09.24

SMSS 표준 보고서를 사용하여 사용 가능한 리소스를 빠르게 확인
T-SQL을 사용하여 SQL Server 메모리 내 데이터베이스를 모니터링하는 방법
메모리 소비자의 내부 구조를 자세히 설명합니다.
DMF를 사용하여 SQL Server 내에서 공간 관리 추진
SQL Server 내에서 메모리 내 컨테이너 모니터링
메모리 내 체크 포인트 모드의 세부 정보를 가져 오는 방법
In-Memory 데이터베이스 내 사용 / 여유 공간 결정
그리고 더…

소개

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

시작하기

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

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

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

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

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

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;

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

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

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

IF

(

SELECT COUNT(1)

FROM sys.data_spaces

WHERE type = 'FX'

) > 0

BEGIN

SELECT OBJECT_NAME(object_id) AS tblName,

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

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

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

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

FROM sys.dm_db_xtp_table_memory_stats;

END;

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

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

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

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

FROM sys.dm_xtp_system_memory_consumers;

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

SELECT memory_consumer_type,

memory_consumer_type_desc,

allocated_bytes / 1024 [allocated_bytes_kb],

used_bytes / 1024 [used_bytes_kb],

allocation_count

FROM sys.dm_xtp_system_memory_consumers;

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

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

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

SELECT type clerk_type,

name,

memory_node_id,

pages_kb / 1024 pages_mb

FROM sys.dm_os_memory_clerks

WHERE type LIKE '%xtp%';

특성	데이터 형식	기술
이름	nvarchar (256)	이 메모리 담당자의 내부적으로 할당 된 이름을 지정합니다. 구성 요소에는 특정 유형의 여러 메모리 담당자가있을 수 있습니다. 구성 요소는 특정 이름을 사용하여 동일한 유형의 메모리 담당자를 식별하도록 선택할 수 있습니다. nullable이 아닌 열입니다.
memory_node_id	smallint	메모리 노드의 ID를 반환합니다. 또한 nullable이 아닌 필드입니다.
pages_kb	Bigint	적용 대상 : SQL Server 2012 ~ SQL Server 2017.
pages_kb	Bigint	이 메모리 클러 크에 할당 된 페이지 메모리의 양 (KB)을 지정합니다. nullable 열이 아닙니다.

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

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

SELECT TOP (10) type MemoryClerkType,

SUM(pages_kb) / 1024 AS SizeMB

FROM sys.dm_os_memory_clerks

GROUP BY type

ORDER BY 2 DESC;

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

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

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

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

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

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

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

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

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

노트 :

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

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

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

SELECT SERVERPROPERTY('IsXTPSupported') AS [IsXTPSupported]

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

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

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

USE SQLShackInMemDB;

GO

EXEC sp_spaceused

@updateusage = 'FALSE',

@mode = 'ALL',

@oneresultset = '1',

@include_total_xtp_storage = '1';

GO

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

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

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

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

시작하기

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

고정 드라이브 및 볼륨 정보

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

SELECT SUBSTRING(fixed_drive_path, 0, 2) Drive,

drive_type_desc [Description],

CONVERT(DECIMAL(18, 2), free_space_in_bytes / 1024.0 / 1024.0 / 1024) AS [AvailableSpace(GB)]

FROM sys.dm_os_enumerate_fixed_drives WITH(NOLOCK);

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

SELECT

@@SERVERNAME Server,

Volume,

CAST(SizeGB as DECIMAL(10,2)) CapacityGB,

CAST((SizeGB - FreeGB) as DECIMAL(10,2)) UsedGB,

CAST(FreeGB as DECIMAL(10,2)) FreeGB,

CAST([%Free] as DECIMAL(10,2))[%Free]

FROM(

SELECT distinct(volume_mount_point) Volume,

(total_bytes/1048576)/1024.00 as SizeGB,

(available_bytes/1048576)/1024.00 as FreeGB,

(select ((available_bytes/1048576* 1.0)/(total_bytes/1048576* 1.0) *100)) as '%Free'

FROM sys.master_files AS f CROSS APPLY

sys.dm_os_volume_stats(f.database_id, f.file_id)

group by volume_mount_point, total_bytes/1048576,

available_bytes/1048576

)T

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

USE SQLShackInMemDB

GO

EXEC sp_spaceused @updateusage = 'FALSE', @mode = 'ALL', @oneresultset = '1', @include_total_xtp_storage = '1';

GO

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

SELECT SUM(xtp_precreated) xtp_precreated,

SUM(xtp_used) xtp_used,

SUM(xtp_pending_truncation) xtp_pending_truncation

FROM

(

SELECT SUM(file_size_in_bytes / 1024) xtp_precreated,

0 xtp_used,

0 xtp_pending_truncation

FROM sys.dm_db_xtp_checkpoint_files

WHERE state_desc = 'PRECREATED'

UNION ALL

SELECT 0 xtp_precreated,

SUM(file_size_in_bytes / 1024) xtp_used,

0 xtp_pending_truncation

FROM sys.dm_db_xtp_checkpoint_files

WHERE state_desc != 'PRECREATED'

AND state_desc != 'WAITING FOR LOG TRUNCATION'

UNION ALL

SELECT 0 xtp_precreated,

0 xtp_used,

SUM(file_size_in_bytes / 1024) xtp_pending_truncation

FROM sys.dm_db_xtp_checkpoint_files

WHERE state_desc = 'WAITING FOR LOG TRUNCATION'

) T;

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

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

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

USE SQLShackInMemDB;

GO

IF

(

SELECT COUNT(1)

FROM sys.data_spaces

WHERE TYPE = 'FX'

) > 0

BEGIN

SELECT DB_NAME(DB_ID()) Database_Name,

[TYPE] = A.TYPE_DESC,

[FILE_Name] = A.name,

[FILEGROUP_NAME] = fg.name,

[File_Location] = A.PHYSICAL_NAME,

[FILESIZE_MB] = CONVERT(DECIMAL(10, 2), A.SIZE / 128.0),

[USEDSPACE_MB] = CASE

WHEN CONVERT(DECIMAL(10, 2), A.SIZE / 128.0-((SIZE / 128.0)-CAST(FILEPROPERTY(A.NAME, 'SPACEUSED') AS INT)/128.0)) IS NULL

THEN T.[Used Space GB]

ELSE CONVERT(DECIMAL(10, 2), A.SIZE / 128.0-((SIZE / 128.0)-CAST(FILEPROPERTY(A.NAME, 'SPACEUSED') AS INT)/128.0))

END,

[FREESPACE_MB] = CASE

WHEN CONVERT(DECIMAL(10, 2), A.SIZE / 128.0-CAST(FILEPROPERTY(A.NAME, 'SPACEUSED') AS INT)/128.0) IS NULL

THEN t.[Free Space GB]

ELSE CONVERT(DECIMAL(10, 2), A.SIZE / 128.0-CAST(FILEPROPERTY(A.NAME, 'SPACEUSED') AS INT)/128.0)

END,

[FREESPACE_%] = CASE

WHEN CONVERT(DECIMAL(10, 2), ((A.SIZE / 128.0-CAST(FILEPROPERTY(A.NAME, 'SPACEUSED') AS INT)/128.0)/(A.SIZE / 128.0))*100) IS NULL

THEN T.[Percent Used]

ELSE CONVERT(DECIMAL(10, 2), ((A.SIZE / 128.0-CAST(FILEPROPERTY(A.NAME, 'SPACEUSED') AS INT)/128.0)/(A.SIZE / 128.0))*100)

END,

[AutoGrow] = 'By '+CASE is_percent_growth

WHEN 0

THEN CAST(growth / 128 AS VARCHAR(10))+' MB -'

WHEN 1

THEN CAST(growth AS VARCHAR(10))+'% -'

ELSE ''

END+CASE max_size

WHEN 0

THEN 'DISABLED'

WHEN-1

THEN ' Unrestricted'

ELSE ' Restricted to '+CAST(max_size / (128) AS VARCHAR(10))+' MB'

END+CASE is_percent_growth

WHEN 1

THEN ' [autogrowth by percent, BAD setting!]'

ELSE ''

END

FROM sys.database_files A

LEFT JOIN sys.filegroups fg ON A.data_space_id = fg.data_space_id

LEFT JOIN

(

SELECT [Name] AS [Logical Name],

ISNULL([DATA], 0) + ISNULL([DELTA], 0) + ISNULL([ROOT], 0) AS [Used Space GB],

[FREE] AS [Free Space GB],

CAST((ISNULL([DATA], 0) + ISNULL([DELTA], 0) + ISNULL([ROOT], 0)) / (ISNULL([DATA], 0) + ISNULL([DELTA], 0) + ISNULL([FREE], 0) + ISNULL([ROOT], 0)) * 100 AS DECIMAL(5, 2)) AS [Percent Used]

FROM

(

SELECT CF.container_id AS FileID,

CF.file_type_desc AS FileType,

CAST(ROUND(SUM(file_size_in_bytes) / 1024.0 / 1024 / 1024, 3) AS DECIMAL(9, 3)) AS FileSizeGB,

DF.[name] AS [Name],

DF.physical_name AS [FileName]

FROM sys.dm_db_xtp_checkpoint_files AS CF

INNER JOIN sys.database_files AS DF ON DF.file_id = CF.container_id

GROUP BY DF.[file_id],

file_type_desc,

CF.container_id,

DF.[name],

DF.physical_name

) AS Base PIVOT(SUM(FileSizeGB) FOR FileType IN([DATA],

[DELTA],

[FREE],

[ROOT])) AS Pvt

) T ON T.[Logical Name] = a.name;

END;

다음 결과는 SQL Server 메모리 내 데이터베이스 파일의 공간 사용량 세부 정보를 보여줍니다.

SQL Server 메모리 내 OLTP의 사용에 대한 저장소 세부 정보를 가져 오려면 다음 쿼리를 사용합니다.

Select CF.container_id As [File ID],

Cast(Sum(file_size_in_bytes)/1024.0/1024 As DECIMAL(9,3)) As [File Size GB],

DF.[name] As [Logical Name],

DF.physical_name As [Container]

From sys.dm_db_xtp_checkpoint_files As CF

Inner join sys.database_files As DF On DF.file_id = CF.container_id

Group By DF.[file_id], CF.container_id, DF.[name], DF.physical_name

상태 및 파일 유형별로 스토리지 사용률을 분석하려면 다음 쿼리를 실행하십시오.

SELECT CF.container_id As [FileID],

CF.file_type_desc As [FileType],

CAST(ROUND(SUM(file_size_in_bytes)/1024.0/1024, 3) As DECIMAL(9,3)) As [FileSizeMB],

DF.[name] As [Logical Name],

DF.physical_name As [Container]

FROM sys.dm_db_xtp_checkpoint_files As CF

INNER JOIN sys.database_files As DF On DF.file_id = CF.container_id

GROUP BY DF.[file_id], CF.container_id, CF.file_type_desc,DF.[name],DF.physical_name

ORDER BY FILE_ID

컨테이너 정보를 해당 드라이브와 병합합니다.

SELECT

@@SERVERNAME Server,

Volume,

CAST(SizeGB as DECIMAL(10,2)) CapacityGB,

CAST((SizeGB - FreeGB) as DECIMAL(10,2)) UsedGB,

CAST(FreeGB as DECIMAL(10,2)) FreeGB,

CAST([%Free] as DECIMAL(10,2))[%Free],

T2.*

FROM(

SELECT distinct(volume_mount_point) Volume,

(total_bytes/1048576)/1024.00 as SizeGB,

(available_bytes/1048576)/1024.00 as FreeGB,

(select ((available_bytes/1048576* 1.0)/(total_bytes/1048576* 1.0) *100)) as '%Free'

FROM sys.master_files AS f CROSS APPLY

sys.dm_os_volume_stats(f.database_id, f.file_id)

group by volume_mount_point, total_bytes/1048576,

available_bytes/1048576

)T1

inner join

(

Select FileID As [File ID],

[Name] As [Logical Name],

[FileName] As [BaseContainer],

-- substring([FileName],0,4),

isnull([DATA],0) + isnull([DELTA],0) +isnull([FREE],0) + isnull([ROOT],0) As [Total Size GB],

isnull([DATA],0) + isnull([DELTA],0) + isnull([ROOT],0)As [Used Space GB],

[FREE] As [Free Space GB],

cast((isnull([DATA],0) + isnull([DELTA],0) + isnull([ROOT],0))/ ( isnull([DATA],0) + isnull([DELTA],0) +isnull([FREE],0) + isnull([ROOT],0))*100 as decimal(5,2)) As [Percent Used],

DB_ID() As [Database ID]

From (Select CF.container_id As FileID,

CF.file_type_desc As FileType,

Cast(Round(Sum(file_size_in_bytes)/1024.0/1024/1024, 3) As Decimal(9,3)) As FileSizeGB,

DF.[name] As [Name],

DF.physical_name As [FileName]

From sys.dm_db_xtp_checkpoint_files As CF

Inner join sys.database_files As DF On DF.file_id = CF.container_id

Group By DF.[file_id],file_type_desc, CF.container_id, DF.[name], DF.physical_name) As Base

Pivot (Sum(FileSizeGB)

For FileType In ([DATA], [DELTA], [FREE], [ROOT])) As Pvt

)T2

on T1.Volume=substring(T2.[BaseContainer],0,4)

SQL Server 메모리 내 데이터 구조 및 XML을 사용하여 HTML 이메일 작성

의도 한 수신자에게 알림을 보내는 이메일을 작성해 보겠습니다. HTML 태그를 동적으로 생성 할 수있는 많은 방법이 있지만 XML 요소를 사용하면 대부분의 태그 준비가 훨씬 쉬워집니다.

--Declare the variable

DECLARE

@body varchar(max),

@Header varchar(max),

@HTML varchar(max)

Set @Header = '<html><head>' +

'<style>' +

'td {border: solid black 1px;padding-left:5px;padding-right:5px;padding-top:1px;padding-bottom:1px;font-size:11pt;} ' +

'</style>' +

'</head>' +

'<body><table cellpadding=2 cellspacing=2 border=2>' +

'<tr bgcolor=#FFEFD3><td align=center>Server Name</td>' +

'<td align=center>Volume</td>' +

'<td align=center>CapacityGB</td>' +

'<td align=center>UsedGB</td>' +

'<td align=center>FreeGB</td>'+

'<td align=center>%Free</td>'+

'<td align=center>FileID</td>'+

'<td align=center>LogicalName</td>'+

'<td align=center>BaseContainer</td>'+

'<td align=center>TotalSizeGB</td>'+

'<td align=center>UsedSpaceGB</td>'+

'<td align=center>FreeSpaceGB</td>'+

'<td align=center>FreeSpaceGB</td></tr>'

-- return output

Select @Body=(

SELECT

@@SERVERNAME As [TD],

Volume As [TD],

CAST(SizeGB as DECIMAL(10,2)) As [TD],

CAST((SizeGB - FreeGB) as DECIMAL(10,2)) As [TD],

CAST(FreeGB as DECIMAL(10,2)) As [TD],

CAST([%Free] as DECIMAL(10,2))As [TD],

T2.[FileID] As [TD],

T2.[LogicalName]As [TD],

T2.[BaseContainer] As [TD],

T2.[TotalSizeGB] As [TD],

T2.[UsedSpaceGB] As [TD],

T2.[FreeSpaceGB] As [TD],

T2.[PercentUsed] As [TD]

FROM(

SELECT distinct(volume_mount_point) Volume,

(total_bytes/1048576)/1024.00 as SizeGB,

(available_bytes/1048576)/1024.00 as FreeGB,

(select ((available_bytes/1048576* 1.0)/(total_bytes/1048576* 1.0) *100)) as '%Free'

FROM sys.master_files AS f CROSS APPLY

sys.dm_os_volume_stats(f.database_id, f.file_id)

group by volume_mount_point, total_bytes/1048576,

available_bytes/1048576

)T1

inner join

(

Select FileID As [FileID],

[Name] As [LogicalName],

[FileName] As [BaseContainer],

-- substring([FileName],0,4),

isnull([DATA],0) + isnull([DELTA],0) +isnull([FREE],0) + isnull([ROOT],0) As [TotalSizeGB],

isnull([DATA],0) + isnull([DELTA],0) + isnull([ROOT],0)As [UsedSpaceGB],

[FREE] As [FreeSpaceGB],

cast((isnull([DATA],0) + isnull([DELTA],0) + isnull([ROOT],0))/ ( isnull([DATA],0) + isnull([DELTA],0) +isnull([FREE],0) + isnull([ROOT],0))*100 as decimal(5,2)) As [PercentUsed],

DB_ID() As [DatabaseID]

From (Select CF.container_id As FileID,

CF.file_type_desc As FileType,

Cast(Round(Sum(file_size_in_bytes)/1024.0/1024/1024, 3) As Decimal(9,3)) As FileSizeGB,

DF.[name] As [Name],

DF.physical_name As [FileName]

From sys.dm_db_xtp_checkpoint_files As CF

Inner join sys.database_files As DF On DF.file_id = CF.container_id

Group By DF.[file_id],file_type_desc, CF.container_id, DF.[name], DF.physical_name) As Base

Pivot (Sum(FileSizeGB)

For FileType In ([DATA], [DELTA], [FREE], [ROOT])) As Pvt

)T2

on T1.Volume=substring(T2.[BaseContainer],0,4)

For XML raw('tr'), Elements

)

Select @HTML = @Header + @body + '</table></body></html>';

PRINT @HTML

db 메일 구성을 사용하여 이메일을 보내기 위해 코드를 더욱 단순화 할 수 있습니다.

성능 카운터

SQL Server 메모리 내 OLTP 엔진은 모니터링 및 문제 해결에 도움이되는 성능 카운터를 제공합니다. 아래 쿼리를 실행하여 현재 사용 가능한 성능 카운터를 확인할 수 있습니다.

SELECT object_name as ObjectName, counter_name as CounterName,

cntr_value, cntr_type

FROM sys.dm_os_performance_counters WHERE object_name LIKE '%XTP%';

SQL Server는 성능 모니터에서 SQL Server 메모리 내 OLTP 활동을 모니터링하는 데 사용할 수있는 개체와 카운터를 제공합니다. 개체 및 카운터는 SQL Server 2014 (12.x)부터 컴퓨터에있는 지정된 SQL Server 버전의 모든 인스턴스에서 공유됩니다.

노트 :

이전 버전에서 성능 개체 / 카운터 이름은 SQL Server 메모리 내 개체에 대해 XTP로 시작하는 데 사용되었습니다. 이제 SQL Server 2016 (13.x)부터 이름은 다음 패턴과 같습니다.

SQL Server <버전> XTP 트랜잭션 로그

이 경우 SQL Server 2016 XTP 트랜잭션 로그

다음 SQL을 실행하여 Perfmon을 사용하여 카운터의 유효성을 검사하고 모니터링합니다.

CREATE TABLE dbo.InMemorySQLshackDemo

(ID INT,

AuthorName VARCHAR(100) NULL,

CONSTRAINT PK_InMemorySQLshackDemo_ID PRIMARY KEY NONCLUSTERED HASH(ID)

WITH(BUCKET_COUNT = 10000)

) WITH(MEMORY_OPTIMIZED = ON, DURABILITY = SCHEMA_AND_DATA);

INSERT INTO [dbo].InMemorySQLshackDemo

([ID],

AuthorName

)

VALUES

(1,

'Prashanth Jayaram'

);

GO

SELECT *

FROM InMemorySQLshackDemo;

SET NOCOUNT ON;

DECLARE @id INT= 100;

BEGIN TRAN;

WHILE @id < 500

BEGIN

INSERT INTO InMemorySQLshackDemo(id,

AuthorName)

VALUES(@id,

'Prashanth');

SET @id = @id + 1;

END;

WAITFOR DELAY '00:00:15';

COMMIT TRAN;

GO

SET NOCOUNT ON;

DECLARE @id INT= 5000;

BEGIN TRAN;

WHILE @id < 5050

BEGIN

INSERT INTO InMemorySQLshackDemo(id,

AuthorName)

VALUES(@id,

'Prashanth');

SET @id = @id + 1;

END;

WAITFOR DELAY '00:00:15';

ROLLBACK TRAN;

노트 :

SQL Server 메모리 내 데이터베이스 실행 취소 트랜잭션이 기록되지 않습니다. 이 경우 세이브 포인트 롤백 트랜잭션에 대한 작은 스파이크를 볼 수 있습니다.

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

SQL Server 2019 메모리 최적화 TempDB 메타 데이터 (0)	2020.09.24
SQL Server 메모리 내 데이터베이스 내부 메모리 구조 모니터링 (0)	2020.09.24
SQL Server의 메모리 내 OLTP에 대한 간략한 개요 (0)	2020.09.24
SQL Server 접속 네트워크 프로토콜 (0)	2020.09.24
NUMA를 이용한 포트별로 물리적 CPU 분산 (0)	2020.09.10

메모리 내 OLTP 진화의 세부 사항
메모리 내 최적화 된 테이블을 보여주는 샘플 제공
템플릿을 사용하여 메모리 내 최적화 테이블 만들기
메모리 내 메모리 사용 고려 사항 설명
그리고 더…

SQL Database의 메모리 내 기술은 응용 프로그램의 성능을 향상시킬 수 있으며 데이터 수집, 데이터로드 및 분석 쿼리와 같은 워크로드에 적합한 선택입니다.

빠르게 추진되는 혁신에 적응하고 새로운 경쟁을 충족하는 비즈니스의 능력은 도전입니다. DBA의 까다로운 작업은 항상 변화하는 요구 사항을 채택하고 서비스 및 기업의 요구 사항을 충족하는 올바른 설계 전략을 공식화하는 경향이 있습니다. 우리 중 많은 사람들이 확장 옵션의 원활한 통합을위한 절충 요소에 대해 의문을 갖게 될 것이며 고성능 컴퓨팅 시스템을 설계하려면 더 나은 하드웨어 구성에 크게 의존합니다. 메모리 비용 감소, 멀티 코어 프로세서, CPU 클럭 속도 증가와 같은 하드웨어 추세가 급증하는 하드웨어 시대는 인 메모리 컴퓨팅의 아키텍처 설계를 촉진했습니다.

성능이 핵심이고 시스템이 거의 실시간 데이터에 대해 작동해야하는 시스템에서 In-Memory 기술 솔루션이 선택됩니다. 기술이 유행하는 방식은 이러한 새로운 기능의 진화로 이어집니다.

소개

메모리 내 OLTP는 SQL Server에 통합 된 메모리 최적화 된 관계형 데이터 관리 엔진이자 네이티브 저장 프로 시저 컴파일러입니다. Microsoft는 가장 까다로운 OLTP 워크로드를 처리하기 위해 메모리 내 OLTP를 설계했습니다. 대부분의 경우 모든 로깅 및 I / O를 방지하기 위해 DURABILITY = SCHEMA_ONLY 옵션을 사용하여 메모리 최적화 테이블을 만들 수 있습니다.

메모리 내 OLTP는 다음 개념을 도입합니다.

메모리 내 최적화 된 테이블 및 인덱스
비 내구성 테이블, 기존 임시 테이블
고유하게 컴파일 된 저장 프로 시저 및 UDF
테이블 변수에 대한 메모리 최적화 테이블 유형 – 임시 개체의 대체로 사용할 수 있습니다.
그리고 더…

메모리 내 OLTP 시스템의 의미

짧은 대기 시간, 높은 처리량, 빠른 응답 시간
고효율
고성능
잠금 에스컬레이션이 없거나 전혀없는 관리는 낙관적 동시성 모델, 더 나은 동시성 관리를 통해 이루어집니다.

메모리 내 OLTP 권장?

다음 조건 중 하나 이상이 "예"인 시스템이 메모리 내 OLTP로 마이그레이션 할 때 얻을 수있는 잠재적 인 이점을 심각하게 고려하십시오.

성능 및 확장 성 향상이 필요한 기존 SQL Server (또는 기타 관계형 데이터베이스) 애플리케이션
데이터베이스 병목 현상을 겪고있는 RDBMS – 주로 잠금 / 래칭 또는 코드 실행 관련
인지 된 성능 오버 헤드로 인해 중요한 성능 경로에서 관계형 데이터베이스를 사용하지 않는 환경

혜택

경합 제거
I / O 로깅 최소화
효율적인 데이터 검색
코드 실행 시간 최소화
CPU 효율성
I / O 감소 / 제거

한정

테이블에는 하나 이상의 인덱스가 있어야합니다.
HEAP의 개념 없음
응용 프로그램 잠금을 제외하고 메모리 내 OLTP는 표준 SQL Server 쿼리와 같은 레코드를 잠그는 기능을 제공하지 않습니다.
메모리 제한

앞서 언급했듯이 메모리 최적화 테이블을 구성하는 데이터 구조는 모두 메모리에 저장되며 기존 B- 트리 개체와 달리 내구성있는 저장소로 지원되지 않습니다. 메모리 최적화 행을 저장하기에 충분한 메모리를 사용할 수없는 시나리오는 문제가 될 수 있습니다. 마이그레이션을 평가할 때 필요한 메모리 크기를 결정하십시오. 추가 메모리 할당이 필요한 여러 버전의 행을 생성 할 수있는 워크로드를 고려하는 것도 중요합니다.

메모리 내 OLTP 설계 고려 사항

각 비즈니스 트랜잭션의 시간을 줄이는 것은 전반적인 성능 측면에서 중요한 목표가 될 수 있습니다. Transact-SQL 코드를 고유하게 컴파일 된 저장 프로 시저로 마이그레이션하고 트랜잭션 실행 대기 시간을 줄이는 것은 전반적인 사용자 환경을 개선하는 데 중요한 요소입니다.

시작하기

memory_optimimized_data 옵션을 사용하여 파일 그룹 만들기
그룹에 논리 파일 구현
memory_optimization 옵션을 사용하여 테이블 생성

참고 : Azure SQL Database에서 메모리 내 기술은 프리미엄 서비스 계층에서만 사용할 수 있습니다. 다음 기사에서 이에 대해 더 자세히 설명하겠습니다.

파일 그룹 만들기

ALTER DATABASE AdventureWorks2014

ADD FILEGROUP InMemAdventureWorks2014FG CONTAINS MEMORY_OPTIMIZED_DATA ;

ALTER DATABASE AdventureWorks2014

ADD FILE (

NAME=InMemAdventureWorks2014File,

FILENAME='f:\PowerSQL\InMemAdventureWorks2014File')

TO FILEGROUP InMemAdventureWorks2014FG;

이제 데이터베이스에 대해 MEMORY_OPTIMIZED_DATA 옵션이 활성화되었는지 확인하겠습니다.

SELECT

sfg.name Name,

sdf.name logicalFileName,

sfg.type_desc,

sdf.physical_name

FROM sys.filegroups sfg JOIN sys.database_files sdf ON sfg.data_space_id = sdf.data_space_id

WHERE sfg.type = 'FX' AND sdf.type = 2

또는

데이터베이스 속성을 찾아보고 파일 그룹 옵션을 선택합니다. 오른쪽 창에서 memory_optimized_data를 볼 수 있습니다.

이 섹션에서는 인라인 메모리 최적화 기술에 필요한 테이블 생성 및 다양한 매개 변수 사용에 대해 다룹니다.

MEMORY_OPTIMIZED = ON : 테이블이 메모리에 최적화되었습니다.
DURABILITY = SCHEMA_ONLY : 서버 재부팅 시까 지 사용 가능한 스키마 및 데이터입니다. 다시 시작한 후 유일한 스키마는 메모리에 있습니다.
1. 응용 프로그램에 대한 세션 상태 관리 유지
2. ETL 시나리오에서 스테이징 테이블로 일반적으로 사용됨
3. 임시 테이블
DURABILITY = SCHEMA_AND_DATA : 메모리에서 항상 사용 가능한 스키마 및 데이터. 데이터는 메모리에 영구적이며 메모리 최적화 테이블을 만들 때 기본 설정입니다.

SCHEMA_ONLY

다음 예제에서는 내구성 옵션 SCHEMA_ONLY를 사용하여 InsertInMemDemo 라는 메모리 내 최적화 된 테이블 을 만듭니다.

CREATE TABLE InsertInMemDemo

(

Id INT NOT NULL,

data varchar(25)

constraint pk_id_1 primary key nonclustered(id))

WITH

(MEMORY_OPTIMIZED=ON, DURABILITY=SCHEMA_ONLY)

다음으로 InsertDemo 테이블의 더미 데이터를 In-Memory 최적화 테이블에 삽입하고 테이블 간의 레코드 수를 확인합니다. 이것은 단지 예입니다. 여러 가지 방법으로 레코드를 삽입 할 수 있습니다.

이제 SQL 인스턴스를 다시 시작하겠습니다. 이것은 새로 생성 된 메모리 내 테이블의 지속성을 테스트하기위한 것입니다. 아래 이미지에서 새로 생성 된 메모리 내 최적화 된 테이블 지속성은 일시적이며 메모리에 바인딩되어 있음을 알 수 있습니다. 인스턴스가 다시 시작될 때마다 데이터가 메모리에서 플러시됩니다.

SCHEMA_AND_DATA

다음 예제는 데이터 지속성과 고성능으로 메모리 내 최적화 된 테이블을 생성했습니다.

CREATE TABLE InsertInMemDemo

(Id INT NOT NULL,

data VARCHAR(25)

CONSTRAINT pk_id_1 PRIMARY KEY NONCLUSTERED (id)

) WITH(MEMORY_OPTIMIZED = ON, DURABILITY = SCHEMA_AND_DATA);

다음 T-SQL 문은 메모리 내 최적화 된 테이블에 대한 기존 개체의 성능 영향을 보여주는 예입니다.

SET NOCOUNT ON;

DECLARE @start DATETIME= GETDATE();

DECLARE @id INT= 1;

WHILE @id < 10000

BEGIN

INSERT INTO InsertInMemDemo

(id,

data

)

VALUES

(@id,

'SQLShackDemo'

);

SET @id = @id + 1;

END;

SELECT DATEDIFF(s, @start, GETDATE()) AS [MemInsert];

GO

DECLARE @start DATETIME= GETDATE();

DECLARE @id INT= 1;

WHILE @id < 10000

BEGIN

INSERT INTO InsertDemo

(id,

data

)

VALUES

(@id,

'SQLShackDemo'

);

SET @id = @id + 1;

END;

DATEDIFF(s, @start, GETDATE()) AS [Insert];

DROP TABLE InsertInMemDemo;

DROP TABLE InsertDemo;

샘플 출력은 기존의 비 메모리 최적화 개체보다 37 배 빠르다는 것을 증명합니다.

메모리 크기 고려 사항

각 델타 파일의 크기는 메모리가 16GB보다 큰 컴퓨터의 경우 약 16MB, 16GB 이하의 컴퓨터의 경우 1MB입니다. SQL Server 2016부터 SQL Server는 저장소 하위 시스템이 충분히 빠르다고 판단되는 경우 대형 검사 점 모드를 사용할 수 있습니다. 대형 검사 점 모드에서 델타 파일의 크기는 128MB입니다.

인 메모리 최적화 테이블-데이터는 데이터 및 델타 파일 쌍에 저장됩니다. 체크 포인트 파일 쌍 (CFP)이라고도합니다. 데이터 파일은 DML 명령을 저장하는 데 사용되며 델타 파일은 삭제 된 행에 사용됩니다. DML 작업 중에 많은 CFP가 생성되므로 복구 시간과 디스크 공간 사용량이 늘어납니다.

다음 예에서 샘플 테이블 dbo. InMemDemoOrderTBL은 T-SQL 코드 조각을 사용하여 더미 값으로 생성되고로드 된 다음 메모리 계산이 이어집니다.

CREATE TABLE dbo.InMemDemoOrderTBL

(Order_ID INT NOT NULL,

CustomerName VARCHAR(25),

Order_Date DATETIME DEFAULT GETDATE(),

Description NVARCHAR(100),

CONSTRAINT PK_ID PRIMARY KEY NONCLUSTERED(Order_ID)

) WITH(MEMORY_OPTIMIZED = ON, DURABILITY = SCHEMA_AND_DATA);

다음 T-SQL은 샘플 행을 생성하는 데 사용되는 코드입니다.

SET NOCOUNT ON;

DECLARE @start DATETIME= GETDATE();

DECLARE @id INT= 1;

WHILE @id < 500

BEGIN

INSERT INTO InMemDemoOrderTBL(Order_ID,

CustomerName,

Description)

VALUES(@id,

'SQLShackDemo',

'Table and Row Size Computation example');

SET @id = @id + 1;

END;

SELECT *

FROM InMemDemoOrderTBL;

문자열 함수 DATALENGTH는 설명 열의 크기를 생성하는 데 사용됩니다.

SELECT AVG(DATALENGTH(Description)) AS TEXTFieldSize FROM InMemDemoOrderTBL

크기는 SUM ([데이터 유형 크기])으로 계산됩니다.

비트 : 1
Tinyint : 1
Smallint : 2
정수 : 4
실제 : 4
Smalldatetime : 4
스몰 머니 : 4
Bigint : 8
일시 : 8
Datetime2 : 8
플로트 : 8
돈 : 8
숫자 (정밀도 <= 18) : 8
시간 : 8
숫자 (정밀도> 18) : 16
고유 식별자 : 16

다음 표는 데이터 및 인덱스의 크기를 추정하는 데 필요한 주요 지표를 정의합니다.

헤더 유형	데이터 구조	바이트
RowHeader	32
TS 시작	8
TS 종료	8
StmtID	4
IdxLinCount	2
IndexPointerArray	8
행 데이터	페이로드 (= 4 (Order_ID) +24 (CustomerName) +8 (Order_Date) +76 (설명의 평균 길이))	106

크기 데이터 및 인덱스 크기를 추정하려면 다음 공식을 사용하십시오.

데이터 크기	[RowHeaderBytes + Index * (인덱스 당 8 바이트) + RowData] * No_Of_Rows
{[(32+ (1 * 8) +113] * 499} / 1024	74.55762
인덱스 크기	[PointerSize (idxLinCount + IndexPointArray) + sum (keyColumnDataTypes)] * No_of_Rows
((2 + 8 + 4) * 499) / 1024	6.822266
테이블 크기	DataSize + IndexSize	81.39355

참고 : 계산 된 데이터 및 인덱스 크기는 거의 정확한 값입니다. 행 버전의 경우 추가 75kb가 필요하며 추가 성장을 위해서는 30 % (~ 25kb)를 예약하는 것이 좋습니다. 인 메모리 최적화 테이블을 효율적으로 처리하려면 총 180kb의 메모리가 필요합니다.

SELECT *

FROM sys.dm_db_xtp_table_memory_stats

WHERE object_id = OBJECT_ID('dbo.InMemDemoOrderTBL');

요약:

이 기사에서는 In-Memory 최적화 테이블과 그 기능에 대한 다양한 개념을 다루었습니다. 메모리 고려 사항 및 요구 사항의 의미를 이해하기 위해 중요한 부분을 포함했습니다. 한계를 아는 것이 좋습니다. 이에 대해서는 다음 기사에서 자세히 설명하겠습니다. 오늘날 세계에서 기술은 매우 중요하며 성능 측면에서 측정됩니다. 메모리 내 OLTP는 SQL 2014에 도입 된 메모리 중심 기능입니다. SQL Server 엔진에 통합되고 최신 하드웨어 용으로 설계된 고성능 메모리 최적화 엔진입니다.

막대한 트랜잭션 오버 헤드가있는 기존 OLTP 시스템은 휘발성 공간에서 트랜잭션 관리를 평가하고 애플리케이션 실행 성능을 크게 향상시킵니다. 이 기능을 올바르게 사용하면 성능이 크게 향상 될 수 있습니다.

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

SQL Server 메모리 내 데이터베이스 내부 메모리 구조 모니터링 (0)	2020.09.24
SQL Server 메모리 내 데이터베이스 개체의 내부 데이터 구조를 모니터링하는 방법 (0)	2020.09.24
SQL Server 접속 네트워크 프로토콜 (0)	2020.09.24
NUMA를 이용한 포트별로 물리적 CPU 분산 (0)	2020.09.10
SQL Server 인덱스 활성 / 비활성 하기 (0)	2020.08.31

Client 에서 SQL Server로 연결하기 위해서는 어떠한 프로토콜들을 사용할까요?

많은 분들은 IP를 사용하죠. (IP 또는 컴퓨터이름)

그렇다면, 접속 시 사용하는 프로토콜은 어떠한 것들이 있을까요?

공유 메모리, TCP/IP, 명명된 파이프, VIA 가 그것들입니다.

해당 프로토콜의 사용유무는 SQL Server 구성관리자(SQL Server Configuration Manager)에서 확인과 설정이 가능합니다.

1. 공유 메모리(Shared Memory)

A. 서버에서 직접 접속할 때 사용할 때 쓸 수 있습니다. 즉 로컬컴퓨터가 서버인 경우에만 됩니다.

B. 접속형태는 <servername>[\instancename] 입니다.

C. “.”이나 “(local)” 또는 “localhost”와 같은 형태로 사용이 가능합니다.

D. 동일한 컴퓨터인 경우 가장 우수한 성능을 보입니다.

E. 포트를 지정할 수 없습니다.

F. 접속상태를 확인하면 다음과 같습니다.

SELECT net_transport

FROM sys.dm_exec_connections

WHERE session_id = @@SPID;

2. TCP/IP

A. 가장 많이 사용하는 형태입니다.

B. 서버주소를 IP 주소 또는 컴퓨터 이름을 사용합니다.

C. 기본 포트는 1433입니다.

D. TCP: <servername>[\instancename>],<port> 또는 TCP:<ipaddress>[\instancename],<port>와 같은 형태로 사용이 가능하며,

“TCP:” 는 생략할 수 있습니다. 또한, 기본 포트 1433을 사용할 때에는 포트번호를 생략할 수 있습니다.

E. 로컬컴퓨터라도 “127.0.0.1”과 같이 IP Address를 사용하면 공유메모리가 아닌 TCP/IP로 동작합니다.

F. 접속상태를 확인하면 다음과 같습니다.

SELECT net_transport

FROM sys.dm_exec_connections

WHERE session_id = @@SPID;

3. 명명된 파이프(Named Pipe)

A. 간혹 사용하는 경우가 있지만 그다지 많이 사용하지 않는 방식입니다.

B. 구성관리자에 명시된 이름을 사용합니다.

C. 기본포트는 445 이며, 포트를 변경할 수 없습니다.

D. np:\\<ipaddress>\pipe\sql\query 또는 np:\\<computer_name>\pipe\sql\query 의 형태로 사용이 가능하며,
“np:”는 생략할 수 있습니다.

E. 위 예제는 제가 일부러 \\.\pipe\sql\query 에서 \\.\pipe\sql\query1 로 바꾼 것이며,

F. 구성관리자에서 다음과 같이 변경하였습니다.

G. 파이프명이 일치하지 않을 경우 다음과 같이 접속오류가 발생합니다.

H. 접속상태를 확인하면 다음과 같습니다.

SELECT net_transport

FROM sys.dm_exec_connections

WHERE session_id = @@SPID;

4. VIA(Virtual Interface Adapter)

A. VIA 프로토콜은 하드웨어와 함께 동작하기 때문에 거의 사용하지 않고 있습니다.

B. 또한, 다음 버전부터는 제거된다고 합니다.

참고 URL : http://msdn.microsoft.com/ko-kr/library/ms190611.aspx

출처: https://windtrap.tistory.com/75 [곰탱이의 개인블로그]

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

SQL Server 메모리 내 데이터베이스 개체의 내부 데이터 구조를 모니터링하는 방법 (0)	2020.09.24
SQL Server의 메모리 내 OLTP에 대한 간략한 개요 (0)	2020.09.24
NUMA를 이용한 포트별로 물리적 CPU 분산 (0)	2020.09.10
SQL Server 인덱스 활성 / 비활성 하기 (0)	2020.08.31
쿼럼 리소스 이동 (0)	2020.08.29

–Find All the Blocked Processes

SELECT

spid,

status,

loginame=SUBSTRING(loginame,1,12),

hostname=SUBSTRING(hostname,1, 12),

blk = CONVERT(char(3), blocked),

dbname=SUBSTRING(DB_NAME(dbid),1, 10),

cmd,

waittype

FROM master.dbo.sysprocesses

WHERE spid IN (SELECT blocked FROM master.dbo.sysprocesses)

–Kill all the Blocked Processes of a Database

DECLARE @DatabaseName nvarchar(50)

Set the Database Name

SET @DatabaseName = N’Datbase_Name’

Select the current Daatbase

SET @DatabaseName = DB_NAME()

DECLARE @SQL varchar(max)

SET @SQL = ”

SELECT @SQL = @SQL + ‘Kill ‘ + Convert(varchar, SPId) + ‘;’

FROM MASTER..SysProcesses

WHERE DBId = DB_ID(@DatabaseName) AND SPId <> @@SPId

and spid IN (SELECT blocked FROM master.dbo.sysprocesses)

–You can see the kill Processes ID

–SELECT @SQL

–Kill the Processes

EXEC(@SQL)

Or You can use the following script

— Script to kill all blocked processes

declare @max_count int, @count int, @sqlstring varchar(100)

declare @spid_table table (spid int NOT NULL)

INSERT @spid_table

select spid

from master.dbo.sysprocesses

where spid in (select blocked from master.dbo.sysprocesses where blocked <> 0) and blocked = 0

select @max_count = MAX(spid) FROM @spid_table

select top 1 @count = spid from @spid_table

while @count <= @max_count

begin

select @sqlstring = ‘kill ‘ + CONVERT(varchar(4), @count)

exec(@sqlstring)

print @sqlstring

IF @count = @max_count

begin

break

end

ELSE

BEGIN

select top 1 @count = spid FROM @spid_table where spid > @count

end

Similarly DBA can kill all the Processes of a Database as

— Kill all the Processes of a Database

DECLARE @DatabaseName nvarchar(50)

–Set the Database Name

SET @DatabaseName = N’Datbase_Name’

Select the current Daatbase

SET @DatabaseName = DB_NAME()

DECLARE @SQL varchar(max)

SET @SQL = ”

SELECT @SQL = @SQL + ‘Kill ‘ + Convert(varchar, SPId) + ‘;’

FROM MASTER..SysProcesses

WHERE DBId = DB_ID(@DatabaseName) AND SPId <> @@SPId

–You can see the kill Processes ID

–SELECT @SQL

–Kill the Processes

EXEC(@SQL)

—Identify the blocking query

SELECT
db.name DBName,
tl.request_session_id,
wt.blocking_session_id,
OBJECT_NAME(p.OBJECT_ID) BlockedObjectName,
tl.resource_type,
h1.TEXT AS RequestingText,
h2.TEXT AS BlockingTest,
tl.request_mode
FROM sys.dm_tran_locks AS tl
INNER JOIN sys.databases db ON db.database_id = tl.resource_database_id
INNER JOIN sys.dm_os_waiting_tasks AS wt ON tl.lock_owner_address =wt.resource_address
INNER JOIN sys.partitions AS p ON p.hobt_id =tl.resource_associated_entity_id
INNER JOIN sys.dm_exec_connections ec1 ON ec1.session_id =tl.request_session_id
INNER JOIN sys.dm_exec_connections ec2 ON ec2.session_id =wt.blocking_session_id
CROSS APPLY sys.dm_exec_sql_text(ec1.most_recent_sql_handle) AS h1
CROSS APPLY sys.dm_exec_sql_text(ec2.most_recent_sql_handle) AS h2
GO

-- Script to view all current processes / sessions on the server

select * from master.dbo.sysprocesses

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

DELETE 복원 프로시저 (0)	2020.10.20
EXEC와 동적(adhoc) 쿼리 (0)	2020.10.05
오래된 커서 (0)	2020.09.11
테이블 명세서 (0)	2020.08.29
sp_who3 (0)	2020.08.29

SELECT creation_time, cursor_id, name, c.session_id, login_name   
FROM sys.dm_exec_cursors(0) AS c   
JOIN sys.dm_exec_sessions AS s ON c.session_id = s.session_id   
WHERE DATEDIFF(HH, c.creation_time, GETDATE()) >= 1;  -- 시간
GO

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

EXEC와 동적(adhoc) 쿼리 (0)	2020.10.05
Find and Kill all the Blocked Process/Query (0)	2020.09.22
테이블 명세서 (0)	2020.08.29
sp_who3 (0)	2020.08.29
특정 문자열 포함하는 프로시저 (0)	2020.08.28

1. 물리적 CPU 리소스 분산이 필요한 경우

하나의 SQL Server에서 다른 타입의 쿼리가 수행되는 경우에

원활한 서비스를 위해 물리적인 리소스(CPU)를 분산 시켜 주어야 하는 이슈가 있을 수 있습니다.

예를 들어보면, 하나는 CPU자원을 많이 사용하지는 않지만, 많이 요청되는 OLTP성 쿼리,

다른 하나는 많이 수행되지는 않지만 CPU자원을 많이 사용하는 배치성 쿼리인 환경을 볼 수 있습니다.

이러한 경우에 배치성 쿼리로 인해 OLTP 쿼리 성능에 영향을 줄 수 있습니다.

이때 해결 할 수 있는 방법으로는 각각의 쿼리 타입마다 물리적인 리소스(CPU)를 할당하여
같은 다른 타입의 형태의 쿼리가 같은 CPU를 사용하지 못하게 한다면, 위와 같은 문제를 처리할 수 있습니다.

2. 해결할 수 있는 방법들

가장 먼저 떠오르는 방법은,
하나의 물리적인 머신에 두 개의 인스턴스를 올리고, affinity mask를 설정하는 것입니다.

또한 물리적인 메모리를 각각의 인스턴스에서 설정하여, 메모리 크기도 조절할 수 있는
장점이 있습니다. 하지만, 인스턴스가 다른 관계로 데이터를 동기화 해주어야 하는 부담이
존재하게 되고 전체적인 운영비용이 높아질 수 밖에 없습니다.

뭐~ 다른 방법으로 그냥 MAXDOP를 줘서 CPU사용을 제한 하는 것도 방법일 수 있지만,
MAXDOP의 경우 물리적인 CPU 지정 및 사용량을 지정을 할 수 없기에
배치가 수행되는 CPU에서 수행되는 OLTP성 쿼리는 여전히 문제가 될 수 있습니다.

음~ 그럼 SQL Server 2008의 리소스 관리자를 생각할 수 있습니다.

하지만 SQL Server 2008 Enterprise에서만 지원하는 제약이 있습니다.

3. 또 하나의 방법 NUMA

여기서 소개하려는 방법은 NUMA 를 이용한 방법 입니다.
상황에 따라 최선의 솔루션은 달라지기에 모든 상황에 대한 최선의 솔루션이라고는 볼 수 없습니다.
그리고 여기서는 NUMA를 소개하고자 하는 것이
아니기에 NUMA에 대한 소개는 건너뛰겠습니다.

요즘 판매되는 네할렘 기반의 프로세스는 메모리 컨트롤러가 CPU내부에 위치하고 있어
기본적으로 2CPU 이상인 경우 NUMA를 지원하고 있습니다.

만약 NUMA를 지원하지 않는 환경이라도 soft NUMA를 설정 할 수 있기에 크게 상관은 없습니다.

그럼 NUMA를 가지고 어떻게 할 수 있을까요?

BOL을 보면 NUMA 노드 별로 TCP/IP 포트를 설정할 수 있는 방법을 소개 하고 있습니다.
바로 이 설정으로 쿼리 타입별 CPU 리소스를 분리 할 수 있습니다.
만약 하드웨어 NUMA로 지원되는 노드가 아니라 더 세분하게 CPU 리소스를 분산하고자 한다면
soft NUMA를 추가적으로 설정해서 처리할 수 있습니다.

간단하게 예를 들면 1000포트는 0번 NUMA 노드, 2000번 포트는 1번 NUMA 노드,
3000번 포트는 모든 NUMA를 사용하게 설정 할 수 있습니다.

이렇게 설정하게 된 후 각각의 클라이언트에서 각 쿼리 타입별로 사용 포트를 변경해서

사용하면 CPU 리소스 간섭 없이 처리 할 수 있습니다.

간단한 구성 방법은 아래를 참고하시길 바랍니다.

[soft NUMA 설정 방법] http://msdn.microsoft.com/ko-kr/library/ms345357.aspx

Soft NUMA를 설정하거나, NUMA를 지원하는 장비라면 sp_readerrorlog에서 아래와 같은 메시지를 확인할 수 있습니다.

2009-08-28 15:10:23.940 서버 Node configuration: node 0: CPU mask: 0x000000aa Active CPU mask: 0x000000aa.
2009-08-28 15:10:23.940 서버 Node configuration: node 1: CPU mask: 0x00000055 Active CPU mask: 0x00000055.

[NUMA 노드에 TCP/IP 포트 매핑] http://msdn.microsoft.com/ko-kr/library/ms345346.aspx

TCP/IP의 포트를 NUMA노드에 맵핑 후 재시작 하면 SP_READERORLOG 에서는 아래와 같이 확인할 수 있습니다.

2009-08-28 15:10:24.860 서버 Server is listening on [ 'any' <ipv4> 3000].
2009-08-28 15:10:24.860 서버 SQL Server Network Interfaces initialized listeners on node 0 of a multi-node (NUMA) server configuration with node affinity mask 0x00000003.
2009-08-28 15:10:24.860 서버 Server is listening on [ 'any' <ipv4> 1000].
2009-08-28 15:10:24.860 서버 SQL Server Network Interfaces initialized listeners on node 0 of a multi-node (NUMA) server configuration with node affinity mask 0x00000001.
2009-08-28 15:10:24.860 서버 Server is listening on [ 'any' <ipv4> 2000].
2009-08-28 15:10:24.860 서버 SQL Server Network Interfaces initialized listeners on node 1 of a multi-node (NUMA) server configuration with node affinity mask 0x00000002.

각각의 포트별로 스트레스를 주고 작업관리자를 확인해 보면, NUMA 노드 만을 사용하는 것을 확인할 수 있습니다

1000번 포트 사용	2000번 포트 사용	3000번 포트 사용

출처: https://hyoksong.tistory.com/42 [Hyok Song]

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

SQL Server의 메모리 내 OLTP에 대한 간략한 개요 (0)	2020.09.24
SQL Server 접속 네트워크 프로토콜 (0)	2020.09.24
SQL Server 인덱스 활성 / 비활성 하기 (0)	2020.08.31
쿼럼 리소스 이동 (0)	2020.08.29
SQL Server Logs 파일 수 변경 및 로그 순환(CYCLE) (0)	2020.08.29

SQL Server 인덱스 활성 / 비활성 하기

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

데이터베이스를 운영하다 보면 수 많은 인덱스를 접하게 된다. 하지만 여기 있는 인덱스가 모두 사용 되는 것일까? 인덱스의 유용 여부를 파악하기란 쉽지 않다. 그렇다고 잘 쓰지 않을 것 같다고 생각되는 인덱스를 무심코 삭제 했다가는 정말 난감한 상황이 발생 한다. 만약 어떤 인덱스가 있을 때 이 인덱스의 유용 여부에 대해서 판단하고 싶을 때 어떻게 할까?

SQL Server 2005 이상 버전에서는 테이블 또는 뷰에 대해서 인덱스를 활성 / 비활성 할 수 있다. 인덱스 사용을 비활성화 하면 인덱스를 사용하지 않아 인덱스에 따른 영향을 평가 할 수 있다.

우선 현재 인덱스를 살펴 보자. 다음과 같이 클러스터드 인덱스가 생성되어 있으며 활성 상태인 것을 확인 할 수 있다.

SELECT

NAME AS INDEXNAME, TYPE_DESC, FILL_FACTOR,

CASE WHEN IS_DISABLED = 0 THEN 'ENABLED'

WHEN IS_DISABLED = 1 THEN 'DISABLED' END AS [INDEXUSAGE]

FROM SYS.INDEXES

WHERE OBJECT_ID = OBJECT_ID('TBL_A')

GO

[인덱스 비활성화 만들기]

T-SQL을 이용하여 비활성 상태로 변경하자.

ALTER INDEX CI_TBL_A_COL1 ON TBL_A DISABLE

GO

SELECT

NAME AS INDEXNAME, TYPE_DESC, FILL_FACTOR,

CASE WHEN IS_DISABLED = 0 THEN 'ENABLED'

WHEN IS_DISABLED = 1 THEN 'DISABLED' END AS [INDEXUSAGE]

FROM SYS.INDEXES

WHERE OBJECT_ID = OBJECT_ID('TBL_A')

GO

SSMS에서 UI로도 가능 하다. 인덱스에서 마우스 오른쪽을 클릭하여 [사용 안 함]을 선택 한다.

또는 인덱스 속성 창에서 [옵션] – [인덱스 사용] 체크 박스를 해제 하면 비활성화 된다.

[인덱스 활성화 만들기]

인덱스를 활성화 하는 방법에는 어떤 방법이 있으까?

T-SQL을 이용하여 활성화 상태로 변경하자. 비활성 상태로 만들기 위해서 DISABLE 명령어를 사용하였다면 활성하는 REBUILD 명령어를 사용해야 한다.

ALTER INDEX CI_TBL_A_COL1 ON TBL_A REBUILD

GO

SELECT

NAME AS INDEXNAME, TYPE_DESC, FILL_FACTOR,

CASE WHEN IS_DISABLED = 0 THEN 'ENABLED'

WHEN IS_DISABLED = 1 THEN 'DISABLED' END AS [INDEXUSAGE]

FROM SYS.INDEXES

WHERE OBJECT_ID = OBJECT_ID('TBL_A')

GO

또는 인덱스를 새로 만들어야 한다.

CREATE CLUSTERED INDEX CI_TBL_A_COL1 ON TBL_A (COL1)

WITH (DROP_EXISTING = ON)

GO

SELECT

NAME AS INDEXNAME, TYPE_DESC, FILL_FACTOR,

CASE WHEN IS_DISABLED = 0 THEN 'ENABLED'

WHEN IS_DISABLED = 1 THEN 'DISABLED' END AS [INDEXUSAGE]

FROM SYS.INDEXES

WHERE OBJECT_ID = OBJECT_ID('TBL_A')

GO

SSMS의 UI에서도 가능 하다. 인덱스에서 마우스 오른쪽을 클릭하여 [다시 작성]을 선택 한다.

또는 인덱스 속성 창에서 [옵션] – [인덱스 사용] 체크박스를 선택하면 인덱스가 활성화 된다.

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

SQL Server 접속 네트워크 프로토콜 (0)	2020.09.24
NUMA를 이용한 포트별로 물리적 CPU 분산 (0)	2020.09.10
쿼럼 리소스 이동 (0)	2020.08.29
SQL Server Logs 파일 수 변경 및 로그 순환(CYCLE) (0)	2020.08.29
악성 쿼리 찾아내기 - ReadTrace (0)	2020.08.29

Database

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

TempDB 사용 요약

Shrink TempDB 활동 개요

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

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

소개

시작하다

T-SQL 스크립트 준비

T-SQL 스크립트

OStress 유틸리티

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

결론

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

소개

시작하기

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

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

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

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

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

SQL Server 메모리 내 컨테이너 정보 검색

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

SQL Server 메모리 내 데이터 구조 및 XML을 사용하여 HTML 이메일 작성

성능 카운터

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

소개

메모리 내 OLTP 시스템의 의미

메모리 내 OLTP 권장?

혜택

한정

메모리 내 OLTP 설계 고려 사항

시작하기

파일 그룹 만들기

SCHEMA_ONLY

SCHEMA_AND_DATA

메모리 크기 고려 사항

요약:

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

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

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

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

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

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

티스토리툴바