Recently I have been working with the Wiki project – just to prove that my desktop PC can be as fast as Windows Azure in hosting and searching through the entire Wiki content.
In order to do that I created a local database which contains all wikipedia articles in XML format and it uses XML indexing and Full-Text indexing to perform the search for keywords and also to rank the results.
The articles are already imported – there are 11,946,951 articles in the database. (There were a few articles which were not imported due to some XML error, but the ones that are imported will be enough to prove a point.)
So, right now I am populating the Full Text search catalog, and while I am doing that I can’t help but ask myself some questions:
- how many rows are indexed?
- how big is my Full Text catalog?
- how do I know when the Full Text catalog is populated?
- where is my Full-Text catalog saved?
And since I am a lazy kid, I like to answer my questions with T-SQL code and save the code for later reuse.
Here is some insight on the Full-Text properties:
declare @FTCatalogName nvarchar(250) set @FTCatalogName = 'WikiDocumentFT' SELECT cat.name AS [Name], cat.fulltext_catalog_id AS [ID], CAST(FULLTEXTCATALOGPROPERTY(cat.name,'AccentSensitivity') AS bit) AS [IsAccentSensitive], CAST(cat.is_default AS bit) AS [IsDefault], dp.name AS [Owner], FULLTEXTCATALOGPROPERTY(cat.name,'LogSize') AS [ErrorLogSize], FULLTEXTCATALOGPROPERTY(cat.name,'IndexSize') AS [FullTextIndexSize (MB)], FULLTEXTCATALOGPROPERTY(cat.name,'ItemCount') AS [ItemCount], FULLTEXTCATALOGPROPERTY(cat.name,'PopulateStatus') AS [PopulationStatus], 0 AS [PopulationCompletionAge], cast(null as datetime) AS [PopulationCompletionDate], ISNULL(cat.path,N'') AS [RootPath], CAST((select (case when exists(select distinct object_id from sys.fulltext_indexes fti where cat.fulltext_catalog_id = fti.fulltext_catalog_id and OBJECTPROPERTY(object_id, 'IsTable')=1) then 1 else 0 end)) AS bit) AS [HasFullTextIndexedTables], FULLTEXTCATALOGPROPERTY(cat.name,'PopulateCompletionAge') AS [PopulationCompletionDateInt], FULLTEXTCATALOGPROPERTY(cat.name,'PopulateCompletionAge') AS [PopulationCompletionAgeInt], GETDATE() AS [PopulationCompletionAgeNow] FROM sys.fulltext_catalogs AS cat LEFT OUTER JOIN sys.filegroups AS fg ON cat.data_space_id = fg.data_space_id LEFT OUTER JOIN sys.database_principals AS dp ON cat.principal_id=dp.principal_id WHERE (cat.name=@FTCatalogName)
And here is another script which will tell when the Full-Text catalog population was started:
SELECT TE.name AS [EventName] , DB_NAME(t.DatabaseID) AS DatabaseName , t.DatabaseID , t.NTDomainName , t.ApplicationName , t.LoginName , t.SPID , t.StartTime , t.IsSystem FROM sys.fn_trace_gettable(CONVERT(VARCHAR(150), ( SELECT TOP 1 f.[value] FROM sys.fn_trace_getinfo(NULL) f WHERE f.property = 2 )), DEFAULT) T JOIN sys.trace_events TE ON T.EventClass = TE.trace_event_id WHERE te.name = 'FT:Crawl Started' OR te.name = 'FT:Crawl Aborted' OR te.name = 'FT:Crawl Stopped'
This script is from the Default trace audit tool I developed earlier this year. You can download it from here: Default Trace Audit download.
And here is one more script, which elaborates on the status of the Full-Text catalog population:
DECLARE @CatalogName VARCHAR(MAX) SET @CatalogName = 'WikiDocumentFT' SELECT DATEADD(ss, FULLTEXTCATALOGPROPERTY(@CatalogName,'PopulateCompletionAge'), '1/1/1990') AS LastPopulated ,(SELECT CASE FULLTEXTCATALOGPROPERTY(@CatalogName,'PopulateStatus') WHEN 0 THEN 'Idle' WHEN 1 THEN 'Full Population In Progress' WHEN 2 THEN 'Paused' WHEN 3 THEN 'Throttled' WHEN 4 THEN 'Recovering' WHEN 5 THEN 'Shutdown' WHEN 6 THEN 'Incremental Population In Progress' WHEN 7 THEN 'Building Index' WHEN 8 THEN 'Disk Full. Paused' WHEN 9 THEN 'Change Tracking' END) AS PopulateStatus FROM sys.fulltext_catalogs AS cat
To answer the question “Where the Full-text catalog is saved” is not that easy.
Starting with SQL 2008, the Full-text catalogs are part of the database engine and not saved on the disk system anymore.
The Full-Text catalogs in SQL 2012 are saved in one of the database filegroups.