full text indexing opening many connections

i created a job that performs indexing. the execution time takes only a
second. i noticed, however, using SQL Profiler that the process opened 32
connections!
trace details...
EventClass: Audit Login
ApplicationName: PKM
NTUserName: System
LoginName: NT AUTHORITY\SYSTEM
this many connections is unacceptable. is this normal behavior for sql server?
Why do you think it is unacceptable?
SQL FTS spawns many threads to grab as much data as possible simultaneously
to make indexing happen as fast as possible.
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
"Karel" <Karel@.discussions.microsoft.com> wrote in message
news:54AB17DB-A491-48CD-B513-8195EE761F34@.microsoft.com...
>i created a job that performs indexing. the execution time takes only a
> second. i noticed, however, using SQL Profiler that the process opened 32
> connections!
> trace details...
> EventClass: Audit Login
> ApplicationName: PKM
> NTUserName: System
> LoginName: NT AUTHORITY\SYSTEM
>
> this many connections is unacceptable. is this normal behavior for sql
> server?
|||One of my web applications threw the exception:
Timeout expired. The timeout period elapsed prior to obtaining a connection
from the pool. This may have occurred because all pooled connections were in
use and max pool size was reached.
After an investigation I found no errors in the web application that could
cause it use more than one sql connection, and i discovered that the indexing
process, which swallowed up many connections, took place at the very instant
of the error. I am suspecting that the max connections was reached as a
result of the full text indexing process.
"Hilary Cotter" wrote:

> Why do you think it is unacceptable?
> SQL FTS spawns many threads to grab as much data as possible simultaneously
> to make indexing happen as fast as possible.
> --
> 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
>
> "Karel" <Karel@.discussions.microsoft.com> wrote in message
> news:54AB17DB-A491-48CD-B513-8195EE761F34@.microsoft.com...
>
>
|||PKM uses a different connection pool. It won't exhaust your web application
connection pool.
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
"Karel" <Karel@.discussions.microsoft.com> wrote in message
news:75BCD0DE-308F-4F2C-9BFA-0A19F74200C5@.microsoft.com...[vbcol=seagreen]
> One of my web applications threw the exception:
> Timeout expired. The timeout period elapsed prior to obtaining a
> connection
> from the pool. This may have occurred because all pooled connections were
> in
> use and max pool size was reached.
> After an investigation I found no errors in the web application that could
> cause it use more than one sql connection, and i discovered that the
> indexing
> process, which swallowed up many connections, took place at the very
> instant
> of the error. I am suspecting that the max connections was reached as a
> result of the full text indexing process.
> "Hilary Cotter" wrote:
|||Yeah, i understand what you just said. I'm not accusing the fulltext process
of tapping into the web app's connection. As far as i know, thats just not
possible. The problem im having is that the full text indexing process eats
up all available connections, not allowing anything else to connect.
Here is the scenario:
1. begin fulltext process: exec sp_fulltext_catalog N'Category', N'start_full'
2. in query analyzer execute: sp_who2 (view the fulltext process using 50+
connections)
3. start the webapp: hit the website and trigger a sql connection.
result: The web app can't connect. why? because the fulltext process used up
all of the connections! The web application then times out and throws the
exception.
"Hilary Cotter" wrote:

> PKM uses a different connection pool. It won't exhaust your web application
> connection pool.
> --
> 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
>
> "Karel" <Karel@.discussions.microsoft.com> wrote in message
> news:75BCD0DE-308F-4F2C-9BFA-0A19F74200C5@.microsoft.com...
>
>

Full text content index corrupt

