Backup and restore databases and I/O usage

Question

Post reply

Backup and restore databases and I/O usage

sqlfriend

SSC Guru

Points: 52769
More actions
January 14, 2013 at 2:48 pm

#287595

Does backup and restore databases use a lot of I/O?
And which one uses more I/O, backup or restore?
Thanks

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

Orlando Colamatteo SSC Guru Points: 182276 More actions · Answer 1

sqlfriends (1/14/2013)
Does backup and restore databases use a lot of I/O?

Yes.

And which one uses more I/O, backup or restore?

It depends on where the backup file resides, whether compression is on or off, lots of factors.

Why are you asking?

There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato

sqlfriend SSC Guru Points: 52769 More actions · Answer 2

I ask because our network people are investigating some SAN I/O issues.

They are wondering the backup or restore could generate a lot of I/O and cause problems.

They just found out one of manual retore db from production to test generate a lot of I/Os.

We have scheduled regular backups, we don't have retore scheduled, but occassionally do manual restore.

So that is why this question raised, I would like to know if restore takes much I/o than backup.

Thanks

Orlando Colamatteo SSC Guru Points: 182276 More actions · Answer 3

sqlfriends (1/14/2013)
I ask because our network people are investigating some SAN I/O issues.
They are wondering the backup or restore could generate a lot of I/O and cause problems.
They just found out one of manual retore db from production to test generate a lot of I/Os.
We have scheduled regular backups, we don't have retore scheduled, but occassionally do manual restore.
So that is why this question raised, I would like to know if restore takes much I/o than backup.
Thanks

It depends a lot of variables.

Suffice it to say that both backups and restores are I/O intense operations and they can cause contention for I/O depending on where the backup, data and log files reside.

There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato

sqlfriend SSC Guru Points: 52769 More actions · Answer 4

For backups it usually happens on the same server.

For restores we usually do manually restore on local by copying the backup file to local.

Does reindex or update statistics also use a lot of I/Os?

Thanks

SQLRNNR SSC Guru Points: 281334 More actions · Answer 5

Reindex can also cause higher IO use

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

Grant Fritchey SSC Guru Points: 398712 More actions · Answer 6

Updating statistics can cause a large number of reads if you're doing a full scan. But the number of writes is extremely small.

Backup and restore are almost exclusively I/O operations. If you just think about it, this makes sense. They both of them are reading and writing every single page in the database, for FULL backups. You can look at your system cache using sys.dm_exec_query_stats to see which queries have used more of the disk.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

sqlfriend SSC Guru Points: 52769 More actions · Answer 7

Thanks, I use a query to check I/O usage like refered in this article:

http://sqlblogcasts.com/blogs/steveh/archive/2011/03/22/do-you-send-output-from-sys-dm-exec-query-stats-to-your-developers.aspx

My question is : does the query in the link include I/O usage for back up and restore processes?

It has a column names AvgIO, but when I did this query on a couple of our servers, I don't see any that has something about backup?

Any ideas ?

Thanks,

Grant Fritchey SSC Guru Points: 398712 More actions · Answer 8

This is the first time I've looked for the backup command in sys.dm_exec_query_stats, and you know what? It's not there. So no, I would not say this is a good way to measure all system i/o. This is specific to the data manipulation queries within the system. That's because this is for statistics about cached query plans, and backup doesn't have a query plan. You would want to look at more generic metrics using performance monitor to see overall i/o on the system.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning