March 13, 2025 at 7:05 am
I'm running some restoration activity as part of a rehearsal to prepare for an actual SQL server upgrade from 2008 to 2022. We're planning to restore the backups taken on 2008 R2 database to SQL 2022.
Our backups are stored in a storage shared between the old & new servers.
We have completed this activity 2 times and both recorded almost identical completion time.
However on the 3rd run I notice the restoration time took almost 3 times longer compared to previously, small databases full restoration which took about 10mins in the past 2 runs now took more than 30mins while large 4TB database which took 8 hours to restore now took 18 hours!
There hasn't been any drastic changes to the DB sizes in the source server. Our restore script are the same & on this new server there are not any other activities besides the restore job. I tried to restart the server and rerun the restoration, yet it is still slower compared to the 2 runs previously.
Is there any idea where I should be looking to start my investigation?
March 13, 2025 at 7:26 am
I'm running some restoration activity as part of a rehearsal to prepare for an actual SQL server upgrade from 2008 to 2022. We're planning to restore the backups taken on 2008 R2 database to SQL 2022.
Our backups are stored in a storage shared between the old & new servers.
We have completed this activity 2 times and both recorded almost identical completion time.
However on the 3rd run I notice the restoration time took almost 3 times longer compared to previously, small databases full restoration which took about 10mins in the past 2 runs now took more than 30mins while large 4TB database which took 8 hours to restore now took 18 hours!
There hasn't been any drastic changes to the DB sizes in the source server. Our restore script are the same & on this new server there are not any other activities besides the restore job. I tried to restart the server and rerun the restoration, yet it is still slower compared to the 2 runs previously.
Is there any idea where I should be looking to start my investigation?
Hi Jason,
March 13, 2025 at 8:57 am
The target server is basically an empty server hosting SQL 2022 it hasn't gone live yet, thus no jobs running there. So I could say no changes was done at target server. Before the restore all databases in the target server was dropped.
I reach out to my system admin and they found something rather interesting. the server's IO latency increase a lot on the third restore. During first & second restore, the IO avg latency was around 60ms while for this restore IO avg latency was around 300ms. Any clue what might causes this?
March 14, 2025 at 11:07 am
Instant file initialization in sql 2022? https://learn.microsoft.com/en-us/sql/relational-databases/databases/database-instant-file-initialization?view=sql-server-ver16
However the latency points to hardware ( perhaps an ssd buffer got full, your sql server got migrated to slower storage due inactivity ...)
March 14, 2025 at 7:39 pm
What storage array are you using - and did they configure the storage using some type of tiering architecture?
I had a similar issue where everything worked fine the first time - but came back the next day and saw latency issues. Turned out they put us on tiered storage and the database files were pushed down to the lowest tier because they hadn't been accessed in XX time. That lowest tier happened to be 7500RPM drives.
If the new server is a VM - is the storage presented from the VM pool or is that storage presented directly to the VM?
Was that latency seen on the NIC - or was it seen on the HBA? If the NIC - get security/firewall and network teams involved to see if they changed any firewall rules (or made any changes - which they will say they didn't, but probably did).
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply