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: ].
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!
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.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"Charlie Pyne" <cpyne@.partners.org> wrote in message
news:%23SjvpQb%23EHA.3988@.TK2MSFTNGP11.phx.gbl...
> 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: ].
>
> *** Sent via Developersdex http://www.codecomments.com ***
> Don't just participate in USENET...get rewarded for it!
|||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.codecomments.com ***
Don't just participate in USENET...get rewarded for it!
|||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?
"Charlie Pyne" <cpyne@.partners.org> wrote in message
news:elauz0b%23EHA.3368@.TK2MSFTNGP10.phx.gbl...
> 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.codecomments.com ***
> Don't just participate in USENET...get rewarded for it!
|||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...b;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
SQL Full Text Search Blog
http://spaces.msn.com/members/jtkane/
"Charlie Pyne" <cpyne@.partners.org> wrote in message
news:#SjvpQb#EHA.3988@.TK2MSFTNGP11.phx.gbl...
> 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: ].
>
> *** Sent via Developersdex http://www.codecomments.com ***
> Don't just participate in USENET...get rewarded for it!
|||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//defaul...b;EN-US;240833
Thanks,
John
SQL Full Text Search Blog
http://spaces.msn.com/members/jtkane/
"John Kane" <jt-kane@.comcast.net> wrote in message
news:u3WGySe#EHA.2016@.TK2MSFTNGP15.phx.gbl...
> 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...b;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
> --
> SQL Full Text Search Blog
> http://spaces.msn.com/members/jtkane/
>
> "Charlie Pyne" <cpyne@.partners.org> wrote in message
> news:#SjvpQb#EHA.3988@.TK2MSFTNGP11.phx.gbl...
>
|||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
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!
|||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
SQL Full Text Search Blog
http://spaces.msn.com/members/jtkane/
"Charlie Pyne" <cpyne@.partners.org> wrote in message
news:uVSXfgj#EHA.600@.TK2MSFTNGP09.phx.gbl...
> 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
> 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
>
> *** Sent via Developersdex http://www.codecomments.com ***
> Don't just participate in USENET...get rewarded for it!
|||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
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!
|||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
SQL Full Text Search Blog
http://spaces.msn.com/members/jtkane/
"Charlie Pyne" <cpyne@.partners.org> wrote in message
news:OeQeKOY$EHA.4092@.TK2MSFTNGP09.phx.gbl...
> 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
>
> *** Sent via Developersdex http://www.codecomments.com ***
> Don't just participate in USENET...get rewarded for it!
