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

고정 드라이브 및 볼륨 정보 목록 만들기
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

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

클러스터 그룹 이름 확인

C:\> cluster group

통상적으로 영문일 경우 "Cluster Group" 한글일 경우 "클러스터 그룹" 으로 표시됨

쿼럼 리소스 이동

위 환경에서는 클러스터 그룹의 이름이 한글이므로 한글로 아래와 같이 입력

C:\>Cluster group "클러스터 그룹" /move:이동할서버(노드명)

* 클러스트 그룹이 영문일 경우 아래와 같이 이름만 변경

C:\>Cluster group "Cluster Group" /move:이동할서버(노드명)

Windows 2012에서의 쿼럼리소스 이동

Windows 2012 Failover cluster의 경우 Core 리소스 이동이라는 메뉴로

쿼럼리소스 및 디스크 이동을 클러스터 관리자(GUI)에서 할 수 있음

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

NUMA를 이용한 포트별로 물리적 CPU 분산 (0)	2020.09.10
SQL Server 인덱스 활성 / 비활성 하기 (0)	2020.08.31
SQL Server Logs 파일 수 변경 및 로그 순환(CYCLE) (0)	2020.08.29
악성 쿼리 찾아내기 - ReadTrace (0)	2020.08.29
Master Key (0)	2020.08.29

SQL Server 서비스가 시작될 때마다 새로운 로그 파일이 생성되게 됩니다. 즉, 서비스를 재시작하지 않으면 Error Log 파일 size 가 크게 늘어날 수 있습니다.

기본적으로 C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\Log 폴더에 ERRORLOG ~ ERRORLOG.n(1~6) 까지의 파일이 생성되게 됩니다. 기본값으로 로그 파일 개수를 6개로 제한하였기 때문입니다. 이것은 SQL Server 를 6번 재시작 한다면 그 이전 로그는 사라진다는 것을 의미합니다.

필요에 따라 Error Log 파일 개수를 변경할 수 있습니다.

SSMS - Management - SQL Server Logs - Configure - '재활용 이전의 오류 로그 파일 수 제한' 변경

아래 자료는 10개로 변경한 뒤 그 결과를 확인한 그림입니다.

T-SQL 로 확인해 봅니다.

EXEC xp_enumerrorlogs

보관 # 날짜 로그 파일 크기(바이트)

----------------------------------------------------------------

0 09/21/2009 10:47 1708

1 09/21/2009 10:47 1976

10 09/18/2009 11:13 311706

2 09/21/2009 10:47 1976

3 09/21/2009 10:47 1976

4 09/21/2009 10:47 1976

5 09/21/2009 10:47 1976

6 09/21/2009 10:47 1976

7 09/21/2009 10:47 11480

8 09/21/2009 10:25 11814

9 09/18/2009 11:58 13948

추가로 Registry NumErrorLogs 값을 변경할 수도 있습니다.

HKEY_LOCAL_MACHINE', N'Software\Microsoft\Microsoft SQL Server\MSSQL10.MSSQLServer\MSSQLServer', N'NumErrorLogs', REG_DWORD, 10

현재 설정된 값을 확인하기 위해서도 Reg 명령을 사용할 수 있습니다.

C:\>reg query "HKLM\Software\Microsoft\Microsoft SQL Server\MSSQL10.mssqlserver\mssqlserver" /v NumerrorLogs

그리고 또 하나!

SQL Server Error Log 파일의 size 가 늘어나면 SSMS 에서 불러오는 데 작은 고통을 느낄 수 있습니다. 서비스를 재시작 하지 않고 단지 로그 파일만 순환(CYCLE) 시키는 방법이 있으니 아래 T-SQL 을 참조하시면 되겠습니다.

EXEC sp_cycle_errorlog ;

[참고자료]

sp_cycle_errorlog(Transact-SQL)

http://msdn.microsoft.com/ko-kr/library/ms182512.aspx

출처: https://laigo.kr/356 [Lai Go's Blog]

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

