Efficient SQL Server connections.

[MrPeds]

Hi,

In all of my DTS packages I use a SQL Server connection per SQL task.

For example, if I have 3 Execute SQL Tasks I have 3 SQL connections defined.

Do I need to do this? Is there any performance increase or decrease if I only use 1 connection that is shared by each Execute SQL task?

I have found on a few occasions where I have lots of connections and Execute SQL tasks that I get a memory leak.

Has anybody got any advice about this?

Thanks,

MrPeds

 

[JohnDTampaBay]

Its probably not necessary to have a connection for every task. Use the same connection for tasks that run in series, when one task must complete before the other begins. Use separate connections for tasks that run in parallel. Good luck!

From BOL: DTS, connections > DTS Connections

Each connection can be used by only one DTS task at a time because the connections are single-threaded. When designing a package that requires multiple task connections, consider opening up several connections and balancing the load to improve performance.

If two tasks use the same connection, they are compelled to execute serially, rather than in parallel. If two tasks use different connections, they may execute in parallel. If two tasks use separate connections that refer to the same instance of SQL Server, they will execute in parallel. If both of these tasks have joined the package transaction, the package fails.

--John
-----------------------------------
Behold! As a wild ass in the desert
go forth I to do my work.
--Gurnie Hallock (Dune)

 

[MrPeds]

John,

Does this mean that even when I am carrying out serial operations the memory space for the other SQL connections is already assigned?

Put another way, does the connection get established only when the step is executed or when the DTS Task starts up?

Thanks,

MrPeds

 

[JohnDTampaBay]

I believe the connection is established when the DTS package needs to use it for the first time, but I think it stays open until the package completes. If you create three tasks to run serially with three separate connections, you will have three connections open during the last task but only be using one.

--John
-----------------------------------
Behold! As a wild ass in the desert
go forth I to do my work.
--Gurnie Hallock (Dune)

 

[MrPeds]

I am guessing that my memory leak could therefore be caused by having lots of SQL connections open towards the end of the DTS task, and this seems to be the case.

I am also assuming that if this is the case, as more and more memory is allocated to my SQL connections then performance would degrade when i try executing a complex SQL update query.

I will need to investigatehow much memory space is take up per connection.

MrPeds