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 생성 스크립트는 파라메터에 따라 여러가지 정보를 반환한다.
- 아무런 매개변수 값이 없다면 생성된 마지막 두 개의 스냅샷을 비교 한다.
- 시작 스냅샷 ID를 전달하면 마지막 스냅샷과 해당 전달 받은 스냅샷을 비교 한다.
- 시작 스냅샷 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
