Hello , i use an mvc application. and i got lot of sleeping pools in sql server after using [exec sp_who2] and after debugging , i found that these Lines added eactly in line : var abc = [Select].FirstOrdefault();

i have max Pool size = 100 , pooling =True , min pool size = 10 The min pool size = 10 setting means you will always have at least 10 connections once the first connection is opened. All connections will be sleeping when no query is currently executing. This is unrelated to EF or [Select].FirstOrdefault(); ; you will see the same behavior with any query. You may have more than 10 connections (up to the max pool size of 100) if over 10 queries were executed concurrently. The sleeping connections in excess of 10 will be closed after a few minutes of inactivity.

EntityFramework Leave SLEEPING sessions (after Firstordefault)

samir karim 25

Hello ,

i use an mvc application.

and i got lot of sleeping pools in sql server after using [exec sp_who2]
User's image

and after debugging , i found that these Lines added eactly in line :

var abc = [Select].FirstOrdefault();

AgaveJoe 29,786 Reputation points

2024-01-10T13:00:52.1166667+00:00

Sleeping means there's an active connection but nothing is executing at the time the sp_who2 ran. For example, if connecting to DB but not executing a script.

Is there a problem?
samir karim 25 Reputation points

2024-01-10T13:34:02.8333333+00:00

i dont understund your answer ,and there is More informations using sys.dm_exec_sessions
AgaveJoe 29,786 Reputation points

2024-01-10T14:09:40.0466667+00:00

i dont understund your answer

It is hard to formulate an answer when there's no question.

If you are asking why there are "sleeping" statuses the reason is there is a connection but not activity as already explained. This happens because of SQL Connection Pooling. Connection pooling keeps connections open for reuse because opening a new connection is expensive.

https://learn.microsoft.com/en-us/dotnet/framework/data/adonet/sql-server-connection-pooling

If you have a specific question then please ask.

Maybe reading the official documentation related to the procedure you are running will help answer any questions you have?

https://learn.microsoft.com/en-us/sql/relational-databases/system-dynamic-management-views/sys-dm-exec-sessions-transact-sql?view=sql-server-ver16

https://learn.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sp-who-transact-sql?view=sql-server-ver16
samir karim 25 Reputation points

2024-01-10T14:59:23.3366667+00:00

the problem is i have a lot of sleeping sessions.

and when i search in code , i found that one of the cause of this probleme is a Line :

var abc = [Select].FirstOrdefault();

this line => Add a line with sleeping statut in sql server

so my question is

this problem is normal using EntityFramwork ?

if Not , what can i modify in My code ?

i have max Pool size = 100 , pooling =True , min pool size = 10
Dan Guzman 9,261 Reputation points

2024-01-10T15:56:24.7066667+00:00

min pool size = 10 means you will have at least 10 connections in the pool, which will be sleeping when no queries are currently being executed. This is normal for any query, not just the one generated by [Select].FirstOrdefault();. If you remove min pool size, the sleeping connections will be closed after a few minutes of inactivity.
SurferOnWww 3,816 Reputation points

2024-01-11T01:40:11.8233333+00:00

I suggest that you will try the sample code in the following Microsoft document to see how the NumberOfActiveConnectionPools, NumberOfActiveConnections, NumberOfFreeConnections and the other counter items increase / decrease. Performance Counters in ADO.NET
Lan Huang-MSFT 30,176 Reputation points Microsoft Vendor

2024-01-11T06:44:14.89+00:00

Hi @samir karim,

and when i search in code , i found that one of the cause of this probleme is a Line :var abc = [Select].FirstOrdefault();

A status of sleeping means the session is connected but not actively running anything. According to the information I investigated, if according to what you said, EntityFramework Leave SLEEPING sessions (after Firstordefault). Maybe you can check if FirstorDefault returns null. When calling the FirstOrDefault method, if no value is returned, EntityFramework will close the connection. You can try using the .AsNoTracking() method [[Select].AsNoTracking().FirstOrdefault()]; to see if there is any improvement. If there is no improvement, it should have nothing to do with [Select].FirstOrdefault();.
samir karim 25 Reputation points

2024-01-11T13:45:41.8833333+00:00

Unfortunately , nothing changed :(
always a sleeping Statut added after this Line : [Select].FirstOrdefault() (max pool size was reached)
AgaveJoe 29,786 Reputation points

2024-01-11T13:59:56.9233333+00:00

You still have not explained the problem. You keep describing how SQL connection pooling works. If you feel there is a problem with code not closing connections properly then shared enough code to reproduce this issue.
Dan Guzman 9,261 Reputation points

2024-01-11T14:39:07.99+00:00

always a sleeping Statut added after this Line : [Select].FirstOrdefault() (max pool size was reached)

Do you mean you see 100 sleeping connections after executing [Select].FirstOrdefault() once when you step through the code in a debugger?
samir karim 25 Reputation points

2024-01-11T14:50:06.2633333+00:00

@Dan Guzman afetr [Select].FirstOrdefault() , 10 sleeping Lines added (when i execute sp_who2)
AgaveJoe 29,786 Reputation points

2024-01-11T15:04:01.48+00:00

10 connects seems reasonable. What do you expect to see?
Can you provide sample code and configuration so we are not guessing?
samir karim 25 Reputation points

2024-01-11T15:12:14.6866667+00:00

@AgaveJoe but why they still in database for ever, and that generate an error : all pooled connections were in use and max pool size was reached
AgaveJoe 29,786 Reputation points

2024-01-11T15:25:39.05+00:00

but why they still in database for ever, and that generate an error : all pooled connections were in use and max pool size was reached

The members on this forum can only evaluate the information you provide. Now, you're telling us you are receiving an error. Is the error...???

Timeout expired. The timeout period elapsed prior to obtaining a connection from the pool. This may have occurred because all pooled connections were in use and max pool size was reached.

If so, there is a bug in your code. It could be a loop or manually managing connections or a service lifetime.
Is this an ASP.NET Framework application? Is there anyway you can provide code that reproduces this issue?

1 answer

Dan Guzman 9,261 Reputation points

2024-01-10T16:08:39.12+00:00

i have max Pool size = 100 , pooling =True , min pool size = 10

The min pool size = 10 setting means you will always have at least 10 connections once the first connection is opened. All connections will be sleeping when no query is currently executing. This is unrelated to EF or [Select].FirstOrdefault();; you will see the same behavior with any query.

You may have more than 10 connections (up to the max pool size of 100) if over 10 queries were executed concurrently. The sleeping connections in excess of 10 will be closed after a few minutes of inactivity.
Please sign in to rate this answer.

0 comments No comments
Sign in to comment

Use comments to ask for clarification, additional information, or improvements to the question.

Share via

EntityFramework Leave SLEEPING sessions (after Firstordefault)

1 answer

Your answer