Full-Text Search Part-II

Full-Text Index Fragmentation and Crawl
Full-Text Index kullanırken kontrol edilmesi gereken 2 bilgi vardır. Bunlardan biri fragmentation diğeri ise Crawl bilgisi.

Fragmentation Bilgisine aşağıdaki scriptinden ulaşılabilir;

WITH FragmentationDetails
AS (
	SELECT 
		table_id,
        COUNT(*) AS FragmentsCount,
        CONVERT(DECIMAL(9,2), SUM(data_size/(1024.*1024.))) AS IndexSizeMb,
        CONVERT(DECIMAL(9,2), MAX(data_size/(1024.*1024.))) AS largest_fragment_mb
    FROM sys.fulltext_index_fragments
    GROUP BY table_id
)
SELECT 
	DB_NAME()				AS DatabaseName,
	ftc.fulltext_catalog_id AS CatalogId, 
	ftc.[name]				AS CatalogName, 
	fti.change_tracking_state AS ChangeTrackingState,
    fti.object_id				AS BaseObjectId, 
	QUOTENAME(OBJECT_SCHEMA_NAME(fti.object_id)) + '.' + QUOTENAME(OBJECT_NAME(fti.object_id)) AS BaseObjectName,
	f.IndexSizeMb		    AS IndexSizeMb, 
	f.FragmentsCount    	AS FragmentsCount, 
	f.largest_fragment_mb   AS IndexLargestFragmentMb,
	f.IndexSizeMb - f.largest_fragment_mb AS IndexFragmentationSpaceMb,
    CASE
		WHEN f.IndexSizeMb = 0 THEN 0
		ELSE 
			100.0 * (f.IndexSizeMb - f.largest_fragment_mb) / f.IndexSizeMb
	END AS IndexFragmentationPct
FROM 
	sys.fulltext_catalogs ftc
JOIN 
	sys.fulltext_indexes fti
ON 
	fti.fulltext_catalog_id = ftc.fulltext_catalog_id
JOIN FragmentationDetails f
    ON f.table_id = fti.object_id
;

Full-text crawl olduğunda veri akışı sağlanmaz ve aramalarda veriyi bulamayabilirsiniz.
Aramanın devam etmesi için aşağıdaki script çalıştırılmalı.
ALTER FULLTEXT INDEX ON CatalogName SET CHANGE_TRACKING MANUAL; ALTER FULLTEXT INDEX ON TableName SET CHANGE_TRACKING AUTO

Yukarıdaki script çalıştırıldıktan sonra,

select 
FULLTEXTCATALOGPROPERTY ('CatalogName','ItemCount') as [ItemCount],
FULLTEXTCATALOGPROPERTY ('CatalogName','MergeStatus') as [MergeStatus],
FULLTEXTCATALOGPROPERTY ('CatalogName','PopulateCompletionAge') as [PopulateCompletionAge],--Bu değeri aşağıda dateadd kısmında kullanacağız.
FULLTEXTCATALOGPROPERTY ('CatalogName','PopulateStatus') as [PopulateStatus],
FULLTEXTCATALOGPROPERTY ('CatalogName','ImportStatus') as [ImportStatus]
from sys.fulltext_catalogs as cat

scripti ile [PopulateCompletionAge] konu altında gelen veri takip edilmeli, dönen sayıyı;
select DATEADD (ss, PopulateCompletionAge değeri,’1/1/1990′)
sorgusuna bakılarak hangi tarihi crawl ettiği gözlemlenmeli.
!Eğer ki PopulateCompletionAge değişme gözlemlenmiyorsa;

Alter FULLTEXT INDEX On CatalogName Start update Population

komutu ile crawl update edilmeli.
Bu scrip ile crawl_type_desc değeri full_crawl’dan update crawl’a dönmesi beklenmeli. Tablo eğer küçük ise bu kısa zamanda olur. Eğer ki büyük ise günler alabilir.

SELECT [t].[name] [table_name], 
       [i].[name] [index_name],
       [fi].[change_tracking_state_desc], 
       [fi].[has_crawl_completed], 
       [fi].[crawl_type_desc], 
       [fi].[crawl_end_date],
       [ius].[last_user_update], 
       [ius].[last_user_seek],
       (SELECT [name]+',' FROM [sys].[fulltext_index_columns] [fc] INNER JOIN [sys].[columns] [c] ON [c].[object_id] = [fc].[object_id] AND [c].[column_id] = [fc].[column_id] WHERE [fc].[object_id] = [fi].[object_id] FOR XML PATH('')) [columns],
       (CASE WHEN [fi].[crawl_end_date] < ISNULL([ius].[last_user_update], [ius].[last_user_seek]) THEN 'ALTER FULLTEXT INDEX ON ['+[t].[name]+'] SET CHANGE_TRACKING MANUAL; ALTER FULLTEXT INDEX ON ['+[t].[name]+'] SET CHANGE_TRACKING AUTO' ELSE '' END) [Command]
FROM [sys].[fulltext_indexes] [fi]
INNER JOIN [sys].[indexes] [i] ON [i].[index_id] = [fi].[unique_index_id] AND [i].[object_id] = [fi].[object_id]
INNER JOIN [sys].[tables] [t] ON [t].[object_id] = [fi].[object_id]
LEFT JOIN [sys].[dm_db_index_usage_stats] [ius] ON [ius].[index_id] = [fi].[unique_index_id] AND [ius].[object_id] = [fi].[object_id] AND [ius].[database_id] = DB_ID()
ORDER BY [table_name], [index_name]