데이터베이스 개발자 Tip & 강좌

SQLER의 개발자들이 만들어가는 데이터베이스 사용자 Tip & 강좌 게시판입니다. SQL서버, Oracle, MySQL 등 여러 클라우드/오픈소스 기반 데이터베이스 개발 및 운영 관련 팁과 쿼리 노하우를 이곳에서 가장 먼저 접하실 수 있습니다. 많은 도움 되시길 바랍니다.

DMV를 이용한 SQL Server IO 성능 모니터 스냅샷 만들기

 

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

 

SQL Server에서 I/O 문제는 성능에 가장 큰 영향일 끼치는 요소 중 하나이다. I/O 대기 시간을 줄일 수 있다면 성능을 향상 시킬 수 있다. I/O 성능에 대한 부분은 근본적으로는 하드웨어의 성능과 밀접한 관련이 있다. 하지만 쿼리 튜닝을 통해서 어느 정도 해결 할 수 있다. DMV를 이용하여 I/O 분석을 위한 스냅샷을 만들어 보자.

 

특정 기간 또는 주기적으로 I/O를 수집하고 비교하여 잠재적인 I/O 병목 현상을 식별 할 수 있어야 한다. 이번 실습 방법은 1일 1회 또는 시간 당 1회 등 특정 간격으로 데이터를 수집하여 비교하여 스냅샷의 차이를 확인 하는 것이다.

 

I/O 통계는 sys.dm_io_virtual_file_stats, sys.master_files를 사용한다.

 

[sys.dm_io_virtual_file_stats 반환 정보]

열 이름

데이터 형식

설명

Database_id

Smallint

데이터베이스 ID

File_id

Smallint

파일의 ID

Sample_ms

Int

컴퓨터가 시작된 이후 경과시간(밀리초)

Num_of_reads

Bigint

파일에 대해 읽기가 실행 된 횟수

Num_of_bytes_read

Bigint

파일에 대해서 실행 된 읽기의 총 바이트 수

Io_stall_read_ms

Bigint

사용자가 파일에 대한 읽기가 실행될 때 까지 대기한 총 시간(밀리초)

Num_of_writes

Bigint

파일에 대해 쓰기가 실행 된 횟수

Num_of_bytes_written

Bigint

파일에 대해 실행된 쓰기의 총 바이트 수

Io_stall_write_ms

Bigint

사용자가 파일에 대한 쓰기가 완료될 때까지 대기한 총 시간(밀리초)

Io_stall

Bigint

사용자가 파일에 대한 I/O가 완료될 때까지 대기한 총 시간(밀리초)

Size_on_disk_bytes

Bigint

이 파일에 대해 디스크에서 사용된 바이트 수. 스파스 파일의 경우 데이터베이스 스냅숏에 사용도니 디스크이 실제 바이트 수.

File_handle

varbinary

파일에 대한 Windows 파일 핸들

 

 

I/O 통계를 저장할 테이블을 생성 한다. 아래 스크립트는 2개의 테이블을 생성한다.

-- create a table for snapshot sequence generation

CREATE TABLE io_snapshots

( snap_id INT IDENTITY NOT NULL,

snapshot_creation_date DATETIME NOT NULL )

GO

ALTER TABLE io_snapshots ADD CONSTRAINT PK_io_snapshots PRIMARY KEY ( snap_id )

GO

 

-- create a table for the io statistics

CREATE TABLE io_snapshots_statistics

( snap_id INT NOT NULL,

[db_id] smallint NOT NULL,

[file_id] smallint NOT NULL,

database_name SYSNAME ,

physical_file_name SYSNAME ,

Diff_Number_of_reads bigint ,

Diff_Bytes_Read bigint ,

Diff_Read_stall_time_ms bigint ,

Diff_Number_of_writes bigint ,

Diff_Bytes_written bigint ,

Diff_Write_stall_time_ms bigint ,

Diff_Read_Write_stall_ms bigint ,

size_on_disk_MB bigint )

GO

ALTER TABLE io_snapshots_statistics ADD CONSTRAINT PK_io_snapshots_statistics

PRIMARY KEY ( snap_id , [db_id] , [file_id] )

