Full Text Max Size?

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Charlie Pyne - 13 Jan 2005 21:09 GMT

Hi,

Does anyone have experience with Full Text catalogs on large tables? We
have a full text catalog on a table with about 30 million rows. Acording
to BOL, once you get over 1 million you'll need to make some
adjustements. Our system works, but often we get the following errors
with ad hoc queries. The server has 8GB of RAM and 4 3GHz processors.
Does anyone have experience working with a table this big? Any
suggetions as to what could cause these errors? The only thing I could
find was BUG#: 469483 on MS's site, but we're not using any OR clauses

Thanks

Here's the errors....

(query)
SELECT * FROM acc_results ar
WHERE CONTAINS(finding_text, 'cell')

#1
Server: Msg 7619, Level 16, State 1, Line 1
Execution of a full-text operation failed. Not enough storage is
available to process this command.

#2
Server: Msg 7342, Level 16, State 1, Line 1
Unexpected NULL value returned for column '[FULLTEXT:acc_results].KEY'
from the OLE DB provider 'Full-text Search Engine'. This column cannot
be NULL.
OLE DB error trace [Non-interface error: Unexpected NULL value returned
for the column: ProviderName='Full-text Search Engine',
TableName='[FULLTEXT:acc_results]', ColumnName='KEY'].

#3
Server: Msg 7399, Level 16, State 1, Line 1
OLE DB provider 'Full-text Search Engine' reported an error.
[OLE/DB provider returned message: Not enough storage is available to
process this command.]
OLE DB error trace [OLE/DB Provider 'Full-text Search Engine'
IRowset::GetNextRows returned 0x80004005: ].

Reply to this Message

Hilary Cotter - 13 Jan 2005 21:51 GMT

Not enough storage is a common OS related error - relating to available
memory I think. Can you do things like surf on this machine, map network
drives, etc? I have heard of a 35 million row SQL FTS search solution -
IIRC.

SQL FTS 2005 has been tested to 2 billion rows.

Signature

Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html

>
> Hi,
[quoted text clipped - 40 lines]
> *** Sent via Developersdex http://www.developersdex.com ***
> Don't just participate in USENET...get rewarded for it!

Reply to this Message

Charlie Pyne - 13 Jan 2005 22:14 GMT

Everything else seems to work fine. What is IIRC? I searched on it,
but got a bunch of different results back.

Reply to this Message

Hilary Cotter - 14 Jan 2005 00:24 GMT

IIRC = If I Recall Correctly.

Ok, can you review your application log for events from MSSearch saying
master merge paused.

Also is this a cluster?

> Everything else seems to work fine. What is IIRC? I searched on it,
> but got a bunch of different results back.
>
> *** Sent via Developersdex http://www.developersdex.com ***
> Don't just participate in USENET...get rewarded for it!

Reply to this Message

John Kane - 14 Jan 2005 02:56 GMT

Charlie,
Yes, I do. First of all, what is the exact version of SQL Server (7.0 or
2000) and on what OS platform is it deployed? Could you post the full output
of -- SELECT @@version -- as this is VERY helpful information in
troubleshooting such SQL FTS performance issues!

Secondly, and depending upon what your is your SQL Server version and SP
level as well as the exact query that your are getting these errors, you may
want to checkout KB article 818883 "PRB: You may receive error 7619 when you
run a full-text query that contains many OR operators in the search string"
at http://support.microsoft.com/default.aspx?scid=kb;en-us;818883 as one of
the symptoms is error: "Not enough storage is available to complete this
operation" as this occurs on SQL Server 2000 SP3 and when your CONTAINS
query contains many logical OR operators.

However, because of the larger number of rows (30 million), in your
FT-enable table, you may have encountered additional problems related to the
Master Merge. Specifically, you should review your server's Application
Event log for "Microsoft Search" or MssCi source events - errors, warnings
AND informational for any related Master Merge paused conditions.
Furthermore, you should review the Performance Monitor (Perfmon) counters
for Microsoft Gatherer: Reason to back off, Microsoft Gatherer Projects:
Gatherer Paused Flag, and Microsoft Search Indexer Catalogs: Merge Progress
to determine if there is an existing issue with the Master Merge process.

Depending upon your reply, I can provide you with more specific
recommendations.
Thanks,
John

Signature

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

> Hi,
>
[quoted text clipped - 39 lines]
> *** Sent via Developersdex http://www.developersdex.com ***
> Don't just participate in USENET...get rewarded for it!

Reply to this Message

John Kane - 14 Jan 2005 04:03 GMT

Charlie,
A quick follow-up... I think you may benefit from this KB article:
303459 (Q303459) "INF: How to Improve the Performance of FTS Queries for
Large Tables" http://support.microsoft.com/?id=303459 and make changes to
the MaxPropStoreCachedSize registry key value as well as use the Top_N_Rank
parameter in all of your SQL FTS queries, per KB article: 240833 (Q240833)
"FIX: Full-Text Search Performance Improved via Support for TOP"
http://support.microsoft.com//default.aspx?scid=kb;EN-US;240833

Thanks,
John

Signature

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

> Charlie,
> Yes, I do. First of all, what is the exact version of SQL Server (7.0 or
[quoted text clipped - 69 lines]
> > *** Sent via Developersdex http://www.developersdex.com ***
> > Don't just participate in USENET...get rewarded for it!

Reply to this Message

Charlie Pyne - 14 Jan 2005 12:54 GMT

Hi John,

Thanks for the reply. I'm not at work so I can't run @@version at this
time, but I can tell you that this server is SQL 2000 Enterprise Edition
with SP3a on Win2k Advanced Server with SP3.

We do have the MaxPropStoreCachedSize parameter in place and currently
it is set to 2000. The size of those two files listed in the article is

> 2GB. One thing I noticed last night when testing this against our test

server, (same hardware/os/sql), is the errors don't seem to happen when
this parameter is removed. The mssearch.exe process stays around 100MB
and the query completes successfully. With this key in place, the
memory used by mssearch grows to around 1 or 1.5GB and then we get the
"not enough storage" error. Mssearch.exe then just sits there holding
that amount of ram until we stop/start the search service.

I haven't had a chance to experiment with this yet, but someone else
thought that both the /3GB and /PAE switches that are currently present
could be causing problems. SQL is configured to only use 4GB of the 8GB
total, but for some reason performance of non-full text queries take a
major hit when we remove /3GB. WE've tried making the same 4GB
available to SQL with just the /PAE switch, but it doesn't seem to work
as well.

I'll check for the event log errors that you mentioned next week.

Thanks for your help. Please let me know if you have any other ideas.

Charlie

Reply to this Message

John Kane - 14 Jan 2005 14:58 GMT

You're welcome, Charlie,
When you get to work, also check the server's Application event log for
"Microsoft Search" or MssCi source events - errors, warnings AND
informational for any related Master Merge paused conditions as this where
you'll find the true cause of what is causing the MSSearch service to appear
to hang. Additionally, you may want to start with smaller numbers for
setting the MaxPropStoreCachedSize value, say 500 MB to 1000 (or 1GB) and
then test until you get the failure as tuning this parameter is not an exact
science.

No, using the /3GB and /PAE switches is not the problem as the MSSearch
service with the resource_usage level set to 5 (dedicated) can only use a
max. of 512MB of RAM, if and only if this memory is available and not used
by any other process. Furthermore, the MSSearch service cannot use the
memory above the 2Gb limit.

Regards,
John

Signature

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

> Hi John,
>
[quoted text clipped - 28 lines]
> *** Sent via Developersdex http://www.developersdex.com ***
> Don't just participate in USENET...get rewarded for it!

Reply to this Message

Charlie Pyne - 18 Jan 2005 17:31 GMT

Hi John,

I don't see any errors related to Master Merge. I do see this error a
bunch of times during our initial full population...

The entry <MSSQL75://SQLSERVER/286302EC/00000342> in the hash map on
project <SQLServer SQL0000600005> cannot be updated. Error: 8007054e - A
description for this error could not be found.

MS had told us to set MaxPropStoreCachedSize based on the size of these
two files...

00000001.ps1 = 775MB
00000001.ps2 = 2.26GB

If I set this to 256, the problem seems to go away. With 256 set, I've
let the SELECT * query that we've been using to reproduce the error run
for about 10 minutes before I cancel it. With 2000 set, we'll get the
"not enough storage" error after about 2 minutes.

We also have the priorty of the service set to the default of 3. I
haven't found much in terms of how this should be set other than
1=background and 5=dedicated. Do you have any suggestions? This is a
system that is used by doctors to save case notes and query historical
notes using full text and standard sql queries. We'd like to maximize
the performance of full text, but we don't want to have a negative
effect on regular SQL. We'd also like to cut down on the time needed
for full & incremental rebuilds. Fulls now take 1 week and incrementals
take about 1 day. From what I've read, we should be using change
tracking. We plan on doing this as soon as we have this current problem
resolved. Any other suggestions beyond this?

Thanks for your help
Charlie

Reply to this Message

John Kane - 18 Jan 2005 21:25 GMT

Hi Charlie,
With SQL Server 2000 FTS you're somewhat limited with tables of this scale.
You could try to horizontally partition the table into multiple smaller
tables (say partition on a date range or some other column specific range of
values) and the FT Index the smaller tables using separate FT Catalogs. I'd
also highly recommend that you use CONTAINSTABLE or FREETEXTTABLE with the
Top_N_Rank value (see KB article Q240833) when running FTS SELECT * queries
against the larger FT Catalog. I'd also highly recommend that you look at
SQL Server 2005 as many of these limitations go away with Yukon FTS.

If you're server has lots of RAM, you can use sp_fulltext_service
'resource_usage', 5 to set the MSSearch service to 5 (dedicated), and it
will use the max amount of RAM (512Mb), but only if the RAM is not being
used by either the OS or by SQL Server. However, this will not fix the error
caused by large *.ps* files, as does setting the MaxPropStoreCachedSize
registry key value.

Once you've resolved this issue, use Change tracking with Update Index in
background, but be aware that an automatic Incremental or Full Population
will be run when you set this feature, so the MSSearch service can re-sync
up the FT Catalog with the SQL table data.

Thanks,
John

Signature

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

> Hi John,
>
[quoted text clipped - 33 lines]
> *** Sent via Developersdex http://www.developersdex.com ***
> Don't just participate in USENET...get rewarded for it!

Reply to this Message

Charlie Pyne - 20 Jan 2005 14:19 GMT

Hi John,

Thanks for your help with this. The more that I look at this, the more
that I realize that CONTAINSTABLE is exactly what needs to be added to
the stored procedure behind this ad hoc query window. The end user had
no need to return any more that 100 rows, but is returning 600000 in the
case where this error is happening. Thanks!

If you understand this, could you explain the difference between setting
the resource usage to 5 and the MaxPropStoreCachedSize setting? When I
first looked at this, I assumed that MaxPropStoreCachedSize is the
amount of ram that mssearch.exe is allowed to use. When I set this to
128, mssearch seems to use roughly 128MB durring full text queries and
setting it to 1000 roughly = 1GB. Based on what you said about resource
useage, setting it to 5 = 512MB. Could you explain the difference
between the two and when one is bennificial over the other?

Another intesting thing regarding MaxPropStoreCachedSize is the error
seems to happen with this set to anything over 128. Based on this,
we've decided to set this back to 128. I was hesitant to do this since
the MS documentation suggests this should be much higher for a catalog
of our size, but I can't argue with the fact that 128 works. What does
MaxPropStoreCachedSize actually do? Our system has a very large
catalog, but very few concurrent full text users. At any given time
there are only one or two people running full text queries. The only
thing I can guess is MaxPropStoreCachedSize helps for many concurrent
queries???

Thanks again!
Charlie

Reply to this Message