Hello all:

I have an application that uses threading to access my SQL Server 2000
database. It can produce, at most, 20 threads at a time. Yesterday and
this morning, I was happily testing this app and it performed
beautifully. Monitoring the threads via Enterprise Manager, Current
Activity, Process Info, I was seeing one SPID for each thread...
precisely as I expected.
Then, this evening, it changed. Now when I test the app, I find 6 rows
in the Process Info pane for each thread, each with the same SPID (but
with differing Context ID's). For example, I will have 6 rows for SPID
53, then 6 for SPID 54, and so forth. This results in some 120
processes, which I don't want. Furthermore, my threads now fail with
the error that they could not obtain a connection from the pool.
When I examine each SPID's properties, instead of seeing, in the "Last
T-SQL Batch" box, the queries that should have been getting executed
against the database, I see "sp_execsql;1" in every case...
I have not changed my threading code since this morning, when it worked
perfectly. Nor have I altered my database structure! Can anyone
explicate this behavior for me?

Thanks much,
zdrakec

zdrakec (zdra***@yahoo.com) writes:
When you see more than one row per spid, it is because SQL Server is using
parallel threads to execute the query. This has nothing to do with the
threads in your app. Parallelism can be a good thing, but often parallel
plans are less effective than single plans. You can turn off parallelism
for a query by adding

    OPTION (MAXDOP 1)

at the end of the query.

The connection pool is the client API, and this is a different problem
than parallel threads in SQL Server. I believe a common error in ADO .Net
applications is to close the connection, without closing the command
first.

Hello Erland:

Thank you for the information. There is no "close" method on the ADO
Command object. Are you saying that I must dispose of the command
object before I close the connection?
Also, I wonder why my server began doing this in the evening, when it
did not do this in the morning...
Any further thoughts?
Once again, thanks for the info...
Cheers,
zdrakec

zdrakec (zdra***@yahoo.com) writes:
I don't know if this applies to ADO, but it you are leaving less things
to fate if dispose your Command objects explicitly.

There are a couple of possibilities. One is that statistics had changed,
so SQL Server recompiled the queries. Another is that the first invocation
of the queries or stored procedures had parameters that were not typical
for the rest of the accesses. Since SQL Server uses a cost-based optimizer
that makes it decisions based on estimates, performance is not always
deterministic.

Hi Erland:

I discovered that after a reimport on the tables involved in one of the
queries, my indices were gone (!!!) I am rebuilding (the tables are
very large and join together!) I am rebuilding them and expect this to
fix the problem...
So as you say, the statistics had indeed changed!!!!

Thanks much,
zdrakec

TSQL Debugger Oddity
Whats Wrong with the Query?
Newbie:UNION with SELECT TOP
store image
DB design question.