GO

ALTER TABLE io_snapshots_statistics ADD CONSTRAINT FK_io_snapshots_statistics_io_snapshots

FOREIGN KEY ( snap_id ) REFERENCES io_snapshots ( snap_id )

GO

 

 

 

I/O 통계를 저장하기 위한 SP를 생성 한다.

-- The First Procedure.

CREATE PROC [dbo].[usp_io_vf_stats_snap]

AS

BEGIN

SET NOCOUNT ON

INSERT INTO io_snapshots ( snapshot_creation_date ) SELECT GETDATE ()

 

INSERT INTO io_snapshots_statistics

( snap_id ,

[db_id] ,

[file_id] ,

database_name ,

physical_file_name ,

Diff_Number_of_reads ,

Diff_Bytes_Read ,

Diff_Read_stall_time_ms ,

Diff_Number_of_writes ,

Diff_Bytes_written ,

Diff_Write_stall_time_ms ,

Diff_Read_Write_stall_ms ,

size_on_disk_MB )

SELECT

( SELECT MAX ( snap_id ) FROM io_snapshots ),

db_files.database_id ,

db_files. FILE_ID ,

DB_NAME ( db_files.database_id ) AS Database_Name ,

db_files.physical_name AS File_actual_name ,

num_of_reads AS Number_of_reads ,

num_of_bytes_read AS Bytes_Read ,

io_stall_read_ms AS Read_time_stall_ms ,

num_of_writes AS Number_of_writes ,

num_of_bytes_written AS Bytes_written ,

io_stall_write_ms AS Write_time_stall_ms ,

io_stall AS Read_Write_stall_ms ,

size_on_disk_bytes / POWER ( 1024 , 2 ) AS size_on_disk_MB

FROM

sys.dm_io_virtual_file_stats (NULL,NULL) dm_io_vf_stats ,

sys.master_files db_files

WHERE

db_files.database_id = dm_io_vf_stats.database_id

AND db_files.[file_id] = dm_io_vf_stats.[file_id] ;

 

SET NOCOUNT OFF

 

END

GO

 

 

 

아래 SP 생성 스크립트는 파라메터에 따라 여러가지 정보를 반환한다.

  1. 아무런 매개변수 값이 없다면 생성된 마지막 두 개의 스냅샷을 비교 한다.
  2. 시작 스냅샷 ID를 전달하면 마지막 스냅샷과 해당 전달 받은 스냅샷을 비교 한다.
  3. 시작 스냅샷 ID와 종료 스냅샷 ID를 입력하면 두 기간을 비교 한다.

 

-- The Second Procedure.

CREATE PROC [dbo].[usp_compare_io_stats_snaps]

( @start_snap_ID INT = NULL,

@end_snap_ID INT = NULL)

AS

DECLARE @end_snp INT

DECLARE @start_snp INT

BEGIN

SET NOCOUNT ON

 

IF ( @end_snap_ID IS NULL)

SELECT @end_snp = MAX ( snap_id ) FROM io_snapshots

ELSE SET @end_snp = @end_snap_ID

 

IF ( @start_snap_ID IS NULL)

SELECT @start_snp = @end_snp - 1

ELSE SET @start_snp = @start_snap_ID

 

 

SELECT

CONVERT ( VARCHAR ( 12 ), S.snapshot_creation_date , 101 ) AS snapshot_creation_date ,

A.database_name ,

A.physical_file_name ,

A.size_on_disk_MB ,

A.Diff_Number_of_reads - B.Diff_Number_of_reads AS Diff_Number_of_reads ,

A.Diff_Bytes_read - B.Diff_Bytes_read AS Diff_Bytes_read ,

A.Diff_Read_stall_time_ms - B.Diff_Read_stall_time_ms AS Diff_Read_stall_time_ms ,

A.Diff_Number_of_writes - B.Diff_Number_of_writes AS Diff_Number_of_writes ,

A.Diff_Bytes_written - B.Diff_Bytes_written AS Diff_Bytes_written ,

A.Diff_Write_stall_time_ms - B.Diff_Write_stall_time_ms AS Diff_Write_stall_time_ms ,

