SQL Server 메모리 내 데이터베이스 개체의 내부 데이터 구조를 모니터링하는 방법

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 데이터베이스 파일이 있는지 여부에 관계없이 운영 체제에서 사용할 수있는 각 드라이브에 사용할 수있는 공간 만 표시합니다.

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><b>Server Name</b></td>' +                   '<td align=center><b>Volume</b></td>' +                   '<td align=center><b>CapacityGB</b></td>' +                   '<td align=center><b>UsedGB</b></td>' +                   '<td align=center><b>FreeGB</b></td>'+           '<td align=center><b>%Free</b></td>'+           '<td align=center><b>FileID</b></td>'+           '<td align=center><b>LogicalName</b></td>'+           '<td align=center><b>BaseContainer</b></td>'+           '<td align=center><b>TotalSizeGB</b></td>'+           '<td align=center><b>UsedSpaceGB</b></td>'+           '<td align=center><b>FreeSpaceGB</b></td>'+           '<td align=center><b>FreeSpaceGB</b></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 메모리 내 데이터베이스 실행 취소 트랜잭션이 기록되지 않습니다. 이 경우 세이브 포인트 롤백 트랜잭션에 대한 작은 스파이크를 볼 수 있습니다.