Populating text index takes too long....

[imosri]

Hi,

I am text indexing a table with 12Gb of text, 300K records.

I am running SQL 2K SP 4 on a Dell 2650 dual 3Ghz CPUs, 4Gb memory.

A full population takes about 3 hours, the question here is mssearch.exe is using 100Mb memory and only 3-4% CPU usage. The server has plenty of resources left available.

Is there a way to improve performance? I mean, make mssearch.exe to use more memory and CPU cycles to perform the task faster.

The target database is not being used by any user when doing this process.

Regards,

 

[Catadmin]

Right-click your server and go to properties. See what memory allocation is set at on it. It might be set with a min-max property that doesn't enable it to fully utilize all the available server memory.

Also, might want to look at the CPU Priority. If "Boost SQL Server priority" isn't checked, try checking it and see if that helps.

FYI: You might have to restart either the services or the actual box after making changes.



Catadmin - MCDBA, MCSA
"No, no. Yes. No, I tried that. Yes, both ways. No, I don't know. No again. Are there any more questions?"
-- Xena, "Been There, Done That"

 

[mrdenny]

The mssearch service always runs very low.

You shouldn't have to do a full population very often. You should be able to do a differential population to catch just the changes. You can also setup the full text index to track changes so that populations aren't needed.

Denny
MCSA (2003) / MCDBA (SQL 2000) / MCTS (SQL 2005) / MCITP Database Administrator (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[imosri]

Catadmin, mrdenny

Thanks for your respones.

The state of the database is 99% fixed data alredy. So differential population will be fast.

The thing here is this is a partial test database, about 1/10 of the production database. When doing the full population mssearch seems very linear when processing. This means that it would take 30 hours on the production database to comlete the task.

Considering that, the next question would be if the population process is affected by locks issued bye the normal operation of the system.

Catadmin, the modifications for memory and processor are for SQL, as I understand mssearch is independent from SQL Server. I'll try them anyway. Thanks

Regards,

 

[mrdenny]

Yes it would be affected by normal database locking. However if the database is 99% fixed that shouldn't be much of an issue as everything should be select locks which are shared.

Denny
MCSA (2003) / MCDBA (SQL 2000) / MCTS (SQL 2005) / MCITP Database Administrator (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com