An automatic monthly delete has recently grown from 15 to 20 million rows. It is now filling my 70GB T-Log completly. I don't have any space to expand the T-Log. Do I have any options other than reducing the number of rows in the delete?Is the database in full or simple recovery mode?
In either case, batch your deletes in small chunks instead of trying to do them all at once (say 1M or 100K or 10K at a time).
If you are in simple mode, when the batch finishes, a database checkpoint will occur and the log will be truncated and the space allocated for reuse.
If you are in full recovery mode, perform a tran log backup before starting the next batch and the log will be truncated and the space allocated for reuse.|||In either case, batch your deletes in small chunks instead of trying to do them all at once (say 1M or 100K or 10K at a time).
x2.
Also, you should set a performance alert on you transaction log to kick off when the log reaches xx% full. Have the action of the alert set to kick off a transaction log backup. This will NOT work unless you batch your deletes into smaller quantities, but it should work well in conjunction with batching.
For example, my db is set to do a custom defrag Sunday nights. The defrag is broken up into about 50 steps and takes about 45 minutes. During the process, about 50 GB of data is written to the t-log (which is 35 GB in size). I have the performance alert set to trigger when the t-log is 60% full with a ten minute interval set between responses.
This way, when the log file gets to about 60% full, the transaction log backup kicks off automatically. The process repeats itself until the defrags are complete.
Be SURE to set the response interval to something realistic; I once forgot and watched as the response kicked off every second (for something different) and there was nothing I could do to stop it.
Regards,
hmscott
2012年3月29日星期四
full text serch with no user istance?
HI
I' making an archive applicaton that will be distribuited on cd.
the application contain a simple database witn one million of document with author , title and full document in text format.
i want to make a full text serch on the documents.
this cd will be distribuited to normal user so it have to be simple to install and use.
in the cd I'have put the setup of sql server express advenced with the .ini file for an automatic setup with the required component(full-text serch engine too)
when the installation program has finished you have a directory with a simple c# program and the .mdf and .ldf file for the database.
in the database you have 4 columns "id" "title" "author" "text"
with "SQL Server Management Studio Express" I have create the full text on the database using this sql line
"CREATE FULLTEXT CATALOG cat" to create the catalog then I have create an index key on ID column and then a full text for text column.
now the probelm is that i can make a full-text serch in a user istance.
in visual studio 2005 i select the option "insert existing element in the project" then I'have select the mdf file, but after the insert all the query work fine expet the "SELECT * FROM db WHERE CONTAINS(tilte,'word')" it say me "unable to do a full-text serch in an user istance.
I'have tried to manipulate the connection string setting user istance=false but I have encountered two problem.
1- I can't set a file location like |program dir|\db.mdb
2- if i set a real file location the program work only on my pc and only if the visul studio in open.
whot I have to do to make it work?
Full-text Search does not work in User Instances.
Mike
|||this means that is impossible with c# and sql server express advanced to make an application with full text serch, that can be distribuite on cd for normal user?
2012年3月25日星期日
Full Text Search Index build is running too long
I did a DTS insert from a .CSV file and loaded 10 million records into a
table. I am doing a full text index on 3 columns in the table. The full
text index has been populating now for 24 hours continuous. How can I tell
if it's hung, or is it possible that it really takes that long to run? It
took only about 30 minutes to load 10 million records into the database.Brian,
Take a look at Current Activity - Process Info in Enterprise Manager. Might
find some useful insight there.
HTH
Jerry
"Brian Kitt" <BrianKitt@.discussions.microsoft.com> wrote in message
news:13440424-19D0-4E7D-9E23-1BFEB9513303@.microsoft.com...
>I did a DTS insert from a .CSV file and loaded 10 million records into a
> table. I am doing a full text index on 3 columns in the table. The full
> text index has been populating now for 24 hours continuous. How can I
> tell
> if it's hung, or is it possible that it really takes that long to run? It
> took only about 30 minutes to load 10 million records into the database.
table. I am doing a full text index on 3 columns in the table. The full
text index has been populating now for 24 hours continuous. How can I tell
if it's hung, or is it possible that it really takes that long to run? It
took only about 30 minutes to load 10 million records into the database.Brian,
Take a look at Current Activity - Process Info in Enterprise Manager. Might
find some useful insight there.
HTH
Jerry
"Brian Kitt" <BrianKitt@.discussions.microsoft.com> wrote in message
news:13440424-19D0-4E7D-9E23-1BFEB9513303@.microsoft.com...
>I did a DTS insert from a .CSV file and loaded 10 million records into a
> table. I am doing a full text index on 3 columns in the table. The full
> text index has been populating now for 24 hours continuous. How can I
> tell
> if it's hung, or is it possible that it really takes that long to run? It
> took only about 30 minutes to load 10 million records into the database.
2012年3月22日星期四
Full text search and sepcial characters like &(ampersand)
I think this may have been asked for million times... but for some strange reason, i couldn't find anything related about that in this forum.....
So, just wondering if there is any way to handle special characters like "&" in full text search ?
like "AT&T" ?
cause when I do a simple select * contains AT&T, it won't return any result ...
thank you in advance.
I believe this is because of the noise word filter. Sql treats & as a word breaker, so what Sql is actually executing is "AT T"However, if you look at the noise file, both "at" and "t" are filtered out of full text searches. So my suggestion would be to take these two out of the appropriate noise file and check the search again. Otherwise, you're probably going to have to do some transformation of data.
You can test this theory by inserting a new record like Jones&Smith into the same column that AT&T is in. If you can get it to return results against either "Jones" or "Smith" than the noise filter is your problem.|||
thank you so much
I think that is the problem
but then I have another problem...
since my website is host on a share server... i don't have access to the noise file...
Is there anything I can do on my side so that i can include the noise words that are defined in the nosie file?
thank you so much
|||That is a good question. My inclination is that you are SOL. An alternative would have to replace all the ampersands in that column with a token or something. An example would be AT[AMP]TBut then you would be responsible for everywhere in your code, to know to replace an & going in with [AMP], and also take it back to & when it comes back out.|||
Thank you so much
But i guess that would mean a whole lot of replacing to handle...
haha, so there isn't any way... to tell the sql server to .... "hey, ignore the noise words when doing full text index for a specific table", right?
haha
thank you so much
-------------------
大作
|||The only thing I have been able to find is this thread which suggests using quotes, but the answer appears to be in FREETEXT. Take a look at the FREETEXT option and see if it does what you need.http://groups.google.com/group/microsoft.public.sqlserver.fulltext/browse_thread/thread/235f8f5cb0f12c47/edad245b4216abe6%23edad245b4216abe6sql
2012年3月19日星期一
Full Text Max Size?
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 we randomly 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: ].just taking #1 and #3 at face value (I know nothing is what it seems), I would imagine you need more space on your hard drive.
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 we randomly 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: ].just taking #1 and #3 at face value (I know nothing is what it seems), I would imagine you need more space on your hard drive.
Full Text Max Size?
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!
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!
2012年3月11日星期日
Full Text Index hanging status=runnable waittype=NETWORKIO
I have a database with 5 tables, each with full text index. 4 of them build
just fine, the 5th, has 10 million rows. The Full Text index never seems to
finish on that one. I've let it run for as long as 36 hours and it never
finishes. When I look in the Management panel, under process info. It tells
me my status is runnable, and waittype is NETWORKIO. I've checked it over
and over and it always is in this state. The MSSEARCH task is running with a
very high % of CPU. Any advice on where I can go from here to figure out
what the problem is?Brian,
Try running a DBCC CHECKTABLE on the table.
HTH
Jerry
"Brian Kitt" <BrianKitt@.discussions.microsoft.com> wrote in message
news:0CD8143A-1D89-496E-BCA7-EE772790D532@.microsoft.com...
>I have a database with 5 tables, each with full text index. 4 of them
>build
> just fine, the 5th, has 10 million rows. The Full Text index never seems
> to
> finish on that one. I've let it run for as long as 36 hours and it never
> finishes. When I look in the Management panel, under process info. It
> tells
> me my status is runnable, and waittype is NETWORKIO. I've checked it over
> and over and it always is in this state. The MSSEARCH task is running
> with a
> very high % of CPU. Any advice on where I can go from here to figure out
> what the problem is?|||I figured it out!!!!!!!!!!!!!!!
I looked at the data, and 1 column that I was indexing on, had one word, the
word 'book' for about 7,000,000 rows! I stopped indexing on that column, and
it works fine now!
"Brian Kitt" wrote:
> I have a database with 5 tables, each with full text index. 4 of them build
> just fine, the 5th, has 10 million rows. The Full Text index never seems to
> finish on that one. I've let it run for as long as 36 hours and it never
> finishes. When I look in the Management panel, under process info. It tells
> me my status is runnable, and waittype is NETWORKIO. I've checked it over
> and over and it always is in this state. The MSSEARCH task is running with a
> very high % of CPU. Any advice on where I can go from here to figure out
> what the problem is?
just fine, the 5th, has 10 million rows. The Full Text index never seems to
finish on that one. I've let it run for as long as 36 hours and it never
finishes. When I look in the Management panel, under process info. It tells
me my status is runnable, and waittype is NETWORKIO. I've checked it over
and over and it always is in this state. The MSSEARCH task is running with a
very high % of CPU. Any advice on where I can go from here to figure out
what the problem is?Brian,
Try running a DBCC CHECKTABLE on the table.
HTH
Jerry
"Brian Kitt" <BrianKitt@.discussions.microsoft.com> wrote in message
news:0CD8143A-1D89-496E-BCA7-EE772790D532@.microsoft.com...
>I have a database with 5 tables, each with full text index. 4 of them
>build
> just fine, the 5th, has 10 million rows. The Full Text index never seems
> to
> finish on that one. I've let it run for as long as 36 hours and it never
> finishes. When I look in the Management panel, under process info. It
> tells
> me my status is runnable, and waittype is NETWORKIO. I've checked it over
> and over and it always is in this state. The MSSEARCH task is running
> with a
> very high % of CPU. Any advice on where I can go from here to figure out
> what the problem is?|||I figured it out!!!!!!!!!!!!!!!
I looked at the data, and 1 column that I was indexing on, had one word, the
word 'book' for about 7,000,000 rows! I stopped indexing on that column, and
it works fine now!
"Brian Kitt" wrote:
> I have a database with 5 tables, each with full text index. 4 of them build
> just fine, the 5th, has 10 million rows. The Full Text index never seems to
> finish on that one. I've let it run for as long as 36 hours and it never
> finishes. When I look in the Management panel, under process info. It tells
> me my status is runnable, and waittype is NETWORKIO. I've checked it over
> and over and it always is in this state. The MSSEARCH task is running with a
> very high % of CPU. Any advice on where I can go from here to figure out
> what the problem is?
Full Text Index hanging status=runnable waittype=NETWORKIO
I have a database with 5 tables, each with full text index. 4 of them build
just fine, the 5th, has 10 million rows. The Full Text index never seems to
finish on that one. I've let it run for as long as 36 hours and it never
finishes. When I look in the Management panel, under process info. It tell
s
me my status is runnable, and waittype is NETWORKIO. I've checked it over
and over and it always is in this state. The MSSEARCH task is running with
a
very high % of CPU. Any advice on where I can go from here to figure out
what the problem is?Brian,
Try running a DBCC CHECKTABLE on the table.
HTH
Jerry
"Brian Kitt" <BrianKitt@.discussions.microsoft.com> wrote in message
news:0CD8143A-1D89-496E-BCA7-EE772790D532@.microsoft.com...
>I have a database with 5 tables, each with full text index. 4 of them
>build
> just fine, the 5th, has 10 million rows. The Full Text index never seems
> to
> finish on that one. I've let it run for as long as 36 hours and it never
> finishes. When I look in the Management panel, under process info. It
> tells
> me my status is runnable, and waittype is NETWORKIO. I've checked it over
> and over and it always is in this state. The MSSEARCH task is running
> with a
> very high % of CPU. Any advice on where I can go from here to figure out
> what the problem is?|||I figured it out!!!!!!!!!!!!!!!
I looked at the data, and 1 column that I was indexing on, had one word, the
word 'book' for about 7,000,000 rows! I stopped indexing on that column, an
d
it works fine now!
"Brian Kitt" wrote:
> I have a database with 5 tables, each with full text index. 4 of them bui
ld
> just fine, the 5th, has 10 million rows. The Full Text index never seems
to
> finish on that one. I've let it run for as long as 36 hours and it never
> finishes. When I look in the Management panel, under process info. It te
lls
> me my status is runnable, and waittype is NETWORKIO. I've checked it over
> and over and it always is in this state. The MSSEARCH task is running wit
h a
> very high % of CPU. Any advice on where I can go from here to figure out
> what the problem is?
just fine, the 5th, has 10 million rows. The Full Text index never seems to
finish on that one. I've let it run for as long as 36 hours and it never
finishes. When I look in the Management panel, under process info. It tell
s
me my status is runnable, and waittype is NETWORKIO. I've checked it over
and over and it always is in this state. The MSSEARCH task is running with
a
very high % of CPU. Any advice on where I can go from here to figure out
what the problem is?Brian,
Try running a DBCC CHECKTABLE on the table.
HTH
Jerry
"Brian Kitt" <BrianKitt@.discussions.microsoft.com> wrote in message
news:0CD8143A-1D89-496E-BCA7-EE772790D532@.microsoft.com...
>I have a database with 5 tables, each with full text index. 4 of them
>build
> just fine, the 5th, has 10 million rows. The Full Text index never seems
> to
> finish on that one. I've let it run for as long as 36 hours and it never
> finishes. When I look in the Management panel, under process info. It
> tells
> me my status is runnable, and waittype is NETWORKIO. I've checked it over
> and over and it always is in this state. The MSSEARCH task is running
> with a
> very high % of CPU. Any advice on where I can go from here to figure out
> what the problem is?|||I figured it out!!!!!!!!!!!!!!!
I looked at the data, and 1 column that I was indexing on, had one word, the
word 'book' for about 7,000,000 rows! I stopped indexing on that column, an
d
it works fine now!
"Brian Kitt" wrote:
> I have a database with 5 tables, each with full text index. 4 of them bui
ld
> just fine, the 5th, has 10 million rows. The Full Text index never seems
to
> finish on that one. I've let it run for as long as 36 hours and it never
> finishes. When I look in the Management panel, under process info. It te
lls
> me my status is runnable, and waittype is NETWORKIO. I've checked it over
> and over and it always is in this state. The MSSEARCH task is running wit
h a
> very high % of CPU. Any advice on where I can go from here to figure out
> what the problem is?
Full Text Index hanging status=runnable waittype=NETWORKIO
I have a database with 5 tables, each with full text index. 4 of them build
just fine, the 5th, has 10 million rows. The Full Text index never seems to
finish on that one. I've let it run for as long as 36 hours and it never
finishes. When I look in the Management panel, under process info. It tells
me my status is runnable, and waittype is NETWORKIO. I've checked it over
and over and it always is in this state. The MSSEARCH task is running with a
very high % of CPU. Any advice on where I can go from here to figure out
what the problem is?
Brian,
Try running a DBCC CHECKTABLE on the table.
HTH
Jerry
"Brian Kitt" <BrianKitt@.discussions.microsoft.com> wrote in message
news:0CD8143A-1D89-496E-BCA7-EE772790D532@.microsoft.com...
>I have a database with 5 tables, each with full text index. 4 of them
>build
> just fine, the 5th, has 10 million rows. The Full Text index never seems
> to
> finish on that one. I've let it run for as long as 36 hours and it never
> finishes. When I look in the Management panel, under process info. It
> tells
> me my status is runnable, and waittype is NETWORKIO. I've checked it over
> and over and it always is in this state. The MSSEARCH task is running
> with a
> very high % of CPU. Any advice on where I can go from here to figure out
> what the problem is?
|||I figured it out!!!!!!!!!!!!!!!
I looked at the data, and 1 column that I was indexing on, had one word, the
word 'book' for about 7,000,000 rows! I stopped indexing on that column, and
it works fine now!
"Brian Kitt" wrote:
> I have a database with 5 tables, each with full text index. 4 of them build
> just fine, the 5th, has 10 million rows. The Full Text index never seems to
> finish on that one. I've let it run for as long as 36 hours and it never
> finishes. When I look in the Management panel, under process info. It tells
> me my status is runnable, and waittype is NETWORKIO. I've checked it over
> and over and it always is in this state. The MSSEARCH task is running with a
> very high % of CPU. Any advice on where I can go from here to figure out
> what the problem is?
just fine, the 5th, has 10 million rows. The Full Text index never seems to
finish on that one. I've let it run for as long as 36 hours and it never
finishes. When I look in the Management panel, under process info. It tells
me my status is runnable, and waittype is NETWORKIO. I've checked it over
and over and it always is in this state. The MSSEARCH task is running with a
very high % of CPU. Any advice on where I can go from here to figure out
what the problem is?
Brian,
Try running a DBCC CHECKTABLE on the table.
HTH
Jerry
"Brian Kitt" <BrianKitt@.discussions.microsoft.com> wrote in message
news:0CD8143A-1D89-496E-BCA7-EE772790D532@.microsoft.com...
>I have a database with 5 tables, each with full text index. 4 of them
>build
> just fine, the 5th, has 10 million rows. The Full Text index never seems
> to
> finish on that one. I've let it run for as long as 36 hours and it never
> finishes. When I look in the Management panel, under process info. It
> tells
> me my status is runnable, and waittype is NETWORKIO. I've checked it over
> and over and it always is in this state. The MSSEARCH task is running
> with a
> very high % of CPU. Any advice on where I can go from here to figure out
> what the problem is?
|||I figured it out!!!!!!!!!!!!!!!
I looked at the data, and 1 column that I was indexing on, had one word, the
word 'book' for about 7,000,000 rows! I stopped indexing on that column, and
it works fine now!
"Brian Kitt" wrote:
> I have a database with 5 tables, each with full text index. 4 of them build
> just fine, the 5th, has 10 million rows. The Full Text index never seems to
> finish on that one. I've let it run for as long as 36 hours and it never
> finishes. When I look in the Management panel, under process info. It tells
> me my status is runnable, and waittype is NETWORKIO. I've checked it over
> and over and it always is in this state. The MSSEARCH task is running with a
> very high % of CPU. Any advice on where I can go from here to figure out
> what the problem is?
2012年3月7日星期三
Full Text - Index Population - HDD Access
Hi,
I'm indexing a single field on a table with 10 million rows in it, its
currently taking about 48 hours to build.
I've been looking into performance bottlenecks on the system and see that
this delay is being mainly caused by MSSearch writing massive amounts of
data to the FT Index location. Since the finished index is only about 400mb
I can only imagine that the population process is rewriting the data again
and again to the disk as it is updated.
Is this a fair assumption? If so is there anyway around it, maybe to tell
MSSearch to use ram until the population is build then write the index to
disk?
Thanks
Michael
No.
There is the resource usage setting which may increase performance during
the indexing process (sp_fulltext_service 'catalogname','resource_usgae',5)
The more resources you can spend to improve the indexing process the better,
normally you get the best bang for your buck by investing in the disk
subsystem. For instance placing your catalogs on a RAID 10, 1, or 01 array.
01 is not considered to be a good choice as it does not offer the the
protection 10 does.
Also your database should be raid 5 depending on the level of write activity
on it.
What type of content are you indexing. You will get best performance by
indexing text content as opposed to indexing documents in their native
format (doc, xls, pdf, etc).
Please review the full text white paper on SQL FTS performance at:
http://support.microsoft.com/support...er.asp?GSSNB=1
Save this document as a zip and extract it.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"Mike Davies" <michael.davies@.synsoft.co.uk> wrote in message
news:ezLfihZmEHA.3824@.TK2MSFTNGP12.phx.gbl...
> Hi,
> I'm indexing a single field on a table with 10 million rows in it, its
> currently taking about 48 hours to build.
> I've been looking into performance bottlenecks on the system and see that
> this delay is being mainly caused by MSSearch writing massive amounts of
> data to the FT Index location. Since the finished index is only about
400mb
> I can only imagine that the population process is rewriting the data again
> and again to the disk as it is updated.
> Is this a fair assumption? If so is there anyway around it, maybe to tell
> MSSearch to use ram until the population is build then write the index to
> disk?
> Thanks
> Michael
>
|||Mike
What hardware is it running on?
Is the Full-Text Index on the same physical drive/lun as the database? It
should be possible to increase IO throughput by placing the Full-Text Index
on its own drive.
Are you using SCSI disks? If so are you using the faster 15k RPM disks as
apposed to 10k RPM disks.
How much RAM does the machine have?
How many processors and what spec are they?
Is SQL Server configured to leave some RAM free for the Operating System and
other Applications, i.e. if you have 2GB RAM is SQL configured to use max
1.5GB?
You might want to try SQL Server 2005 Beta 2. I understand that Full-Text
Search is much faster at indexing.
http://msdn.microsoft.com/library/en...asp?frame=true
Rich
"Hilary Cotter" wrote:
> No.
> There is the resource usage setting which may increase performance during
> the indexing process (sp_fulltext_service 'catalogname','resource_usgae',5)
> The more resources you can spend to improve the indexing process the better,
> normally you get the best bang for your buck by investing in the disk
> subsystem. For instance placing your catalogs on a RAID 10, 1, or 01 array.
> 01 is not considered to be a good choice as it does not offer the the
> protection 10 does.
> Also your database should be raid 5 depending on the level of write activity
> on it.
> What type of content are you indexing. You will get best performance by
> indexing text content as opposed to indexing documents in their native
> format (doc, xls, pdf, etc).
> Please review the full text white paper on SQL FTS performance at:
> http://support.microsoft.com/support...er.asp?GSSNB=1
> Save this document as a zip and extract it.
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
>
> "Mike Davies" <michael.davies@.synsoft.co.uk> wrote in message
> news:ezLfihZmEHA.3824@.TK2MSFTNGP12.phx.gbl...
> 400mb
>
>
|||Mike,
Yes, it is a fair assumption. You should review the SQL Server 2000 BOL
title "Full-text Search Recommendations" as well as the FTS Deployment white
paper. Are there workarounds? Yes. You can ensure that the disk drive that
your FT Catalog resides is separate from your database files as well as very
fast (>10,000 RPMs) and configured as RAID0 or RAID10. The Full Text
Indexing process is primarily I/O bound, but can also consume much memory
and CPU usage from time-to-time during Shadow Merges and at the end of the
process due to Master Merge.
Regards,
John
"Mike Davies" <michael.davies@.synsoft.co.uk> wrote in message
news:ezLfihZmEHA.3824@.TK2MSFTNGP12.phx.gbl...
> Hi,
> I'm indexing a single field on a table with 10 million rows in it, its
> currently taking about 48 hours to build.
> I've been looking into performance bottlenecks on the system and see that
> this delay is being mainly caused by MSSearch writing massive amounts of
> data to the FT Index location. Since the finished index is only about
400mb
> I can only imagine that the population process is rewriting the data again
> and again to the disk as it is updated.
> Is this a fair assumption? If so is there anyway around it, maybe to tell
> MSSearch to use ram until the population is build then write the index to
> disk?
> Thanks
> Michael
>
|||Hi Rich,
It's running on a single P4 Prescott at 3Ghz with 4GB of RAM. SQL Server
currently has access to all system RAM but is sat at approx 1.7Gb during the
indexing process.
Both the FullText index and the SQL database are on separate physical SATA
drives, however because of an apparent incompatibility issue with Windows
and running the two SATA drives on their own bus they are both running
through the standard IDE bus, I imagine this is not doing the performance
any favours.
Thanks for the 2005 tip, I'll give that a try.
Can you offer any other suggestions to the setup?
Thanks
Mike
"Richard Yeo" <RichardYeo@.discussions.microsoft.com> wrote in message
news:692389A4-ED05-42C7-B645-982E7E9CE989@.microsoft.com...
> Mike
> What hardware is it running on?
> Is the Full-Text Index on the same physical drive/lun as the database? It
> should be possible to increase IO throughput by placing the Full-Text
Index
> on its own drive.
> Are you using SCSI disks? If so are you using the faster 15k RPM disks as
> apposed to 10k RPM disks.
> How much RAM does the machine have?
> How many processors and what spec are they?
> Is SQL Server configured to leave some RAM free for the Operating System
and
> other Applications, i.e. if you have 2GB RAM is SQL configured to use max
> 1.5GB?
> You might want to try SQL Server 2005 Beta 2. I understand that Full-Text
> Search is much faster at indexing.
>
http://msdn.microsoft.com/library/en...asp?frame=true[vbcol=seagreen]
> Rich
>
> "Hilary Cotter" wrote:
during[vbcol=seagreen]
'catalogname','resource_usgae',5)[vbcol=seagreen]
better,[vbcol=seagreen]
array.[vbcol=seagreen]
activity[vbcol=seagreen]
http://support.microsoft.com/support...er.asp?GSSNB=1[vbcol=seagreen]
that[vbcol=seagreen]
of[vbcol=seagreen]
again[vbcol=seagreen]
tell[vbcol=seagreen]
to[vbcol=seagreen]
|||Mike
Have you implemented the /3GB switch in the OS boot.ini? If not by default
2GB is set aside for apps and 2GB for OS. By using this switch 3GB is set
aside for apps and 1GB for OS. This probably explains why SQL Server is only
using 1.7GB RAM. It would probably be using more if you had the /3GB switch
set. Try setting /3GB switch, instruct SQL Server to use Physical RAM, and
set max memory for SQL Server to be 2.5GB. This will leave 0.5GB for other
apps including MSSearch (I think), file system caching, etc.
Even if you don't have the budget for a 2 proc SQL license I would always
recommend purchasing a second physical processor and configuring SQL Server
to only use one of the processors. This leaves the second processor free for
other applications, OS, remote administration.
You might want to try the manufacturers web-site regarding BIOS / firmware
updates for your SATA / IDE issue.
Rich
"Mike Davies" wrote:
> Hi Rich,
> It's running on a single P4 Prescott at 3Ghz with 4GB of RAM. SQL Server
> currently has access to all system RAM but is sat at approx 1.7Gb during the
> indexing process.
> Both the FullText index and the SQL database are on separate physical SATA
> drives, however because of an apparent incompatibility issue with Windows
> and running the two SATA drives on their own bus they are both running
> through the standard IDE bus, I imagine this is not doing the performance
> any favours.
> Thanks for the 2005 tip, I'll give that a try.
> Can you offer any other suggestions to the setup?
> Thanks
> Mike
>
> "Richard Yeo" <RichardYeo@.discussions.microsoft.com> wrote in message
> news:692389A4-ED05-42C7-B645-982E7E9CE989@.microsoft.com...
> Index
> and
> http://msdn.microsoft.com/library/en...asp?frame=true
> during
> 'catalogname','resource_usgae',5)
> better,
> array.
> activity
> http://support.microsoft.com/support...er.asp?GSSNB=1
> that
> of
> again
> tell
> to
>
>
|||Mike,
Rich, while the /3GB switch can help SQL Server 2000, it has no affect on
the "Microsoft Search" (mssearch.exe) FT Search engine as it can only use up
to a max of 512MB of physical RAM. However, you must set the resource_usage
value to 5 (dedicated) via sp_fulltext_service 'resource_usage', 5 and you
must have at least 512Mb of RAM reserved for the MSSearch service that is
NOT used by the OS or by SQL Server.
If you can purchase an additional CPU, 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:
You can 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.
Regards,
John
"Richard Yeo" <RichardYeo@.discussions.microsoft.com> wrote in message
news:2AEB4663-795F-48F6-BA68-DEAA0D1C4BD8@.microsoft.com...
> Mike
> Have you implemented the /3GB switch in the OS boot.ini? If not by default
> 2GB is set aside for apps and 2GB for OS. By using this switch 3GB is set
> aside for apps and 1GB for OS. This probably explains why SQL Server is
only
> using 1.7GB RAM. It would probably be using more if you had the /3GB
switch
> set. Try setting /3GB switch, instruct SQL Server to use Physical RAM, and
> set max memory for SQL Server to be 2.5GB. This will leave 0.5GB for other
> apps including MSSearch (I think), file system caching, etc.
> Even if you don't have the budget for a 2 proc SQL license I would always
> recommend purchasing a second physical processor and configuring SQL
Server
> to only use one of the processors. This leaves the second processor free
for[vbcol=seagreen]
> other applications, OS, remote administration.
> You might want to try the manufacturers web-site regarding BIOS / firmware
> updates for your SATA / IDE issue.
> Rich
>
> "Mike Davies" wrote:
Server[vbcol=seagreen]
the[vbcol=seagreen]
SATA[vbcol=seagreen]
Windows[vbcol=seagreen]
performance[vbcol=seagreen]
It[vbcol=seagreen]
as[vbcol=seagreen]
System[vbcol=seagreen]
max[vbcol=seagreen]
Full-Text[vbcol=seagreen]
http://msdn.microsoft.com/library/en...asp?frame=true[vbcol=seagreen]
disk[vbcol=seagreen]
the[vbcol=seagreen]
by[vbcol=seagreen]
native[vbcol=seagreen]
http://support.microsoft.com/support...er.asp?GSSNB=1[vbcol=seagreen]
its[vbcol=seagreen]
see[vbcol=seagreen]
amounts[vbcol=seagreen]
about[vbcol=seagreen]
data[vbcol=seagreen]
to[vbcol=seagreen]
index[vbcol=seagreen]
|||"Richard Yeo" <RichardYeo@.discussions.microsoft.com> wrote in message
news:2AEB4663-795F-48F6-BA68-DEAA0D1C4BD8@.microsoft.com...
> Mike
>
<SNIP>
> Even if you don't have the budget for a 2 proc SQL license I would always
> recommend purchasing a second physical processor and configuring SQL
> Server
> to only use one of the processors. This leaves the second processor free
> for
> other applications, OS, remote administration.
>
</SNIP>
Please be careful about that because, in general, you have to purchase a
processor license for every processor that the OS can see, whether that
processor is configured to run SQL Server or not. For more information,
please refer to the SQL Server 2000 Licensing FAQ web page at
http://www.microsoft.com/sql/howtobuy/faq.asp. The third question under
Licensing is reproduced below for your benefit.
Q. Do I have to license all of the processors in a server?
A. You only have to acquire licenses for processors that are
accessible to any copy of the operating system upon which SQL Server 2000 is
installed.
Sincerely,
Stephen Dybing
This posting is provided "AS IS" with no warranties, and confers no rights.
begin 666 ts.gif
J1TE&.#EA`0`!`( ``````/___R'Y! $`````+ `````!``$```(!1 `[
`
end
|||"Richard Yeo" <RichardYeo@.discussions.microsoft.com> wrote in message
news:2AEB4663-795F-48F6-BA68-DEAA0D1C4BD8@.microsoft.com...
> Mike
> Have you implemented the /3GB switch in the OS boot.ini? If not by default
> 2GB is set aside for apps and 2GB for OS. By using this switch 3GB is set
> aside for apps and 1GB for OS. This probably explains why SQL Server is
only
> using 1.7GB RAM. It would probably be using more if you had the /3GB
switch
> set. Try setting /3GB switch, instruct SQL Server to use Physical RAM, and
> set max memory for SQL Server to be 2.5GB. This will leave 0.5GB for other
> apps including MSSearch (I think), file system caching, etc.
> Even if you don't have the budget for a 2 proc SQL license I would always
> recommend purchasing a second physical processor and configuring SQL
Server
> to only use one of the processors. This leaves the second processor free
for
> other applications, OS, remote administration.
Unless thigns have changed, last I knew, MS didn't allow that.
|||Stephen
Thanks for clarifying that.
Obviously there is a difference between what the product is capable of and
what the licensing folk think the product is or should be capable of.
In this instance I feel the Product Team got it right. In the real world
people often have to run more than one application on a server and should not
be forced to buy additional licenses.
With the move in the industry to consolidate this could a lot of unnecessary
expense.
Lets hope this license issue is addressed in the next release of SQL Server
2005.
Rich
"Stephen Dybing [MSFT]" wrote:
> "Richard Yeo" <RichardYeo@.discussions.microsoft.com> wrote in message
> news:2AEB4663-795F-48F6-BA68-DEAA0D1C4BD8@.microsoft.com...
> <SNIP>
> </SNIP>
> Please be careful about that because, in general, you have to purchase a
> processor license for every processor that the OS can see, whether that
> processor is configured to run SQL Server or not. For more information,
> please refer to the SQL Server 2000 Licensing FAQ web page at
> http://www.microsoft.com/sql/howtobuy/faq.asp. The third question under
> Licensing is reproduced below for your benefit.
> Q. Do I have to license all of the processors in a server?
> A. You only have to acquire licenses for processors that are
> accessible to any copy of the operating system upon which SQL Server 2000 is
> installed.
>
>
> --
> Sincerely,
> Stephen Dybing
> This posting is provided "AS IS" with no warranties, and confers no rights
I'm indexing a single field on a table with 10 million rows in it, its
currently taking about 48 hours to build.
I've been looking into performance bottlenecks on the system and see that
this delay is being mainly caused by MSSearch writing massive amounts of
data to the FT Index location. Since the finished index is only about 400mb
I can only imagine that the population process is rewriting the data again
and again to the disk as it is updated.
Is this a fair assumption? If so is there anyway around it, maybe to tell
MSSearch to use ram until the population is build then write the index to
disk?
Thanks
Michael
No.
There is the resource usage setting which may increase performance during
the indexing process (sp_fulltext_service 'catalogname','resource_usgae',5)
The more resources you can spend to improve the indexing process the better,
normally you get the best bang for your buck by investing in the disk
subsystem. For instance placing your catalogs on a RAID 10, 1, or 01 array.
01 is not considered to be a good choice as it does not offer the the
protection 10 does.
Also your database should be raid 5 depending on the level of write activity
on it.
What type of content are you indexing. You will get best performance by
indexing text content as opposed to indexing documents in their native
format (doc, xls, pdf, etc).
Please review the full text white paper on SQL FTS performance at:
http://support.microsoft.com/support...er.asp?GSSNB=1
Save this document as a zip and extract it.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"Mike Davies" <michael.davies@.synsoft.co.uk> wrote in message
news:ezLfihZmEHA.3824@.TK2MSFTNGP12.phx.gbl...
> Hi,
> I'm indexing a single field on a table with 10 million rows in it, its
> currently taking about 48 hours to build.
> I've been looking into performance bottlenecks on the system and see that
> this delay is being mainly caused by MSSearch writing massive amounts of
> data to the FT Index location. Since the finished index is only about
400mb
> I can only imagine that the population process is rewriting the data again
> and again to the disk as it is updated.
> Is this a fair assumption? If so is there anyway around it, maybe to tell
> MSSearch to use ram until the population is build then write the index to
> disk?
> Thanks
> Michael
>
|||Mike
What hardware is it running on?
Is the Full-Text Index on the same physical drive/lun as the database? It
should be possible to increase IO throughput by placing the Full-Text Index
on its own drive.
Are you using SCSI disks? If so are you using the faster 15k RPM disks as
apposed to 10k RPM disks.
How much RAM does the machine have?
How many processors and what spec are they?
Is SQL Server configured to leave some RAM free for the Operating System and
other Applications, i.e. if you have 2GB RAM is SQL configured to use max
1.5GB?
You might want to try SQL Server 2005 Beta 2. I understand that Full-Text
Search is much faster at indexing.
http://msdn.microsoft.com/library/en...asp?frame=true
Rich
"Hilary Cotter" wrote:
> No.
> There is the resource usage setting which may increase performance during
> the indexing process (sp_fulltext_service 'catalogname','resource_usgae',5)
> The more resources you can spend to improve the indexing process the better,
> normally you get the best bang for your buck by investing in the disk
> subsystem. For instance placing your catalogs on a RAID 10, 1, or 01 array.
> 01 is not considered to be a good choice as it does not offer the the
> protection 10 does.
> Also your database should be raid 5 depending on the level of write activity
> on it.
> What type of content are you indexing. You will get best performance by
> indexing text content as opposed to indexing documents in their native
> format (doc, xls, pdf, etc).
> Please review the full text white paper on SQL FTS performance at:
> http://support.microsoft.com/support...er.asp?GSSNB=1
> Save this document as a zip and extract it.
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
>
> "Mike Davies" <michael.davies@.synsoft.co.uk> wrote in message
> news:ezLfihZmEHA.3824@.TK2MSFTNGP12.phx.gbl...
> 400mb
>
>
|||Mike,
Yes, it is a fair assumption. You should review the SQL Server 2000 BOL
title "Full-text Search Recommendations" as well as the FTS Deployment white
paper. Are there workarounds? Yes. You can ensure that the disk drive that
your FT Catalog resides is separate from your database files as well as very
fast (>10,000 RPMs) and configured as RAID0 or RAID10. The Full Text
Indexing process is primarily I/O bound, but can also consume much memory
and CPU usage from time-to-time during Shadow Merges and at the end of the
process due to Master Merge.
Regards,
John
"Mike Davies" <michael.davies@.synsoft.co.uk> wrote in message
news:ezLfihZmEHA.3824@.TK2MSFTNGP12.phx.gbl...
> Hi,
> I'm indexing a single field on a table with 10 million rows in it, its
> currently taking about 48 hours to build.
> I've been looking into performance bottlenecks on the system and see that
> this delay is being mainly caused by MSSearch writing massive amounts of
> data to the FT Index location. Since the finished index is only about
400mb
> I can only imagine that the population process is rewriting the data again
> and again to the disk as it is updated.
> Is this a fair assumption? If so is there anyway around it, maybe to tell
> MSSearch to use ram until the population is build then write the index to
> disk?
> Thanks
> Michael
>
|||Hi Rich,
It's running on a single P4 Prescott at 3Ghz with 4GB of RAM. SQL Server
currently has access to all system RAM but is sat at approx 1.7Gb during the
indexing process.
Both the FullText index and the SQL database are on separate physical SATA
drives, however because of an apparent incompatibility issue with Windows
and running the two SATA drives on their own bus they are both running
through the standard IDE bus, I imagine this is not doing the performance
any favours.
Thanks for the 2005 tip, I'll give that a try.
Can you offer any other suggestions to the setup?
Thanks
Mike
"Richard Yeo" <RichardYeo@.discussions.microsoft.com> wrote in message
news:692389A4-ED05-42C7-B645-982E7E9CE989@.microsoft.com...
> Mike
> What hardware is it running on?
> Is the Full-Text Index on the same physical drive/lun as the database? It
> should be possible to increase IO throughput by placing the Full-Text
Index
> on its own drive.
> Are you using SCSI disks? If so are you using the faster 15k RPM disks as
> apposed to 10k RPM disks.
> How much RAM does the machine have?
> How many processors and what spec are they?
> Is SQL Server configured to leave some RAM free for the Operating System
and
> other Applications, i.e. if you have 2GB RAM is SQL configured to use max
> 1.5GB?
> You might want to try SQL Server 2005 Beta 2. I understand that Full-Text
> Search is much faster at indexing.
>
http://msdn.microsoft.com/library/en...asp?frame=true[vbcol=seagreen]
> Rich
>
> "Hilary Cotter" wrote:
during[vbcol=seagreen]
'catalogname','resource_usgae',5)[vbcol=seagreen]
better,[vbcol=seagreen]
array.[vbcol=seagreen]
activity[vbcol=seagreen]
http://support.microsoft.com/support...er.asp?GSSNB=1[vbcol=seagreen]
that[vbcol=seagreen]
of[vbcol=seagreen]
again[vbcol=seagreen]
tell[vbcol=seagreen]
to[vbcol=seagreen]
|||Mike
Have you implemented the /3GB switch in the OS boot.ini? If not by default
2GB is set aside for apps and 2GB for OS. By using this switch 3GB is set
aside for apps and 1GB for OS. This probably explains why SQL Server is only
using 1.7GB RAM. It would probably be using more if you had the /3GB switch
set. Try setting /3GB switch, instruct SQL Server to use Physical RAM, and
set max memory for SQL Server to be 2.5GB. This will leave 0.5GB for other
apps including MSSearch (I think), file system caching, etc.
Even if you don't have the budget for a 2 proc SQL license I would always
recommend purchasing a second physical processor and configuring SQL Server
to only use one of the processors. This leaves the second processor free for
other applications, OS, remote administration.
You might want to try the manufacturers web-site regarding BIOS / firmware
updates for your SATA / IDE issue.
Rich
"Mike Davies" wrote:
> Hi Rich,
> It's running on a single P4 Prescott at 3Ghz with 4GB of RAM. SQL Server
> currently has access to all system RAM but is sat at approx 1.7Gb during the
> indexing process.
> Both the FullText index and the SQL database are on separate physical SATA
> drives, however because of an apparent incompatibility issue with Windows
> and running the two SATA drives on their own bus they are both running
> through the standard IDE bus, I imagine this is not doing the performance
> any favours.
> Thanks for the 2005 tip, I'll give that a try.
> Can you offer any other suggestions to the setup?
> Thanks
> Mike
>
> "Richard Yeo" <RichardYeo@.discussions.microsoft.com> wrote in message
> news:692389A4-ED05-42C7-B645-982E7E9CE989@.microsoft.com...
> Index
> and
> http://msdn.microsoft.com/library/en...asp?frame=true
> during
> 'catalogname','resource_usgae',5)
> better,
> array.
> activity
> http://support.microsoft.com/support...er.asp?GSSNB=1
> that
> of
> again
> tell
> to
>
>
|||Mike,
Rich, while the /3GB switch can help SQL Server 2000, it has no affect on
the "Microsoft Search" (mssearch.exe) FT Search engine as it can only use up
to a max of 512MB of physical RAM. However, you must set the resource_usage
value to 5 (dedicated) via sp_fulltext_service 'resource_usage', 5 and you
must have at least 512Mb of RAM reserved for the MSSearch service that is
NOT used by the OS or by SQL Server.
If you can purchase an additional CPU, 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:
You can 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.
Regards,
John
"Richard Yeo" <RichardYeo@.discussions.microsoft.com> wrote in message
news:2AEB4663-795F-48F6-BA68-DEAA0D1C4BD8@.microsoft.com...
> Mike
> Have you implemented the /3GB switch in the OS boot.ini? If not by default
> 2GB is set aside for apps and 2GB for OS. By using this switch 3GB is set
> aside for apps and 1GB for OS. This probably explains why SQL Server is
only
> using 1.7GB RAM. It would probably be using more if you had the /3GB
switch
> set. Try setting /3GB switch, instruct SQL Server to use Physical RAM, and
> set max memory for SQL Server to be 2.5GB. This will leave 0.5GB for other
> apps including MSSearch (I think), file system caching, etc.
> Even if you don't have the budget for a 2 proc SQL license I would always
> recommend purchasing a second physical processor and configuring SQL
Server
> to only use one of the processors. This leaves the second processor free
for[vbcol=seagreen]
> other applications, OS, remote administration.
> You might want to try the manufacturers web-site regarding BIOS / firmware
> updates for your SATA / IDE issue.
> Rich
>
> "Mike Davies" wrote:
Server[vbcol=seagreen]
the[vbcol=seagreen]
SATA[vbcol=seagreen]
Windows[vbcol=seagreen]
performance[vbcol=seagreen]
It[vbcol=seagreen]
as[vbcol=seagreen]
System[vbcol=seagreen]
max[vbcol=seagreen]
Full-Text[vbcol=seagreen]
http://msdn.microsoft.com/library/en...asp?frame=true[vbcol=seagreen]
disk[vbcol=seagreen]
the[vbcol=seagreen]
by[vbcol=seagreen]
native[vbcol=seagreen]
http://support.microsoft.com/support...er.asp?GSSNB=1[vbcol=seagreen]
its[vbcol=seagreen]
see[vbcol=seagreen]
amounts[vbcol=seagreen]
about[vbcol=seagreen]
data[vbcol=seagreen]
to[vbcol=seagreen]
index[vbcol=seagreen]
|||"Richard Yeo" <RichardYeo@.discussions.microsoft.com> wrote in message
news:2AEB4663-795F-48F6-BA68-DEAA0D1C4BD8@.microsoft.com...
> Mike
>
<SNIP>
> Even if you don't have the budget for a 2 proc SQL license I would always
> recommend purchasing a second physical processor and configuring SQL
> Server
> to only use one of the processors. This leaves the second processor free
> for
> other applications, OS, remote administration.
>
</SNIP>
Please be careful about that because, in general, you have to purchase a
processor license for every processor that the OS can see, whether that
processor is configured to run SQL Server or not. For more information,
please refer to the SQL Server 2000 Licensing FAQ web page at
http://www.microsoft.com/sql/howtobuy/faq.asp. The third question under
Licensing is reproduced below for your benefit.
Q. Do I have to license all of the processors in a server?
A. You only have to acquire licenses for processors that are
accessible to any copy of the operating system upon which SQL Server 2000 is
installed.
Sincerely,
Stephen Dybing
This posting is provided "AS IS" with no warranties, and confers no rights.
begin 666 ts.gif
J1TE&.#EA`0`!`( ``````/___R'Y! $`````+ `````!``$```(!1 `[
`
end
|||"Richard Yeo" <RichardYeo@.discussions.microsoft.com> wrote in message
news:2AEB4663-795F-48F6-BA68-DEAA0D1C4BD8@.microsoft.com...
> Mike
> Have you implemented the /3GB switch in the OS boot.ini? If not by default
> 2GB is set aside for apps and 2GB for OS. By using this switch 3GB is set
> aside for apps and 1GB for OS. This probably explains why SQL Server is
only
> using 1.7GB RAM. It would probably be using more if you had the /3GB
switch
> set. Try setting /3GB switch, instruct SQL Server to use Physical RAM, and
> set max memory for SQL Server to be 2.5GB. This will leave 0.5GB for other
> apps including MSSearch (I think), file system caching, etc.
> Even if you don't have the budget for a 2 proc SQL license I would always
> recommend purchasing a second physical processor and configuring SQL
Server
> to only use one of the processors. This leaves the second processor free
for
> other applications, OS, remote administration.
Unless thigns have changed, last I knew, MS didn't allow that.
|||Stephen
Thanks for clarifying that.
Obviously there is a difference between what the product is capable of and
what the licensing folk think the product is or should be capable of.
In this instance I feel the Product Team got it right. In the real world
people often have to run more than one application on a server and should not
be forced to buy additional licenses.
With the move in the industry to consolidate this could a lot of unnecessary
expense.
Lets hope this license issue is addressed in the next release of SQL Server
2005.
Rich
"Stephen Dybing [MSFT]" wrote:
> "Richard Yeo" <RichardYeo@.discussions.microsoft.com> wrote in message
> news:2AEB4663-795F-48F6-BA68-DEAA0D1C4BD8@.microsoft.com...
> <SNIP>
> </SNIP>
> Please be careful about that because, in general, you have to purchase a
> processor license for every processor that the OS can see, whether that
> processor is configured to run SQL Server or not. For more information,
> please refer to the SQL Server 2000 Licensing FAQ web page at
> http://www.microsoft.com/sql/howtobuy/faq.asp. The third question under
> Licensing is reproduced below for your benefit.
> Q. Do I have to license all of the processors in a server?
> A. You only have to acquire licenses for processors that are
> accessible to any copy of the operating system upon which SQL Server 2000 is
> installed.
>
>
> --
> Sincerely,
> Stephen Dybing
> This posting is provided "AS IS" with no warranties, and confers no rights
2012年2月19日星期日
FTS query performance on SQL 2005
I am seeing some query performance issues on a full-text search on SQL 2005.
My table has about 20 million rows, containing an integer primary key and a
field of type text. This is running on a dual core Xeon 2.8 Ghz processor, 2
GB RAM, 15k RPM drives in a RAID 5 configuration.
My query looks like this:
select ItemID FROM Item WHERE CONTAINS(ItemText, 'there')
which returns about 40,000 rows, but the query takes over 2 minutes! Is that
normal performance for this beefy server for such a simple query? If I add
"TOP 20" after the SELECT, the query takes under 1 second.
I have checked the hardware and I can't seem to find constraints, either in
CPU, memory or disk. Any ideas why that query takes over 2 minutes?
Thanks.
Speed depends on the complexity of your query and the amount of rows you are
returning. In your case you have a simple query and what appears to be
causing the performance problem. I would use containstable with the
top_n_by_rank parameter to limit your results set to 100 or 200 rows.
Is this SQL 2005? There are some optimizations for SQL 2005 which will offer
better performance.
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Bahama Joe" <someone@.microsoft.com> wrote in message
news:ub9L57y%23GHA.1224@.TK2MSFTNGP05.phx.gbl...
>I am seeing some query performance issues on a full-text search on SQL
>2005. My table has about 20 million rows, containing an integer primary key
>and a field of type text. This is running on a dual core Xeon 2.8 Ghz
>processor, 2 GB RAM, 15k RPM drives in a RAID 5 configuration.
> My query looks like this:
> select ItemID FROM Item WHERE CONTAINS(ItemText, 'there')
> which returns about 40,000 rows, but the query takes over 2 minutes! Is
> that normal performance for this beefy server for such a simple query? If
> I add "TOP 20" after the SELECT, the query takes under 1 second.
> I have checked the hardware and I can't seem to find constraints, either
> in CPU, memory or disk. Any ideas why that query takes over 2 minutes?
> Thanks.
>
|||Thanks for the reply. Yes, this is SQL 2005 - what optimizations are you
referring to? I believe I have applied all optimizations that I've been able
to find in the various posts and online docs.
I know that I can limit the size of the resultset with top_n_by_rank, but in
this case, I'm trying to get back the full set of results. Another form of
my query is to do a "select count(*)", which has the same response times. I
believe this is because the full resultset is returned from the FTE back to
SQL Server, and then the count is taken on that. Is there a way to structure
the query to tell the FTE that you just want the count of results, so that
it doesn't ship the full results back to SQL Server?
Essentially, I'm trying to create a search engine, where the results will be
displayed back to the user in paginated form, so it will always display a
subset of the resultset, but I'd like to also display "Showing results 1-20
of 40,000", so I need a way to get the size of the resultset.
With regard to hardware, would you expect that I will get the most bang for
my buck by a) spreading my data across more disks (via RAID) in a single
server, b) creating a cluster of separate servers, c) adding more memory, or
d) adding more CPU's?
Thanks.
|||Thanks for the reply. Yes, this is SQL 2005 - what optimizations are you
referring to? I believe I have applied all optimizations that I've been able
to find in the various posts and online docs.
I know that I can limit the size of the resultset with top_n_by_rank, but in
this case, I'm trying to get back the full set of results. Another form of
my query is to do a "select count(*)", which has the same response times. I
believe this is because the full resultset is returned from the FTE back to
SQL Server, and then the count is taken on that. Is there a way to structure
the query to tell the FTE that you just want the count of results, so that
it doesn't ship the full results back to SQL Server?
Essentially, I'm trying to create a search engine, where the results will be
displayed back to the user in paginated form, so it will always display a
subset of the resultset, but I'd like to also display "Showing results 1-20
of 40,000", so I need a way to get the size of the resultset.
With regard to hardware, would you expect that I will get the most bang for
my buck by a) spreading my data across more disks (via RAID) in a single
server, b) creating a cluster of separate servers, c) adding more memory, or
d) adding more CPU's?
Thanks.
|||I'm struggling with the same issues as you.
Basically what i do is bank on the fact that it is rare for most people to
look beyond the first page of results, so I write the results of the search
to a table, and return it to a data reader displaying the first 25 results
and a count of all search results. Repeat searches go against the cached
table.
The optimizations are
1) use 64 bit
2) use a high resource_usage keeping in mind this can cause locking
3) reorganize your catalogs frequently
4) set ft crawl bandwidth (max) and ft notify bandwidth (max) to 0
5) max full-text crawl range to the number of cpu's on your system
6) convert your binary data to text
and you get the best bank for your buck by placing the full-text catalog on
its own disk subsystem and controller with the fastest disks available. RAID
5 offers best read performance, but for frequently updated catalogs use raid
10.
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Bahama Joe" <someone@.microsoft.com> wrote in message
news:epadrr9%23GHA.4712@.TK2MSFTNGP03.phx.gbl...
> Thanks for the reply. Yes, this is SQL 2005 - what optimizations are you
> referring to? I believe I have applied all optimizations that I've been
> able
> to find in the various posts and online docs.
> I know that I can limit the size of the resultset with top_n_by_rank, but
> in
> this case, I'm trying to get back the full set of results. Another form of
> my query is to do a "select count(*)", which has the same response times.
> I
> believe this is because the full resultset is returned from the FTE back
> to
> SQL Server, and then the count is taken on that. Is there a way to
> structure
> the query to tell the FTE that you just want the count of results, so that
> it doesn't ship the full results back to SQL Server?
> Essentially, I'm trying to create a search engine, where the results will
> be
> displayed back to the user in paginated form, so it will always display a
> subset of the resultset, but I'd like to also display "Showing results
> 1-20
> of 40,000", so I need a way to get the size of the resultset.
> With regard to hardware, would you expect that I will get the most bang
> for
> my buck by a) spreading my data across more disks (via RAID) in a single
> server, b) creating a cluster of separate servers, c) adding more memory,
> or
> d) adding more CPU's?
> Thanks.
>
>
|||Thanks for the reply. I had already applied optimizations 2, 4 and 5, based
on one of your earlier posts in another thread. I don't have access to a
64-bit server, but this is a good suggestion. My data is static data, so I
don't expect I'll need to reorganize the catalogs frequently. Also, I only
have text data stored in a single column of type 'text', no binary data.
With regard to caching, I find that subsequent queries using the same search
string (i.e. to support the user going to the next page) come back in about
3-4 ms, meaning that there's already some good caching in place within SQL
Server. It seems that relying on that should be sufficient, instead of
creating my own sepearate caching mechanism as you describe. Is that not
your experience? The most pressing issue I'm trying to resolve is why the
first query (without a top_n_by_rank) can sometimes take 2-3 minutes to
respond.
Thanks.
|||This could be caching of the catalog pages. What is your max server memory
setting?
sp_configure 'max server memory (MB)'
Also what happens if you just issue a new contains query ie like this
select * from TableName where contains(*,'"George Bush"')
supposing you have not searched for George Bush recently and he is in your
content?
It could be that the other tables are causing the performance hit and not
your full-text queries themselves.
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Bahama Joe" <someone@.microsoft.com> wrote in message
news:ON3tvnE$GHA.5092@.TK2MSFTNGP04.phx.gbl...
> Thanks for the reply. I had already applied optimizations 2, 4 and 5,
> based on one of your earlier posts in another thread. I don't have access
> to a 64-bit server, but this is a good suggestion. My data is static data,
> so I don't expect I'll need to reorganize the catalogs frequently. Also, I
> only have text data stored in a single column of type 'text', no binary
> data.
> With regard to caching, I find that subsequent queries using the same
> search string (i.e. to support the user going to the next page) come back
> in about 3-4 ms, meaning that there's already some good caching in place
> within SQL Server. It seems that relying on that should be sufficient,
> instead of creating my own sepearate caching mechanism as you describe. Is
> that not your experience? The most pressing issue I'm trying to resolve is
> why the first query (without a top_n_by_rank) can sometimes take 2-3
> minutes to respond.
> Thanks.
>
|||Hello Bahama,
Is this an upgrade or a new SQL install?
Make sure you update your statistics and possibly rebuild your indexes.
To get a count it is better to do select count(1) from containstable (table,
column,search)
To get the paged results do SELECT TOP x where x covers the page the users
wants.
Make sure you SQL box isn't using all the memory, you need to share it with
full text.
How big is your database? 20 million rows and 2Gb of cache doesn't allow
for much of you DB to be cached.
What indexes do you have on your tables?
Simon Sabin
SQL Server MVP
http://sqlblogcasts.com/blogs/simons
> Thanks for the reply. I had already applied optimizations 2, 4 and 5,
> based on one of your earlier posts in another thread. I don't have
> access to a 64-bit server, but this is a good suggestion. My data is
> static data, so I don't expect I'll need to reorganize the catalogs
> frequently. Also, I only have text data stored in a single column of
> type 'text', no binary data.
> With regard to caching, I find that subsequent queries using the same
> search string (i.e. to support the user going to the next page) come
> back in about 3-4 ms, meaning that there's already some good caching
> in place within SQL Server. It seems that relying on that should be
> sufficient, instead of creating my own sepearate caching mechanism as
> you describe. Is that not your experience? The most pressing issue I'm
> trying to resolve is why the first query (without a top_n_by_rank) can
> sometimes take 2-3 minutes to respond.
> Thanks.
>
|||Hilary,
Here is the result of that query:
name=max server memory (MB)
minimum=16
maximum=2147483647
config_value=4096
run_value=4096
Is that configured correctly for a server w/ 2GB of physical RAM?
Also, in one of your earlier posts, you mentioned setting "max full-text
crawl range" to the number of CPU's on the system. I have a dual-core Xeon
processor, meaning it has 2 CPU's (in one chip). However, due to Hyper
Threading, Windows and SQL Server see this server as having 4 CPU's, so I
have that value set to 4. Is that a good setting?
All of my tests have been on just the single table that contains my
full-text index, such as "select ItemID from Item where
contains(ItemText,'"George Bush"')", so there are no other tables involved.
Thanks for all of your insightful replies.
|||Hello Simon,
This is a new SQL install. I just created the table, loaded it with all 20
million rows from a text file via "Import data...", then created the
full-text index (which took about 2 hours). My table basically includes an
"int" primary key (clustered index) column and a "text" column with the text
to be indexed. The table also includes 2 other "text" fields, but they're
not involved in these queries. The result is a database .MDF file of approx.
12GB and a full-text index with files of approx. 2GB, with the full-text
index containing approx. 1.4 million unique keys.
When I look at task manager for memory usage, I see the following:
sqlservr.exe --> mem usage=740MB, VM size=749MB
msftesql.exe --> mem usage=7.6MB, VM size=5MB
Right after a reboot, these numbers are continually rising, but after a
number of queries, these numbers steady out to the ones listed above. Based
on this, would you recommend that I change my memory configuration?
Thanks.
My table has about 20 million rows, containing an integer primary key and a
field of type text. This is running on a dual core Xeon 2.8 Ghz processor, 2
GB RAM, 15k RPM drives in a RAID 5 configuration.
My query looks like this:
select ItemID FROM Item WHERE CONTAINS(ItemText, 'there')
which returns about 40,000 rows, but the query takes over 2 minutes! Is that
normal performance for this beefy server for such a simple query? If I add
"TOP 20" after the SELECT, the query takes under 1 second.
I have checked the hardware and I can't seem to find constraints, either in
CPU, memory or disk. Any ideas why that query takes over 2 minutes?
Thanks.
Speed depends on the complexity of your query and the amount of rows you are
returning. In your case you have a simple query and what appears to be
causing the performance problem. I would use containstable with the
top_n_by_rank parameter to limit your results set to 100 or 200 rows.
Is this SQL 2005? There are some optimizations for SQL 2005 which will offer
better performance.
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Bahama Joe" <someone@.microsoft.com> wrote in message
news:ub9L57y%23GHA.1224@.TK2MSFTNGP05.phx.gbl...
>I am seeing some query performance issues on a full-text search on SQL
>2005. My table has about 20 million rows, containing an integer primary key
>and a field of type text. This is running on a dual core Xeon 2.8 Ghz
>processor, 2 GB RAM, 15k RPM drives in a RAID 5 configuration.
> My query looks like this:
> select ItemID FROM Item WHERE CONTAINS(ItemText, 'there')
> which returns about 40,000 rows, but the query takes over 2 minutes! Is
> that normal performance for this beefy server for such a simple query? If
> I add "TOP 20" after the SELECT, the query takes under 1 second.
> I have checked the hardware and I can't seem to find constraints, either
> in CPU, memory or disk. Any ideas why that query takes over 2 minutes?
> Thanks.
>
|||Thanks for the reply. Yes, this is SQL 2005 - what optimizations are you
referring to? I believe I have applied all optimizations that I've been able
to find in the various posts and online docs.
I know that I can limit the size of the resultset with top_n_by_rank, but in
this case, I'm trying to get back the full set of results. Another form of
my query is to do a "select count(*)", which has the same response times. I
believe this is because the full resultset is returned from the FTE back to
SQL Server, and then the count is taken on that. Is there a way to structure
the query to tell the FTE that you just want the count of results, so that
it doesn't ship the full results back to SQL Server?
Essentially, I'm trying to create a search engine, where the results will be
displayed back to the user in paginated form, so it will always display a
subset of the resultset, but I'd like to also display "Showing results 1-20
of 40,000", so I need a way to get the size of the resultset.
With regard to hardware, would you expect that I will get the most bang for
my buck by a) spreading my data across more disks (via RAID) in a single
server, b) creating a cluster of separate servers, c) adding more memory, or
d) adding more CPU's?
Thanks.
|||Thanks for the reply. Yes, this is SQL 2005 - what optimizations are you
referring to? I believe I have applied all optimizations that I've been able
to find in the various posts and online docs.
I know that I can limit the size of the resultset with top_n_by_rank, but in
this case, I'm trying to get back the full set of results. Another form of
my query is to do a "select count(*)", which has the same response times. I
believe this is because the full resultset is returned from the FTE back to
SQL Server, and then the count is taken on that. Is there a way to structure
the query to tell the FTE that you just want the count of results, so that
it doesn't ship the full results back to SQL Server?
Essentially, I'm trying to create a search engine, where the results will be
displayed back to the user in paginated form, so it will always display a
subset of the resultset, but I'd like to also display "Showing results 1-20
of 40,000", so I need a way to get the size of the resultset.
With regard to hardware, would you expect that I will get the most bang for
my buck by a) spreading my data across more disks (via RAID) in a single
server, b) creating a cluster of separate servers, c) adding more memory, or
d) adding more CPU's?
Thanks.
|||I'm struggling with the same issues as you.
Basically what i do is bank on the fact that it is rare for most people to
look beyond the first page of results, so I write the results of the search
to a table, and return it to a data reader displaying the first 25 results
and a count of all search results. Repeat searches go against the cached
table.
The optimizations are
1) use 64 bit
2) use a high resource_usage keeping in mind this can cause locking
3) reorganize your catalogs frequently
4) set ft crawl bandwidth (max) and ft notify bandwidth (max) to 0
5) max full-text crawl range to the number of cpu's on your system
6) convert your binary data to text
and you get the best bank for your buck by placing the full-text catalog on
its own disk subsystem and controller with the fastest disks available. RAID
5 offers best read performance, but for frequently updated catalogs use raid
10.
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Bahama Joe" <someone@.microsoft.com> wrote in message
news:epadrr9%23GHA.4712@.TK2MSFTNGP03.phx.gbl...
> Thanks for the reply. Yes, this is SQL 2005 - what optimizations are you
> referring to? I believe I have applied all optimizations that I've been
> able
> to find in the various posts and online docs.
> I know that I can limit the size of the resultset with top_n_by_rank, but
> in
> this case, I'm trying to get back the full set of results. Another form of
> my query is to do a "select count(*)", which has the same response times.
> I
> believe this is because the full resultset is returned from the FTE back
> to
> SQL Server, and then the count is taken on that. Is there a way to
> structure
> the query to tell the FTE that you just want the count of results, so that
> it doesn't ship the full results back to SQL Server?
> Essentially, I'm trying to create a search engine, where the results will
> be
> displayed back to the user in paginated form, so it will always display a
> subset of the resultset, but I'd like to also display "Showing results
> 1-20
> of 40,000", so I need a way to get the size of the resultset.
> With regard to hardware, would you expect that I will get the most bang
> for
> my buck by a) spreading my data across more disks (via RAID) in a single
> server, b) creating a cluster of separate servers, c) adding more memory,
> or
> d) adding more CPU's?
> Thanks.
>
>
|||Thanks for the reply. I had already applied optimizations 2, 4 and 5, based
on one of your earlier posts in another thread. I don't have access to a
64-bit server, but this is a good suggestion. My data is static data, so I
don't expect I'll need to reorganize the catalogs frequently. Also, I only
have text data stored in a single column of type 'text', no binary data.
With regard to caching, I find that subsequent queries using the same search
string (i.e. to support the user going to the next page) come back in about
3-4 ms, meaning that there's already some good caching in place within SQL
Server. It seems that relying on that should be sufficient, instead of
creating my own sepearate caching mechanism as you describe. Is that not
your experience? The most pressing issue I'm trying to resolve is why the
first query (without a top_n_by_rank) can sometimes take 2-3 minutes to
respond.
Thanks.
|||This could be caching of the catalog pages. What is your max server memory
setting?
sp_configure 'max server memory (MB)'
Also what happens if you just issue a new contains query ie like this
select * from TableName where contains(*,'"George Bush"')
supposing you have not searched for George Bush recently and he is in your
content?
It could be that the other tables are causing the performance hit and not
your full-text queries themselves.
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Bahama Joe" <someone@.microsoft.com> wrote in message
news:ON3tvnE$GHA.5092@.TK2MSFTNGP04.phx.gbl...
> Thanks for the reply. I had already applied optimizations 2, 4 and 5,
> based on one of your earlier posts in another thread. I don't have access
> to a 64-bit server, but this is a good suggestion. My data is static data,
> so I don't expect I'll need to reorganize the catalogs frequently. Also, I
> only have text data stored in a single column of type 'text', no binary
> data.
> With regard to caching, I find that subsequent queries using the same
> search string (i.e. to support the user going to the next page) come back
> in about 3-4 ms, meaning that there's already some good caching in place
> within SQL Server. It seems that relying on that should be sufficient,
> instead of creating my own sepearate caching mechanism as you describe. Is
> that not your experience? The most pressing issue I'm trying to resolve is
> why the first query (without a top_n_by_rank) can sometimes take 2-3
> minutes to respond.
> Thanks.
>
|||Hello Bahama,
Is this an upgrade or a new SQL install?
Make sure you update your statistics and possibly rebuild your indexes.
To get a count it is better to do select count(1) from containstable (table,
column,search)
To get the paged results do SELECT TOP x where x covers the page the users
wants.
Make sure you SQL box isn't using all the memory, you need to share it with
full text.
How big is your database? 20 million rows and 2Gb of cache doesn't allow
for much of you DB to be cached.
What indexes do you have on your tables?
Simon Sabin
SQL Server MVP
http://sqlblogcasts.com/blogs/simons
> Thanks for the reply. I had already applied optimizations 2, 4 and 5,
> based on one of your earlier posts in another thread. I don't have
> access to a 64-bit server, but this is a good suggestion. My data is
> static data, so I don't expect I'll need to reorganize the catalogs
> frequently. Also, I only have text data stored in a single column of
> type 'text', no binary data.
> With regard to caching, I find that subsequent queries using the same
> search string (i.e. to support the user going to the next page) come
> back in about 3-4 ms, meaning that there's already some good caching
> in place within SQL Server. It seems that relying on that should be
> sufficient, instead of creating my own sepearate caching mechanism as
> you describe. Is that not your experience? The most pressing issue I'm
> trying to resolve is why the first query (without a top_n_by_rank) can
> sometimes take 2-3 minutes to respond.
> Thanks.
>
|||Hilary,
Here is the result of that query:
name=max server memory (MB)
minimum=16
maximum=2147483647
config_value=4096
run_value=4096
Is that configured correctly for a server w/ 2GB of physical RAM?
Also, in one of your earlier posts, you mentioned setting "max full-text
crawl range" to the number of CPU's on the system. I have a dual-core Xeon
processor, meaning it has 2 CPU's (in one chip). However, due to Hyper
Threading, Windows and SQL Server see this server as having 4 CPU's, so I
have that value set to 4. Is that a good setting?
All of my tests have been on just the single table that contains my
full-text index, such as "select ItemID from Item where
contains(ItemText,'"George Bush"')", so there are no other tables involved.
Thanks for all of your insightful replies.
|||Hello Simon,
This is a new SQL install. I just created the table, loaded it with all 20
million rows from a text file via "Import data...", then created the
full-text index (which took about 2 hours). My table basically includes an
"int" primary key (clustered index) column and a "text" column with the text
to be indexed. The table also includes 2 other "text" fields, but they're
not involved in these queries. The result is a database .MDF file of approx.
12GB and a full-text index with files of approx. 2GB, with the full-text
index containing approx. 1.4 million unique keys.
When I look at task manager for memory usage, I see the following:
sqlservr.exe --> mem usage=740MB, VM size=749MB
msftesql.exe --> mem usage=7.6MB, VM size=5MB
Right after a reboot, these numbers are continually rising, but after a
number of queries, these numbers steady out to the ones listed above. Based
on this, would you recommend that I change my memory configuration?
Thanks.
订阅：
博文 (Atom)