How long does the Data Explorer Refresh take for each database?

Question

Each Sunday at 03:00 UTC the Stack Exchange Data Explorer gets refreshed. It takes 6 to 8 hours to refresh all 350+ databases that hold the Posts, Votes, History etc for the Main and Meta sites.

I was wondering if we can know how long each database takes so I don't have to wait till 11:00 UTC if I'm not interested in all databases.

What are the start and end times of the restore for each database and how much time did that restore took?

Answer 1

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]
    from '
    , cast(quotename(name) as nvarchar(max)), '.sys.objects o
    where type in (''U'', ''V'')
    ')
  , ' union all ')
  , ') data order by [seconds] desc')
from sys.databases
where database_id > 6
and name not like '%_temp'

exec(@sql)

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.