SQL Server 인덱스 활성 / 비활성 하기 (0)	2020.08.31
쿼럼 리소스 이동 (0)	2020.08.29
악성 쿼리 찾아내기 - ReadTrace (0)	2020.08.29
Master Key (0)	2020.08.29
[DMV] 특정 프로시저의 실행계획이 바뀌었다? (0)	2020.08.29

일반적으로 튜닝은 전체적으로 다음과 같은 단계들로 이루어진다.

무엇이 문제인지를 확인하고 현재 상태를 진단하여 튜닝의 기준선(Baseline)을 정의
병목이 발생하는 지점이 어디인지를 확인
확인된 병목을 해소할 방법을 모색
해소방법을 적용한 후 다시 성능을 측정하여 (1단계 기준선 대비) 실제로 성능이 향상되었는지를 확인

그래서 튜닝의 고수들은 가장 중요한 것이 "성능의 측정기준을 확인하는 것"이라고도 하지만, 나같은 하수에게는 아무래도 그것보다는 병목을 찾아내는 부분이 더 관심이 가게 마련이다. 6^^

보통 Profiler 를 사용하여 현재 DB에서 실행되는 SQL을 확인하고 악성 SQL을 확인하게 되는데... 이 작업을 하다 보면 다음과 같은 문제가 발생한다.

Type 1 : 분당 300건씩 실행되는 SQL
SELECT IP_ADDR FROM IP_TABLE WHERE SABUN = '29472' -- 실행시간 500ms

SELECT IP_ADDR FROM IP_TABLE WHERE SABUN = '38749' -- 실행시간 500ms
SELECT IP_ADDR FROM IP_TABLE WHERE SABUN = '32945' -- 실행시간 500ms
...

Type 2 : 분당 1건씩 실행되는 SQL
SELECT SERIAL, IP_ADDR, START_DATE, END_DATE FROM IP_TABLE WHERE EMPNO NOT IN (SELECT EMPNO FROM INSATABLE WHERE STATUS = 'R') -- 실행시간 1000ms

위와 같은 두가지 유형의 SQL 중 어느것이 더 악성일까? 당연히 실행 빈도가 훨씬 높은 Type 1이다. 하지만 문제는 이걸 엄청나게 많은 SQL들이 섞여 있는 Profiler에서 육안으로 판단해내기가 쉽지 않다는 것이다. 따라서 악성 SQL을 확인할 때는 다음과 같이 SQL을 일반화해서 비교해야 한다.

Type 1 :
SELECT IP_ADDR FROM IP_TABLE WHERE SABUN = '##' -- 평균 실행시간 500ms, 총 실행시간 150000 ms

Type 2 :
SELECT SERIAL, IP_ADDR, START_DATE, END_DATE FROM IP_TABLE WHERE EMPNO NOT IN (SELECT EMPNO FROM INSATABLE WHERE STATUS = '##') -- 평균 실행시간 1000ms 실행시간 1000 ms

SQL Server 2000 에서는 수집한 SQL을 일반화하기 위해 Read80Trace 프로그램을 사용했었는데... SQL Server 2005에서 수집한 WorkLoad를 분석시키니.. 에러가 나고 제대로 실행이 되지 않았다.
알고 보니 SQL Server 2005에서는 ReadTrace 라는 2005 버젼의 SQL 일반화 Tool 이 따로 제공되고 있었다.
(프로그램 이름에서 80이 떨어진 걸 보니 이 프로그램은 추후 업그레이드될 SQL Server 버젼에서도 일반적으로 사용가능한 모양이다.)

이 프로그램을 사용하는 단계는 전체적으로 다음과 같다.

RML 패키지 설치
부하(Workload) 수집
수집한 Workload 를 분석
분석 결과 확인

※ RML Utility 도움말에서는 SQLDiag.exe로 시스템 정보를 수집하는 내용이 있지만, 이것은 튜닝대상 서버의 환경을 분석하여 베이스 정보를 수집하기 위한 과정이고, Report 생성에 필요한 과정은 아니라고 도움말에 나와있다. ^^;

