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

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 성능에 문제가 있을 때 이를 미리 감지하여 어느 곳에서 문제가 있는지 빠르게 파악하여 해결 할 수 있도록 하자.

 

참고자료

• http://www.mssqltips.com/sqlservertip/2350/creating-io-performance-snapshots-to-find-sql-server-performance-problems/
• http://msdn.microsoft.com/ko-kr/library/ms190326.aspx

 

2013-01-21 / 강성욱 / http://sqlmvp.kr