Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question
add comment

2 Answers

up vote 1 down vote accepted

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
share|improve this answer
    
That query works well to check the status of an existing catalog. I'm looking to determine how large an uncreated catalog will be, based on the data that will be thrown into it. Sorry for the confusion. I updated my question accordingly. –  dangowans Aug 13 '12 at 19:11
2  
Can you create a database somewhere else and load a sample amount of data? I don't know of any other way to estimate. It really depends on the average size of your documents as well as the number of noise words you have defined. –  datagod Aug 13 '12 at 19:32
    
I do have an R&D server available for testing, so that may be the only way. Just wondering if a magic formula existed for calculating such a thing. –  dangowans Aug 13 '12 at 19:38
add comment

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.

share|improve this answer
    
I wonder if there are some statistics out there, like the "average number of unique words in an English article" that would make this calculation more accurate. –  dangowans Aug 14 '12 at 11:30
    
Or the "average number of noise words in an English article". The final number can only get better. –  dangowans Aug 14 '12 at 11:31
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.