full text indexing opening many connections

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Karel - 02 Mar 2006 15:47 GMT

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?

Reply to this Message

Hilary Cotter - 02 Mar 2006 16:19 GMT

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.

Signature

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

>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
[quoted text clipped - 9 lines]
> this many connections is unacceptable. is this normal behavior for sql
> server?

Reply to this Message

Karel - 02 Mar 2006 16:42 GMT

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.

> Why do you think it is unacceptable?
>
[quoted text clipped - 14 lines]
> > this many connections is unacceptable. is this normal behavior for sql
> > server?

Reply to this Message

Hilary Cotter - 02 Mar 2006 17:49 GMT

PKM uses a different connection pool. It won't exhaust your web application
connection pool.

Signature

> One of my web applications threw the exception:
>
[quoted text clipped - 32 lines]
>> > this many connections is unacceptable. is this normal behavior for sql
>> > server?

Reply to this Message

Karel - 02 Mar 2006 19:39 GMT

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.

> PKM uses a different connection pool. It won't exhaust your web application
> connection pool.
[quoted text clipped - 35 lines]
> >> > this many connections is unacceptable. is this normal behavior for sql
> >> > server?

Reply to this Message

Simon Sabin - 26 Apr 2006 12:57 GMT

Hello Karel,

It suggest that the server is hitting a limit of some sort either, network,
processor, memory or disk. It won't be connections because SQL can have thousands
of connections.

What is the spec of your box?

Do you have the full text indexes on a seperate drive.

> 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,
[quoted text clipped - 79 lines]
>>>>> this many connections is unacceptable. is this normal behavior for
>>>>> sql server?

Reply to this Message

Simon Sabin - 26 Apr 2006 12:50 GMT

This is the way Full text works to index the data

Why is it unaccepatble?

The call to start indexing is an asynchronous call. i.e. it will finish before
the indexing has been finished.

> i created a job that performs indexing. the execution time takes only
> a second. i noticed, however, using SQL Profiler that the process
[quoted text clipped - 8 lines]
> this many connections is unacceptable. is this normal behavior for sql
> server?

Reply to this Message