I found last_wait_type is ASYNC_IO_COMPLETION for this backup job. We
have sufficient disk space available.
This blogpost from Paul Randal gives you most of the information regarding this wait stat
...The official definition of ASYNC_IO_COMPLETION is ‘Occurs when a task
is waiting for I/Os to finish....
...Long-duration ASYNC_IO_COMPLETION waits commonly are from data backups....
A question similar to yours can be found on that same blog.
In his case a backup of a small database (1,5GB) was taking over 15 minutes.
...I’m facing a high level of ASYNC_IO_COMPLETION into one of my environments what is resulting in backups of small databases (around 1,5GB) to take more than 15 minutes. I have a feeling that it is related to low performance on the I/O
subsystem. But I’m not 100% confidente to go ahead on this diagnostic
to ask for analysis or changes on that.
Yup – that’s what it is. Try doing a backup to nul, as that will give
you time taken to read from the data files. The difference is the time
being taken to write out the backup files – there’s your ammo.
This is the command to do that:
BACKUP DATABASE DatabaseName TO DISK = 'NUL'
And compare this with the backup time you mentioned previously.
Keep in mind that this will still start a huge amount of I/O operations on your server.
And the backup job is also showing suspended state
Suspended state simply means that it is waiting on resources, I/O based in your case.
Check what else is running on the instance, any maintenance plans such as index rebuilds, statistic updates, integrity checks?
You could start logging queries with sp_whoisactive and perhaps logging it in a table.
Striping your backups accross multiple files can help in reducing backup time. More on that here.
My go to is 4 files but you could test different settings.
Since you are on
sql server 2012, if you are not using
TDE you could leverage backup compression as well.
More information on making backups go faster can be found here