좀 더 자세히 살펴보면 다음과 같다.

Step 1. RML 패키지 설치
Workload 분석 툴인 ReadTrace와 Workload Replay 툴인 O'Stress로 이루어진 RML Utility (Replay Markup Language Utility)를 다운받아 설치한다. 가급적이면 분석 대상인 DB서버에 바로 설치하는 것이 여러 모로 편하다. 다음의 URL에서 다운로드할 수 있다.

http://support.microsoft.com/kb/944837

설치 중에 RML Reporter 프로그램을 설치하겠냐고 묻는데 함께 설치한다.

Step 2. DB 에서 부하(Workload)를 수집
Profiler나 SQLDiag, SQL을 수집하는 스크립트(첨부) 등을 사용하여 현재 DB에서 수행되는 SQL (Workload)을 수집하고 trc 파일로 저장한다.
Profiler를 사용하는 방법은... 쉽기는 한데 Workload 수집시에 서버 부하가 높고 내부적으로 ReadTrace가 요구하는 모든 이벤트를 수집해야 하는데 이 조건을 만족시키는 템플릿을 구하기가 어렵고 (내가 만들긴 귀찮고^^)... 모 등등 해서 아래의 SQL문으로 수집하는 것이 낫다. (스크립트 출처 : RML Utility 도움말)

TraceCaptureDef.sql

해당 DB서버에서 SQL Server Management Studio로 열고 trc 파일 저장위치('InsertFileNameHere') 부분을 'D:\Test'와 같이 수정하여 실행해준다. trc 확장자는 자동으로 append되므로 붙일 필요가 없으며, 만약의 경우를 대비하여 OS가 설치된 파티션이나 mdf/ldf 파일이 존재하는 파티션과 다른 파티션에 저장하는 것이 좋다.

실행하면 아래와 같은 결과가 SELECT되는데 이 때 내가 실행시킨 수집(Trace) 세션의 ID (아래 그림에서는 "2")를 기억해놓도록 한다.

"메시지" 탭을 선택하면 이 수집(Trace) 작업을 중지시킬 때 뭐라고 입력해야 하는지를 친절하게 알려주고 있다.

탐색기에서 아래와 같이 trc 파일에 Workload 결과가 쌓이는 것을 볼 수 있다. 처음에는 한동안 trc 파일 사이즈가 0KB로 남아있을 수 있는데, 새로고침을 누르면서 몇분간 기다리면 파일 사이즈가 늘어나는 것을 확인할 수 있다.

충분한 Workload가 수집되면 다음의 SQL을 실행하여 Trace를 중지시킨다.

exec sp_trace_setstatus 2, 0 -- stop trace job
exec sp_trace_setstatus 2, 2 -- delete and deallocate trace job

Step 3. 수집한 Workload 를 분석

이제 수집된 Workload를 분석하기 위해 ReadTrace 프로그램을 사용한다.
ReadTrace 프로그램을 DB서버 Local에 설치했다면 이 작업도 당연히 DB서버 로컬에서 수행해야 한다.
ReadTrace.exe 는 커맨드 창에서 실행시켜야 하는 프로그램인데, 설치된 경로를 PATH 환경변수에 지정하기 위해 RML Utility에서 지원하는 RML Command창을 사용한다.

커맨드 창을 연 후 수집한 Workload 파일(trc 파일)이 있는 곳으로 이동하여 ReadTrace.exe를 실행시킨다.
(만약 RML 커맨드창이 아니라면 ReadTrace.exe 파일의 Full Path를 타이핑해주어야 한다.)

실행시키는 명령어는 ReadTrace /? 를 쳐보면 Usage를 볼 수 있으나 기본적으로는 아래와 같은 한가지 파라메터만 주면 된다.

D:\Temp\ReadTrace -ITraceFileName.trc

위와 같이 주면 기본적으로 분석 결과가 PerfAnalysis 라는 DB에 저장된다. 이 경우 여러번 분석하게 되면 이전 결과를 덮어쓰게 되므로 다음과 같이 DB 이름도 지정해줄 수 있다. (당연한 얘기지만... DB가 없으면 생성된다.)

D:\Temp\ReadTrace -ITraceFileName.trc -dPerfDB_20081218_1

Step 4. 분석 결과 확인

ReadTrace를 설치할 때 자동으로 함께 설치되는 RML Reporter 프로그램을 사용하여 분석된 결과를 Visual한 Report로 확인하고 악성 SQL을 찾아낸다. 최초 1번은 자동으로 실행되며 이후 레포트를 다시 보려면 다음과 같이 수동으로 Reporter 프로그램을 실행시키면 된다.

레포트의 첫 페이지는 아래와 같다.
중앙의 큰 그래프는 Workload 수집 시간 중의 부하 변화를 그래프로 보여준다.

여기서 부하를 어떤 기준으로 Grouping할지를 선택할 수 있는데, "Application Name"을 클릭할 경우 아래와 같이 Grouping할 Application을 선택할 수 있다. (한번 클릭할 때마다 탭이 새로 열린다.)

여기서 원하는 Application을 선택하면 아래와 같이 해당 Application이 실행시킨 SQL을 분석하여 보여준다.

위의 그래프를 보면 CPU Usage, Duration, Logical Read, Logical Write 등 다양한 기준 별로 사용율 Top 10 SQL을 보여주고 있다. 그래프에서는 1, 2, 3.. 이렇게 SQL의 번호만 표시되고 있는데, 스크롤 바를 조금만 내려보면 각 번호에 해당하는 SQL을 아래와 같이 보여준다.

위의 SQL중 하나를 클릭하면 아래와 같이 각 SQL에 대한 상세 정보를 볼 수 있다.

솔직히 난... 그래피컬하고 화려한 ReadTrace의 레포트보다 일목요연하고 가지고다니기 편한 Read80Trace의 html 레포트가 더 좋아보인다. (뭐 ReadTrace의 레포트도 Excel 내보내기를 지원하긴 한다.)

이렇게 악성SQL을 찾아내면... 그 다음은 개선만 하면 된다.
뭐, CPU사용율과 Logical Reads가 높은 SQL이라면 인덱스 쪽을 고려해볼 것이고, CPU사용율에 비해 Duration이 높은 경우라면 Blocking을 의심해볼 수 있을 것이다.

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

쿼럼 리소스 이동 (0)	2020.08.29
SQL Server Logs 파일 수 변경 및 로그 순환(CYCLE) (0)	2020.08.29
Master Key (0)	2020.08.29
[DMV] 특정 프로시저의 실행계획이 바뀌었다? (0)	2020.08.29
SQL Server2012 Checkpoint 제어 (0)	2020.08.29

A 노드에서 백업 받은 SMK가 B 노드에서 복원이 않되는 것은 자연 스러운 현상입니다.

B 노드에서 SMK를 읽기 위해서는 DAPI를 사용하는데, 이는 SQL Server의 자격 증명으로부터 파생된 키를 이용하게 됩니다.

즉 이로 인해 동일 서비스 계정이 아닐 경우에는 SMK의 복원 자체가 불가능합니다.

만약 강제로 복원을 시도하는 경우에는 SMK가 재 생성되는 것과 마찮가지로써, 크리덴셜을 이용하는 링크드 서버 및 기타 인증서를 이용한 암호화 구성에 문제가 발생할 수 있습니다.

[현상]

Veritas/Legato 클러스터에 Fail over 직후에 Linked Server 등이 연결이 안된다.

메세지 15466, 수준 16, 상태 2

[원인]

SMK 정보 변경

연결된 서버 암호, 인증서 또는 데이터베이스 마스터 키를 처음으로 암호화할 필요가 있을 때 자동으로 생성됨

SMK는 로컬 시스템 키 또는 Windows 데이터 보호 API(DAPI)를 사용하여 암호화된다. 이 API는 SQL Server 서비스 계정의 Windows 자격 증명으로부터 파생된 키를 사용한다.

서비스 마스터 키의 암호는 해당 키가 만들어진 서비스 계정이나 해당 서비스 계정의 Windows 자격 증명에 대한 액세스 권한이 있는 보안 주체에 의해서만 해독될 수 있다. 따라서 SQL Server 서비스를 실행 중인 Windows 계정을 변경하면 서비스 마스터 키의 암호 해독도 새 계정으로 활성화해야 한다.

[해결 방안]

1. 서비스를 Fail back 한다.

2. SMK를 백업 한다.

3. 두 노드 모두 SQL 서버의 시작 계정을 도메인 계정으로 변경 한다. (해당 작업은 동일 머신 상에서 진행 되어야 합니다.)

4. 연결 오류 발견 시 기존에 백업했던 SMK를 복원 한다.

[기타]

- 백업 및 복원

BACKUP SERVICE MASTER KEY TO FILE = 'c:\service_master_key' ENCRYPTION BY PASSWORD = 'password'

RESTORE SERVICE MASTER KEY FROM FILE = 'c:\service_master_key' DECRYPTION BY PASSWORD = 'password' -- [FORCE]

- 재생성

ALTER SERVICE MASTER KEY REGENERATE

- 계정 변경

ALTER SERVICE MASTER KEY WITH OLD_ACCOUNT = 'old_service_account', OLD_PASSWORD = 'old_service_account_password'

http://blogs.msdn.com/lcris/archive/2005/07/08/437048.aspx

http://blogs.msdn.com/lcris/archive/2006/04/10/572678.aspx

http://blogs.msdn.com/lcris/archive/2005/09/30/475822.aspx

SMK에 대한 간단한 설명 : http://nexondbteam.tistory.com/35

SMK 생성 강제 적용 : http://support.microsoft.com/kb/914261

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

SQL Server Logs 파일 수 변경 및 로그 순환(CYCLE) (0)	2020.08.29
악성 쿼리 찾아내기 - ReadTrace (0)	2020.08.29
[DMV] 특정 프로시저의 실행계획이 바뀌었다? (0)	2020.08.29
SQL Server2012 Checkpoint 제어 (0)	2020.08.29
sp_MSforeachdb (0)	2020.08.29

DBA가 관리하고 있는 DBMS에 특정 저장프로시저가 재컴파일 되면서 뜻하지 않게 원하지 않는 플랜 즉, 잘못된 플랜이 만들어 지면서 서버의 리소스를 소비하는 현상을 경험한 적이 있는 DBA 분들이 있을꺼에요. (없다고요?? 그럼 경험해 보셔야 해요. ^^;;;)

아무튼 우리 회사에서도 특정 저장프로시저가 재컴파일 되면서 INDEX SEEK를 해야 함에도 INDEX SCAN으로 플랜이 만들어지고 이로 인해서 서버의 리소스가 사용률이 약간 상승하는 일이 비일비재 해요. 이를 해결하기 위하여 여러가지 방법이 있겠지만, 전 왠지 이런 방법은 없을까? 라는 생각을 해봤어요.

재컴파일 참고 URL : http://support.microsoft.com/kb/243586

특정 프로시저 즉, 관심대상이 되는 프로시저의 플랜을 저장해 두었다가 현재 캐쉬되어 실행되고 있는 플랜의 물리적 연산자와 저장해둔 플랜의 물리적 연산자가 틀려지게 되면 알 수 있는 방법이 없을까?

그래서 만들고 있지요.

- 특정 프로시저들의 플랜을 저장한다. (물론 테이블을 만들어서)

- 현재 캐쉬되고 실행되는 플랜의 물리적 연산자와 저장된 플랜의 물리적 연산자와 비교 한다.

- 동일하면 일치라는 메시지를 출력한다.

- 동일하지 않다면 현재 캐쉬되어 실행되고 있는 플랜의 물리적 연산자 중 SCAN 이라는 물리적 연산자가 있으면 몇 개가 있고 그 물리적 연산자로 출력되는 예상 행수가 몇 개인지 출력한다.

[위 스크린샷이 저장해 놓은 플랜과 현재 캐쉬되어 실행되고 있는 플랜과 동일할 경우 출력되는 메시지 입니다.]

[위 스크린샷이 저장해 놓은 플랜과 현재 캐쉬되어 실행되고 있는 플랜이 동일 하지 않다면 SCAN 정보를 출력하는 메시지 입니다.]

두번째 스크린샷의 연산자 해석 방법은 해당 플랜의 SCAN의 포함되어 있는 물리적 연산자는 Clustered Index Scan 과 Table Scan 이 있으며, Clustered Index Scan 은 해당 플랜에서 총 3번이 있으며 그 3번의 평균 예상행수는 26만건 이며, Table Scan은 해당 플랜에 총 1번 있으며 그 1번의 예상행수는 45건 이라는 의미입니다. 해당 로직을 사용해 보고 공유할 만한 스크립트가 만들어 지면 올리겠습니다. 근데 멋있지 않나요?? 저만 멋있는건가요 ㅜ.ㅜ;;

특정 프로시저가 재 컴파일이 되면서 실행계획에 문제가 발생하면 CPU 사용률이 급격히 올라가기에 이 현상을 모니터링 하기 위해서 만들었고, 스크립트는 아래와 같습니다.

시작은 이랬습니다. DMV로 실행계획을 볼수 있고, 해당 실행계획은 XML로 되어 있으니 해당 XML에 있는 물리적 연산자를 몽땅 추출하여 내가 원하지 않는 연산자가 있는지 없는지를 알면 되지 않을까?

SELECT

-- d.plan_handle ,

OBJECT_NAME(b.objectid, b.dbid) [SP명] ,

c.query_plan AS [프로시저 전체 실행계획]

-- d.usecounts AS [실행계획이 만들어 진 이후로 사용된 횟수] ,

-- b.text AS [프로시저 내용] ,

--'EXEC ' + DB_NAME( b. dbid ) + '.DBO.sp_recompile ' + OBJECT_NAME ( b. objectid, b.dbid ) AS [ReCompile] ,

--'DBCC FreeProcCache (' + sys. fn_varbintohexstr( d.plan_handle ) + ')' AS [실행계획 삭제]

FROM sys.dm_exec_cached_plans AS d

CROSS APPLY sys.dm_exec_sql_text(d.plan_handle) AS b

CROSS APPLY sys.dm_exec_query_plan(d.plan_handle) AS c

WHERE OBJECT_NAME(b.objectid, b.dbid) = 'usp_Disp_SelectCategorySumGoodsList_v3'

AND b.text NOT LIKE '%sys.dm_exec_cached_plans%'

그럼 일단, 플랜을 저장하고 XML을 Query로 이용해서 물리연산자를 추출해 보자라고 생각했죠.

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

* 쿼리 플랜을 저장한다.

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

IF OBJECT_ID('tempdb..#AT_QueryPlanXml') IS NOT NULL

BEGIN

DROP TABLE #AT_QueryPlanXml

END ;

-- 임시테이블을 만든다.

CREATE TABLE #AT_QueryPlanXml ( spName VARCHAR(500) ,

spPlan XML )

-- 특정 저장프로시저의 플랜을 저장한다.

-- 이왕 이면 주의대상 저장프로시저를 저장

INSERT INTO #AT_QueryPlanXml

( spName ,

spPlan )

SELECT

-- d.plan_handle ,

OBJECT_NAME(b.objectid, b.dbid) [SP명] ,

c.query_plan AS [프로시저 전체 실행계획]

-- d.usecounts AS [실행계획이 만들어 진 이후로 사용된 횟수] ,

-- b.text AS [프로시저 내용] ,

--'EXEC ' + DB_NAME( b. dbid ) + '.DBO.sp_recompile ' + OBJECT_NAME ( b. objectid, b.dbid ) AS [ReCompile] ,

--'DBCC FreeProcCache (' + sys. fn_varbintohexstr( d.plan_handle ) + ')' AS [실행계획 삭제]

FROM sys.dm_exec_cached_plans AS d

CROSS APPLY sys.dm_exec_sql_text(d.plan_handle) AS b

CROSS APPLY sys.dm_exec_query_plan(d.plan_handle) AS c

WHERE OBJECT_NAME(b.objectid, b.dbid) = 'usp_Disp_SelectCategorySumGoodsList_v3'

AND b.text NOT LIKE '%sys.dm_exec_cached_plans%'

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

* 저장된 실행계획의 물리연산자를 추출한다.

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

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

SELECT

stmt.stmt_details.value( '@PhysicalOp', 'nvarchar(max)'),

stmt.stmt_details.value( '@EstimateRows', 'nvarchar(max)')

FROM

(

SELECT spPlan

FROM #AT_QueryPlanXml

) AS Result

CROSS APPLY spPlan.nodes('//sp:RelOp' ) AS stmt (stmt_details)

보시는봐와 같이 해당 프로시져 플랜의 연산자는 총 83개 중에 간간히 Clustered Index Scan 연산자를 이용하여 40만건이 넘는 행수를 읽고 있는게 보입니다. 그럼 Clustered Index Scan 이던 Table Scan 이던 해당 플랜 안에 Scan이 몇 개나 있고 그 예상행수는 얼마나 되는지를 판단하기 위하여 아래와 같은 최종 형태의 스크립트를 실행하시면 ...

USE DBA

GO

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

/*--------------------------------------------------------------------------------------------

SP명 : DBA.FN_Query_PlanOp

작성자 : 이 승연

작성일 :

설명 : XML 안에 어떤 물리적연산이 있는지 확인

입력변수:

출력변수:

사용방법:

--------------------------------------------------------------------------------------------

작성자 작성일자 설명

--------------------------------------------------------------------------------------------

--------------------------------------------------------------------------------------------*/

CREATE FUNCTION DBO.FN_Query_PlanOp ( @QueryXML XML )

RETURNS VARCHAR(MAX)

AS

BEGIN

DECLARE @Result VARCHAR(MAX) = '' ;

DECLARE @TMP_Result VARCHAR(MAX) = '' ;

DECLARE @Tmp_QueryXML TABLE ( ShowPlan XML ) ;

DECLARE @Tmp_QueryXMLOp TABLE ( PhysicalOp VARCHAR(500) ,

EstimateRows FLOAT ) ;

--SELECT @Result = STR(@statement_start_offset) + ' : ' + CAST(@QueryXML AS VARCHAR(MAX))

INSERT INTO @Tmp_QueryXML

VALUES ( @QueryXML ) ;

WITH XMLNAMESPACES ( 'http://schemas.microsoft.com/sqlserver/2004/07/showplan' AS sp)

INSERT INTO @Tmp_QueryXMLOp

SELECT

stmt.stmt_details .value ( '@PhysicalOp' , 'nvarchar(max)' ) AS PhysicalOp ,

stmt.stmt_details .value ( '@EstimateRows' , 'nvarchar(max)' ) AS EstimateRows

FROM (

SELECT *

FROM @Tmp_QueryXML

) AS Result

CROSS APPLY ShowPlan. nodes( '//sp:RelOp' ) AS stmt ( stmt_details)

SELECT @TMP_Result = @TMP_Result + ', (' + PhysicalOp + '/'

+ CAST (COUNT(PhysicalOp) AS VARCHAR(100)) + '/'

+ REPLACE(CONVERT (VARCHAR, CONVERT (MONEY, AVG(EstimateRows)), 1),

'.00', '') + ')'

FROM @Tmp_QueryXMLOp

WHERE PhysicalOp LIKE '%SCAN%'

GROUP BY PhysicalOp

SELECT @Result = CASE WHEN @TMP_Result = '' THEN '-'

ELSE RIGHT(@TMP_Result, LEN(@TMP_Result) - 1)

END

-- Return the result of the function

RETURN @Result

END

GO

