2

There's no doubt about importance of doing regular integrity checks on production databases.
I'm testing the impact of changing the maintenance plans and changing how databases are backed up.
First of all, it is necessary to activate the "checksum" pages verification mode, this changing I think gradually impact the performance because it activates when a certain page is read in memory, changed, and written back to disk.

I have started to test this change with backups. I prepared a backup TSQL script that save the duration of each backup command and I ran 100 times each of the following command:

  • BACKUP DATABASE [MyDB] TO DISK = N'nul' (ran 100 times)
  • BACKUP DATABASE [MyDB] TO DISK = N'nul' WITH CHECKSUM (ran 100 times)

MyDB it's a 50gb database with page verify set to "checksum". The results is:

  • Backup with checksum is 20% slowler

I started some test with a larger database (400GB) and I noticed that first backups (with empty cache) are generally slower. After the first backups have completed the duration tends to stabilize.

My questions are:

  • Does the backup process buffers data in cache ? If yes, could this cause a variation of PLE trend or memory pressure ?
  • If backups with checksum are 20% slower what happens with application queries? I don't think my tests are the absolute truth but..
  • Is there any kind of waiting time linked to the checksum process?
  • Do you know if there are people online who have tested the impacts of this change ? Any additional material can be useful.

1 Answer 1

2

Q1

Does the backup process buffers data in cache ? If yes, could this cause a variation of PLE trend or memory pressure ?

The backup process does not read data into the buffer pool, it uses separate threads that use buffers to read and write the data pages. More on that here.

It does create a checkpoint prior to starting the backup. This checkpoint will flush dirty pages to disk.

A full backup does the following:

Force a database checkpoint and make a note of the log sequence number at this point. This flushes all updated-in-memory pages to disk before anything is read by the backup to help minimize the amount of work the recovery part of restore has to do. ...

Source

Q2

If backups with checksum are 20% slower what happens with application queries? I don't think my tests are the absolute truth but..

Performance benchmarking with or without checksum has been tested before.

See here.

In that test, the average was 94.1 seconds for an update query on a checksum enabled database & 93.7 seconds for the same update query on a database without any page verify option. I performed some similar tests (1M row updates with different page verify options) and also found negligible differences.

Some test results:

4x 1M rows updated (nvarchar(256), 156 characters) without checksum:


CPU time = 1235 ms,  elapsed time = 1231 ms.
CPU time = 1218 ms,  elapsed time = 1213 ms.
CPU time = 1547 ms,  elapsed time = 1583 ms.
CPU time = 1203 ms,  elapsed time = 1199 ms.


4x 1M rows updated (nvarchar(256), 156 characters)  with checksum: 

CPU time = 1312 ms,  elapsed time = 1310 ms.
CPU time = 1282 ms,  elapsed time = 1282 ms.
CPU time = 1343 ms,  elapsed time = 1361 ms.
CPU time = 1282 ms,  elapsed time = 1291 ms.

Also I did several backup tests on a database with a data file of 34GB and I am getting nowhere near 20 percent difference.

The backups to 'NUL' getting faster due to caching does appear to be correct.

4 WITH CHECKSUM

BACKUP DATABASE successfully processed 4224190 pages in 89.307 seconds (369.528 MB/sec).
BACKUP DATABASE successfully processed 4224186 pages in 89.339 seconds (369.395 MB/sec).
BACKUP DATABASE successfully processed 4224186 pages in 85.930 seconds (384.050 MB/sec).
BACKUP DATABASE successfully processed 4224186 pages in 83.255 seconds (396.389 MB/sec).


4 WITHOUT CHECKSUM


BACKUP DATABASE successfully processed 4224186 pages in 75.234 seconds (438.650 MB/sec).
BACKUP DATABASE successfully processed 4224186 pages in 74.964 seconds (440.230 MB/sec).
BACKUP DATABASE successfully processed 4224186 pages in 78.338 seconds (421.269 MB/sec).
BACKUP DATABASE successfully processed 4224186 pages in 77.531 seconds (425.654 MB/sec).


4 WITH CHECKSUM

BACKUP DATABASE successfully processed 4224186 pages in 74.598 seconds (442.390 MB/sec).
BACKUP DATABASE successfully processed 4224186 pages in 71.718 seconds (460.155 MB/sec).
BACKUP DATABASE successfully processed 4224186 pages in 71.044 seconds (464.521 MB/sec).
BACKUP DATABASE successfully processed 4224186 pages in 66.624 seconds (495.338 MB/sec).


4 WITHOUT CHECKSUM

BACKUP DATABASE successfully processed 4224186 pages in 63.593 seconds (518.947 MB/sec).
BACKUP DATABASE successfully processed 4224186 pages in 65.338 seconds (505.088 MB/sec).
BACKUP DATABASE successfully processed 4224186 pages in 62.314 seconds (529.599 MB/sec).
BACKUP DATABASE successfully processed 4224186 pages in 61.529 seconds (536.355 MB/sec).

Q3

Is there any kind of waiting time linked to the checksum process?

Not sure what the question is here, maybe the following part of a blogpost can give more insight:

Of course, WHEN you switch a database over to using CHECKSUM there is NOT some magical process or operation that goes through and ‘writes’ CHECKSUMs into your EXISTING pages. If there were, toggling this option could obviously cause some ugly problems. But, instead, since this is effectively a ‘meta-data’ change that instructs SQL Server to start using a new behavior (i.e., CHECKSUM functionality for writes and reads of previously CHECKSUM’d pages), you can safely toggle this change at pretty much any time without fear of bogging down your systems.

Of course, since this, in turn, means that you may have gobs of existing/older data that’s been laying around without CHECKSUM configured, that data (or the data on those pages) will NOT end up getting CHECKSUM values until that data is finally pulled into SQL Server and then re-written. Consequently, if you want to ‘force’ CHECKSUMs into your pages, you’ll need to do full-blown index rebuilds or other modifications that force all data to be pulled in and then rewritten.

Source

Checksums are not applied immediately when enabling the setting, the page needs to have been modified to apply the new checksum.

Q4

Do you know if there are people online who have tested the impacts of this change ? Any additional material can be useful.

See answer to Q2.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.