SQL Server 백업 옵션(BUFFERCOUNT, MAXTRANSFERSIZE)에 따른 성능

SQL Server 백업 옵션(BUFFERCOUNT, MAXTRANSFERSIZE)에 따른 성능

   

대용량 백업시나리오를 잘 만들어야 한다.

아래 테스트는 가상머신에서 그냥 옵션이 궁긍해서 테스트 한 내용이라 수치에는 별 의미를 두지 말자.

BUFFERCOUNT, MAXTRANSFERSIZE

   

이런 옵션이 있었구나 이정도만 알고 잊자. 언제 써야 좋을지 모르겠다. 값이 높을 경우 메모리 이슈가 발생할 수 있으니 조심해야겠다.

[데모DB]

DatabaseName LogicalFileName PhysicalFilePathName RecoveryMode TotalSize UsedSize UsedPercent

PERF_XIO PERF_XIO E:\SQLDATA\PERF_XIO.mdf SIMPLE 51200.00 23725.19 46.34

PERF_XIO PERF_XIO_log E:\SQLDATA\PERF_XIO_log.ldf SIMPLE 1126.44 34.47 3.06

   

백업 옵션에 따라 결과 치 – 별 의미 없는 듯.

DBCC TRACEON (3605, -1)

DBCC TRACEON (3213, -1)

   

BACKUP DATABASE PERF_XIO TO DISK='F:\SQLBackup\PERF_XIO.bak'

BACKUP DATABASE이(가) 3043306개의 페이지를 192.870초 동안 처리했습니다(123.273MB/초).

2016-04-18 17:29:16.120 spid54 Backup/Restore buffer configuration parameters

2016-04-18 17:29:16.120 spid54 Memory limit: 999MB

2016-04-18 17:29:16.120 spid54 BufferCount: 7

2016-04-18 17:29:16.120 spid54 Sets Of Buffers: 1

2016-04-18 17:29:16.120 spid54 MaxTransferSize: 1024 KB

2016-04-18 17:29:16.120 spid54 Min MaxTransferSize: 64 KB

2016-04-18 17:29:16.120 spid54 Total buffer space: 7 MB

2016-04-18 17:29:16.120 spid54 Tabular data device count: 1

2016-04-18 17:29:16.120 spid54 Fulltext data device count: 0

2016-04-18 17:29:16.120 spid54 Filestream device count: 0

2016-04-18 17:29:16.120 spid54 TXF device count: 0

2016-04-18 17:29:16.120 spid54 Filesystem i/o alignment: 512

2016-04-18 17:29:16.120 spid54 Media Buffer count: 7

2016-04-18 17:29:16.120 spid54 Media Buffer size: 1024KB

   

BACKUP DATABASE PERF_XIO TO DISK='F:\SQLBackup\PERF_XIO_C.bak' WITH COMPRESSION, STATS=10

BACKUP DATABASE이(가) 3043306개의 페이지를 206.137초 동안 처리했습니다(115.339MB/초).

2016-04-18 17:33:59.380 spid54 Backup/Restore buffer configuration parameters

2016-04-18 17:33:59.380 spid54 Memory limit: 2999MB

2016-04-18 17:33:59.380 spid54 BufferCount: 7

2016-04-18 17:33:59.380 spid54 Sets Of Buffers: 3

2016-04-18 17:33:59.380 spid54 MaxTransferSize: 1024 KB

2016-04-18 17:33:59.380 spid54 Min MaxTransferSize: 64 KB

2016-04-18 17:33:59.380 spid54 Total buffer space: 21 MB

2016-04-18 17:33:59.380 spid54 Tabular data device count: 1

2016-04-18 17:33:59.380 spid54 Fulltext data device count: 0

2016-04-18 17:33:59.380 spid54 Filestream device count: 0

2016-04-18 17:33:59.380 spid54 TXF device count: 0

2016-04-18 17:33:59.380 spid54 Filesystem i/o alignment: 512

2016-04-18 17:33:59.380 spid54 Media Buffer count: 7

2016-04-18 17:33:59.380 spid54 Media Buffer size: 1024KB

2016-04-18 17:33:59.380 spid54 Encode Buffer count: 7

   

   

BACKUP DATABASE PERF_XIO TO

DISK='F:\SQLBackup1\PERF_XIO_C_B_C_1.bak',

DISK='F:\SQLBackup2\PERF_XIO_C_B_C_2.bak',

DISK='F:\SQLBackup3\PERF_XIO_C_B_C_3.bak'

wITH COMPRESSION, STATS=10

   

BACKUP DATABASE이(가) 3043306개의 페이지를 129.799초 동안 처리했습니다(183.174MB/초).

2016-04-18 17:39:03.260 spid54 Backup/Restore buffer configuration parameters

2016-04-18 17:39:03.260 spid54 Memory limit: 2999MB

2016-04-18 17:39:03.260 spid54 BufferCount: 17

2016-04-18 17:39:03.260 spid54 Sets Of Buffers: 3

2016-04-18 17:39:03.260 spid54 MaxTransferSize: 1024 KB

2016-04-18 17:39:03.260 spid54 Min MaxTransferSize: 64 KB

2016-04-18 17:39:03.260 spid54 Total buffer space: 51 MB

2016-04-18 17:39:03.260 spid54 Tabular data device count: 1

2016-04-18 17:39:03.260 spid54 Fulltext data device count: 0

2016-04-18 17:39:03.260 spid54 Filestream device count: 0

2016-04-18 17:39:03.260 spid54 TXF device count: 0

2016-04-18 17:39:03.260 spid54 Filesystem i/o alignment: 512

2016-04-18 17:39:03.260 spid54 Media Buffer count: 17

2016-04-18 17:39:03.260 spid54 Media Buffer size: 1024KB

2016-04-18 17:39:03.260 spid54 Encode Buffer count: 17

  

   

BACKUP DATABASE PERF_XIO TO

DISK='F:\SQLBackup1\PERF_XIO_C_B_C_M_1.bak',

DISK='F:\SQLBackup2\PERF_XIO_C_B_C_M_2.bak',

DISK='F:\SQLBackup3\PERF_XIO_C_B_C_M_3.bak'

wITH COMPRESSION, STATS=10

, MAXTRANSFERSIZE=2097152

   

BACKUP DATABASE이(가) 3043306개의 페이지를 150.830초 동안 처리했습니다(157.633MB/초).

2016-04-18 17:42:54.220 spid54 Backup/Restore buffer configuration parameters

2016-04-18 17:42:54.220 spid54 Memory limit: 2999MB

2016-04-18 17:42:54.220 spid54 BufferCount: 17

2016-04-18 17:42:54.220 spid54 Sets Of Buffers: 3

2016-04-18 17:42:54.220 spid54 MaxTransferSize: 2048 KB

2016-04-18 17:42:54.220 spid54 Min MaxTransferSize: 64 KB

2016-04-18 17:42:54.220 spid54 Total buffer space: 102 MB

2016-04-18 17:42:54.220 spid54 Tabular data device count: 1

2016-04-18 17:42:54.220 spid54 Fulltext data device count: 0

2016-04-18 17:42:54.220 spid54 Filestream device count: 0

2016-04-18 17:42:54.220 spid54 TXF device count: 0

2016-04-18 17:42:54.220 spid54 Filesystem i/o alignment: 512

2016-04-18 17:42:54.220 spid54 Media Buffer count: 17

2016-04-18 17:42:54.220 spid54 Media Buffer size: 2048KB

2016-04-18 17:42:54.220 spid54 Encode Buffer count: 17

  

   

   

BACKUP DATABASE PERF_XIO TO

DISK='F:\SQLBackup1\PERF_XIO_C_B_C_B_1.bak',

DISK='F:\SQLBackup2\PERF_XIO_C_B_C_B_2.bak',

DISK='F:\SQLBackup3\PERF_XIO_C_B_C_B_3.bak'

WITH COMPRESSION, STATS=10

, BUFFERCOUNT = 200

BACKUP DATABASE이(가) 3043306개의 페이지를 169.316초 동안 처리했습니다(140.422MB/초).

2016-04-18 17:48:50.780 spid54 Backup/Restore buffer configuration parameters

2016-04-18 17:48:50.780 spid54 Memory limit: 2999MB

2016-04-18 17:48:50.780 spid54 BufferCount: 200

2016-04-18 17:48:50.780 spid54 Sets Of Buffers: 3

2016-04-18 17:48:50.780 spid54 MaxTransferSize: 1024 KB

2016-04-18 17:48:50.780 spid54 Min MaxTransferSize: 64 KB

2016-04-18 17:48:50.780 spid54 Total buffer space: 600 MB

2016-04-18 17:48:50.780 spid54 Tabular data device count: 1

2016-04-18 17:48:50.780 spid54 Fulltext data device count: 0

2016-04-18 17:48:50.780 spid54 Filestream device count: 0

2016-04-18 17:48:50.780 spid54 TXF device count: 0

2016-04-18 17:48:50.780 spid54 Filesystem i/o alignment: 512

2016-04-18 17:48:50.780 spid54 Media Buffer count: 200

2016-04-18 17:48:50.780 spid54 Media Buffer size: 1024KB

2016-04-18 17:48:50.780 spid54 Encode Buffer count: 200