SQL Server 2005: Full-text search space requirements

Question

Is there a way to calculate the physical drive space required for a full-text search catalog? Space is cheap, but I'd like to know what I'm getting into.

I'm looking at a table of about 200 articles ranging in length. I'd like to index the title field, and the body of the article.

UPDATE: I'm somewhat looking to predict the future. For example, 200 articles, the title is varchar(500) and the article body is varchar(max). So, without creating the index, can an estimate be done on how large the full-text search catalog may be?

Answer 1

Here is some old code I wrote for SQL 2000. It still works on SQL 2005. You can use this to come up with a rough estimate of how much space you are using per document, then multiply that by your estimated number of documents.

   select 'CatalogName'   = left([name],30),
          'Status'        = case(FULLTEXTCATALOGPROPERTY ([name],'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 is full.  Paused.'
                              when 9 then 'Change tracking'
                              else 'Unknown'       
                            end,
          'ItemCount'     = FULLTEXTCATALOGPROPERTY (name,'itemcount'),
          'IndexSize(MB)' = FULLTEXTCATALOGPROPERTY (name,'indexsize'),
          'UniqueWords'   = FULLTEXTCATALOGPROPERTY (name,'uniquekeycount'),
          'ErrorLogBytes' = FULLTEXTCATALOGPROPERTY (name,'logsize'),
          'Location'      = left(s.path,50) 
   from sysfulltextcatalogs s

Answer 2

Found a link that helps with the estimate.

http://msdn.microsoft.com/en-us/library/ms345119%28v=sql.90%29.aspx#yukonftsea_topic3

So, lets play.

Doing some digging into other article-like tables I have, I've found the longest article to be about 4000 characters in length. I'll assume all of the words are unique, and none of the words are noise. Lets say every word is 5 characters. So, there are approximately (4000 / 5) = 800 unique words or tokens.

The longest title, 70 characters. Using the same logic, (70 / 5) = 14 unique words or tokens.

The simplified index structure, as shown in the link, indicates that there are additional columns for tracking columns, documents, and occurrences, along with additional columns not shown. In this crazy example, lets say 10 integers, at 4 bytes a piece.

So,

5 bytes per word + 40 bytes for index details = 45 bytes per record

45 bytes x (800 + 14) records = 36,630 bytes total per article

36,630 x 200 articles = 7,326,000 bytes

Which is approximately 7 MB.

These are all rough numbers, especially as I'm unclear on how many additional columns there are, how the compression works, etc. Even if I'm off by "a long shot", it's still likely less than 10 MB. I think I can spare that.