-- 위 스칼라 함수 생성 후 아래와 같은 쿼리를 실행한다.

DECLARE @sp_Name VARCHAR(MAX) = '저장프로시저' ;

DECLARE @Query_Plan TABLE ( [plan_handle] [varbinary](64) NOT NULL ,

[SP명] [nvarchar](128) NULL ,

[프로시저 전체 실행계획] [xml] NULL ,

[실행계획이 만들어 진 이후로 사용된 횟수] [int] NOT NULL ,

[프로시저 내용] [nvarchar](MAX) NULL ,

[ReCompile] [nvarchar](279) NULL ,

[실행계획 삭제] [nvarchar](MAX) NULL ,

[statement_start_offset] [int] NOT NULL ,

[statement_end_offset] [int] NOT NULL ,

[ShowPlan] [xml] NULL ) ;

WITH A AS ( SELECT d.plan_handle ,

OBJECT_NAME(b.objectid, b.dbid) [SP명] ,

c.query_plan AS [프로시저 전체 실행계획] ,

d.usecounts AS [실행계획이 만들어 진 이후로 사용된 횟수] ,

b.text AS [프로시저 내용] ,

'EXEC ' + DB_NAME(b.dbid) + '.DBO.sp_recompile '

+ OBJECT_NAME(b.objectid, b.dbid) AS [ReCompile] ,

'DBCC FreeProcCache ('

+ sys.fn_varbintohexstr(d.plan_handle) + ')' AS [실행계획 삭제]

FROM sys.dm_exec_cached_plans AS d

CROSS APPLY sys.dm_exec_sql_text(d.plan_handle) AS b

CROSS APPLY sys.dm_exec_query_plan(d.plan_handle) AS c

WHERE OBJECT_NAME(b.objectid, b.dbid) = @sp_Name

AND b.text NOT LIKE '%sys.dm_exec_cached_plans%'

),

B AS ( SELECT A.plan_handle ,

A.[SP명] ,

A.[프로시저 전체 실행계획] ,

A.[실행계획이 만들어 진 이후로 사용된 횟수] ,

A.[프로시저 내용] ,

A.[ReCompile] ,

A.[실행계획 삭제] ,

qs.statement_start_offset ,

qs.statement_end_offset

FROM A AS A

INNER JOIN sys.dm_exec_query_stats AS qs

ON A.plan_handle = qs.plan_handle

)

INSERT INTO @Query_Plan

SELECT B.* ,

CAST(qp.query_plan AS XML) AS [ShowPlan]

FROM B

CROSS APPLY sys.dm_exec_text_query_plan(B.plan_handle,

B.statement_start_offset,

B.statement_end_offset)

AS qp

SELECT DBA.DBO.FN_Query_PlanOp(ShowPlan) AS [물리적연산] ,

[SP명] ,

[프로시저 전체 실행계획] ,

[실행계획이 만들어 진 이후로 사용된 횟수] ,

[프로시저 내용] ,

[ReCompile] ,

[실행계획 삭제] ,

statement_start_offset ,

statement_end_offset ,

[ShowPlan]

FROM @Query_Plan

해당 프로시저에 Clustered Index Scan 이 4건이 있으며 그 4건에 대한 평균 예상행수는 20만건이 있다고 출력을 해주고 있습니다. 그럼 이제 정상적인 플랜을 미리 저장해 두고 해당 스크립트로 현재 돌아가고 있는 플랜의 연산자를 추출하여 비교하면 어떤 연산자가 새로 생겼는지 기존에는 어떻게 돌고 있다가 재 컴파일 되면서 지금은 이렇게 돌고 있구나 라고 판단하실 수 있을 겁니다.

출처: https://ddoung2.tistory.com/359 [DDoung2]

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

악성 쿼리 찾아내기 - ReadTrace (0)	2020.08.29
Master Key (0)	2020.08.29
SQL Server2012 Checkpoint 제어 (0)	2020.08.29
sp_MSforeachdb (0)	2020.08.29
dbforge Activity Monitor Permission (0)	2020.08.29