SQL Server Performance Forum – Threads Archive
Multiple spids when one application is running
When my client runs my application, I see multiple spids are created in Current Activity/Process Info.1.What does that mean?
2.What is causing that?
3.How can we prevent this? This is a VB application using ADO Controls. thanks,
sqlspeed
This happens during parallel execution and where the statement is being executed by multiple threads. You can confirm this by looking at the execution context id column "ecid". MVPs confirmed in one of their article as This behavior is common when there is more than 1
resource available to perform the TSQL task. i.e. more than 1 drive, more than 1 controller, more than 1 processor.. _________
Satya SKJ
Moderator
SQL-Server-Performance.Com
Also look at:
http://www.sql-server-performance.com/forum/topic.asp?TOPIC_ID=1883 1 and 2.
Using VB and ADO a common cause is that you are not closing your database connections in the code. Or that you are opening a lot of connections in a loop or something. It also occures if you use a lot of server side cursors with ADO (instead of disconnected clientside cursors) since the server side cursor will lock data longer (and are slower) and thus when new connections are made they are forced to create a new connection in the connection pool instead of using an existing one. 3.
Close your database connections as soon as you are finished with them. And if you do not need server side cursors from an application perspective, switch to disconnected clientside cursors if possible. The cursor type is set on the recordset object like rs.CursorLocation = X. Serverside cursors are default when doing a simple rs.Open. Search the net to see how to create disconnected clientside recordsets. If you really need serverside cursors (you work with large ammount of data or for some application requirements, or easier lock management) then there is nothing wrong with multiple connections. As long as you close them as soon as possible. /Argyle
Thank you for yr response. I will check to see that all my adodc controls are closed before I exit out of the application. This is an application done 5 years ago using Adodc controls (not ADO objects). I had imagined that the Adodc controls clean up after themselves. thanks,
sqlspeed
]]>