Question on partitioning indexes

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Kyle Jedrusiak - 27 Sep 2005 16:54 GMT

We have two tables that have full text indexes, currently both are using the
same catalog.

One table is much larger and the column being indexed contains more data.

Would there be any advantage of seperating the indexes into tow seperate
catalogs?

Kyle!

Reply to this Message

John Kane - 28 Sep 2005 04:44 GMT

Kyle,
This is one of those questions, where the answer is that it depends... First
of all, see SQL Server 2000 BOL title "Full-Text Search Recommendations" -
"There are also full-text indexing and searching considerations when
determining whether to include multiple SQL tables in one full-text catalog
versus one SQL table per full-text catalog. There is a trade-off between
performance and maintenance when considering this design question with large
SQL tables and you may want to test both options for your environment. If
you choose to have multiple SQL tables in one full-text catalog, you incur
the overhead of longer-running full-text search queries as well because
incremental populations will force the full-text indexing of all other SQL
tables in that full-text catalog. If you choose to have a single SQL table
per full-text catalog and have multiple SQL tables full-text indexed, you
have the overhead of maintaining separate full-text catalogs with a total
limit of 256 full-text catalogs per server."

Another consideration is whether or not you are using CONTAINSTABLE or
FREETEXTTABLE with RANK as having multiple tables in one FT Catalog can
affect the Ranking values...

Hope that helps!
John

Signature

SQL Full Text Search Blog
http://spaces.msn.com/members/jtkane/

> We have two tables that have full text indexes, currently both are using
> the same catalog.
[quoted text clipped - 5 lines]
>
> Kyle!

Reply to this Message

Kyle Jedrusiak - 28 Sep 2005 14:50 GMT

This is good stuff. The article was good as well.

We can't seperate the catalog onto a different drive as we only have a RAID5
setup with 6 physical drives and one logical drive.

One table has over 100K records, the other over 94K records. It's not
millions of records, but we are trying to tweak search performace as much as
we can. I don't forsee ever adding 252 more catalogs anywhere in the
future. So seperating the FTI for each table into it's own catalog
shouldn't be an issue.

Thanks

Kyle

> Kyle,
> This is one of those questions, where the answer is that it depends...
[quoted text clipped - 28 lines]
>>
>> Kyle!

Reply to this Message

John Kane - 28 Sep 2005 15:48 GMT

You're welcome, Kyle,
Actually, I wrote that years ago (before SQL 2000 shipped) while I was at
MSFT. You may want to review the collection of FTS related articles at

SQL Server 2000 Full-Text Search Resources and Links
http://spaces.msn.com/members/jtkane/Blog/cns!1pWDBCiDX1uvH5ATJmNCVLPQ!305.entry

for more information on performance and problems/workarounds.

Enjoy!
John

Signature

SQL Full Text Search Blog
http://spaces.msn.com/members/jtkane/

> This is good stuff. The article was good as well.
>
[quoted text clipped - 44 lines]
>>>
>>> Kyle!

Reply to this Message