August 18, 2015 at 7:16 am
Hello All,
I am troubleshooting I/O / Network issues and I'd like to find on average how fast SQL restores are going in regards to MBps / Mbps / Gbps, etc.
Is this possible to do? I have yet to find any T-SQL syntax that will give me this information.
Thank you for your help!
Michael
August 18, 2015 at 9:45 am
Using DMVs
you can use something Like this
SELECT session_id as SPID, command, a.text AS Query, start_time, percent_complete, dateadd(second,estimated_completion_time/1000, getdate()) as estimated_completion_time
FROM sys.dm_exec_requests r CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) a
WHERE r.command in ('BACKUP DATABASE','RESTORE DATABASE')
August 18, 2015 at 11:51 am
I have used this one (and still do) but I cannot figure out how to output the MBps/Mbps/GBps/Gbps rate at which it is restoring!
August 18, 2015 at 12:19 pm
Run Windows Resource monitor on the server where the DB is being restored and look at the Disk stats.
August 18, 2015 at 11:08 pm
Real time is a little tricky to do, but this will restore the database and tell you the average speed of the data transfer:
restore database DatabaseName
from disk = 'F:\backups\DatabaseName_backup_1234567890.bak'
with
move 'DataFile_LogicalName' to 'd:\data\DatabaseName_Data.mdf'
,move 'LogFile_LogicalName' to 'e:\logs\DatabaseName_Log.ldf'
,replace
,recovery
,stats = 10
The stats option is the important part. This will give you percentage complete in chunks of 10 percent, and also provide you number of pages, data transfer speed etc. for the restore. If you use it with BACKUP DATABASE you'll get the same sort of stats.
August 19, 2015 at 2:30 am
Although it wont give you the network stats you may want, using sp_whoisactive will give you the percentage complete to a few decimal places. You may be able to determine restore rates based on different scenarios and work out the different bottleneck values.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply