I don’t do a lot of work with full text. It’s interesting tech and I wish I used it more. Recently we needed to do a timing run for the full population of a table with about 80 million rows. The dm_fts_index_population views shows the status while it’s populating, but not the estimated completion time (or the final completion time either). This is far from fully baked, but I ran it at about the 90 minute mark and the estimate of 130 minutes ended up being close. For my purposes knowing to come back and check in an hour or two (or 24) was all I needed, but it might also save running the full population to completion as we change thread counts to see the impact. Why does the population time matter? Recovery if it breaks and we have to rebuild.
declare @StartTime datetime declare @TotalRows int = 0 declare @DoneRows int = 0 declare @SecondsSoFar int = 0 set nocount on --get the start time for this population select @StartTime = start_time from sys.dm_fts_index_population -- where database_id = x and catalog_id = x and table_id = x --get row count from the table select @TotalRows = count(*) from ~~yourtable~~ with (nolock) --get how many done so far select @DoneRows = sum(processed_row_count) from sys.dm_fts_population_ranges --where something --how much time elapsed so far, in seconds select @SecondsSoFar = datediff(ss, @StartTime, getdate()) --guess the completion time print 'rows completed = ' + convert(varchar(255), @DoneRows) print 'rows remaining = ' + convert(varchar(255), @TotalRows - @DoneRows) print dateadd(ss, (@TotalRows / (@DoneRows / @secondssofar)), @StartTime)
SQLAndy 