Hi,

I have an application which maintains around 25-30 connections to the
database.
The status for all those are "sleeping" most of the time.
The database is MSDE.

I would like to know what are the effects on performance, locking and any
other issuses due to this "sleeping" connections.

Any technical document or link would be of great help

hi,
[quoted text, click to view]

no effects on on performance and/or locking... just memory footprint and
"wasted" resources...
each connection, live or sleeping, will eat about 24kb of memory, calculated
as 12 KB + 3 times the Network Packet Size (default setting that can be
partially customized via sp_configure system stored procedure modifying the
'user connections' setting), used to store the data structures holding the
connection 's context, as long as for buffer used to send and receive the
relative associated network streams (default to 4KB network packet setting),
that can be stolen from the buffer pool memory region and/or the MemToLeave
memory area... as these memory regions are not infinite (:D) you are wasting
some bytes.. :D
--
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtm http://italy.mvps.org
DbaMgr2k ver 0.12.0 - DbaMgr ver 0.58.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
--------- remove DMO to reply