March 10, 2004 at 10:57 am
We have NetIQ monitor the SQL servers, one thing I am confused is that the number of user connections are different from SP_who.
When I query sp_who, a lot connections are in sleeping mode, is that still counted in user connections? When will the sleeping login be disconneted from server? does it still use the resources while sleeping?
March 10, 2004 at 11:12 am
Books On Line (BOL) says:
Provides information about current Microsoft® SQL Server™ users and processes. The information returned can be filtered to return only those processes that are not idle.
Please note that the information returned by sp_who includes "processes". I am pretty sure these includes processes necessary for SQL Server to run. i.e. What is called the "lazy writer thread" would be one of these processes.
GaryA
March 10, 2004 at 9:01 pm
One of the reason why you might see sleeping threads would be if connections are being pooled by your application. Regarding active threads, Something what i was told from one of the microsoft support engineers was that if you query sysprocesses and if the spid has a kpid associated to it, it is considered to be an active thread.
March 18, 2004 at 9:55 am
tyang:sp_who/sp_who2 in BOL=>
The sp_who result set will be sorted in ascending order according to the spid values. In case of parallel processing, sub-threads are created for the specific spid. The main thread is indicated as spid =xxx and ecid =0. The other sub-threads have the same spid = xxx, but with ecid > 0. Thus, multiple rows for that spid number will be returned -- grouped together within that spid's placement in the overall list. The sub-threads will be listed in random order, except for the parent thread (ecid = 0), which will be listed first for that spid.
Which means that a user connection can have many 'rows' in sp_who.
SQL Server 2000 reserves SPID values from 1 through 50 for internal use, while SPID values 51 or higher represent user sessions
So that means that up to 50 SPIDs are used by the system at any given time.
Yes. Sleeping SPIDs are in 99.95 'active connections' in the sense that they represent an open connection between the SQL server and a computer. They will not be automatically killed by SQL Server. If you have sleeping connections that you need to manually kill it can mean that you have some other problem with your applications (like .DLLs loaded in memory clientside with Connections open but without a connection handle (memory leak)).
Sleeping SPIDs generally does not consume much resources unless it is both sleeping and blocking (open X-transactions for example). Locking problems are a totally other story on the other hand.
Regards, Hanslindgren!
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply