2012年3月19日星期一

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...
>
>

没有评论:

发表评论