A.Diff_Read_Write_stall_ms - B.Diff_Read_Write_stall_ms AS Diff_Read_Write_stall_ms ,

DATEDIFF ( hh , S1.snapshot_creation_date , S.snapshot_creation_date ) AS Diff_time_hours

FROM

io_snapshots S ,

io_snapshots S1 ,

io_snapshots_statistics A ,

io_snapshots_statistics B

WHERE

S.snap_id = @end_snp AND

S.snap_id = A.snap_id AND

B.snap_id = @start_snp AND

A.[db_id] = B.[db_id] AND

A.[file_id] = B.[file_id] AND

S1.snap_id = @start_snp AND

S1.snap_id = B.snap_id

ORDER BY

A.database_name ,

A.physical_file_name

 

SET NOCOUNT OFF

END

GO

 

 

 

파라메터에 따른 스냅샷 결과 확인을 할 수 있다. 마지막 스냅샷 시간, 데이터베이스의 물리적 파일 위치, 수집된 통계와 두 개의 스냅샷 사이의 시간차이를 확인 할 수 있다.

exec usp_compare_io_stats_snaps @start_snap_ID = NULL, @end_snap_ID = NULL

go

 

exec usp_compare_io_stats_snaps @start_snap_ID = 1, @end_snap_ID = NULL

go

 

exec usp_compare_io_stats_snaps @start_snap_ID = 1, @end_snap_ID = 2

go

 

 

 

주기적으로 I/O 통계를 수집하여 I/O 성능에 문제가 있을 때 이를 미리 감지하여 어느 곳에서 문제가 있는지 빠르게 파악하여 해결 할 수 있도록 하자.

 

참고자료

 


강성욱 / jevida@naver.com

Microsoft SQL Server MVP

Blog : http://sqlmvp.kr

Facebook : http://facebook.com/sqlmvp

No. Subject Author Date Views
1731 SQL Server 그래픽 실행 계획 및 텍스트 실행 계획 jevida(강성욱) 2016.09.14 3126
1730 SQL Server에서 Trigger 활성 / 비활성 감시 jevida(강성욱) 2016.09.14 1469
1729 DDL Trigger를 이용한 데이터베이스 변경 사항 추적 jevida(강성욱) 2016.09.14 1230
1728 Trigger를 이용한 SQL Server 커넥션 풀링 확인 jevida(강성욱) 2016.09.14 1088
1727 SQL Server Trigger jevida(강성욱) 2016.09.14 938
1726 인덱스에 대한 SORT_IN_TEMPDB 옵션 jevida(강성욱) 2016.09.14 846
1725 인덱스 DDL 작업의 디스크 공간 요구 사항 jevida(강성욱) 2016.09.14 912
1724 XML nodes() 함수를 이용한 OPENXML 교체 jevida(강성욱) 2016.09.14 945
1723 XQuery를 사용한 XML 데이터 업데이트 jevida(강성욱) 2016.09.14 1883
1722 BCP XML 파일 형식 jevida(강성욱) 2016.09.14 1255
1721 SQL Server로 데이터 가져오기 jevida(강성욱) 2016.09.14 1223
1720 SQL Server Stored Procedure 암호화 jevida(강성욱) 2016.09.14 2689
1719 SQL Server 대칭키 vs 비대칭키 암호화 jevida(강성욱) 2016.09.14 1687
1718 SQL Server 마스터 키 관리 jevida(강성욱) 2016.09.14 1750
1717 대칭키를 사용하여 SQL Server 암호화(열 수준) 하기 jevida(강성욱) 2016.09.13 5673
1716 DMV를 사용하여 누락된 인덱스 확인 jevida(강성욱) 2016.09.13 1343
1715 DMV를 이용한 SQL Server 대기 상태 확인 jevida(강성욱) 2016.09.13 3807
1714 DMV를 이용한 SQL Server 성능 카운터 확인 jevida(강성욱) 2016.09.13 1787
» DMV를 이용한 SQL Server IO 성능 모니터 스냅샷 만들기 jevida(강성욱) 2016.09.13 1244
1712 DMV를 활용한 SQL Server 모니터링 jevida(강성욱) 2016.09.13 1261





XE Login