Full Text Search resultset count

For performance reasons I am limiting the resultset to 100 rows. However, I would like to have the performance but need the total count of rows found. How can I keep the performance gain from limiting to top 100 and still get the total count.

Could I do something like this:

select top 100 count(id), field1, field2, field3 from ....

Would this return 100 in the count field?

Thanks

I think T-SQL does not support above sematic. field1, field2, ... will need to be part of group by or aggregate function.

Back to you problem, it seems like you want to know total hit count and want it performs well. The problem is that to get total hit count it requires to get the whole result set which is major part of the cost. By using top 100 really will not save you much. If you can remove the requirement to get total hit count, you can then use containstable with a top_n parameter, it will save you a lot for full-text search part.

thanks,

jingwei

Full Text search performance tuning.

Hi Everyone,
I have a performance issue with a SQL Server query and i need some
assistance with it. It would be great if you could offer your inputs on
it.
We have a SQL Server database that is of a rather large size [20GB +].
The most significant table "Order_Detail" has a large number of rows [a
few million rows]. We need a query that will retrieve a keyword search
result in the shortest span of time from this table. The resultset we
need is dependent on a few other tables. The query we use has joins on
these tables. These other tables are also of similar sizes.
The problem is that the query takes too long a time to execute to be
practical.Could anyone take a look at the query and let me know how to
optimise it best ?
FTSI is not implemented on the Order_Details table.
The tables - Order, Order_Detail, Order_Tracker are indexed on the
"order_id" column.
-- SQL query --
SELECT o.order_id as "Order ID", o.date AS 'Received date',
od.po_amount as "Amount", c.EmailAdress AS 'Email_address', ot.msg as
'Email Text'
FROM Order o, Order_Details od, Order_Tracker ot, Customer c
WHERE od.order_ID = o.order_ID
AND od.customerID = c.customerID
AND od.poID = ot.poID
AND o.date >= ** X date **
AND o.date <= ** Y date **
AND ot.Email=c.Email
AND ot.Msg like '%<Keyword to be searched for>%'
ORDER BY o.order_ID, o.date, o.customerID
-- End of query --
*Note->The query is modified to give you a sense of what i am trying to
do. It is not the exact same query i use here.
We tried to use FTSI and "CONTAINS" to search for the keyword. It gives
better performance but only marginally. What are your thoughts on using
FTSI when the query will end up using complex joins and large volumes
of data ? If this approach will solve our performance issue, can
someone direct me on how best to do it ?
Our only constraints are that the database structure cannot be
modified. However the SQL query or the approach can be.
Thank you for your inputs in advance!
Regards,
Venky
The short answer is that other than full-text indexing an indexed view in
sql 2005 or partitioning by date there is no easy way to increase your
performance. I suspect you may also have a lazy spool. Could you post the
execution plan here so we can evaluate it.
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
<rv.venky@.gmail.com> wrote in message
news:1159890834.003818.162910@.m73g2000cwd.googlegr oups.com...
> Hi Everyone,
> I have a performance issue with a SQL Server query and i need some
> assistance with it. It would be great if you could offer your inputs on
> it.
> We have a SQL Server database that is of a rather large size [20GB +].
> The most significant table "Order_Detail" has a large number of rows [a
> few million rows]. We need a query that will retrieve a keyword search
> result in the shortest span of time from this table. The resultset we
> need is dependent on a few other tables. The query we use has joins on
> these tables. These other tables are also of similar sizes.
> The problem is that the query takes too long a time to execute to be
> practical.Could anyone take a look at the query and let me know how to
> optimise it best ?
> FTSI is not implemented on the Order_Details table.
> The tables - Order, Order_Detail, Order_Tracker are indexed on the
> "order_id" column.
> -- SQL query --
> SELECT o.order_id as "Order ID", o.date AS 'Received date',
> od.po_amount as "Amount", c.EmailAdress AS 'Email_address', ot.msg as
> 'Email Text'
> FROM Order o, Order_Details od, Order_Tracker ot, Customer c
> WHERE od.order_ID = o.order_ID
> AND od.customerID = c.customerID
> AND od.poID = ot.poID
> AND o.date >= ** X date **
> AND o.date <= ** Y date **
> AND ot.Email=c.Email
> AND ot.Msg like '%<Keyword to be searched for>%'
> ORDER BY o.order_ID, o.date, o.customerID
> -- End of query --
> *Note->The query is modified to give you a sense of what i am trying to
> do. It is not the exact same query i use here.
>
> We tried to use FTSI and "CONTAINS" to search for the keyword. It gives
> better performance but only marginally. What are your thoughts on using
> FTSI when the query will end up using complex joins and large volumes
> of data ? If this approach will solve our performance issue, can
> someone direct me on how best to do it ?
> Our only constraints are that the database structure cannot be
> modified. However the SQL query or the approach can be.
> Thank you for your inputs in advance!
> Regards,
> Venky
>
|||Hello Venky
Please post the scripts for your tables. This is essential to work out what
needs to be indexed.
Why are you not joining Order_tracker to Order by OrderId?
Simon Sabin
SQL Server MVP
http://sqlblogcasts.com/blogs/simons

> Hi Everyone,
> I have a performance issue with a SQL Server query and i need some
> assistance with it. It would be great if you could offer your inputs
> on
> it.
> We have a SQL Server database that is of a rather large size [20GB +].
> The most significant table "Order_Detail" has a large number of rows
> [a few million rows]. We need a query that will retrieve a keyword
> search result in the shortest span of time from this table. The
> resultset we need is dependent on a few other tables. The query we use
> has joins on these tables. These other tables are also of similar
> sizes.
> The problem is that the query takes too long a time to execute to be
> practical.Could anyone take a look at the query and let me know how to
> optimise it best ?
> FTSI is not implemented on the Order_Details table.
> The tables - Order, Order_Detail, Order_Tracker are indexed on the
> "order_id" column.
> -- SQL query --
> SELECT o.order_id as "Order ID", o.date AS 'Received date',
> od.po_amount as "Amount", c.EmailAdress AS 'Email_address', ot.msg as
> 'Email Text'
> FROM Order o, Order_Details od, Order_Tracker ot, Customer c
> WHERE od.order_ID = o.order_ID
> AND od.customerID = c.customerID
> AND od.poID = ot.poID
> AND o.date >= ** X date **
> AND o.date <= ** Y date **
> AND ot.Email=c.Email
> AND ot.Msg like '%<Keyword to be searched for>%'
> ORDER BY o.order_ID, o.date, o.customerID
> -- End of query --
> *Note->The query is modified to give you a sense of what i am trying
> to do. It is not the exact same query i use here.
> We tried to use FTSI and "CONTAINS" to search for the keyword. It
> gives
> better performance but only marginally. What are your thoughts on
> using
> FTSI when the query will end up using complex joins and large volumes
> of data ? If this approach will solve our performance issue, can
> someone direct me on how best to do it ?
> Our only constraints are that the database structure cannot be
> modified. However the SQL query or the approach can be.
> Thank you for your inputs in advance!
> Regards,
> Venky

Full text search performance problem

Hi,
We are using Full Text Search on SQL 2000. The search performance is
fine.
Our problem is that when a record is added to the table, the average
time it takes before we can find it with a CONTAINS is 20 seconds.
This is with only one user connected.
The tables has only 200000 rows. The field is a VARCHAR of less than
200 characters.
I don't know the exact spec of the server but its a 2CPU, 4GB RAM with
fast disks. If the exact spec is important I can find out.
Thanks for your help.
Sylvain
Sylvain,
Are you using "Change Tracking" with "Update Index in Background" and
concerned that once a record (row) is added that it takes 20 seconds before
a SELECT * from FT-enable_table where CONTAINS(*,'search_word') will return
the record (row) where search_word exists?
If not, is your concern more about the total time it takes to return a
results using the above FTS query? If so, you may want to use CONTAINSTABLE
and the Top_N_Rank parameter, for example:
use Northwind
go
SELECT e.EmployeeID, e.LastName, ct.[RANK]
FROM Employees AS e
JOIN CONTAINSTABLE(Employees, Notes, 'French',10) AS ct ON e.EmployeeID
= ct.[KEY]
ORDER BY ct.[RANK] DESC
Note, the value 10 in the above query as this represents the Top N (10) by
Rank value. Using this parameter on containstable or freetexttable can
improve the FTS query performance, but you should experiment with the value
as if you set it too low, you can miss some results, see KB article 240833
(Q240833) "FIX: Full-Text Search Performance Improved via Support for TOP"
at
http://support.microsoft.com//defaul...b;EN-US;240833 for more
info on this.
You should also review SQL Server 2000 BOL title "Full-text Search
Recommendations" as well.
Regards,
John
"Sylvain Hamel" <redhotsly@.hotmail.com> wrote in message
news:4716a77b.0408311303.531320fa@.posting.google.c om...
> Hi,
> We are using Full Text Search on SQL 2000. The search performance is
> fine.
> Our problem is that when a record is added to the table, the average
> time it takes before we can find it with a CONTAINS is 20 seconds.
> This is with only one user connected.
> The tables has only 200000 rows. The field is a VARCHAR of less than
> 200 characters.
> I don't know the exact spec of the server but its a 2CPU, 4GB RAM with
> fast disks. If the exact spec is important I can find out.
> Thanks for your help.
> Sylvain
|||John,

> Are you using "Change Tracking" with "Update Index in Background" and
Yes.

