Hi there,
We have a problem with a slow startup of the SQL 2005 full text engine on a
new box. The first query (after a reboot or first query in the morning)
against the full text index is very slow, it takes around 30 seconds to
execute. The full text index is very small (~2 mb), so this should not be an
issue.
On another server with the same database we don't see this problem. The
first query is executed in less than a second.
The only difference between the servers is that the server with the slow
full text is running SQL 2005 Standard x64 and Windows 2003 Standard x64.
The other server is running SQL 2005 Standard and Windows 2003 Standard.
Any suggestions?
I was just about to do a post for the same thing. I'm seeing this same
problem on two machines; both are Win2k3 Enterprise (32 bit); one is SQL
Server 2005 Enterprise and one is using 2005 Developer; both machines have
2GB RAM. The first query using FTS that I run takes ~45 seconds and then
every one after that is very fast (< 1 second), for about 10 or 15 minutes.
Every 10 or 15 minutes (haven't timed it exactly), it does the 45 second
thing again. Both machines have the same database (one's a development copy),
and the table with the index is only about 60MB, with ~40K rows.
Any help would be much appreciated.
thanks,
michael
"Rasmus Waeherns" wrote:
> Hi there,
> We have a problem with a slow startup of the SQL 2005 full text engine on a
> new box. The first query (after a reboot or first query in the morning)
> against the full text index is very slow, it takes around 30 seconds to
> execute. The full text index is very small (~2 mb), so this should not be an
> issue.
> On another server with the same database we don't see this problem. The
> first query is executed in less than a second.
> The only difference between the servers is that the server with the slow
> full text is running SQL 2005 Standard x64 and Windows 2003 Standard x64.
> The other server is running SQL 2005 Standard and Windows 2003 Standard.
> Any suggestions?
>
>
|||Hi there,
We tried everything, from reinstalling SQL Server 2005, disabling services,
restoring new databases, rebuilding full text index - you name it, and
didn't find a solution for the problem.
We've now reinstalled Windows 2003 x64 and SQL 2005 x64 on the server and
now the problems are gone. Weird!
Best regards,
Rasmus Waehrens
"ackphht" <ackphht@.discussions.microsoft.com> wrote in message
news:1B500AE2-5EBD-461C-90FE-6B1594D0B9F8@.microsoft.com...[vbcol=seagreen]
>I was just about to do a post for the same thing. I'm seeing this same
> problem on two machines; both are Win2k3 Enterprise (32 bit); one is SQL
> Server 2005 Enterprise and one is using 2005 Developer; both machines have
> 2GB RAM. The first query using FTS that I run takes ~45 seconds and then
> every one after that is very fast (< 1 second), for about 10 or 15
> minutes.
> Every 10 or 15 minutes (haven't timed it exactly), it does the 45 second
> thing again. Both machines have the same database (one's a development
> copy),
> and the table with the index is only about 60MB, with ~40K rows.
> Any help would be much appreciated.
> thanks,
> michael
> "Rasmus Waeherns" wrote:
2012年3月29日星期四
2012年3月27日星期二
Full Text Search slow, then fast for 30 minutes, then slow again
We've just upgraded a database to SQL 2005 Standard sp1 on a Windows 2003 sp1 server and we are experiencing a problem with Full Text Search. We recreated a Full Text Catalogue in the new SQL 2005 db and it seemed to populate fine. However, now when we run a query on that FTI for the first time it takes about 30-45 seconds to return a result. After that first query, subsequent queries return the result immediately. Then if there is an approximate 30 minute gap until the next query is run then it goes back to taking 30-45 seconds to return the result. It seems as though the results of the query are cached and remain in the chache as long as queries continue to be submitted at <30 minute intervals. If a query is not submitted in 30 minutes then the cache expires. That is only my theory. Has anyone seen this issue? Any insite you can provide is greatly appreciated. Thanks.Does your system have internet access? On loading the system resources for FTS, it will connect to the internet to validate the signature. The duration is about what you've reported, 45sec. After about 30 minutes of no activity, the resouces will be unloaded to save memory.
So, to keep the resources in memory you could create a job that execute the query every 10minutes.|||
Hi OJ,
The signature of what? Is there any option to disable this validation?
Please advice. Thanks, Radu.
|||It's the signature of the DLLs/binaries used by MsSearch (FTS) service. You can turn off the checking by runningexec sp_fulltext_Service 'verify_signature',0
Full Text Search slow, then fast for 30 minutes, then slow again
We've just upgraded a database to SQL 2005 Standard sp1 on a Windows 2003 sp1 server and we are experiencing a problem with Full Text Search. We recreated a Full Text Catalogue in the new SQL 2005 db and it seemed to populate fine. However, now when we run a query on that FTI for the first time it takes about 30-45 seconds to return a result. After that first query, subsequent queries return the result immediately. Then if there is an approximate 30 minute gap until the next query is run then it goes back to taking 30-45 seconds to return the result. It seems as though the results of the query are cached and remain in the chache as long as queries continue to be submitted at <30 minute intervals. If a query is not submitted in 30 minutes then the cache expires. That is only my theory. Has anyone seen this issue? Any insite you can provide is greatly appreciated. Thanks.Does your system have internet access? On loading the system resources for FTS, it will connect to the internet to validate the signature. The duration is about what you've reported, 45sec. After about 30 minutes of no activity, the resouces will be unloaded to save memory.
So, to keep the resources in memory you could create a job that execute the query every 10minutes.|||
Hi OJ,
The signature of what? Is there any option to disable this validation?
Please advice. Thanks, Radu.
|||It's the signature of the DLLs/binaries used by MsSearch (FTS) service. You can turn off the checking by runningexec sp_fulltext_Service 'verify_signature',0
2012年3月21日星期三
Full Text Search
Hi,
I am wondering if *Full Text Search* is efficient or not (not too slow ?!)
with a table having more than 200.000 records ?
Thanks !Lionel,
Yes, it is efficient and not too slow with either FT Indexing or FT
Searching of a table with more than 200.000 rows.
In fact, Full-text Indexing (FTI) and Full-text Search (FTS) of a table with
200K rows is well within the "sweet spot" for not only SQL Server 7.0, but
SQL Server 2000 and SQL Server 2000 (Yukon) will rock for FT-enabled tables
of this *small* size!!
What is your primary concern? FTI or FTS performance with larger tables? If
you have a table with a more than 2 million rows, you may need to tune both
the hardware and software configurations, (see SQL Server 2000 BOL title
"Full-text Search Recommendations" for more details), but still you should
be able to FT Index million+ row tables.
Regards,
John
--
SQL Full Text Search Blog
http://spaces.msn.com/members/jtkane/
"Lionel" <nospam@.org.org> wrote in message
news:Of3BxwpDFHA.1496@.TK2MSFTNGP14.phx.gbl...
> Hi,
> I am wondering if *Full Text Search* is efficient or not (not too slow
?!)
> with a table having more than 200.000 records ?
> Thanks !
>|||I'm new to this - where can I find this doc Full-text Search recommendations
?
Looking for performance tuning tips. What is a BOL?
Thanks
"John Kane" wrote:
> Lionel,
> Yes, it is efficient and not too slow with either FT Indexing or FT
> Searching of a table with more than 200.000 rows.
> In fact, Full-text Indexing (FTI) and Full-text Search (FTS) of a table wi
th
> 200K rows is well within the "sweet spot" for not only SQL Server 7.0, but
> SQL Server 2000 and SQL Server 2000 (Yukon) will rock for FT-enabled table
s
> of this *small* size!!
> What is your primary concern? FTI or FTS performance with larger tables? I
f
> you have a table with a more than 2 million rows, you may need to tune bot
h
> the hardware and software configurations, (see SQL Server 2000 BOL title
> "Full-text Search Recommendations" for more details), but still you should
> be able to FT Index million+ row tables.
> Regards,
> John
> --
> SQL Full Text Search Blog
> http://spaces.msn.com/members/jtkane/
>
> "Lionel" <nospam@.org.org> wrote in message
> news:Of3BxwpDFHA.1496@.TK2MSFTNGP14.phx.gbl...
> ?!)
>
>|||You're welcome, Chris,
The BOL is short for "Books Online" and is the online (vs. printed)
documentation that ships with SQL Server 2000. You can also find an Internet
version of "Full-text Search Recommendations" at:
http://msdn.microsoft.com/library/d...m
er.asp
See also my blog entry: "SQL Server 2000 Full-Text Search Resources and
Links" at:
http://spaces.msn.com/members/jtkane/Blog/cns!1pWDBCiDX1uvH5ATJmNCVLPQ!305.e
ntry
Hope that helps!
John
--
SQL Full Text Search Blog
http://spaces.msn.com/members/jtkane/
"Chris" <Chris@.discussions.microsoft.com> wrote in message
news:93C06810-E298-4148-963F-79584FAAA640@.microsoft.com...
> I'm new to this - where can I find this doc Full-text Search
recommendations?
> Looking for performance tuning tips. What is a BOL?
> Thanks
> "John Kane" wrote:
>
with
but
tables
If
both
should
slow
I am wondering if *Full Text Search* is efficient or not (not too slow ?!)
with a table having more than 200.000 records ?
Thanks !Lionel,
Yes, it is efficient and not too slow with either FT Indexing or FT
Searching of a table with more than 200.000 rows.
In fact, Full-text Indexing (FTI) and Full-text Search (FTS) of a table with
200K rows is well within the "sweet spot" for not only SQL Server 7.0, but
SQL Server 2000 and SQL Server 2000 (Yukon) will rock for FT-enabled tables
of this *small* size!!
What is your primary concern? FTI or FTS performance with larger tables? If
you have a table with a more than 2 million rows, you may need to tune both
the hardware and software configurations, (see SQL Server 2000 BOL title
"Full-text Search Recommendations" for more details), but still you should
be able to FT Index million+ row tables.
Regards,
John
--
SQL Full Text Search Blog
http://spaces.msn.com/members/jtkane/
"Lionel" <nospam@.org.org> wrote in message
news:Of3BxwpDFHA.1496@.TK2MSFTNGP14.phx.gbl...
> Hi,
> I am wondering if *Full Text Search* is efficient or not (not too slow
?!)
> with a table having more than 200.000 records ?
> Thanks !
>|||I'm new to this - where can I find this doc Full-text Search recommendations
?
Looking for performance tuning tips. What is a BOL?
Thanks
"John Kane" wrote:
> Lionel,
> Yes, it is efficient and not too slow with either FT Indexing or FT
> Searching of a table with more than 200.000 rows.
> In fact, Full-text Indexing (FTI) and Full-text Search (FTS) of a table wi
th
> 200K rows is well within the "sweet spot" for not only SQL Server 7.0, but
> SQL Server 2000 and SQL Server 2000 (Yukon) will rock for FT-enabled table
s
> of this *small* size!!
> What is your primary concern? FTI or FTS performance with larger tables? I
f
> you have a table with a more than 2 million rows, you may need to tune bot
h
> the hardware and software configurations, (see SQL Server 2000 BOL title
> "Full-text Search Recommendations" for more details), but still you should
> be able to FT Index million+ row tables.
> Regards,
> John
> --
> SQL Full Text Search Blog
> http://spaces.msn.com/members/jtkane/
>
> "Lionel" <nospam@.org.org> wrote in message
> news:Of3BxwpDFHA.1496@.TK2MSFTNGP14.phx.gbl...
> ?!)
>
>|||You're welcome, Chris,
The BOL is short for "Books Online" and is the online (vs. printed)
documentation that ships with SQL Server 2000. You can also find an Internet
version of "Full-text Search Recommendations" at:
http://msdn.microsoft.com/library/d...m
er.asp
See also my blog entry: "SQL Server 2000 Full-Text Search Resources and
Links" at:
http://spaces.msn.com/members/jtkane/Blog/cns!1pWDBCiDX1uvH5ATJmNCVLPQ!305.e
ntry
Hope that helps!
John
--
SQL Full Text Search Blog
http://spaces.msn.com/members/jtkane/
"Chris" <Chris@.discussions.microsoft.com> wrote in message
news:93C06810-E298-4148-963F-79584FAAA640@.microsoft.com...
> I'm new to this - where can I find this doc Full-text Search
recommendations?
> Looking for performance tuning tips. What is a BOL?
> Thanks
> "John Kane" wrote:
>
with
but
tables
If
both
should
slow
Full Text Query Starts Slow
I've created a FullTextCatalog on one of my Databases and added a full text index on one of my text columns.
The first time I run a query against the data it takes roughly 40-45 seconds to return data. After that it blazes and runs in under a second. If I don't query it for 20-30 minutes, it will take 40-45 seconds again and then fly until the next break.
Is there a configuration setting somewhere that I'm missing on this? Currently the Index is only about 5MB so it should take that long to read in when I'm querying it.
I don't think it has anything to do with size because the actual return can contain anywhere from 10-80K rows and the speed is about the same.
I'm using Standard edition on a 2003 Standard Server if that plays into the potential problem at all. We're currently downloading and installing the new Service Pack to see if that fixes it, but for some reason I'm not holding my breath. I'm assuming that there is something we need to change in the configuration.
Let me know if I'm missing any pertinent information. Thanks.http://www.sql-server-performance.com/full_text_search.asp & http://www.sql-server-performance.com/tb_search_optimization.asp for your action & information|||I have verified that nothing is being populated at the time I'm running these queries, so the first one should be the problem. We'll be loading 500,000 records per day once in the morning. I have the Catalog set to manual population, so that I re-populate after the load has completed.
In 2005 I didn't think that it had to go out to the MS Search searvice? I thought it was now all contained within SQL Server. In any regards, this is happening exactly the same way regard less of the query or amount of data returned. A call just to ContainsTable() with no other joins that returns 3 or 70.000 rows takes roughly the same amount of time.sql
The first time I run a query against the data it takes roughly 40-45 seconds to return data. After that it blazes and runs in under a second. If I don't query it for 20-30 minutes, it will take 40-45 seconds again and then fly until the next break.
Is there a configuration setting somewhere that I'm missing on this? Currently the Index is only about 5MB so it should take that long to read in when I'm querying it.
I don't think it has anything to do with size because the actual return can contain anywhere from 10-80K rows and the speed is about the same.
I'm using Standard edition on a 2003 Standard Server if that plays into the potential problem at all. We're currently downloading and installing the new Service Pack to see if that fixes it, but for some reason I'm not holding my breath. I'm assuming that there is something we need to change in the configuration.
Let me know if I'm missing any pertinent information. Thanks.http://www.sql-server-performance.com/full_text_search.asp & http://www.sql-server-performance.com/tb_search_optimization.asp for your action & information|||I have verified that nothing is being populated at the time I'm running these queries, so the first one should be the problem. We'll be loading 500,000 records per day once in the morning. I have the Catalog set to manual population, so that I re-populate after the load has completed.
In 2005 I didn't think that it had to go out to the MS Search searvice? I thought it was now all contained within SQL Server. In any regards, this is happening exactly the same way regard less of the query or amount of data returned. A call just to ContainsTable() with no other joins that returns 3 or 70.000 rows takes roughly the same amount of time.sql
2012年3月11日星期日
Full text index population is slow
I have 1 milion rows on a SQL server 2005 SP1 with 2 Text columns to
be indexed.
The DB is replicated on another server. Transactional.
On the Publisher the population take the CPU near the 100% for 3 day
and then the work is done.
On the subscriber, that is also the distributor the work don't end.
The CPU of the server is at the 100% (2 XEON 4GB Ram).
In the profiler i see many commands SP_fulltext_getdbdata ma the
sp_fulltext_pending_changes return ever the same numbre of pending
rows or more.
The columns contains italian word.
Someone can help me.
Something is very wrong here. What do the gatherer logs say. You can find
them in c:\Program Files\Microsoft sql server\mssql.X\mssql\log
and they look like SQLFT0018700017.log.X
Hilary Cotter
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
<innocenzo.daraio@.gmail.com> wrote in message
news:1176273986.698099.26940@.l77g2000hsb.googlegro ups.com...
>I have 1 milion rows on a SQL server 2005 SP1 with 2 Text columns to
> be indexed.
> The DB is replicated on another server. Transactional.
> On the Publisher the population take the CPU near the 100% for 3 day
> and then the work is done.
> On the subscriber, that is also the distributor the work don't end.
> The CPU of the server is at the 100% (2 XEON 4GB Ram).
> In the profiler i see many commands SP_fulltext_getdbdata ma the
> sp_fulltext_pending_changes return ever the same numbre of pending
> rows or more.
> The columns contains italian word.
> Someone can help me.
>
be indexed.
The DB is replicated on another server. Transactional.
On the Publisher the population take the CPU near the 100% for 3 day
and then the work is done.
On the subscriber, that is also the distributor the work don't end.
The CPU of the server is at the 100% (2 XEON 4GB Ram).
In the profiler i see many commands SP_fulltext_getdbdata ma the
sp_fulltext_pending_changes return ever the same numbre of pending
rows or more.
The columns contains italian word.
Someone can help me.
Something is very wrong here. What do the gatherer logs say. You can find
them in c:\Program Files\Microsoft sql server\mssql.X\mssql\log
and they look like SQLFT0018700017.log.X
Hilary Cotter
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
<innocenzo.daraio@.gmail.com> wrote in message
news:1176273986.698099.26940@.l77g2000hsb.googlegro ups.com...
>I have 1 milion rows on a SQL server 2005 SP1 with 2 Text columns to
> be indexed.
> The DB is replicated on another server. Transactional.
> On the Publisher the population take the CPU near the 100% for 3 day
> and then the work is done.
> On the subscriber, that is also the distributor the work don't end.
> The CPU of the server is at the 100% (2 XEON 4GB Ram).
> In the profiler i see many commands SP_fulltext_getdbdata ma the
> sp_fulltext_pending_changes return ever the same numbre of pending
> rows or more.
> The columns contains italian word.
> Someone can help me.
>
2012年2月26日星期日
Full population very slow
I've got SQL2000 Enterprise edition running on windows 2000 advanced server,
we've recently upgraded from SQL sp2 to sp3a, this caused a catalog rebuild
but full population is taking forever. The column with Full text search
enabled is of image type, there are 2.5 million record in the table, about
7GB in size.
I've started full population 2 days ago and it's still showing "Population
in progress", when I view the property page of the catalog, I can see ~
2300000 items, catalog size: 190MB, unique key count: 362586. It appears to
be processing 1000 items per minute.
The server has 8 CPUs, 8GB RAM with /PAE and /3GB enabled, heavy duty SAN
disks.
mssdmn.exe is the most active process, it's using between 30-40 CPU on one
processor only, disk utilization is minimal and plenty of physical memory
available for use. This process is using 6 threads, 10MB RAM, mssearch.exe is
using 50MB RAM and 16 threads.
Is there any way of speeding up population?
Thanks,
Ad
set sp_fulltext_service 'resource_usage', 5
"Ad" <Ad@.discussions.microsoft.com> wrote in message
news:B94E0F3A-7655-4D8A-8605-EA1BC43E6329@.microsoft.com...
> I've got SQL2000 Enterprise edition running on windows 2000 advanced
> server,
> we've recently upgraded from SQL sp2 to sp3a, this caused a catalog
> rebuild
> but full population is taking forever. The column with Full text search
> enabled is of image type, there are 2.5 million record in the table, about
> 7GB in size.
> I've started full population 2 days ago and it's still showing "Population
> in progress", when I view the property page of the catalog, I can see ~
> 2300000 items, catalog size: 190MB, unique key count: 362586. It appears
> to
> be processing 1000 items per minute.
> The server has 8 CPUs, 8GB RAM with /PAE and /3GB enabled, heavy duty SAN
> disks.
> mssdmn.exe is the most active process, it's using between 30-40 CPU on one
> processor only, disk utilization is minimal and plenty of physical memory
> available for use. This process is using 6 threads, 10MB RAM, mssearch.exe
> is
> using 50MB RAM and 16 threads.
> Is there any way of speeding up population?
> Thanks,
> Ad
>
|||Ad,
There are a couple of thing you can do to improve the FT Indexing
performance without stopping & re-locating the FT Catalog. You can set the
MSSearch service's to use one CPU and then use sp_configure to set the
affinity of SQL Server to that cpu to avoid cpu contention during heavy
MSSearch CPU usage via the following method - launch the Tskmgr.exe from the
AT command on the multi-proc server where sql server resides, as follows:
at <current_time+1min> /interactive taskmgr.exe
when it launches, you can then set "cpu affinity" for the MSSearch service
to a cpu or set of cpu's not being used by SQL Server. Then you would use
sp_configure to set SQL Server's cpu affinity to the other cpu's, thus
preventing the cpu usage of mssearch from affecting your sql server
processing. Note, this only works on multi-processor servers and is a
secured method for achieving this feature.
Additionally, as your 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.
Finally, I'd highly recommend that you review all the resources "SQL Server
2000 Full-Text Search Resources and Links" at:
http://spaces.msn.com/members/jtkane/Blog/cns!1pWDBCiDX1uvH5ATJmNCVLPQ!305.entry
Regards,
John
SQL Full Text Search Blog
http://spaces.msn.com/members/jtkane/
"Ad" <Ad@.discussions.microsoft.com> wrote in message
news:B94E0F3A-7655-4D8A-8605-EA1BC43E6329@.microsoft.com...
> I've got SQL2000 Enterprise edition running on windows 2000 advanced
server,
> we've recently upgraded from SQL sp2 to sp3a, this caused a catalog
rebuild
> but full population is taking forever. The column with Full text search
> enabled is of image type, there are 2.5 million record in the table, about
> 7GB in size.
> I've started full population 2 days ago and it's still showing
"Population
> in progress", when I view the property page of the catalog, I can see ~
> 2300000 items, catalog size: 190MB, unique key count: 362586. It appears
to
> be processing 1000 items per minute.
> The server has 8 CPUs, 8GB RAM with /PAE and /3GB enabled, heavy duty SAN
> disks.
> mssdmn.exe is the most active process, it's using between 30-40 CPU on one
> processor only, disk utilization is minimal and plenty of physical memory
> available for use. This process is using 6 threads, 10MB RAM, mssearch.exe
is
> using 50MB RAM and 16 threads.
> Is there any way of speeding up population?
> Thanks,
> Ad
>
|||sp_fulltext_service 'resource_usage', 5 , did not make any difference, does
it need stop and re-start of MSSearch service?
Thanks,
Ad
|||Ad,
I didn't think it would make much difference. However, where you get the
biggest bang for your buck, is ensuring that the FT Catalog is on a separate
disk drive from your database files. Can you stop the Full Population, drop
and re-create the FT Catalog on separate disk array or better yet, on a
separate disk controller? See SQL 2000 BOL title "Full-text Search
Recommendations" for more info on this.
Regards,
John
SQL Full Text Search Blog
http://spaces.msn.com/members/jtkane/
"Ad" <Ad@.discussions.microsoft.com> wrote in message
news:B72E38AF-4FFF-4F60-9F84-BF56213AC220@.microsoft.com...
> sp_fulltext_service 'resource_usage', 5 , did not make any difference,
does
> it need stop and re-start of MSSearch service?
> Thanks,
> Ad
>
>
|||The FT Catalog is on a dedicated SAN disk which is almost idle, in fact the
whole disk is cached. So there are plenty of resources on the server that FT
can't utilize, this leads me to believe FT has scalability issues.
Regards,
Ad
"John Kane" wrote:
> Ad,
> I didn't think it would make much difference. However, where you get the
> biggest bang for your buck, is ensuring that the FT Catalog is on a separate
> disk drive from your database files. Can you stop the Full Population, drop
> and re-create the FT Catalog on separate disk array or better yet, on a
> separate disk controller? See SQL 2000 BOL title "Full-text Search
> Recommendations" for more info on this.
> Regards,
> John
> --
> SQL Full Text Search Blog
> http://spaces.msn.com/members/jtkane/
>
> "Ad" <Ad@.discussions.microsoft.com> wrote in message
> news:B72E38AF-4FFF-4F60-9F84-BF56213AC220@.microsoft.com...
> does
>
>
we've recently upgraded from SQL sp2 to sp3a, this caused a catalog rebuild
but full population is taking forever. The column with Full text search
enabled is of image type, there are 2.5 million record in the table, about
7GB in size.
I've started full population 2 days ago and it's still showing "Population
in progress", when I view the property page of the catalog, I can see ~
2300000 items, catalog size: 190MB, unique key count: 362586. It appears to
be processing 1000 items per minute.
The server has 8 CPUs, 8GB RAM with /PAE and /3GB enabled, heavy duty SAN
disks.
mssdmn.exe is the most active process, it's using between 30-40 CPU on one
processor only, disk utilization is minimal and plenty of physical memory
available for use. This process is using 6 threads, 10MB RAM, mssearch.exe is
using 50MB RAM and 16 threads.
Is there any way of speeding up population?
Thanks,
Ad
set sp_fulltext_service 'resource_usage', 5
"Ad" <Ad@.discussions.microsoft.com> wrote in message
news:B94E0F3A-7655-4D8A-8605-EA1BC43E6329@.microsoft.com...
> I've got SQL2000 Enterprise edition running on windows 2000 advanced
> server,
> we've recently upgraded from SQL sp2 to sp3a, this caused a catalog
> rebuild
> but full population is taking forever. The column with Full text search
> enabled is of image type, there are 2.5 million record in the table, about
> 7GB in size.
> I've started full population 2 days ago and it's still showing "Population
> in progress", when I view the property page of the catalog, I can see ~
> 2300000 items, catalog size: 190MB, unique key count: 362586. It appears
> to
> be processing 1000 items per minute.
> The server has 8 CPUs, 8GB RAM with /PAE and /3GB enabled, heavy duty SAN
> disks.
> mssdmn.exe is the most active process, it's using between 30-40 CPU on one
> processor only, disk utilization is minimal and plenty of physical memory
> available for use. This process is using 6 threads, 10MB RAM, mssearch.exe
> is
> using 50MB RAM and 16 threads.
> Is there any way of speeding up population?
> Thanks,
> Ad
>
|||Ad,
There are a couple of thing you can do to improve the FT Indexing
performance without stopping & re-locating the FT Catalog. You can set the
MSSearch service's to use one CPU and then use sp_configure to set the
affinity of SQL Server to that cpu to avoid cpu contention during heavy
MSSearch CPU usage via the following method - launch the Tskmgr.exe from the
AT command on the multi-proc server where sql server resides, as follows:
at <current_time+1min> /interactive taskmgr.exe
when it launches, you can then set "cpu affinity" for the MSSearch service
to a cpu or set of cpu's not being used by SQL Server. Then you would use
sp_configure to set SQL Server's cpu affinity to the other cpu's, thus
preventing the cpu usage of mssearch from affecting your sql server
processing. Note, this only works on multi-processor servers and is a
secured method for achieving this feature.
Additionally, as your 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.
Finally, I'd highly recommend that you review all the resources "SQL Server
2000 Full-Text Search Resources and Links" at:
http://spaces.msn.com/members/jtkane/Blog/cns!1pWDBCiDX1uvH5ATJmNCVLPQ!305.entry
Regards,
John
SQL Full Text Search Blog
http://spaces.msn.com/members/jtkane/
"Ad" <Ad@.discussions.microsoft.com> wrote in message
news:B94E0F3A-7655-4D8A-8605-EA1BC43E6329@.microsoft.com...
> I've got SQL2000 Enterprise edition running on windows 2000 advanced
server,
> we've recently upgraded from SQL sp2 to sp3a, this caused a catalog
rebuild
> but full population is taking forever. The column with Full text search
> enabled is of image type, there are 2.5 million record in the table, about
> 7GB in size.
> I've started full population 2 days ago and it's still showing
"Population
> in progress", when I view the property page of the catalog, I can see ~
> 2300000 items, catalog size: 190MB, unique key count: 362586. It appears
to
> be processing 1000 items per minute.
> The server has 8 CPUs, 8GB RAM with /PAE and /3GB enabled, heavy duty SAN
> disks.
> mssdmn.exe is the most active process, it's using between 30-40 CPU on one
> processor only, disk utilization is minimal and plenty of physical memory
> available for use. This process is using 6 threads, 10MB RAM, mssearch.exe
is
> using 50MB RAM and 16 threads.
> Is there any way of speeding up population?
> Thanks,
> Ad
>
|||sp_fulltext_service 'resource_usage', 5 , did not make any difference, does
it need stop and re-start of MSSearch service?
Thanks,
Ad
|||Ad,
I didn't think it would make much difference. However, where you get the
biggest bang for your buck, is ensuring that the FT Catalog is on a separate
disk drive from your database files. Can you stop the Full Population, drop
and re-create the FT Catalog on separate disk array or better yet, on a
separate disk controller? See SQL 2000 BOL title "Full-text Search
Recommendations" for more info on this.
Regards,
John
SQL Full Text Search Blog
http://spaces.msn.com/members/jtkane/
"Ad" <Ad@.discussions.microsoft.com> wrote in message
news:B72E38AF-4FFF-4F60-9F84-BF56213AC220@.microsoft.com...
> sp_fulltext_service 'resource_usage', 5 , did not make any difference,
does
> it need stop and re-start of MSSearch service?
> Thanks,
> Ad
>
>
|||The FT Catalog is on a dedicated SAN disk which is almost idle, in fact the
whole disk is cached. So there are plenty of resources on the server that FT
can't utilize, this leads me to believe FT has scalability issues.
Regards,
Ad
"John Kane" wrote:
> Ad,
> I didn't think it would make much difference. However, where you get the
> biggest bang for your buck, is ensuring that the FT Catalog is on a separate
> disk drive from your database files. Can you stop the Full Population, drop
> and re-create the FT Catalog on separate disk array or better yet, on a
> separate disk controller? See SQL 2000 BOL title "Full-text Search
> Recommendations" for more info on this.
> Regards,
> John
> --
> SQL Full Text Search Blog
> http://spaces.msn.com/members/jtkane/
>
> "Ad" <Ad@.discussions.microsoft.com> wrote in message
> news:B72E38AF-4FFF-4F60-9F84-BF56213AC220@.microsoft.com...
> does
>
>
2012年2月19日星期日
fts- contains expression: slow the first time fast the other time
I have a table with 1,500,000 name and first name
I use Full text search on name and first name
when I use a contains expression like
select ... from table1 where contains( name,'abc') the query take 45 secs
immediatly after this test I search for the same or another name and the
query take under 1 secs ...
an hour later app.. I run the same test and the first query take 45 secs again
I try a test with search 1,000,000 different name ( for validate if the
problem was the caching )... the first on take 45 secs app and the 999,999
others case take under 1 secs
on my local computer all search run under 1 secs
Peter Yang from microsoft private news groups think that is a memory
(cache) problem... !!! but I think 45 secs is too high for a single unique
key search in link with a cache problem
could you help me ?
Which microsoft private newsgroup would that be. You should dedicate a
minimum of 1 Gig to your OS, so cap your max sql memory at physical - 1 Gig.
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
"OLAPFOREVER" <OLAPFOREVER@.discussions.microsoft.com> wrote in message
news:D0557CFF-B262-441F-9ADD-7E0E82DE8283@.microsoft.com...
>I have a table with 1,500,000 name and first name
> I use Full text search on name and first name
> when I use a contains expression like
> select ... from table1 where contains( name,'abc') the query take 45
> secs
> immediatly after this test I search for the same or another name and the
> query take under 1 secs ...
> an hour later app.. I run the same test and the first query take 45 secs
> again
>
> I try a test with search 1,000,000 different name ( for validate if the
> problem was the caching )... the first on take 45 secs app and the
> 999,999
> others case take under 1 secs
> on my local computer all search run under 1 secs
> Peter Yang from microsoft private news groups think that is a memory
> (cache) problem... !!! but I think 45 secs is too high for a single
> unique
> key search in link with a cache problem
> could you help me ?
>
I use Full text search on name and first name
when I use a contains expression like
select ... from table1 where contains( name,'abc') the query take 45 secs
immediatly after this test I search for the same or another name and the
query take under 1 secs ...
an hour later app.. I run the same test and the first query take 45 secs again
I try a test with search 1,000,000 different name ( for validate if the
problem was the caching )... the first on take 45 secs app and the 999,999
others case take under 1 secs
on my local computer all search run under 1 secs
Peter Yang from microsoft private news groups think that is a memory
(cache) problem... !!! but I think 45 secs is too high for a single unique
key search in link with a cache problem
could you help me ?
Which microsoft private newsgroup would that be. You should dedicate a
minimum of 1 Gig to your OS, so cap your max sql memory at physical - 1 Gig.
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
"OLAPFOREVER" <OLAPFOREVER@.discussions.microsoft.com> wrote in message
news:D0557CFF-B262-441F-9ADD-7E0E82DE8283@.microsoft.com...
>I have a table with 1,500,000 name and first name
> I use Full text search on name and first name
> when I use a contains expression like
> select ... from table1 where contains( name,'abc') the query take 45
> secs
> immediatly after this test I search for the same or another name and the
> query take under 1 secs ...
> an hour later app.. I run the same test and the first query take 45 secs
> again
>
> I try a test with search 1,000,000 different name ( for validate if the
> problem was the caching )... the first on take 45 secs app and the
> 999,999
> others case take under 1 secs
> on my local computer all search run under 1 secs
> Peter Yang from microsoft private news groups think that is a memory
> (cache) problem... !!! but I think 45 secs is too high for a single
> unique
> key search in link with a cache problem
> could you help me ?
>
订阅：
博文 (Atom)