"Neils" <neils@shoobridge.de> wrote in message
news:286b284a.0311280216.7df63078@posting.google.com...
> Hi John,
>
> thank you for your fast replay :-))
>
> I hope that after 10 days it will be done...
>
> Right now i'm going to order a Serversystem and i would feel
> better when you take a look on it:
>
> The database includes about 34,000,000 database entries (1 Table).
> 80 Clients are making simultaneous queries on that Data.(DIRECTORY
> ASSISTANCE SERVICE). It is necassary that the responsetime of database
> is around a second.
>
> There should be not only one Server, there should be 3 Servers. One
> for Production the other two for Backup if Server1 goes down
> (Cluster).
>
> The following configuration is planed for each Server:
>
> Dual Xeon 2.8Ghz CPU
> 2GB Ram
> Raid 5 SCSI
> Each HDD 200 GB
> Win2k Server or Win2003 Server
> MSSQL 2000 Server
>
> The Querys includs FREETEXT or CONTAINS parameters.
>
> Is the configuration ok or is there something i should change.
> Is there anything i should pay special attention on it.
>
> Thank you, :-))
> Neils
>
>
>
>
>
> "John Kane" <jt-kane@comcast.net> wrote in message
news:<#$09YxStDHA.980@TK2MSFTNGP10.phx.gbl>...
> > Neils,
> > Hmm... A FT-enabled table with 34 million rows... Depending upon where
you
> > have your FT Catalog residing (on the same drive as your database
files?),
> > this could take a very long time... certainly 8 to 10+ days to complete.
I'd
> > recommend that if you cannot wait that long for the Full Population to
wait
> > that you stop the population and set the "Microsoft Search" Service to
> > manual start and then manually remove the FT Catalog as the MSSearch
service
> > will automaticlly attempt to restart the population.
> >
> > As for a monitioring process, you can use and modifiy the following
T-SQL
> > code:
> >
> > use pubs
> > go
> > BEGIN
> > SET NOCOUNT ON
> > DECLARE @begin datetime
> > DECLARE @end datetime
> > SET @begin = CURRENT_TIMESTAMP
> > EXEC sp_fulltext_catalog 'PubInfo', 'start_full' -- "Full Crawl"
> > -- EXEC sp_fulltext_catalog 'PubInfo', 'start_incremental' --
"Incremental
> > Crawl"
> > --
> > --- Wait for crawl to complete
> > --
> > DECLARE @status int, @itemCount int, @keyCount int, @indexSize int
> > SELECT @status = FullTextCatalogProperty('PubInfo', 'populatestatus')
> > WHILE (@status <> 0)
> > BEGIN
> > WAITFOR DELAY '00:00:01' -- wait for 1 second before checking FT
> > Populatestatus...
> > SELECT @status = FullTextCatalogProperty('PubInfo', 'populatestatus')
> > END
> > SET @end = CURRENT_TIMESTAMP
> > WAITFOR DELAY '00:00:15' -- wait for 15 seconds in order to get correct
FT
> > Property info...
> > SET @itemCount = FullTextCatalogProperty('PubInfo', 'itemcount')
> > SET @keyCount = FullTextCatalogProperty('PubInfo', 'uniquekeycount')
> > SET @indexSize = FullTextCatalogProperty('PubInfo', 'indexsize')
> > PRINT CONVERT(char(30), @begin, 9) + char(09) +
> > CONVERT(char(30), @end, 9) + char(09) +
> > CONVERT(char(30), @end - @begin, 8) + char(09) +
> > CONVERT(char(30), DATEDIFF (hh, @begin, @end)) + char(09) +
> > CONVERT(char(30), DATEDIFF (mi, @begin, @end)) + char(09) +
> > CONVERT(char(30), DATEDIFF (ss, @begin, @end)) + char(09) +
> > CONVERT(varchar(10), @itemCount) + char(09) +
> > CONVERT(varchar(10), @keyCount) + char(09) +
> > CONVERT(varchar(10), @indexSize)
> > SET NOCOUNT OFF
> > END
> > go
> >
> > NOTE: For such a large table, you should modifiy the waitfor to be at
least
> > once ever 5 minutes and the final waitfor to be at least 30 minutes.
Instead
> > of using the PRINT statement, you could write the values out to a
monitoring
> > table. As for how to speed it up, you should review SQL Server 2000 BOL
> > title "Full-Text Search Recommendations" and separate out the FT Catalog
> > folder from your database files, i.e, on a different disk controller and
> > disk array configured as RAID10 or as a strip-set. Also, get more RAM,
at
> > least 1GB and use sp_fulltext_service 'resource_usage' <value> and set
> > <value> to 5 as the MSSearch service can only use 512Mb of RAM, but only
if
> > the RAM is not used by the OS or SQL Server. Additionally, more and
faster
> > CPU's won't hurt as well... Basicly, you need a production level server
to
> > FT Index 34 million rows...
> >
> > Best Regards,
> > John
> >
> >
> >
> > "Neils" <neils@shoobridge.de> wrote in message
> > news:286b284a.0311270949.74247388@posting.google.com...
> > > Hello,
> > >
> > > i have a table with 34,000,000(!) rows on a Sql2000 with Win2k.
> > > (Celeron 1,3 GHZ, 256 MB Ram, 120GB ATA 100)
> > >