데이터베이스 개발자 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
» DMV를 이용한 SQL Server IO 성능 모니터 스냅샷 만들기 jevida(강성욱) 2016.09.13 1247
1712 DMV를 활용한 SQL Server 모니터링 jevida(강성욱) 2016.09.13 1271
1711 세션에 따른 캐시된 쿼리 플랜 설정 확인 jevida(강성욱) 2016.09.13 868
1710 SQL Server 특정 세션에 대한 마지막 실행 문장 확인 jevida(강성욱) 2016.09.13 719
1709 DMV - 데이터베이스 버퍼 메모리 사용량 확인 jevida(강성욱) 2016.09.13 3018
1708 Sys.dm_fts_parser을 이용한 문자열 구문 분석 jevida(강성욱) 2016.09.13 1310
1707 DMV에서 SQL Server 리소스 데이터베이스 값 jevida(강성욱) 2016.09.13 1070
1706 키워드로 PROCEDURE, FUNCTION 찾기 jevida(강성욱) 2016.09.13 1579
1705 BCP 사용 jevida(강성욱) 2016.09.13 3469
1704 DATEADD를 사용한 날짜 추가 및 빼기 jevida(강성욱) 2016.09.13 1155
1703 Change Data Capture(CDC) – 변경 이력 추적 jevida(강성욱) 2016.09.13 2593
1702 SSMS 에서 디버깅 하기 jevida(강성욱) 2016.09.13 7891
1701 CLR 등록 및 활성화 하기 jevida(강성욱) 2016.09.13 1585
1700 SQL Server 확장 이벤트를 사용한 Tempdb 병목현상 추적 jevida(강성욱) 2016.09.13 1622
1699 MAXDOP 설정 jevida(강성욱) 2016.09.13 1848
1698 SQL Server 이름 변경 하기 jevida(강성욱) 2016.09.13 1597
1697 SQL Server 마지막 시작 시간 확인 하기 jevida(강성욱) 2016.09.13 705
1696 여러 포트를 사용하도록 SQL Server 구성 jevida(강성욱) 2016.09.13 1826
1695 SQL Server가 사용중인 TCP/IP 포트 확인 jevida(강성욱) 2016.09.13 11180
1694 SQL Server SP_Congifure 변경 사항 캡처 jevida(강성욱) 2016.09.13 2064





XE Login