We can not know exactly how long it took because that meta data isn't kept in the sys schema of SQL Server. Because we have some insight in how the refresh is implemented we can determine the start and end time by looking at the create_date of the sys.objects rows for the user tables and views. The first table that gets restored is the
Users table. The last View that gets created is
Posts. We can substract the create_date of those records to calculate the number of seconds a restore took.
Combining the above knowledge in this query shows the duration for each database.
Notice how the query leverages our ability to build and run a multi-database query.
declare @sql nvarchar(max)
select @sql = concat('select
rank() over (order by seconds desc) [duration rank]
, rank() over (order by [start]) [start time rank]
, * from (' ,string_agg(concat('
select ''', name , ''' [site]
, min(create_date) [Start]
, max(create_date) [Finish]
, datediff(s, min(create_date), max(create_date)) [seconds]
, (select sum(size)
from ', cast(quotename(name) as nvarchar(max)), '.sys.database_files) [size]
, cast(quotename(name) as nvarchar(max)), '.sys.objects o
where type in (''U'', ''V'')
, ' union all ')
, ') data order by [seconds] desc')
where database_id > 6
and name not like '%_temp'
Today this is the result:
Notice how the duration rank and start time rank do not sync up. Not sure what the signigficance is of this.
Keep in mind SEDE is updated once a week on Sunday.
Give a big shout-out to Monica Cellio for the awesome SEDE Tutorial she wrote.
Say "Hi" in SEDE chat.