Problems with Full Text Index Population

I've currently got a problem with a large full text index on table
that has around 130 million rows. The table has a total of 6 columns
enabled for full-text indexing, and the total size of the index is
around 40GB.
The problem is that the index will, on occasion, show 'paused'. It
does this after the index has been working and updating normally for a
number of weeks, and from this point onwards we have been unable to
re-start population of the index. The only we have found of bringing
the index up again is to do a full population - which takes around 3
weeks.
I found a document that described some of the performance counters
that can be used to monitor what the FTI is doing. In particular, a
counter named Microsoft Gatherer : Reason for Backoff is showing 'High
Notification Rate' (see attached gif) when the index population
pauses.
At present we have both 'change tracking enabled' and 'update index in
background' enabled on the database, and the performance counter is
therefore indicating to me that, given the size of the database, it
simply can't keep up with the number of changes that are happening in
the background.
One option is to stop updating the index in the background, and
schedule an incremental index update at low periods (overnight),
whilst still using the 'change tracking option'.
Has anyone come across this situation before?
I have had a couple of thoughts about things to try
1. the db server has 8 CPUs - I understand that one (or more) CPUs can
be decidated to the MSSearch service.
2. use the sp_fulltext_service, 'resource_usage' stored proc and maybe
set to 4 in order to force the server to dedicate more resources to
the index population
Any advice or suggestions would be gratefully received.
Regards
Craig
Craig,
Could you provide the full output of -- SELECT @.@.version -- as this is very
helpful info in troubleshooting FTS issue. For now, I'm going to assume that
you're using SQL Server 2000 based upon the below information. As Microsoft
has never formally documented the upper limit of rows that SQL Server 2000
will support, as they have only acknowledged that this version was tested up
to 20 million rows. As you're attempting to FT Index a table with 130
million rows, I'd recommend that you consider using SQL Server 2005
(codename Yukon) as it has been acknowledged by Microsoft to have been
tested up to 200 million row. However, keep in mind that SQL Server 2005 is
still in a beta stage (Beta2) and will not be formally released until
sometime in 2005.
I'm assuming that the document you found that spoke of the performance
counters is referenced in "SQL Server 2000 Full-Text Search Deployment white
paper" at http://support.microsoft.com/default...b;en-us;323739
You may also want to review the Dec 2003 version of "SQL Server 2005
Full-Text Search: Internals and Enhancements" at
http://msdn.microsoft.com/library/de...ovyukondev.asp
Finally, you may also want to review this KB article 303459 (Q303459) "INF:
How to Improve the Performance of FTS Queries for Large Tables" at
http://support.microsoft.com/?id=303459
Hope this helps!
John
"cms2001" <info@.craigstreet.com> wrote in message
news:94e7d7f4.0412021704.47e15474@.posting.google.c om...
> I've currently got a problem with a large full text index on table
> that has around 130 million rows. The table has a total of 6 columns
> enabled for full-text indexing, and the total size of the index is
> around 40GB.
> The problem is that the index will, on occasion, show 'paused'. It
> does this after the index has been working and updating normally for a
> number of weeks, and from this point onwards we have been unable to
> re-start population of the index. The only we have found of bringing
> the index up again is to do a full population - which takes around 3
> weeks.
> I found a document that described some of the performance counters
> that can be used to monitor what the FTI is doing. In particular, a
> counter named Microsoft Gatherer : Reason for Backoff is showing 'High
> Notification Rate' (see attached gif) when the index population
> pauses.
> At present we have both 'change tracking enabled' and 'update index in
> background' enabled on the database, and the performance counter is
> therefore indicating to me that, given the size of the database, it
> simply can't keep up with the number of changes that are happening in
> the background.
> One option is to stop updating the index in the background, and
> schedule an incremental index update at low periods (overnight),
> whilst still using the 'change tracking option'.
> Has anyone come across this situation before?
> I have had a couple of thoughts about things to try
> 1. the db server has 8 CPUs - I understand that one (or more) CPUs can
> be decidated to the MSSearch service.
> 2. use the sp_fulltext_service, 'resource_usage' stored proc and maybe
> set to 4 in order to force the server to dedicate more resources to
> the index population
> Any advice or suggestions would be gratefully received.
> Regards
> Craig
|||Hi John
Many thanks for getting back to me on this. Your assumption of SQL
Server 2000 is correct - SP3 to be precise.
And yes, the whitepaper you added a link to was the document i was
talking about. This has proved very useful so far in our
investigations, along with the other link you added.
As far as SQL Server 2005 goes, this is something that we are working
on in the background. I have read up on the new full text capabilites,
and understand that it has been re-written from the ground up. I am
sure that it would provide a much more scalable platform given the
volumme of data we are talking about.
Our current FTI problems on SQL Server 2000 are actually delaying the
launch of a product, so its imperative that we get this up and running
as soon as possible in some shape or form. The business has some
decisions to make around SQL Server 2005, but there are also
associated risks given its current beta state, so I need to persevere
with SQL Server 2000 for now.
I did have a couple of thoughts around possibilites that we could try.
I don't have the exact specs of the hardware we are running this on at
the moment - but i do know that the box has a total of 8 processors.
Given this, i'm thinking about the following
1/ assign MSSearch.exe to CPU0, and set sp_fulltext_service
'resource_usage', to 4 or 5 to allow the full text engine to consume
more resources
2/ assign SQL Server processors CPU1-6
3/ CPU7 should be left free to handle the NIC
As far as i know, MSSearch will only use a single processor, so even
if we commit CPU0 fully (sp_fulltext_service, 'resource_usage', 5) to
MSSearch, SQL Server should still have 6 processors to play with.
There may also be an option to schedule population of the index during
low periods (overnight) rather than in the background. This would be a
compromise on required funtionality, but may at least be a way forward
in the short term. I'm unsure at present of whether this would help
our situation, but may be worth a try. What do you read into the 'High
Notification Rate' - do you think a scheduled index population may
help in this situation.
The other interesting thing is that although we have the 'High
Notification Rate' message, the CPUs so not seem to be maxing out
(running at 15-25%), and IO seems as though it seems to have some
spare capacity.
Again, many thanks for helping us out with this. If you have any
thoughts on the above, or any other guidance or suggestions, you input
would be much appreciated.
Regards
Craig
"John Kane" <jt-kane@.comcast.net> wrote in message news:<eHoqKsO2EHA.3392@.TK2MSFTNGP10.phx.gbl>...[vbcol=seagreen]
> Craig,
> Could you provide the full output of -- SELECT @.@.version -- as this is very
> helpful info in troubleshooting FTS issue. For now, I'm going to assume that
> you're using SQL Server 2000 based upon the below information. As Microsoft
> has never formally documented the upper limit of rows that SQL Server 2000
> will support, as they have only acknowledged that this version was tested up
> to 20 million rows. As you're attempting to FT Index a table with 130
> million rows, I'd recommend that you consider using SQL Server 2005
> (codename Yukon) as it has been acknowledged by Microsoft to have been
> tested up to 200 million row. However, keep in mind that SQL Server 2005 is
> still in a beta stage (Beta2) and will not be formally released until
> sometime in 2005.
> I'm assuming that the document you found that spoke of the performance
> counters is referenced in "SQL Server 2000 Full-Text Search Deployment white
> paper" at http://support.microsoft.com/default...b;en-us;323739
> You may also want to review the Dec 2003 version of "SQL Server 2005
> Full-Text Search: Internals and Enhancements" at
> http://msdn.microsoft.com/library/de...ovyukondev.asp
> Finally, you may also want to review this KB article 303459 (Q303459) "INF:
> How to Improve the Performance of FTS Queries for Large Tables" at
> http://support.microsoft.com/?id=303459
> Hope this helps!
> John
>
> "cms2001" <info@.craigstreet.com> wrote in message
> news:94e7d7f4.0412021704.47e15474@.posting.google.c om...
|||You're welcome, Craig,
I suspected the SQL 2000 FTS Deployment whitepaper was what you were reading
as I'm one of the *authors* of that document and I did all of the original
research. As for your current FTI problems with SQL Server 2000, they are
not going away as 130 million rows is most likely out of reach for SQL2K
FTS. Contact me offline and I can provide you with some alternatives.
In the meanwhile, and relative to setting the MSSearch service CPU affinity,
you can set the MSSearch service's to use one CPU and then use sp_configure
to set the affinity of SQL Server to that cpu to avoid cpu contention during
heavy MSSearch CPU usage via the following method - launch the Tskmgr.exe
from the AT command on the multi-proc server where sql server resides, as
follows:
at <current_time+1min> /interactive taskmgr.exe
when it launches, you can then set "cpu affinity" for the MSSearch service
to a cpu or set of cpu's not being used by SQL Server. Then you would use
sp_configure to set SQL Server's cpu affinity to the other cpu's, thus
preventing the cpu usage of mssearch from affecting your sql server
processing. Note, this only works on multi-processor servers and is a
secured method for achieving this feature. I've also have documented methods
for allowing the MSSearch service CPU affinity settings to survive a server
reboot.
In regards to the 'High Notification Rate', this most likely is one of two
situations, either SQL Server is *pushing* too many rows to the MSSearch
service to handle or the MSSearch service needs to do a shadow merge in
order to free up some contiguous memory allocated space that it needs to
continue. Although, I think the former is the issue, but then I've never FT
Indexed 130 million rows! SQL Server 2000 FT Indexing is mainly I/O bound
and even at a resource_usage level of 5, the max memory usage is set to
512MB of RAM, assuming that it is available and not used by either SQL
Server or the OS.
Regards,
John
"cms2001" <info@.craigstreet.com> wrote in message
news:94e7d7f4.0412031724.65d5a021@.posting.google.c om...
> Hi John
> Many thanks for getting back to me on this. Your assumption of SQL
> Server 2000 is correct - SP3 to be precise.
> And yes, the whitepaper you added a link to was the document i was
> talking about. This has proved very useful so far in our
> investigations, along with the other link you added.
> As far as SQL Server 2005 goes, this is something that we are working
> on in the background. I have read up on the new full text capabilites,
> and understand that it has been re-written from the ground up. I am
> sure that it would provide a much more scalable platform given the
> volumme of data we are talking about.
> Our current FTI problems on SQL Server 2000 are actually delaying the
> launch of a product, so its imperative that we get this up and running
> as soon as possible in some shape or form. The business has some
> decisions to make around SQL Server 2005, but there are also
> associated risks given its current beta state, so I need to persevere
> with SQL Server 2000 for now.
> I did have a couple of thoughts around possibilites that we could try.
> I don't have the exact specs of the hardware we are running this on at
> the moment - but i do know that the box has a total of 8 processors.
> Given this, i'm thinking about the following
> 1/ assign MSSearch.exe to CPU0, and set sp_fulltext_service
> 'resource_usage', to 4 or 5 to allow the full text engine to consume
> more resources
> 2/ assign SQL Server processors CPU1-6
> 3/ CPU7 should be left free to handle the NIC
>
> As far as i know, MSSearch will only use a single processor, so even
> if we commit CPU0 fully (sp_fulltext_service, 'resource_usage', 5) to
> MSSearch, SQL Server should still have 6 processors to play with.
> There may also be an option to schedule population of the index during
> low periods (overnight) rather than in the background. This would be a
> compromise on required funtionality, but may at least be a way forward
> in the short term. I'm unsure at present of whether this would help
> our situation, but may be worth a try. What do you read into the 'High
> Notification Rate' - do you think a scheduled index population may
> help in this situation.
> The other interesting thing is that although we have the 'High
> Notification Rate' message, the CPUs so not seem to be maxing out
> (running at 15-25%), and IO seems as though it seems to have some
> spare capacity.
> Again, many thanks for helping us out with this. If you have any
> thoughts on the above, or any other guidance or suggestions, you input
> would be much appreciated.
> Regards
> Craig
>
>
>
> "John Kane" <jt-kane@.comcast.net> wrote in message
news:<eHoqKsO2EHA.3392@.TK2MSFTNGP10.phx.gbl>...[vbcol=seagreen]
very[vbcol=seagreen]
that[vbcol=seagreen]
Microsoft[vbcol=seagreen]
2000[vbcol=seagreen]
tested up[vbcol=seagreen]
is[vbcol=seagreen]
white[vbcol=seagreen]
http://msdn.microsoft.com/library/de...ovyukondev.asp[vbcol=seagreen]
"INF:[vbcol=seagreen]