Impacts of a change to the Page Verify option
2
votes
1
answer
477
views
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.
Asked by Gio
(107 rep)
Jan 14, 2020, 09:40 PM
Last activity: Jan 15, 2020, 02:22 PM
Last activity: Jan 15, 2020, 02:22 PM