SQL 2005 FT Index build performance

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

jwilmsen@gmail.com - 27 Sep 2005 17:12 GMT

Anyone have any experience building large full text indexes on SQL
Server 2005. I am trying to build a 100 Million row index using
Full-Text 2005 JUNE CTP. My average row size is about 500 chars in a
varchar field.

Index build performance is progressively slowing down. I started out
with a rate of >1.5+M rows/hour and now down to below 200K rows/hour
after completely ~30M rows.

Is there anything I can do to speed up index creation? I have tried
changing 'resource_usage' to 5 but that didn't have much affect. Would
MaxPropStoreCachedSize have any affect on index build? I did notice
that index build is using logging even though my db is set to simple
recovery.

I have tried restarting SQL and FT, although that does not appear to
have any affect on performance. I am obviously concerned that if the
index rate continues to slow down I will never finish.

This is not running on a huge machine, although everything appears to
be running in RAM. It is a 3ghz P4, 1GB RAM, RAID 0 Disk. The CPU is
not taxed and it has constant IO although it doesn't appear extreme.

Thanks in Advance.

Reply to this Message

John Kane - 28 Sep 2005 04:53 GMT

Jwilmsen,
Yes, I do. I worked with a client who FT Indexed a table with 180 million
rows using SQL Server 2005. Are the FT Catalog files on the same drive as
the as the database files? If so, can you re-locate it to another drive
separate from the database files? Does your FT-enabled table have a
clustered index? If not, then create a Clustered index.

What are the run-time values for the following new FTS related sp_configure
parameters? (with show advanced options enabled):
sp_configure

/* -- (edited, new FTS related parameters only)

name minimum maximum config_value
run_value

----------------------------------- ----------- ----------- ------------ -----------

ft crawl bandwidth (max) 0 32767 100 100

ft crawl bandwidth (min) 0 32767 0 0

ft notify bandwidth (max) 0 32767 100 100

ft notify bandwidth (min) 0 32767 0 0

max full-text crawl range 0 256 4 4

if possible, I'd recommend upgrading to the Sept CTP version of SQL Server
2005 as past CTP versions have not be optimized for large.
MaxPropStoreCachedSize is a registry key value that is more approprate for
SQL Server 2000. SQL Server 2005 FT Indexing can be optimized via other
means.

Thanks,
John

Signature

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

> Anyone have any experience building large full text indexes on SQL
> Server 2005. I am trying to build a 100 Million row index using
[quoted text clipped - 20 lines]
>
> Thanks in Advance.

Reply to this Message

jwilmsen@gmail.com - 28 Sep 2005 14:23 GMT

My FT catalog is on the the same disk. I could move it but it would be
off the RAID at that point. My FT enabled table does have a clustered
index and the stats are up to date.

My runtime values are the defaults which you have printed there.

I did add another gig of ram yesterday and my rate went up to 1.5M
rows/hr but overnight has slowed back down to ~500K rows/hr with just
of 49M rows indexed.

It sounds like it might be worth upgrading to the Sept CTP.

If you don't mind me asking, do you have any information on your index?
How long did it take to build on roughly what size rows and hardware?

Thanks,
Jim

Reply to this Message