Hello,
I am getting this error on my full text search...
Msg 7619, Lvl 16, State 1, Execution of a full-text operation failed. The
content index is corrupt. , Server ULSM1, Procedure , Line 1
I am using SQL 2000 Standard...
This happens on only ONE of my tables... i have 2 tables, that are almost
identical in structure, but 1 table has 100k records, and the other has 600k
records..
It is the table that has 600k records where this corrupt action happens...
Can anyone help me with this? This is the first time i've seen this error
(and i've done a couple google searches, but nothing this specific).
Thank you.
Is this table the only table in its catalog? If so rebuild the catalog to
see if this solves the problem. Review the event log to see if there are any
disk errors, or errors from MSSearch or MSSCI.
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
"RL Tech" <tech_support@.nospamuls.com> wrote in message
news:O2Sb09hFGHA.3892@.TK2MSFTNGP12.phx.gbl...
> Hello,
> I am getting this error on my full text search...
> Msg 7619, Lvl 16, State 1, Execution of a full-text operation failed. The
> content index is corrupt. , Server ULSM1, Procedure , Line 1
> I am using SQL 2000 Standard...
> This happens on only ONE of my tables... i have 2 tables, that are almost
> identical in structure, but 1 table has 100k records, and the other has
> 600k
> records..
> It is the table that has 600k records where this corrupt action happens...
> Can anyone help me with this? This is the first time i've seen this error
> (and i've done a couple google searches, but nothing this specific).
> Thank you.
>
|||Hi Hilary,
I'll review the event log, but in the meantime i'll start the rebuild of the
catalog and see if that helps...
Thanks...
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:%23JMqh1lFGHA.1260@.TK2MSFTNGP15.phx.gbl...
> Is this table the only table in its catalog? If so rebuild the catalog to
> see if this solves the problem. Review the event log to see if there are
any[vbcol=seagreen]
> disk errors, or errors from MSSearch or MSSCI.
> --
> 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
> "RL Tech" <tech_support@.nospamuls.com> wrote in message
> news:O2Sb09hFGHA.3892@.TK2MSFTNGP12.phx.gbl...
The[vbcol=seagreen]
almost[vbcol=seagreen]
happens...[vbcol=seagreen]
error
>
|||Thanks hilary, that has helped...
Just an odd error i've never seen, nor could i find any topics on this
error.
Anyway, thanks again.
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:%23JMqh1lFGHA.1260@.TK2MSFTNGP15.phx.gbl...
> Is this table the only table in its catalog? If so rebuild the catalog to
> see if this solves the problem. Review the event log to see if there are
any[vbcol=seagreen]
> disk errors, or errors from MSSearch or MSSCI.
> --
> 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
> "RL Tech" <tech_support@.nospamuls.com> wrote in message
> news:O2Sb09hFGHA.3892@.TK2MSFTNGP12.phx.gbl...
The[vbcol=seagreen]
almost[vbcol=seagreen]
happens...[vbcol=seagreen]
error
>

Full text content index corrupt

Hello,
I am getting this error on my full text search...
Msg 7619, Lvl 16, State 1, Execution of a full-text operation failed. The
content index is corrupt. , Server ULSM1, Procedure , Line 1
I am using SQL 2000 Standard...
This happens on only ONE of my tables... i have 2 tables, that are almost
identical in structure, but 1 table has 100k records, and the other has 600k
records..
It is the table that has 600k records where this corrupt action happens...
Can anyone help me with this? This is the first time i've seen this error
(and i've done a couple google searches, but nothing this specific).
Thank you.Is this table the only table in its catalog? If so rebuild the catalog to
see if this solves the problem. Review the event log to see if there are any
disk errors, or errors from MSSearch or MSSCI.
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
"RL Tech" <tech_support@.nospamuls.com> wrote in message
news:O2Sb09hFGHA.3892@.TK2MSFTNGP12.phx.gbl...
> Hello,
> I am getting this error on my full text search...
> Msg 7619, Lvl 16, State 1, Execution of a full-text operation failed. The
> content index is corrupt. , Server ULSM1, Procedure , Line 1
> I am using SQL 2000 Standard...
> This happens on only ONE of my tables... i have 2 tables, that are almost
> identical in structure, but 1 table has 100k records, and the other has
> 600k
> records..
> It is the table that has 600k records where this corrupt action happens...
> Can anyone help me with this? This is the first time i've seen this error
> (and i've done a couple google searches, but nothing this specific).
> Thank you.
>|||Hi Hilary,
I'll review the event log, but in the meantime i'll start the rebuild of the
catalog and see if that helps...
Thanks...
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:%23JMqh1lFGHA.1260@.TK2MSFTNGP15.phx.gbl...
> Is this table the only table in its catalog? If so rebuild the catalog to
> see if this solves the problem. Review the event log to see if there are
any
> disk errors, or errors from MSSearch or MSSCI.
> --
> 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
> "RL Tech" <tech_support@.nospamuls.com> wrote in message
> news:O2Sb09hFGHA.3892@.TK2MSFTNGP12.phx.gbl...
The[vbcol=seagreen]
almost[vbcol=seagreen]
happens...[vbcol=seagreen]
error[vbcol=seagreen]
>|||Thanks hilary, that has helped...
Just an odd error i've never seen, nor could i find any topics on this
error.
Anyway, thanks again.
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:%23JMqh1lFGHA.1260@.TK2MSFTNGP15.phx.gbl...
> Is this table the only table in its catalog? If so rebuild the catalog to
> see if this solves the problem. Review the event log to see if there are
any
> disk errors, or errors from MSSearch or MSSCI.
> --
> 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
> "RL Tech" <tech_support@.nospamuls.com> wrote in message
> news:O2Sb09hFGHA.3892@.TK2MSFTNGP12.phx.gbl...
The[vbcol=seagreen]
almost[vbcol=seagreen]
happens...[vbcol=seagreen]
error[vbcol=seagreen]
>

Full text content index corrupt

Hello,
I am getting this error on my full text search...
Msg 7619, Lvl 16, State 1, Execution of a full-text operation failed. The
content index is corrupt. , Server ULSM1, Procedure , Line 1
I am using SQL 2000 Standard...
This happens on only ONE of my tables... i have 2 tables, that are almost
identical in structure, but 1 table has 100k records, and the other has 600k
records..
It is the table that has 600k records where this corrupt action happens...
Can anyone help me with this? This is the first time i've seen this error
(and i've done a couple google searches, but nothing this specific).
Thank you.Is this table the only table in its catalog? If so rebuild the catalog to
see if this solves the problem. Review the event log to see if there are any
disk errors, or errors from MSSearch or MSSCI.
--
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
"RL Tech" <tech_support@.nospamuls.com> wrote in message
news:O2Sb09hFGHA.3892@.TK2MSFTNGP12.phx.gbl...
> Hello,
> I am getting this error on my full text search...
> Msg 7619, Lvl 16, State 1, Execution of a full-text operation failed. The
> content index is corrupt. , Server ULSM1, Procedure , Line 1
> I am using SQL 2000 Standard...
> This happens on only ONE of my tables... i have 2 tables, that are almost
> identical in structure, but 1 table has 100k records, and the other has
> 600k
> records..
> It is the table that has 600k records where this corrupt action happens...
> Can anyone help me with this? This is the first time i've seen this error
> (and i've done a couple google searches, but nothing this specific).
> Thank you.
>|||Hi Hilary,
I'll review the event log, but in the meantime i'll start the rebuild of the
catalog and see if that helps...
Thanks...
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:%23JMqh1lFGHA.1260@.TK2MSFTNGP15.phx.gbl...
> Is this table the only table in its catalog? If so rebuild the catalog to
> see if this solves the problem. Review the event log to see if there are
any
> disk errors, or errors from MSSearch or MSSCI.
> --
> 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
> "RL Tech" <tech_support@.nospamuls.com> wrote in message
> news:O2Sb09hFGHA.3892@.TK2MSFTNGP12.phx.gbl...
> > Hello,
> >
> > I am getting this error on my full text search...
> >
> > Msg 7619, Lvl 16, State 1, Execution of a full-text operation failed.
The
> > content index is corrupt. , Server ULSM1, Procedure , Line 1
> >
> > I am using SQL 2000 Standard...
> >
> > This happens on only ONE of my tables... i have 2 tables, that are
almost
> > identical in structure, but 1 table has 100k records, and the other has
> > 600k
> > records..
> >
> > It is the table that has 600k records where this corrupt action
happens...
> >
> > Can anyone help me with this? This is the first time i've seen this
error
> > (and i've done a couple google searches, but nothing this specific).
> >
> > Thank you.
> >
> >
>|||Thanks hilary, that has helped...
Just an odd error i've never seen, nor could i find any topics on this
error.
Anyway, thanks again.
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:%23JMqh1lFGHA.1260@.TK2MSFTNGP15.phx.gbl...
> Is this table the only table in its catalog? If so rebuild the catalog to
> see if this solves the problem. Review the event log to see if there are
any
> disk errors, or errors from MSSearch or MSSCI.
> --
> 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
> "RL Tech" <tech_support@.nospamuls.com> wrote in message
> news:O2Sb09hFGHA.3892@.TK2MSFTNGP12.phx.gbl...
> > Hello,
> >
> > I am getting this error on my full text search...
> >
> > Msg 7619, Lvl 16, State 1, Execution of a full-text operation failed.
The
> > content index is corrupt. , Server ULSM1, Procedure , Line 1
> >
> > I am using SQL 2000 Standard...
> >
> > This happens on only ONE of my tables... i have 2 tables, that are
almost
> > identical in structure, but 1 table has 100k records, and the other has
> > 600k
> > records..
> >
> > It is the table that has 600k records where this corrupt action
happens...
> >
> > Can anyone help me with this? This is the first time i've seen this
error
> > (and i've done a couple google searches, but nothing this specific).
> >
> > Thank you.
> >
> >
>

FTS in SQL2005 and JOINS

Hi, I'm having a slight problem with some of my FTS queries, namely it
seems that in SQL 2005 the execution plans are constructed by first
doing the normal query and then joining with the FTS results.
For instance:
SELECT P.id,P.name
FROM CONTAINSTABLE(prd_Names,phrase,'"hp*"') F
INNER JOIN prd_Names P ON F.[key]=P.id
ORDER BY P.name,F.rank;
Causes a massive index scan on prd_Names table first and only then
joins with the smaller set returned by CONTAINSTABLE. IIRC in 2000 I
didn't have this problem (I think it was the other way around).
Is there any way to make the query behave properly?
The DTD is very simple prd_Names is a table with 3 columns
id (pkey) int
name varchar(100)
phrase varchar(500)
a clustered index on id
and an index on the name column.
BTW the table contains ~400k rows.
Thanks.
I've no idea whether this would work, but have you tried to re-order the
query so that the inner join is the other way around?
Griff
|||Griff wrote:
> I've no idea whether this would work, but have you tried to re-order the
> query so that the inner join is the other way around?
Yep, tried that with no effect. I also rewrote it without using the
JOIN statement: WHERE CONTAINS(..) etc. and also WHERE P.id IN (SELECT
... FROM CONTAINSTABLE()) but neither helped.
Thanks.
|||I think I've missed something - why are you doing an inner join in the first
place?
|||Griff wrote:
> I think I've missed something - why are you doing an inner join in the first
> place?
Because CONTAINSTABLE returns a table with [key],[rank] columns which I
need (afaik it's the normal way to use CONTAINSTABLE)