CODEWITHSUNDEEP
sql-server-2008full-text-searchrebuild
Scott
Scott

Reputation: 658

SQL Server 2008 Full Text Rebuild - Large Memory Usage Issue

Each time I perform a SQL Server 2008 FTS Catalog Rebuild, my server is left in a state where the SQL Server process is at 2.8+ GB of memory and is causing performance degradation of the machine. When recycling the SQL Server service, its goes back down to around 350 MB and performance is back to normal.

Is it normal to have to restart the service after a Full Text Rebuild? Not sure what is going on truly behind the scenes but seems like it is a significant memory leak if I could call it that.

I'm open for any advice. Thanks in advance!

Upvotes: 4

Views: 1730

Answers (1)

Rafael Colucci
Rafael Colucci

Reputation: 6078

Sql server does consume a lot of memory when dealing with full text. It is a normal behavior. Populate a full text catalog is hard task and it requires a lot of processing and memory usage. But there are some things you can do to minimize those effects.

1 - Limit sql server memory usage - Sql Server

2 - Make sure your server collation and database collation are the same to avoid the high usage of tempdb (sql tries to convert database collation to match instance collation).

3 - Use incremental population instead of Full rebuild. Check out the link bellow to see how to do that.

4- Make sure you have sql server 2008 SP2 installed.

5 - Use stop lists.

Also check out this link 10 Ways to Optimize SQL Server Full-text Indexing. It gives nice information you can use to maximize your server performance.

Upvotes: 4

Related Questions