> concerned that once a record (row) is added that it takes 20 seconds before
> a SELECT * from FT-enable_table where CONTAINS(*,'search_word') will return
> the record (row) where search_word exists?
This is exactly my problem. Within the first 20 seconds, the SELECT return
zero rows. After, it works fine and it's fast.
Regards,
Sylvain
|||Hi Sylvain,
I suspected as much... I've emailed Microsoft (the current SQL FTS Dev Lead)
on this issue along with a repro (see the attached SQL script file) sometime
ago and have pinged him since with no reply <sigh>... While I'm sure he's
busy, I had hoped to get a better response on this issue from Microsoft...
Basically, SQL Server 2000 should push modified rows to the un-documented
system table sysfulltextnotify and then to the MSSearch controlled FT
Catalog for updating. I've been told that this push should happen within 1
second, but can lag depending upon other activity on the server as well as
within SQL Server, but 20 seconds is much longer than my repro
demonstrates...
The attached SQL script file demonstrates that even with inserting one row
can take up to 5 to 7 seconds with no other activity on the server and no
other activity within SQL Server. I had hoped to get this addressed and
hopefully filed as a bug, but that did not happen and may not happen as the
time to post the updated row to the MSSearch FT Catalog has never been
documented and so no guaranteed posting time was ever documented...
If you want I can pursue this again with the FTS Dev Lead, if this is
affecting your production server's FT Indexing and FT Search performance and
therefore leading to dissatisfaction for your users. Let me know if you want
me to pursue this.
Thanks,
John
"Sylvain" <Sylvain@.discussions.microsoft.com> wrote in message
news:5D244B5C-C00B-41E4-B343-9F9CA416D441@.microsoft.com...[vbcol=seagreen]
> John,
>
> Yes.
before[vbcol=seagreen]
return
> This is exactly my problem. Within the first 20 seconds, the SELECT return
> zero rows. After, it works fine and it's fast.
> Regards,
> Sylvain
>
begin 666 FTS_with_Change_Tracking_pooling.sql
M#0HM+0E&:6QE;F%M93H)1E137W=I=&A?0VAA;F=E7U1R86-K:6YG7W!O;VQI
M;F<N<W%L#0HM+0E0=7)P;W-E.@.E4;R!T97-T($9422!A;F0@.1E13('=I=&@.@.
M0VAA;F=E('1R86-K:6YG(&%N9"!U<&1A=&4@.:6YD97@.@.:6X@.8F%C:V=R;W5N
M9"!A;F0@.<WES9G5L;'1E>'1N;W1I9GD@.<&]O;&EN9PT*+2T)16YV:7)O;65N
M=#H)57-I;F<@.4U%,(#(P,# @.4U S(&]N(%=I;F1O=W,@.4V5R=F5R(#(P,#,@.
M4E1-#0HM+0E-;V1I9FEE9#H),3$Z,S @.04T@.,R\Q,2\R,# T#0H-"@.T*#0IU
M<V4@.<'5B<PT*9V\-"G-E;&5C="! 0'9E<G-I;VX@.+2T@.36EC<F]S;V9T(%-1
M3"!397)V97(@.(#(P,# @.+2 X+C P+C<V," H4U S*2!A;F0@.."XP,"XU,S0@.
M*%-0,BD-"F=O#0II9B!E>&ES=',@.*'-E;&5C=" J(&9R;VT@.<WES;V)J96-T
M<R!W:&5R92!I9" ](&]B:F5C=%]I9"@.G1E135&%B;&4G*2D-"B @.9')O<"!T
M86)L92!&5%-486)L90T*9V\-"D-214%412!404),12!&5%-486)L92 H#0H@.
M($ME>4-O; EI;G0@.241%3E1)5%D@.*#$L,2D@.3D]4($Y53$P@.#0H@.(" @.0T].
M4U1204E.5"!&5%-486)L95])1%@.@.4%))34%262!+15D@.0TQ54U1%4D5$+ T*
M("!497AT0V]L"71E>'0@.3E5,3"P-"B @.26UA9V5#;VP):6UA9V4@.3E5,3"P-
M"B @.17AT0V]L"6-H87(H,RD@.3E5,3"P@.+2T@.8V%N(&)E('-Y<VYA;64-"B @.
M5&EM95-T86UP0V]L"71I;65S=&%M<"!.54Q,( T**0T*9V\-"@.T*+2T@.5')U
M;F-A=&4@.1&%T82P@.:68@.;F5C97-S87)Y+BX-"BTM(%1254Y#051%(%1!0DQ%
M($944U1A8FQE#0I);G-E<G0@.1E135&%B;&4@.=F%L=65S("@.G36%R>2!H860@.
M82!L:71T;&4@.;&%M8B<L(')E<&QI8V%T92@.G,'@.Q)RPQ,# I+" G='AT)RP@.
M3E5,3"D-"DEN<V5R="!&5%-486)L92!V86QU97,@.*"=->2!D;V<@.8F%R:V5D
M(&QO=61L>2<L(')E<&QI8V%T92@.G,'@.W)RPS,# I+" G='AT)RP@.3E5,3"D@.
M#0I);G-E<G0@.1E135&%B;&4@.=F%L=65S("@.G5&AE(&9O>"!J=6UP960@.; W9E
M<B!T:&4@.9F5N8V4G+"!R97!L:6-A=&4H)S!X."<L.# P*2P@.)W1X="<L($Y5
M3$PI#0I);G-E<G0@.1E135&%B;&4@.=F%L=65S("@.G22!D;W9E(&EN=&\@.=&AE
M('!O;F0G+"!R97!L:6-A=&4H)S!X,B<L,3 P*2P@.)W1X="<L($Y53$PI#0H-
M"@.T*+2T@.1G5L;"U497AT($EN9&5X:6YG("A&5$DI('-E='5P('5S:6YG(")#
M:&%N9V4@.5')A8VMI;F<B(&%N9" B57!D871E($EN9&5X(&EN($)A8VMG<F]U
M;F0B+BXN#0IE>&5C('-P7V9U;&QT97AT7V-A=&%L;V<@.)T944T-A=&%L;V<G
M+"=C<F5A=&4G#0IE>&5C('-P7V9U;&QT97AT7W1A8FQE("=&5%-486)L92<L
M)V-R96%T92<L)T944T-A=&%L;V<G+"=&5%-486)L95])1%@.G#0IE>&5C('-P
M7V9U;&QT97AT7V-O;'5M;B G1E135&%B;&4G+"=497AT0V]L)RPG861D)R M
M+2!A<W-U;65S($5N9VQI<V@.@.;&%N9W5A9V4@.=&5X= T*97AE8R!S<%]F=6QL
M=&5X=%]C;VQU;6X@.)T944U1A8FQE)RPG26UA9V5#;VPG+"=A9&0G+" P># T
M,#DL("=%>'1#;VPG(" -"F=O#0H-"BTM(%-T87)T($94($EN9&5X:6YG+BXN
M(&%N9"!C;VYF:7)M('9I82 B36EC<F]S;V9T(%-E87)C:"(@.<V]U<F-E(&5N
M=')I97,@.:6X@.=&AE('-E<G9E<B=S($%P<&QI8V%T:6]N($5V96YT(&QO9RXN
M+@.T*15A%0R!S<%]F=6QL=&5X=%]T86)L92 G1E135&%B;&4G+" G4W1A<G1?
M8VAA;F=E7W1R86-K:6YG)PT*+RH@.+2T@.<F5T=7)N<R H;VX@.4U%,(#(P,# @.
M4U S*0T*5V%R;FEN9SH@.5&%B;&4@.)T944U1A8FQE)R!D;V5S(&YO= "!H879E
M('1H92!O<'1I;VX@.)W1E>'0@.:6X@.<F]W)R!E;F%B;&5D(&%N9"!H87,@.9G5L
M;"UT97AT(&EN9&5X960@.8V]L=6UN<R -"G1H870@.87)E(&]F('1Y<&4@.:6UA
M9V4L('1E>'0L(&]R(&YT97AT+B!&=6QL+71E>'0@.8VAA;F=E('1R86-K:6YG
M(&-A;FYO="!T<F%C:R!74DE4151%6%0@.;W(@.55!$051%5$585"!O< &5R871I
M;VYS( T*<&5R9F]R;65D(&]N('1H97-E(&-O;'5M;G,N#0HJ+PT*15A%0R!S
M<%]F=6QL=&5X=%]T86)L92 G1E135&%B;&4G+" G4W1A<G1?8F%C:V=R;W5N
M9%]U<&1A=&5I;F1E>"<-"F=O#0H-"@.T*+2T@.16YV:7)O;65N=#H@.4U%,(#(P
M,# @.4U R(&]N(%=I;C)+("8@.4U%,(#(P,# @.4U S(&]N(%=I;C(P,#,-"@.T*
M+2T@.4F5P;R!I;G-T<G5C=&EO;G,Z#0HM+2 Q+B!#:&%N9V4@.=&AE(%=!251&
M3U(@.1$5,05D@.)S P.C P.C!8)R!8('9A;'5E+"!S=&%R=&EN9R!A=" Q('-E
M8V]N9"!A;F0@.:6YC<F5M96YT:6YG('5N=&EL('1H92!S96QE8W0@.< F5T=7)N
M<R Q(')O=RX-"BTM(#(N($AI9VAL:6=H="!T:&4@.8F5L;W<@.8V]D92!A;F0@.
M97AE8W5T92X-"@.T*+2T@.26YS97)T(&%D9&ET:6]N86P@.86YD('5N:7%U92!W
M;W)D('=I=&@.@.0U0@.86YD(%5):4(@.96YA8FQE9" M($1E;&5T92P@.26YS97)T
M(&%N9"!I;F-R96%S92!W86ET9F]R(&1E;&%Y('5N=&EL('-E;&5C=" Q(')O
M=PT*1$5,151%($944U1A8FQE('=H97)E(%1E>'1#;VP@.;&EK9 2 G)71E>'0E
M)R M+2!D96QE=&4@.:6YS97)T960@.<F]W+"!I9B!N96-E<W-A<GD-"F=O#0I)
M3E-%4E0@.1E135&%B;&4@.=F%L=65S*"=497-T(%1%6%0@.1&%T82!F;W(@.<F]W
M(#4G+"!.54Q,+" G9&]C)RP@.3E5,3"D@.#0IG;PT*5T%)5$9/4B!$14Q!62 G
M,# Z,# Z,#4G("TM('=A:70@.9F]R('@.@.<V5C;VYD<R!A;F0@.=&AE;B!R=6X@.
M4U%,($944R!Q=65R>2!T;R!G970@.97-T:6UA=&4@.;V8@.<F5T=7)N('1I;64-
M"G-E;&5C="!497AT0V]L(&9R;VT@.1E135&%B;&4@.=VAE<F4@.8V]N=&%I;G,H
M5&5X=$-O;"PG=&5S="<I#0HO*B M+2!W:71H(%=!251&3U(@.1$5,05D@.<V5T
M('1O(#4@.<V5C;VYD<SH-"B@.Q(')O=RAS*2!A9F9E8W1E9"D-"B@.Q(')O=RAS
M*2!A9F9E8W1E9"D-"@.T*5&5X=$-O;" @.(" @.(" @.(" @.(" @.(" @.(" @.(" @.
M(" @.(" @.(" @.(" @.(" @.(" @.(" @.#0HM+2TM+2TM+2TM+2TM+2TM+2TM+2TM
M+2TM+2TM+2TM+2TM+2TM+2TM+2TM+2TM+2TM+2 -"E1E<W0@.5$585"!$871A
M(&9O<B!R;W<@.-0T**#$@.<F]W*',I(&%F9F5C=&5D*0T**B\-"@.T*+2T@.4W5M
M;6%R>3H@.37D@.=&5S=&EN9R!I;F1I8V%T97,@.=&AA="!O;FQY( &%F=&5R(&$@.
M-2!T;R V('-E8V]N9',@.=V%I=&9O<B!D96QA>2!W:6QL('1H92!S96QE8W0@.
M<F5T=7)N(&$@.:&ET(&%N9"!I9B!Y;W4@.:6YC<F5A<V4-"BTM(" @.(" @.(" @.
M('1H92!W86ET9F]R(&1E;&%Y('1O(#<@.<V5C;VYD<RP@.:70@.<W1I;&P@.;6%Y
M(&YO="!C;VYS:7-T96YT;'D@.<F5T=7)N(#$@.<F]W+B!!9&1I=&EO;F%L;'DL
M('-O;65T:6UE(&5V96X@.:68@.>6]U(&1O('5S90T*+2T)(" @.(#4@.<V5C;VYD
M<RP@.86YD(&EF('EO=2!R92UR=6X@.=&AE(&AI9VAL:6=H=&5D( '%U97)Y+"!Y
M;W4@.;6%Y('-T:6QL(&YO="!G970@.82!R971U<FX@.;V8@.,2!R;W<N#0H-"@.T*
M#0H-"B\J("TM($-L96%N('5P.B!5;BUC;VUM96YT('1H92!F;VQL;W=I;F<@.
M8V]D92!T;R!R96UO=F4@.=&AE($94($-A=&%L;V<L('1A8FQE<PT*=7-E(%!U
M8G,-"F=O#0IE>&5C('-P7V9U;&QT97AT7W1A8FQE("=&5%-486)L92<L)V1R
M;W G#0IE>&5C('-P7V9U;&QT97AT7T-A=&%L;V<@.)T944T-A=&%L;V<G+"=D
C<F]P)PT*9')O<"!T86)L92!&5%-486)L90T*9V\-"BHO#0H`
`
end
|||John, we have opened a call with microsoft regarding this issue. I'll let you
know. Thank you for your help.
Sylvain
"John Kane" wrote:

> Hi Sylvain,
> I suspected as much... I've emailed Microsoft (the current SQL FTS Dev Lead)
> on this issue along with a repro (see the attached SQL script file) sometime
> ago and have pinged him since with no reply <sigh>... While I'm sure he's
> busy, I had hoped to get a better response on this issue from Microsoft...
> Basically, SQL Server 2000 should push modified rows to the un-documented
> system table sysfulltextnotify and then to the MSSearch controlled FT
> Catalog for updating. I've been told that this push should happen within 1
> second, but can lag depending upon other activity on the server as well as
> within SQL Server, but 20 seconds is much longer than my repro
> demonstrates...
> The attached SQL script file demonstrates that even with inserting one row
> can take up to 5 to 7 seconds with no other activity on the server and no
> other activity within SQL Server. I had hoped to get this addressed and
> hopefully filed as a bug, but that did not happen and may not happen as the
> time to post the updated row to the MSSearch FT Catalog has never been
> documented and so no guaranteed posting time was ever documented...
> If you want I can pursue this again with the FTS Dev Lead, if this is
> affecting your production server's FT Indexing and FT Search performance and
> therefore leading to dissatisfaction for your users. Let me know if you want
> me to pursue this.
> Thanks,
> John
>
>
> "Sylvain" <Sylvain@.discussions.microsoft.com> wrote in message
> news:5D244B5C-C00B-41E4-B343-9F9CA416D441@.microsoft.com...
> before
> return
>
>
|||You're welcome, Sylvain,
Yes, please do let me know what you find out from Microsoft!
Thanks,
John
"Sylvain" <Sylvain@.discussions.microsoft.com> wrote in message
news:8B6465AF-A649-4014-A066-44C146A81375@.microsoft.com...
> John, we have opened a call with microsoft regarding this issue. I'll let
you[vbcol=seagreen]
> know. Thank you for your help.
> Sylvain
> "John Kane" wrote:
Lead)[vbcol=seagreen]
sometime[vbcol=seagreen]
he's[vbcol=seagreen]
Microsoft...[vbcol=seagreen]
un-documented[vbcol=seagreen]
1[vbcol=seagreen]
as[vbcol=seagreen]
row[vbcol=seagreen]
no[vbcol=seagreen]
the[vbcol=seagreen]
and[vbcol=seagreen]
want[vbcol=seagreen]
and[vbcol=seagreen]
return[vbcol=seagreen]

Full Text Search Performance

Hello,
I have full text search enabled on two large tables, the first one has 3
million records (FT index column length is around 512-1025 char), and the
other one has around 1.6 records (FT index column length is around 50-120
char)
The simplest query that uses the smiplest form of FT on the first table FT
index search needs 23-30 seconds to complete and from 10-15 seconds on the
seconds table as it the indexed column is smaller.
SQL server is deployed on 4 P servers with 8 GB of memory and attached to a
very powerful SAN system. Memory usage on the server is not very high so I
believe there is enough memory for the mssearch service to use.
I just wonder if this is the normal throughput of the Full Text search of
SQL Server. If not, I will appreciate any tips and hints that might be the
reason of the system.
Ali Salem
That depends. SQL FTS performance is most sensitive to the number of rows
you are returning. You should limit your results set as much as possible - I
think you will find that the practical limit for most applications is around
100-200 rows.
Limit it by using the the top_n_by_rank operator in ContainsTable or
FreeTextTable, ie
USE Northwind
GO
SELECT FT_TBL.Description,
FT_TBL.CategoryName,
KEY_TBL.RANK
FROM Categories AS FT_TBL INNER JOIN
CONTAINSTABLE (Categories, Description,
'test',200
) AS KEY_TBL
ON FT_TBL.CategoryID = KEY_TBL.[KEY]
ORDER BY KEY_TBL.RANK DESC
"Ali Salem" <AliSalem@.discussions.microsoft.com> wrote in message
news:CFE9CB8C-7AE4-44C3-8A3C-BC397585AC1E@.microsoft.com...
> Hello,
> I have full text search enabled on two large tables, the first one has 3
> million records (FT index column length is around 512-1025 char), and the
> other one has around 1.6 records (FT index column length is around 50-120
> char)
> The simplest query that uses the smiplest form of FT on the first table FT
> index search needs 23-30 seconds to complete and from 10-15 seconds on the
> seconds table as it the indexed column is smaller.
> SQL server is deployed on 4 P servers with 8 GB of memory and attached to
a
> very powerful SAN system. Memory usage on the server is not very high so I
> believe there is enough memory for the mssearch service to use.
> I just wonder if this is the normal throughput of the Full Text search of
> SQL Server. If not, I will appreciate any tips and hints that might be the
> reason of the system.
> --
> Ali Salem
|||thanks for your reply.
I cannot limit the search in this way, as there are other parameters in my
query that can affect the returned result set and that are ourside the FTS.
I am asking for any figures about FTS performance? What should I expect from
it? Is this behavior am getting normal for the data size or, I am having
something wrong
"Hilary Cotter" wrote:

> That depends. SQL FTS performance is most sensitive to the number of rows
> you are returning. You should limit your results set as much as possible - I
> think you will find that the practical limit for most applications is around
> 100-200 rows.
> Limit it by using the the top_n_by_rank operator in ContainsTable or
> FreeTextTable, ie
> USE Northwind
> GO
> SELECT FT_TBL.Description,
> FT_TBL.CategoryName,
> KEY_TBL.RANK
> FROM Categories AS FT_TBL INNER JOIN
> CONTAINSTABLE (Categories, Description,
> 'test',200
> ) AS KEY_TBL
> ON FT_TBL.CategoryID = KEY_TBL.[KEY]
> ORDER BY KEY_TBL.RANK DESC
> "Ali Salem" <AliSalem@.discussions.microsoft.com> wrote in message
> news:CFE9CB8C-7AE4-44C3-8A3C-BC397585AC1E@.microsoft.com...
> a
>
>
|||No, this is highly abnormal. Performance should be sub second. However I
need to know what your queries look like, ie how many search arguments, how
many rows are returned, and what language you are querying in.
"Ali Salem" <AliSalem@.discussions.microsoft.com> wrote in message
news:AECBD305-A9FD-4759-B240-B4D970B32BEA@.microsoft.com...
> thanks for your reply.
> I cannot limit the search in this way, as there are other parameters in my
> query that can affect the returned result set and that are ourside the
FTS.
> I am asking for any figures about FTS performance? What should I expect
from[vbcol=seagreen]
> it? Is this behavior am getting normal for the data size or, I am having
> something wrong
> "Hilary Cotter" wrote:
rows[vbcol=seagreen]
possible - I[vbcol=seagreen]
around[vbcol=seagreen]
3[vbcol=seagreen]
the[vbcol=seagreen]
50-120[vbcol=seagreen]
table FT[vbcol=seagreen]
the[vbcol=seagreen]
to[vbcol=seagreen]
so I[vbcol=seagreen]
of[vbcol=seagreen]
the[vbcol=seagreen]
|||Ali Salem,
Could you provide the full output of the following SQL script as it is very
helpful in troubleshooting SQL FTS issue and understanding your environment
and issues!
use <your_database_name_here>
go
SELECT @.@.language
SELECT @.@.version
sp_configure 'default full-text language'
EXEC sp_help_fulltext_catalogs
EXEC sp_help_fulltext_tables
EXEC sp_help_fulltext_columns
EXEC sp_help <your_FT-enable_table_name_here>
SELECT FULLTEXTSERVICEPROPERTY('ResourceUsage')
go
Additionally, where is your FT Catalog located as by default it is created
under the \FTDATA directory where you have SQL Server installed as well as
where your FT-enabled database files (*.mdf, *.ndf, * *.ldf) are located
relative to the location of your FT Catalog folder? If your 'ResourceUsage
is set to 3, you should increase it to 5 (dedicated) via sp_fulltext_service
'resource_usage' <value>, where <value> is 5.
Thanks,
John
"Ali Salem" <AliSalem@.discussions.microsoft.com> wrote in message
news:CFE9CB8C-7AE4-44C3-8A3C-BC397585AC1E@.microsoft.com...
> Hello,
> I have full text search enabled on two large tables, the first one has 3
> million records (FT index column length is around 512-1025 char), and the
> other one has around 1.6 records (FT index column length is around 50-120
> char)
> The simplest query that uses the smiplest form of FT on the first table FT
> index search needs 23-30 seconds to complete and from 10-15 seconds on the
> seconds table as it the indexed column is smaller.
> SQL server is deployed on 4 P servers with 8 GB of memory and attached to
a
> very powerful SAN system. Memory usage on the server is not very high so I
> believe there is enough memory for the mssearch service to use.
> I just wonder if this is the normal throughput of the Full Text search of
> SQL Server. If not, I will appreciate any tips and hints that might be the
> reason of the system.
> --
> Ali Salem
|||Thanks.
- We are using enlgihs language.
- Returned number of rows should be very large (thousands, in some cases it
can be 100, 000). this from the Full-Text Seach, other SQL filters will be
applied as well, but from mssearch this is what will be returned. Please note
that the table size is around 3million records.
- The smiplest query such as SELECT * FROM TABLE1 WHERE CONTAINS(COL1,
'ABCD') need around 30 seconds complete.
I do appreciate any help.
thank you
"Hilary Cotter" wrote:

> No, this is highly abnormal. Performance should be sub second. However I
> need to know what your queries look like, ie how many search arguments, how
> many rows are returned, and what language you are querying in.
>
> "Ali Salem" <AliSalem@.discussions.microsoft.com> wrote in message
> news:AECBD305-A9FD-4759-B240-B4D970B32BEA@.microsoft.com...
> FTS.
> from
> rows
> possible - I
> around
> 3
> the
> 50-120
> table FT
> the
> to
> so I
> of
> the
>
>
|||Below are the result of the script you requested me to run it:
My FT files are stored on SAN storage. So IO should be performing well.
Regarding the ResourceUsage, it is 3. Will not raising it to 5 harm the sql
server performance! I dont want to make the full text search fast by slowing
down SQL Server itself.
I have splitted the result into two posts so that I can post it here.
Thank you for you help
------
us_english
(1 row(s) affected)
------
------
Microsoft SQL Server 2000 - 8.00.534 (Intel X86)
Nov 19 2001 13:23:50
Copyright (c) 1988-2000 Microsoft Corporation
Enterprise Edition on Windows NT 5.0 (Build 2195: Service Pack 3)
(1 row(s) affected)
name minimum maximum config_value
run_value
-- -- -- --
default full-text language 0 2147483647 1033 1033
ftcatid NAME
PATH
STATUS NUMBER_FULLTEXT_TABLES
------
------
------
-- -- --
5 ICSubject
L:\Microsoft SQL Server\MSSQL\FTDATA
0 1
6 Subject
L:\Microsoft SQL Server\MSSQL\FTDATA
0 1
7 Names
L:\Microsoft SQL Server\MSSQL\FTDATA
0 1
(3 row(s) affected)
TABLE_OWNER
TABLE_NAME
FULLTEXT_KEY_INDEX_NAME
FULLTEXT_KEY_COLID FULLTEXT_INDEX_ACTIVE FULLTEXT_CATALOG_NAME
------
------
------
-- -- --
----
dbo
IC_SUBJECTS
PK_IC_SUBJECTS
1 1 ICSubject
dbo
MAIN
PK_INOUT_MAIN
1 1 Subject
dbo
PERSONS
PK_PERSONS
1 1 Names
(3 row(s) affected)
TABLE_OWNER
TABLE_ID TABLE_NAME
FULLTEXT_COLUMN_NAME
FULLTEXT_COLID FULLTEXT_BLOBTP_COLNAME
FULLTEXT_BLOBTP_COLID
FULLTEXT_LANGUAGE
------
-- --
-----
-----
-- --
--- --
dbo
2069582411 IC_SUBJECTS
ITEM_DESCRIPTION_AR
3 NULL
NULL 0
dbo
274100017 MAIN
SUBJECT
42 NULL
NULL 0
dbo
466100701 PERSONS
FIRSTNAME_AR
4 NULL
NULL 0
dbo
466100701 PERSONS
LASTNAME_AR
8 NULL
NULL 0
dbo
466100701 PERSONS
FULL_NAME
9 NULL
NULL 0
dbo
466100701 PERSONS
FATHERNAME_AR
12 NULL
NULL 0
dbo
466100701 PERSONS
GRANDFATHERNAME_AR
14 NULL
NULL 0
(7 row(s) affected)
Name
Owner
Type Created_datetime
------
------
-- --
MAIN
dbo
user table 2004-10-14 18:14:31.457
|||The Rest of the script result, one more post is required
Column_name
Type
Computed Length Prec Scale Nullable
TrimTrailingBlanks
FixedLenNullInSource Collation
------
------
-- -- -- -- --
-- --
-- --
DOCID
int
no 4 10 0 no
(n/a)
(n/a) NULL
DESCRIPTION
varchar
no 255 yes
no
no Arabic_CI_AS
DOCDATE
datetime
no 8 yes
(n/a)
(n/a) NULL
RDOCDATE
datetime
no 8 yes
(n/a)
(n/a) NULL
EXT_PARTY_ID
int
no 4 10 0 yes
(n/a)
(n/a) NULL
EXT_PARTY_REP_ID
int
no 4 10 0 yes
(n/a)
(n/a) NULL
SUBCAT_ID
decimal
no 9 18 0 yes
(n/a)
(n/a) NULL
STATUS_ID
int
no 4 10 0 yes
(n/a)
(n/a) NULL
CONFID_ID
int
no 4 10 0 yes
(n/a)
(n/a) NULL
REFERENCE_NO
varchar
no 50 yes
no
no Arabic_CI_AS
FORWARD_TO
decimal
no 9 18 0 yes
(n/a)
(n/a) NULL
FORWARD_DATE
datetime
no 8 yes
(n/a)
(n/a) NULL
TO_REMIND
decimal
no 9 18 0 yes
(n/a)
(n/a) NULL
REMIND_DATE
datetime
no 8 yes
(n/a)
(n/a) NULL
REMARKS
varchar
no 1024 yes
no
no Arabic_CI_AS
REVISION_NO
varchar
no 50 yes
no
no Arabic_CI_AS
TRAN_DATE
datetime
no 8 yes
(n/a)
(n/a) NULL
USER_ID
varchar
no 32 yes
no
no Arabic_CI_AS
SYSTEM_ID
varchar
no 50 yes
no
no Arabic_CI_AS
IP
varchar
no 50 yes
no
no Arabic_CI_AS
CHECK_SUM
decimal
no 9 18 0 yes
(n/a)
(n/a) NULL
SUBMIT_TYPE
int
no 4 10 0 yes
(n/a)
(n/a) NULL
SUBJECT_TYPE
int
no 4 10 0 yes
(n/a)
(n/a) NULL
SUBJECT
nvarchar
no 8000 yes
(n/a)
(n/a) Arabic_CI_AS
PRIORITY_NO
int
no 4 10 0 yes
(n/a)
(n/a) NULL
PROCESS_ID
int
no 4 10 0 yes
(n/a)
(n/a) NULL
REMIND_TIME
varchar
no 20 yes
no
no Arabic_CI_AS
WAITING_FOR_REPLY
int
no 4 10 0 yes
(n/a)
(n/a) NULL
SITE_ID
int
no 4 10 0 yes
(n/a)
(n/a) NULL
DELIVERY_METHOD_ID
int
no 4 10 0 yes
(n/a)
(n/a) NULL
GROUPID
int
no 4 10 0 yes
(n/a)
(n/a) NULL
LINK_STATUS
int
no 4 10 0 yes
(n/a)
(n/a) NULL
INITIAL_PROCEDURE
int
no 4 10 0 yes
(n/a)
(n/a) NULL
CREATOR_USER_ID
varchar
no 32 yes
no
no Arabic_CI_AS
IS_SPLITTED
bit
no 1 yes
(n/a)
(n/a) NULL
CHAR_FIELD2
varchar
no 500 yes
no
no Arabic_CI_AS
CHAR_FIELD5_AR
nvarchar
no 100 yes
(n/a)
(n/a) Arabic_CI_AS
NUM_FIELD1
decimal
no 9 18 0 yes
(n/a)
(n/a) NULL
CHAR_FIELD66_AR
nvarchar
no 2000 yes
(n/a)
(n/a) Arabic_CI_AS
NUM_FIELD6
decimal
no 9 18 0 yes
(n/a)
(n/a) NULL
FLOAT_FIELD1
int
no 4 10 0 yes
(n/a)
(n/a) NULL
FLOAT_FIELD2
int
no 4 10 0 yes
(n/a)
(n/a) NULL
NUM_FIELD2
decimal
no 9 18 0 yes
(n/a)
(n/a) NULL
NUM_FIELD3
decimal
no 9 18 0 yes
(n/a)
(n/a) NULL
NUM_FIELD4
decimal
no 9 18 0 yes
(n/a)
(n/a) NULL
NUM_FIELD5
decimal
no 9 18 0 yes
(n/a)
(n/a) NULL
DATE_FIELD1
datetime
no 8 yes
(n/a)
(n/a) NULL
DATE_FIELD2
datetime
no 8 yes
(n/a)
(n/a) NULL
CHAR_FIELD1
char
no 50 yes
no
yes Arabic_CI_AS
CHAR_FIELD3
char
no 50 yes
no
yes Arabic_CI_AS
CHAR_FIELD4
char
no 50 yes
no
yes Arabic_CI_AS
CHAR_FIELD5
char
no 50 yes
no
yes Arabic_CI_AS
CHAR_FIELD1_AR
nvarchar
no 510 yes
(n/a)
(n/a) Arabic_CI_AS
CHAR_FIELD2_AR
nvarchar
no 510 yes
(n/a)
(n/a) Arabic_CI_AS
CHAR_FIELD4_AR
nvarchar
no 510 yes
(n/a)
(n/a) Arabic_CI_AS
Identity
Seed Increment
Not For Replication
------
-- ---
--- --
No identity column defined.
NULL NULL
NULL
RowGuidCol
------
No rowguidcol column defined.
Data_located_on_filegroup
------
SECONDARY2
|||Last post of the script result.
index_name
index_description
index_keys
------
------
----- --
------
IX_Index1
nonclustered located on SECONDARY2
DOCID, STATUS_ID, CABID, YEAR,
CONFID_ID, DELIVERY_PLACE, PRIORITY_NO,
IX_Index2
nonclustered located on SECONDARY4
INOUTDOCNO, CABID, DOC_ORIGN_SOURCE,
YEAR, DOCDATE, OUTBOUND_REQUEST
IX_Index3
nonclustered located on SECONDARY2
GROUPID
IX_Index4
nonclustered located on SECONDARY4
YEAR, EXT_DOCNO, RDOCDATE_HJ,
EXT_PARTY_ID, DIWAN_REP_ID, EXT_PARTY_REP_ID
IX_Index5
nonclustered located on SECONDARY2
REQUEST_NO
IX_Index6
nonclustered located on SECONDARY4
STATUS_ID, CABID
PK_MAIN
clustered, unique, primary key located on SECONDARY2
DOCNO
constraint_type
constraint_name
delete_action update_action status_enabled
status_for_replication constraint_keys
------
-- -- -- --
-- --
------
-----
DEFAULT on column OUTBOUND_REQUEST
DF_MAIN_OUTBOUND_REQUEST
(n/a) (n/a) (n/a) (n/a)
(0)
FOREIGN KEY
FK_MAIN_EMPLOYEES
No Action No Action Enabled
Not_For_Replication USER_ID
REFERENCES dbo.EMPLOYEES (EMP_NO)
FOREIGN KEY
FK_MAIN_EMPLOYEES1
No Action No Action Enabled
Not_For_Replication WRITER_ID
REFERENCES dbo.EMPLOYEES (EMP_NO)
FOREIGN KEY
FK_MAIN_EMPLOYEES2
No Action No Action Enabled
Not_For_Replication CREATOR_USER_ID
REFERENCES dbo.EMPLOYEES (EMP_NO)
FOREIGN KEY
FK_MAIN_LO_CONFIDENTIAL
No Action No Action Enabled
Not_For_Replication CONFID_ID
REFERENCES dbo.LO_CONFIDENTIAL (
TAB_ID)
FOREIGN KEY
FK_MAIN_LO_DOC_SOURCE_TYPE
No Action No Action Enabled
Not_For_Replication DOC_ORIGN_SOURCE
REFERENCES dbo.LO_DOC_SOURCE_TYPE
(TAB_ID)
FOREIGN KEY
FK_MAIN_LO_LOOKUPS
No Action No Action Enabled
Not_For_Replication DELIVERY_METHOD_ID
REFERENCES dbo.LO_LOOKUPS (TAB_ID)
FOREIGN KEY
FK_MAIN_LO_LOOKUPS1
No Action No Action Enabled
Not_For_Replication LINK_STATUS
REFERENCES dbo.LO_LOOKUPS (TAB_ID)
FOREIGN KEY
FK_MAIN_LO_LOOKUPS4
No Action No Action Enabled
Not_For_Replication RECEIVE_METHOD_ID
REFERENCES dbo.LO_LOOKUPS (TAB_ID)
FOREIGN KEY
FK_MAIN_LO_PRIORITY
No Action No Action Enabled
Not_For_Replication PRIORITY_NO
REFERENCES dbo.LO_PRIORITY (TAB_ID)
FOREIGN KEY
FK_MAIN_LO_SELECTION
No Action No Action Enabled
Not_For_Replication EXT_PARTY_ID
REFERENCES dbo.LO_SELECTION (
TAB_ID)
FOREIGN KEY
FK_MAIN_LO_SELECTION1
No Action No Action Enabled
Not_For_Replication EXT_PARTY_REP_ID
REFERENCES dbo.LO_SELECTION (
TAB_ID)
FOREIGN KEY
FK_MAIN_LO_SELECTION2
No Action No Action Enabled
Not_For_Replication CITY_ID
REFERENCES dbo.LO_SELECTION (
TAB_ID)
FOREIGN KEY
FK_MAIN_LO_SIGNATURE_TYPE
No Action No Action Enabled
Not_For_Replication DIWAN_REP_ID
REFERENCES dbo.LO_SIGNATURE_TYPE (
TAB_ID)
FOREIGN KEY
FK_MAIN_LO_SITE
No Action No Action Enabled
Not_For_Replication DELIVERY_PLACE
REFERENCES dbo.LO_SITE (SITE_ID)
FOREIGN KEY
FK_MAIN_LO_STATUS
No Action No Action Enabled
Not_For_Replication STATUS_ID
REFERENCES dbo.LO_STATUS (TAB_ID)
FOREIGN KEY
FK_MAIN_MAIN_GROUP_HDR
No Action No Action Enabled
Not_For_Replication GROUPID
REFERENCES dbo.MAIN_GROUP_HDR (
GROUPID)
FOREIGN KEY
FK_MAIN_PROCESSES
No Action No Action Enabled
Not_For_Replication INITIAL_PROCEDURE
REFERENCES dbo.PROCESSES (
PROCESS_ID)
PRIMARY KEY (clustered)
PK_MAIN
(n/a) (n/a) (n/a) (n/a)
DOCNO
Table is referenced by foreign key
------
------
dbo.TASKS: FK_TASKS_MAIN
dbo.ASKST_CHAIN: FK_TASKS_CHAIN_MAIN
dbo.ATTACHMENTS: FK_ATTACHMENTS_MAIN
dbo.ATTACHMENTS: FK_ATTACHMENTS_MAIN1
dbo.ATTACHMENTS_MOV: FK_ATTACH_MOV_MAIN
dbo.COPY: FK_COPY_MAIN
dbo.INQUIRY: FK_INQUIRY_MAIN
dbo.LINKS_F: FK_LINKS_F_MAIN
dbo.CLASS: FK_CLASS_MAIN
dbo.XLASS: FK_CLASS_MAIN
dbo.REQUEST: FK_REQUEST_MAIN
dbo.REQUEST_DTL: FK_REQUEST_DTL_MAIN
dbo.MAIN_FOLLOW: FK_MAIN_FOLLOW_MAIN
dbo.MAIN_GROUP_DTL: FK_MAIN_GROUP_DTL_MAIN
dbo.MAIN_TRACK: FK_MAIN_TRACK_MAIN
dbo.PERSONS: FK_PERSONS_MAIN
dbo.RESERVED: FK_RESERVED_MAIN
dbo.SAVE_TRACK: FK_SAVE_TRACK_MAIN
Table is referenced by views
------
3
(1 row(s) affected)
|||Ali,
First of all, thank you for providing this info as it is most helpful! You
are using SQL Server 2000 SP2 on Windows 2000 Server SP3 and your default
language is US_English (1033). However, all of your char, varchar and
nvarchar columns using the Arabic_CI_AS collation with the FT-enabled column
"Language for Word Breaker" set to Neutral. Can I assume that these column
contain Arabic text?
Yes, I can understand why you want the Resource_Usage level to remain at 3,
but you might try setting to 5, as you can always set it lower if it is
affecting your other SQL Server processing. Although, this bump in the
MSSearch resource usage might not be significant. Note, it affect how much
memory MSSearch can use up to a max if 512MB RAM, if available as well as
the number of concurrent connections allowed.
What is the drive letter for your SAN storage? Could you also run the
following SQL query and post it's results?
sp_helpdb <full_text_enabled_database_name>
I'm not sure if the use of Arabic text is causing the poor query performance
(even with the Neutral wordbreaker), but language is a consideration with
FTS query performance issues.
Thanks again,
John
"Ali Salem" <AliSalem@.discussions.microsoft.com> wrote in message
news:D20604B8-21E5-496F-A957-1F1CF8949716@.microsoft.com...
> Last post of the script result.
>
>
> index_name
>
> index_description
>
> index_keys
>
>
>
> ----
----
> --
> ----

> ----
-- --
> ----
----
> ----
--
> IX_Index1
>
> nonclustered located on SECONDARY2
>
> DOCID, STATUS_ID, CABID, YEAR,
> CONFID_ID, DELIVERY_PLACE, PRIORITY_NO,
> IX_Index2
>
> nonclustered located on SECONDARY4
>
> INOUTDOCNO, CABID, DOC_ORIGN_SOURCE,
> YEAR, DOCDATE, OUTBOUND_REQUEST
> IX_Index3
>
> nonclustered located on SECONDARY2
>
> GROUPID
> IX_Index4
>
> nonclustered located on SECONDARY4
>
> YEAR, EXT_DOCNO, RDOCDATE_HJ,
> EXT_PARTY_ID, DIWAN_REP_ID, EXT_PARTY_REP_ID
> IX_Index5
>
> nonclustered located on SECONDARY2
>
> REQUEST_NO
> IX_Index6
>
> nonclustered located on SECONDARY4
>
> STATUS_ID, CABID
> PK_MAIN
>
> clustered, unique, primary key located on SECONDARY2
>
> DOCNO
>
> constraint_type
>
> constraint_name
>
> delete_action update_action status_enabled
> status_for_replication constraint_keys
>
>
>
> ----
----
> --
> ----
--
> -- -- -- --
> -- --
> ----
----
> ----
--
> DEFAULT on column OUTBOUND_REQUEST
>
> DF_MAIN_OUTBOUND_REQUEST
>
> (n/a) (n/a) (n/a) (n/a)
> (0)
> FOREIGN KEY
>
> FK_MAIN_EMPLOYEES
>
> No Action No Action Enabled
> Not_For_Replication USER_ID
>
>
>
> REFERENCES dbo.EMPLOYEES (EMP_NO)
> FOREIGN KEY
>
> FK_MAIN_EMPLOYEES1
>
> No Action No Action Enabled
> Not_For_Replication WRITER_ID
>
>
>
> REFERENCES dbo.EMPLOYEES (EMP_NO)
> FOREIGN KEY
>
> FK_MAIN_EMPLOYEES2
>
> No Action No Action Enabled
> Not_For_Replication CREATOR_USER_ID
>
>
>
> REFERENCES dbo.EMPLOYEES (EMP_NO)
> FOREIGN KEY
>
> FK_MAIN_LO_CONFIDENTIAL
>
> No Action No Action Enabled
> Not_For_Replication CONFID_ID
>
>
>
> REFERENCES dbo.LO_CONFIDENTIAL (
> TAB_ID)
> FOREIGN KEY
>
> FK_MAIN_LO_DOC_SOURCE_TYPE
>
> No Action No Action Enabled
> Not_For_Replication DOC_ORIGN_SOURCE
>
>
>
> REFERENCES dbo.LO_DOC_SOURCE_TYPE
> (TAB_ID)
> FOREIGN KEY
>
> FK_MAIN_LO_LOOKUPS
>
> No Action No Action Enabled
> Not_For_Replication DELIVERY_METHOD_ID
>
>
>
> REFERENCES dbo.LO_LOOKUPS (TAB_ID)
> FOREIGN KEY
>
> FK_MAIN_LO_LOOKUPS1
>
> No Action No Action Enabled
> Not_For_Replication LINK_STATUS
>
>
>
> REFERENCES dbo.LO_LOOKUPS (TAB_ID)
> FOREIGN KEY
>
> FK_MAIN_LO_LOOKUPS4
>
> No Action No Action Enabled
> Not_For_Replication RECEIVE_METHOD_ID
>
>
>
> REFERENCES dbo.LO_LOOKUPS (TAB_ID)
> FOREIGN KEY
>
> FK_MAIN_LO_PRIORITY
>
> No Action No Action Enabled
> Not_For_Replication PRIORITY_NO
>
>
>
> REFERENCES dbo.LO_PRIORITY (TAB_ID)
> FOREIGN KEY
>
> FK_MAIN_LO_SELECTION
>
> No Action No Action Enabled
> Not_For_Replication EXT_PARTY_ID
>
>
>
> REFERENCES dbo.LO_SELECTION (
> TAB_ID)
> FOREIGN KEY
>
> FK_MAIN_LO_SELECTION1
>
> No Action No Action Enabled
> Not_For_Replication EXT_PARTY_REP_ID
>
>
>
> REFERENCES dbo.LO_SELECTION (
> TAB_ID)
> FOREIGN KEY
>
> FK_MAIN_LO_SELECTION2
>
> No Action No Action Enabled
> Not_For_Replication CITY_ID
>
>
>
> REFERENCES dbo.LO_SELECTION (
> TAB_ID)
> FOREIGN KEY
>
> FK_MAIN_LO_SIGNATURE_TYPE
>
> No Action No Action Enabled
> Not_For_Replication DIWAN_REP_ID
>
>
>
> REFERENCES dbo.LO_SIGNATURE_TYPE (
> TAB_ID)
> FOREIGN KEY
>
> FK_MAIN_LO_SITE
>
> No Action No Action Enabled
> Not_For_Replication DELIVERY_PLACE
>
>
>
> REFERENCES dbo.LO_SITE (SITE_ID)
> FOREIGN KEY
>
> FK_MAIN_LO_STATUS
>
> No Action No Action Enabled
> Not_For_Replication STATUS_ID
>
>
>
> REFERENCES dbo.LO_STATUS (TAB_ID)
> FOREIGN KEY
>
> FK_MAIN_MAIN_GROUP_HDR
>
> No Action No Action Enabled
> Not_For_Replication GROUPID
>
>
>
> REFERENCES dbo.MAIN_GROUP_HDR (
> GROUPID)
> FOREIGN KEY
>
> FK_MAIN_PROCESSES
>
> No Action No Action Enabled
> Not_For_Replication INITIAL_PROCEDURE
>
>
>
> REFERENCES dbo.PROCESSES (
> PROCESS_ID)
> PRIMARY KEY (clustered)
>
> PK_MAIN
>
> (n/a) (n/a) (n/a) (n/a)
> DOCNO
>
> Table is referenced by foreign key
>
>
>
> ----
----
> ----
----
> --
> dbo.TASKS: FK_TASKS_MAIN
> dbo.ASKST_CHAIN: FK_TASKS_CHAIN_MAIN
> dbo.ATTACHMENTS: FK_ATTACHMENTS_MAIN
> dbo.ATTACHMENTS: FK_ATTACHMENTS_MAIN1
> dbo.ATTACHMENTS_MOV: FK_ATTACH_MOV_MAIN
> dbo.COPY: FK_COPY_MAIN
> dbo.INQUIRY: FK_INQUIRY_MAIN
> dbo.LINKS_F: FK_LINKS_F_MAIN
> dbo.CLASS: FK_CLASS_MAIN
> dbo.XLASS: FK_CLASS_MAIN
> dbo.REQUEST: FK_REQUEST_MAIN
> dbo.REQUEST_DTL: FK_REQUEST_DTL_MAIN
> dbo.MAIN_FOLLOW: FK_MAIN_FOLLOW_MAIN
> dbo.MAIN_GROUP_DTL: FK_MAIN_GROUP_DTL_MAIN
> dbo.MAIN_TRACK: FK_MAIN_TRACK_MAIN
> dbo.PERSONS: FK_PERSONS_MAIN
> dbo.RESERVED: FK_RESERVED_MAIN
> dbo.SAVE_TRACK: FK_SAVE_TRACK_MAIN
> Table is referenced by views
>
> ----
----
> --
>
> --
> 3
> (1 row(s) affected)
>
sql

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.

FTS Performance in SQL 2005

I do not see any resolution to this problem mentioned, and have a similar
problem.
I have just implemented a database with 12 tables, one FT index on a text
column for each table. The unique key column is a uniqueidentifier.
This is under SQL Server 2005, SP1, Windows 2003 Server, on an x64 dual
processor system with 16 GB RAM. SQL Server is limited to 12 GB RAM and
nothing else runs on the box.
The query uses CONTAINSTABLE.
We have an automated process that feeds thousands of queries, one at a time,
to run FT searches.
It appears that the searches run fine for a while, then the searches take
longer and longer, until eventually the search never returns, for hours
anyway.
When we see this happening, in Windows Task Manager we see that process
msftesql shows PF Delta up over 50,000.
The Memory Usage and VM Size never increase over about 65 MB and 20MB.
Did you ever find a solution for this ?
Thanks.
Doug Funk
News Data Services
dfunk@.newsdataservice.com
"Simon Sabin" <SimonSabin@.noemail.noemail> wrote in message
news:c4366deffa728c87fe6f490db50@.msnews.microsoft. com...
> Hello KaMa,
> The maximum equates to process ~4.5GB/s thats a lot.
> Can you post you query plans and the output of statistics IO
> Simon Sabin
> SQL Server MVP
> http://sqlblogcasts.com/blogs/simons
>
>
Hi Hilary,
Your comment here is a bit scary. It sounds like the FTS capabilities
of Sql Server 2005 are not ready for production. Can you detail a bit
more the problems you encounter that force you to restart the sql fts
once a week? Is MS aware of that problem? What are their
recommendations? Do you know of any upcoming patch or SP that would fix
this?
Tony.
Hilary Cotter wrote:[vbcol=seagreen]
> We pound full-text search the same way you do. There are advantages to a
> multi-proc machine - a quad or eight way. We have to restart sql fts once a
> week. We find that smaller tables work better - where smaller is 50 million
> or so rows.
> We also found the following settings work well:
> setting a high resource usage to 5 and reorganize frequently.
> set ft crawl bandwidth (max) and ft notify bandwidth (max) to 0,
> set max full-text crawl range to the number of cpu's on your system,
> index text only,
> put your catalogs on the fastest disk subsystem (RAID 10) possible
> preferrably with their own controller,
> and run 64 bit.
>
> --
> 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
>
> "Doug Funk" <doug.funk@.infomax-systems.com> wrote in message
> news:lAN_g.60102$OI1.44332@.newsfe15.lga...
|||Basically we find that the queries start taking longer and a bounce seems to
improve performance.
I have not communicated this to MS. You might want to open a support
incident yourself.
When we had a single table of over 300 million rows and pushing 2 terabytes
we had no end of problems with SQL FTS. After breaking the table up into 50
million row partitions we have had no real problems, but still bounce fts
weekly.
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
<tony.newsgrps@.gmail.com> wrote in message
news:1162399142.153739.316510@.e64g2000cwd.googlegr oups.com...
> Hi Hilary,
> Your comment here is a bit scary. It sounds like the FTS capabilities
> of Sql Server 2005 are not ready for production. Can you detail a bit
> more the problems you encounter that force you to restart the sql fts
> once a week? Is MS aware of that problem? What are their
> recommendations? Do you know of any upcoming patch or SP that would fix
> this?
> Tony.
>
> Hilary Cotter wrote:
>
|||Thank you for your answer. We'll look at partitioning our table if we
get into similar problems.
One quick follow up:
We keep on growing our table and the performance keeps on dropping.
With 8 million rows we had about 15 queries/sec. With 20 million rows,
we dropped to about 2 queries per sec. The server seems completely
underused though. The CPU and memory usage are very low and we see a
lot of page faults. Any idea what happened and how we could get back to
15 queries/sec? Could it be that the index needs to be re-organized or
something like that?
Our table is very simple (2 fields: 1 id, 1 plain text) and our queries
match only a very limited set of documents (100 matching records max
out of 20 millions).
Also, with your 300 millions/2TB table, what performance do you get on
queries (on average) and on what hardware?
Thanks a lot for your guidance.
Tony.
Hilary Cotter wrote:[vbcol=seagreen]
> Basically we find that the queries start taking longer and a bounce seems to
> improve performance.
> I have not communicated this to MS. You might want to open a support
> incident yourself.
> When we had a single table of over 300 million rows and pushing 2 terabytes
> we had no end of problems with SQL FTS. After breaking the table up into 50
> million row partitions we have had no real problems, but still bounce fts
> weekly.
> --
> 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
>
> <tony.newsgrps@.gmail.com> wrote in message
> news:1162399142.153739.316510@.e64g2000cwd.googlegr oups.com...
|||can you do this for me and post the results back here
sp_configure 'max server memory (MB)'
I don't think you have left enough memory for the OS and MSSearch.
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
<tony.newsgrps@.gmail.com> wrote in message
news:1162417354.892553.92200@.k70g2000cwa.googlegro ups.com...
> Thank you for your answer. We'll look at partitioning our table if we
> get into similar problems.
> One quick follow up:
> We keep on growing our table and the performance keeps on dropping.
> With 8 million rows we had about 15 queries/sec. With 20 million rows,
> we dropped to about 2 queries per sec. The server seems completely
> underused though. The CPU and memory usage are very low and we see a
> lot of page faults. Any idea what happened and how we could get back to
> 15 queries/sec? Could it be that the index needs to be re-organized or
> something like that?
> Our table is very simple (2 fields: 1 id, 1 plain text) and our queries
> match only a very limited set of documents (100 matching records max
> out of 20 millions).
> Also, with your 300 millions/2TB table, what performance do you get on
> queries (on average) and on what hardware?
> Thanks a lot for your guidance.
> Tony.
> Hilary Cotter wrote:
>
|||Hi Hilary,
thanks for the reply.
We have total 4gb ram on the server.
Currently SQLServer has max server memory (MB) set to 2048. I tried giving
it less (1024) but didn't notice any performance differences.
Any ideas?
When i allow SQLServer to use more memory (up to 3gb) i notice performance
downgrades with time probably because one SQLServer reaches its 3gb theres
not much left for OS/full-text engine.
What seems really strange is that full-text engine itself only uses about
7mb ram and has approx 100k page faults / sec.
Thanks,
Mikhail
"Hilary Cotter" wrote:
[vbcol=seagreen]
> can you do this for me and post the results back here
> sp_configure 'max server memory (MB)'
> I don't think you have left enough memory for the OS and MSSearch.
> --
> 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
>
> <tony.newsgrps@.gmail.com> wrote in message
> news:1162417354.892553.92200@.k70g2000cwa.googlegro ups.com...

FTS Performance

WE have sql server 2000 with a table around 10 text field and one full-text field. over 1million rows.

How can we realize a 5 seconds or less full text query?
resuts should be order by a date field.

Note: full text field is in Chinese.

if in solution of asp: by what sentence.
if in solution of isapi, by what dev tool?nobody?

FTS Index Build on SQL 2000 and performance questions

I was under the impression that among the improvements in FTS under SQL 2000
was faster population of an index.
I started a full text index on a table Monday evening at around 6:00PM.
30 hours later it's at an item count 391678. (actually that's about where
it was 12 hours ago as I recall.)
In any case, this is as slow, if not SLOWER than the SQL 7.0 box we were
running this on.
And this box has better CPUs and presumably better disks (not sure about the
latter.)
There's nothing in the logs that appear to show anything stuck or hung.
We're looking to move our application to SQL 2000, but if it takes as long
to build indices on SQL 2000 as it does on SQL 7.0, we'll have to look at a
3rd party package.
At this it really looks like nothing's happening.
This copy of the DB was restored from our production server. Would that
make a difference?
--
Greg D. Moore
President Green Mountain Software
Personal: http://stratton.greenms.com
Greg,
Yes, that is true. Although it was not as significant as SQL 2000 to SQL
2005 will be. Primarily, the improvements from SQL Sever 7.0 FTS to SQL
Server 2000 FTS were made in the areas of additional reliability and added
features.
For a 400K item count, it should of completed well before 12 hours. Could
you check your server's Application event log for any "Microsoft Search" and
especially MssCi (review the details for stack traces) source events -
errors, warnings AND informational and post anything that seems out of the
ordinary. If nothing is obvious, then it is possible that the true log entry
may of rolled off your server's application event log, if so, then clear the
log and stop the FT Population if it is in progress, and re-run a Full
Population.
Yes, depending upon how the DB was restored (from a backup or via Copy DB
Wizard, DTS, etc.) as well as any differences in hardware configurations
(especially a difference in the drive letters on where the FT Catalogs
resided) can affect and cause problems.
Regards,
John
"Greg D. Moore (Strider)" <mooregr_deleteth1s@.greenms.com> wrote in message
news:VXgkd.95690$l07.9241@.twister.nyroc.rr.com...
> I was under the impression that among the improvements in FTS under SQL
2000
> was faster population of an index.
> I started a full text index on a table Monday evening at around 6:00PM.
> 30 hours later it's at an item count 391678. (actually that's about where
> it was 12 hours ago as I recall.)
> In any case, this is as slow, if not SLOWER than the SQL 7.0 box we were
> running this on.
> And this box has better CPUs and presumably better disks (not sure about
the
> latter.)
> There's nothing in the logs that appear to show anything stuck or hung.
> We're looking to move our application to SQL 2000, but if it takes as long
> to build indices on SQL 2000 as it does on SQL 7.0, we'll have to look at
a
> 3rd party package.
> At this it really looks like nothing's happening.
> This copy of the DB was restored from our production server. Would that
> make a difference?
>
> --
> --
> Greg D. Moore
> President Green Mountain Software
> Personal: http://stratton.greenms.com
>
|||"John Kane" <jt-kane@.comcast.net> wrote in message
news:uMTtEHuxEHA.1204@.TK2MSFTNGP10.phx.gbl...
> Greg,
> Yes, that is true. Although it was not as significant as SQL 2000 to SQL
> 2005 will be. Primarily, the improvements from SQL Sever 7.0 FTS to SQL
> Server 2000 FTS were made in the areas of additional reliability and added
> features.
Well, I can't wait until SQL 2005 unfortunately. :-)

> For a 400K item count, it should of completed well before 12 hours.
That's what I thought.

>Could
> you check your server's Application event log for any "Microsoft Search"
and
> especially MssCi (review the details for stack traces) source events -
> errors, warnings AND informational and post anything that seems out of the
> ordinary. If nothing is obvious, then it is possible that the true log
entry
> may of rolled off your server's application event log, if so, then clear
the
> log and stop the FT Population if it is in progress, and re-run a Full
> Population.
Nope, as I stated, there's nothing in there out of the ordinary. Only real
event was a master merge right after midnight last night.

> Yes, depending upon how the DB was restored (from a backup or via Copy DB
> Wizard, DTS, etc.) as well as any differences in hardware configurations
> (especially a difference in the drive letters on where the FT Catalogs
> resided) can affect and cause problems.
>
It was a restore from a backup. Does an update stats change things as far
as FT Index is concerned?
In any case, since my first post I gave up, cancelled the obviously failling
population and moved the index to a different drive on the server.
In 20 minutes it's already done 113K rows. So, this is FAR better than what
I was seeing with my first attempt. At this point if it continues like
this, I think performance will be adequate for our needs.
I'll keep you posted on this build.

> Regards,
> John
>
>
> "Greg D. Moore (Strider)" <mooregr_deleteth1s@.greenms.com> wrote in
message[vbcol=seagreen]
> news:VXgkd.95690$l07.9241@.twister.nyroc.rr.com...
> 2000
where[vbcol=seagreen]
> the
long[vbcol=seagreen]
at
> a
>
|||Greg,
Yea, we all have been waiting for Yukon, but alas it's been delayed now for
years...
Q. Does an update stats change things as far as FT Index is concerned?
A. No. update stats are db only and do not affect the FT Index as most
likely the FT Indexing process was "paused", and the posted app. log message
rolled off the event log.
Regards,
John
"Greg D. Moore (Strider)" <mooregr_deleteth1s@.greenms.com> wrote in message
news:Mshkd.1$qv5.0@.twister.nyroc.rr.com...[vbcol=seagreen]
> "John Kane" <jt-kane@.comcast.net> wrote in message
> news:uMTtEHuxEHA.1204@.TK2MSFTNGP10.phx.gbl...
added[vbcol=seagreen]
> Well, I can't wait until SQL 2005 unfortunately. :-)
>
> That's what I thought.
> and
the
> entry
> the
> Nope, as I stated, there's nothing in there out of the ordinary. Only
real[vbcol=seagreen]
> event was a master merge right after midnight last night.
>
DB
> It was a restore from a backup. Does an update stats change things as far
> as FT Index is concerned?
> In any case, since my first post I gave up, cancelled the obviously
failling
> population and moved the index to a different drive on the server.
> In 20 minutes it's already done 113K rows. So, this is FAR better than
what[vbcol=seagreen]
> I was seeing with my first attempt. At this point if it continues like
> this, I think performance will be adequate for our needs.
> I'll keep you posted on this build.
>
> message
SQL[vbcol=seagreen]
6:00PM.[vbcol=seagreen]
> where
were[vbcol=seagreen]
about[vbcol=seagreen]
hung.[vbcol=seagreen]
> long
> at
that
>
|||"John Kane" <jt-kane@.comcast.net> wrote in message
news:ue7E0muxEHA.2568@.TK2MSFTNGP10.phx.gbl...
> Greg,
> Yea, we all have been waiting for Yukon, but alas it's been delayed now
for
> years...
> Q. Does an update stats change things as far as FT Index is concerned?
> A. No. update stats are db only and do not affect the FT Index as most
> likely the FT Indexing process was "paused", and the posted app. log
message
> rolled off the event log.
John, I'll repeat myself again. NOTHING has rolled off the application log.
In any case, I'm not suspecting a DB issue since it started great (doing on
average 6000 rows/minute). In the first 1/2 hour it did over 180K rows.
That was at 12:20 AM.
Since then it appears stuck at 371952 and the CPU usage has dropped to
basically zero. (when it ran for the 1/2 hour I observed it, CPU usage was
about 100% across all 4 CPUs).
The PopulateStatus is still 1 and the size of the errorlog appears to be 103
bytes.
I'm starting to suspect it's the DB, not the FT, but just want to make sure
to see if there's anything else I should be looking at.
|||Greg,
Ok, is it possible that you could save the current Application event log as
a .evt file as well as all non-zero length *.gthr files located under
\FTDATA\SQLServer\GatherLogs and zip these files together and post it here?
If there has been no events roll-off, then an event should be there, and as
Albert Einstein (said?) "God [or the devil] is in the details" as it does
appear that this process is stuck (or paused) for some reason at 371,952,
which is very close to the number (391,678) that you originally posted.
The CPU usage is usually low, except when there are Shadow Merges and at the
end of the Full Population during the Master Merge. A PopulateStatus of 1
means that the "Full population in progress", so hopefully a MSSearch or
MssCi source event will be present to help explain this!
Thanks,
John
"Greg D. Moore (Strider)" <mooregr_deleteth1s@.greenms.com> wrote in message
news:dXmkd.223$qv5.36@.twister.nyroc.rr.com...
> "John Kane" <jt-kane@.comcast.net> wrote in message
> news:ue7E0muxEHA.2568@.TK2MSFTNGP10.phx.gbl...
> for
> message
> John, I'll repeat myself again. NOTHING has rolled off the application
log.
> In any case, I'm not suspecting a DB issue since it started great (doing
on
> average 6000 rows/minute). In the first 1/2 hour it did over 180K rows.
> That was at 12:20 AM.
> Since then it appears stuck at 371952 and the CPU usage has dropped to
> basically zero. (when it ran for the 1/2 hour I observed it, CPU usage
was
> about 100% across all 4 CPUs).
> The PopulateStatus is still 1 and the size of the errorlog appears to be
103
> bytes.
> I'm starting to suspect it's the DB, not the FT, but just want to make
sure
> to see if there's anything else I should be looking at.
>
|||"John Kane" <jt-kane@.comcast.net> wrote in message
news:eznFI00xEHA.1192@.tk2msftngp13.phx.gbl...
> Greg,
> Ok, is it possible that you could save the current Application event log
as
> a .evt file as well as all non-zero length *.gthr files located under
> \FTDATA\SQLServer\GatherLogs and zip these files together and post it
here?
> If there has been no events roll-off, then an event should be there, and
as
> Albert Einstein (said?) "God [or the devil] is in the details" as it does
> appear that this process is stuck (or paused) for some reason at 371,952,
> which is very close to the number (391,678) that you originally posted.
Trust me John, there was NOTHING in the Event log. :-) I can read an event
log.
I also checked the Gatherlogs and nothing was out of the ordinary.
HOWEVER... when I manually stop the population and checked the event log
THEN I got an error.
"The crawl seed <MSSQL75://SQLServer/04659998> in project <SQLServer
SQL0001000007> cannot be accessed. Error: 80040d19 - The filtering process
was stopped because its memory quota was exceeded. To increase the memory
quota of the filtering process, increase the value for the following
registry key: HKEY_LOCAL_MACHINE\Software\Microsoft\Search\1.0\G athering
Manager\FilterProcessMemoryQuota. .
There's not much in the KB about this, but
http://support.microsoft.com/default...b;en-us;308771 appeared to
be close.
So I upped it from 1900000 to 2900000. So far things are working better.
(I did have to rebuild the catalog, but that may be a small price to pay on
a test box. :-)
It's up to 712417 records indexed and still going.
Yeah, I was struck by the total number in the earlier two runs being fairly
close. Not sure if it's coincidence or not.
If this population is still working as of tomorrow I'll call this one
closed.

> The CPU usage is usually low, except when there are Shadow Merges and at
the
> end of the Full Population during the Master Merge. A PopulateStatus of 1
> means that the "Full population in progress", so hopefully a MSSearch or
> MssCi source event will be present to help explain this!
Hopefully, but like I say, there was nothing until I manually stopped it.
Anyway, thanks for the help so far.

> Thanks,
> John
>
>
> "Greg D. Moore (Strider)" <mooregr_deleteth1s@.greenms.com> wrote in
message[vbcol=seagreen]
> news:dXmkd.223$qv5.36@.twister.nyroc.rr.com...
now
> log.
> on
> was
> 103
> sure
>
|||Greg,
Ok, while you may be able to read (all events), many who post here do not
seem to be able to do so... ;-)
Stopping the Full Population and then reviewing the event log was a very
good idea! Are you FT Indexing large (MS Office, Adobe PDF or other file
types?) files stored in an IMAGE column? The KB article doesn't specifically
mention Error: 80040d19, but the recently enhanced (SP3) message text does
make reference to the FilterProcessMemoryQuota registry key, so a connection
between the error and fix could be made, so I will doubt you no moore :-)
I'm not sure how many rows you have left to complete past the 712,417 mark,
but monitor it closely and if it appears to hang again, stop it and check
the event log, if you get the same message increase the
FilterProcessMemoryQuota value. What type of files are you FT Indexing and
what is their avg. and max sizes?
Good Catch!
John
"Greg D. Moore (Strider)" <mooregr_deleteth1s@.greenms.com> wrote in message
news:ZQBkd.468$qv5.49@.twister.nyroc.rr.com...[vbcol=seagreen]
> "John Kane" <jt-kane@.comcast.net> wrote in message
> news:eznFI00xEHA.1192@.tk2msftngp13.phx.gbl...
> as
> here?
> as
does[vbcol=seagreen]
371,952,
> Trust me John, there was NOTHING in the Event log. :-) I can read an
event
> log.
> I also checked the Gatherlogs and nothing was out of the ordinary.
> HOWEVER... when I manually stop the population and checked the event log
> THEN I got an error.
> "The crawl seed <MSSQL75://SQLServer/04659998> in project <SQLServer
> SQL0001000007> cannot be accessed. Error: 80040d19 - The filtering process
> was stopped because its memory quota was exceeded. To increase the memory
> quota of the filtering process, increase the value for the following
> registry key: HKEY_LOCAL_MACHINE\Software\Microsoft\Search\1.0\G athering
> Manager\FilterProcessMemoryQuota. .
> There's not much in the KB about this, but
> http://support.microsoft.com/default...b;en-us;308771 appeared to
> be close.
> So I upped it from 1900000 to 2900000. So far things are working better.
> (I did have to rebuild the catalog, but that may be a small price to pay
on
> a test box. :-)
> It's up to 712417 records indexed and still going.
> Yeah, I was struck by the total number in the earlier two runs being
fairly[vbcol=seagreen]
> close. Not sure if it's coincidence or not.
> If this population is still working as of tomorrow I'll call this one
> closed.
>
> the
1[vbcol=seagreen]
> Hopefully, but like I say, there was nothing until I manually stopped it.
> Anyway, thanks for the help so far.
>
> message
> now
concerned?[vbcol=seagreen]
most[vbcol=seagreen]
application[vbcol=seagreen]
(doing[vbcol=seagreen]
rows.[vbcol=seagreen]
usage[vbcol=seagreen]
be
>
|||"John Kane" <jt-kane@.comcast.net> wrote in message
news:eXHkxK7xEHA.3808@.TK2MSFTNGP15.phx.gbl...
> Greg,
> Ok, while you may be able to read (all events), many who post here do not
> seem to be able to do so... ;-)
> Stopping the Full Population and then reviewing the event log was a very
> good idea! Are you FT Indexing large (MS Office, Adobe PDF or other file
> types?) files stored in an IMAGE column? The KB article doesn't
specifically
> mention Error: 80040d19, but the recently enhanced (SP3) message text does
> make reference to the FilterProcessMemoryQuota registry key, so a
connection
> between the error and fix could be made, so I will doubt you no moore :-)
I have about 8 million rows in this table.
Interestingly enough, last night it appeared to stop at that 712,417 mark
right after I posted.
In fact it was still there when I woke up this morning.
And then I checked a few hours later, it was up over 1 million records and
climbing. But it appears to have done NOTHING for about 8-10 hours. Very
strange.
It's now up to 2,419,059 and still climbing.

> I'm not sure how many rows you have left to complete past the 712,417
mark,
> but monitor it closely and if it appears to hang again, stop it and check
> the event log, if you get the same message increase the
> FilterProcessMemoryQuota value. What type of files are you FT Indexing and
> what is their avg. and max sizes?
>
Not really files, but data uploaded from documents such as Word files,
Quark, Wordperfect, text files, etc.
Not sure avg size, but probably several meg on average. Total table size
is about 44 Gig. The text column is by far the biggest part of the index
(we also have some varchars in there), so I'd guess about 4-5 meg each.

> Good Catch!
> John
>
|||Greg,
Good, so at least you're above the 2.4 million row mark now, with much to
still be processed. Now, that I know you have an 8 million row table, I can
give you a ball park timeframe for when this might take to complete, but
keep in mind that it is a SWAG. As 1 million rows on a poorly configured
server (database & FT Catalog sharing the same disk drive, with 1 CPU and
1GB of RAM) can take up to 14 hours to complete, so expect a similarly
configured server to finish FT Indexing of a table with 8 million rows in
approx. 4 to 5 days. Of course, your mileage may vary and your server's
actual configuration (software & hardware) may be different...
Also, you say that you are not FT Indexing files (i.e.. binary files stored
in an IMAGE datatype), but are in fact storing the text data uploaded from
documents such as Word files, Quark, Wordperfect, text files, etc., so this
textual data is stored in a column defined with the TEXT (or NTEXT)
datatype. Correct? If so, you can get the max and avg metadata via the
following SQL query :
select avg(datalength(Text_column_name)) "Avg. Text Length" from
8_million_row_table_name
select max(datalength(Text_column_name)) "Max. Text Length" from
8_million_row_table_name
While the Text datatype certainly supports well above 4-5 Mb of text in each
row, I'm fairly certain that this is well above what is typical for most FT
Indexing applications and my lie at the heart of the problem. While the BOL
title "Filtering Supported File Types" is referencing "documents" stored in
an IMAGE datatype, when it is stated that "For full-text indexing, a
document must be less than 16 megabytes (MB) in size and must not contain
more than 256 kilobytes (KB) of filtered text." This statement *may* also
apply to plain text stored in a TEXT or NTEXT datatype and this is why
getting both the avg. and especially max values would be most helpful in
understanding this issue.
FYI, I suspect that you've read the SQL 2000 BOL title "Full-text Search
Recommendations" as well as the SQL 2000 FTS deployment white paper at:
http://support.microsoft.com/default...b;en-us;323739 both speak to
software & hardware configuration issues when used with SQL Server 2000 FT
Indexing of tables over 1 million rows.
Thanks,
John
"Greg D. Moore (Strider)" <mooregr_deleteth1s@.greenms.com> wrote in message
news:DPWkd.3206$qv5.2464@.twister.nyroc.rr.com...[vbcol=seagreen]
> "John Kane" <jt-kane@.comcast.net> wrote in message
> news:eXHkxK7xEHA.3808@.TK2MSFTNGP15.phx.gbl...
not[vbcol=seagreen]
> specifically
does[vbcol=seagreen]
> connection
:-)[vbcol=seagreen]
> I have about 8 million rows in this table.
> Interestingly enough, last night it appeared to stop at that 712,417 mark
> right after I posted.
> In fact it was still there when I woke up this morning.
> And then I checked a few hours later, it was up over 1 million records and
> climbing. But it appears to have done NOTHING for about 8-10 hours. Very
> strange.
> It's now up to 2,419,059 and still climbing.
> mark,
check[vbcol=seagreen]
and
> Not really files, but data uploaded from documents such as Word files,
> Quark, Wordperfect, text files, etc.
> Not sure avg size, but probably several meg on average. Total table size
> is about 44 Gig. The text column is by far the biggest part of the index
> (we also have some varchars in there), so I'd guess about 4-5 meg each.
>
>