May 31, 2011 at 1:07 pm
I have been looking for info on how to monitor fragmentation levels of full-text-search (FTS) indexes on an ongoing basis, and came across this link below, that provides a helpful explanation:
Fulltext Fragmentation (iFTS):
http://sqldev.wordpress.com/2008/02/24/fulltext-fragmentation-ifts/
Basically, it suggests that the FTS index should be reorganized once the number of fragments per table reaches 30-50:
SELECT OBJECT_NAME([table_id]) AS TableName, COUNT([fragment_id]) AS Fragments
FROM sys.fulltext_index_fragments
GROUP BY OBJECT_NAME([table_id])
HAVING COUNT([fragment_id]) >= 30
Is this what people use? I would be interested in people's opinions on this. There is not an awful lot of info on this out there.
__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]
June 2, 2011 at 6:33 am
the whole point on all this is you have to find what works for you. If your system is 7 x 24 then your problems are different to those for one which is only available 9 - 5 monday to friday.
I see systems where any type of re-index is set once a week, others every day, some even more than once a day.
It's all down to performance vs maint windows. The longer it takes to rebuild an index the more your problems may become. If you can rebuild every night then do so, if not work out a strategy.
You should track the fragmentation whatever the situation.
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply