snapshot restores getting slower

Question

snapshot restores getting slower

simon_s

Ten Centuries

Points: 1358
More actions
July 24, 2017 at 5:22 am

#402353

Hi, in one of our test environments I've noticed snapshots restores are taking 4 x the amount of time they where taking a couple of weeks ago .. any ideas as to where to look for root cause ?
some (hopefully) pertinent info ;
All servers SQL 2008 R2
Same scripts are being ran each time before restoring so no real change in complexity of transactions
DB size remains relatively constant
Snapshots are restored from local storage, disk space is not an issue
Increase is apparent on 3 separate servers
SQL is restarted pretty much before each restore
There seems to be two threads per restore SPID and both running with a waits mainly ASYNCH_IO_COMPLETION. There are no other processes/jobs running on the box as the same time as the restores.

any words of wisdom gratefully received ?

Steve Jones - SSC Editor SSC Guru Points: 736270 More actions · Answer 1

It would be good to get more information about what is happening here. You've outline something, but there are multiple ways to make a disk snapshot, so I don't quite know what you mean by this term.

simon_s Ten Centuries Points: 1358 More actions · Answer 2

Hi Steve

Yes of course , hth

CREATE DATABASE My_DB1_snapshot ON ( NAME = N'My_DB1', FILENAME = N'c:\shared\My_DB1_snapshot.snap' ) AS SNAPSHOT OF My_DB1;
RESTORE DATABASE My_DB1 from DATABASE_SNAPSHOT = 'My_DB1_snapshot';

Its the restore that has gone from 5 mins per db to 25 mins

many thanks Simon

Perry Whittle SSC Guru Points: 234013 More actions · Answer 3

the snapshot sparse files may be a lot larger now since the original snapshot was taken

-----------------------------------------------------------------------------------------------------------

"Ya can't make an omelette without breaking just a few eggs" 😉

Steve Jones - SSC Editor SSC Guru Points: 736270 More actions · Answer 4

Sorry, got busy yesterday.

I know over time there sometimes are issues. Are you dropping the snapshot? Dropping all snapshots at anytime?

Perry Whittle SSC Guru Points: 234013 More actions · Answer 5

Steve Jones - SSC Editor - Wednesday, July 26, 2017 8:51 AM
Sorry, got busy yesterday.
I know over time there sometimes are issues. Are you dropping the snapshot? Dropping all snapshots at anytime?

in order to restore\revert to a snapshot all others have to be dropped, this is a pre req

-----------------------------------------------------------------------------------------------------------

"Ya can't make an omelette without breaking just a few eggs" 😉

simon_s Ten Centuries Points: 1358 More actions · Answer 6

Steve Jones - SSC Editor - Wednesday, July 26, 2017 8:51 AM
Sorry, got busy yesterday.
I know over time there sometimes are issues. Are you dropping the snapshot? Dropping all snapshots at anytime?

We drop /recreate snapshots on a regular basis so its not that 🙂