백업 확장이벤트로 백업 진행 과정과 소요되는 시간 확인

백업 확장이벤트로 백업 진행 과정과 소요되는 시간 확인

 

• Version : SQL Server 2016

 

SQL Server 2016에서 추가된 새로운 확장 이벤트 sqlserver.backup_restore_progress_trace

를 사용하여 백업과정과 각 과정에서 소요되는 시간을 확인할 수 있다.

 

실습을 위해 확장이벤트를 생성한다.

CREATE EVENT SESSION [Backup progress] ON SERVER ADD EVENT sqlserver.backup_restore_progress_trace ( ACTION(package0.event_sequence)   -- to only capture backup operations: --WHERE [operation_type] = 0   -- to only capture restore operations: --WHERE [operation_type] = 1 ) ADD TARGET package0.event_file ( SET filename = N'C:\SQL_Backup\BackupProgress.xel' ); -- default options are probably ok GO   ALTER EVENT SESSION [Backup progress] ON SERVER STATE = START; GO

 

데이터베이스를 생성하고 데이터를 입력한다.

create database SW_Test go   SELECT s1.* INTO SW_Test.dbo.what FROM sys.all_objects AS s1 CROSS JOIN sys.all_objects; GO

 

백업과 복원을 실행한다.

BACKUP DATABASE SW_Test TO DISK = 'c:\SQL_Backup\SW_Test.bak' WITH INIT, COMPRESSION, STATS = 30; GO   DROP DATABASE SW_Test; GO   RESTORE DATABASE SW_Test FROM DISK = 'c:\SQL_Backup\SW_Test.bak' WITH REPLACE, RECOVERY;

 

 

수집된 데이터를 조회한다. Duration 단위는 밀리세컨드이다.

;WITH x AS ( SELECT ts,op,db,msg,es FROM ( SELECT ts = x.value(N'(event/@timestamp)[1]', N'datetime2'), op = x.value(N'(event/data[@name="operation_type"]/text)[1]', N'nvarchar(32)'), db = x.value(N'(event/data[@name="database_name"])[1]', N'nvarchar(128)'), msg = x.value(N'(event/data[@name="trace_message"])[1]', N'nvarchar(max)'), es = x.value(N'(event/action[@name="event_sequence"])[1]', N'int') FROM ( SELECT x = CONVERT(XML, event_data) FROM sys.fn_xe_file_target_read_file (N'c:\SQL_Backup\BackupProgress*.xel', NULL, NULL, NULL) ) AS y ) AS x WHERE op = N'Backup' -- N'Restore' AND db = N'SW_Test' AND ts > CONVERT(DATE, SYSDATETIME()) ) SELECT x.db, x.op, x.ts, [Message] = x.msg, Duration = COALESCE(DATEDIFF(MILLISECOND, x.ts, LEAD(x.ts, 1) OVER(ORDER BY es)),0) FROM x ORDER BY es;

 

 

 

[참고자료]

http://sqlperformance.com/2015/06/extended-events/t-sql-tuesday-67-backup-restore

 

 

2015-06-16 / 강성욱 / http://sqlmvp.kr

 

Sqlserver 2016, mssql, sqlserver, DBA, Backup, 백업, 데이터베이스 백업, 확장이벤트, XEvent, sqlserver.backup_restore_progress_trace, 백업소요시간, SQL Backup