SQL Sever becoming Non Responsive

Question

SQL Sever becoming Non Responsive

IT researcher

SSCertifiable

Points: 7600
More actions
March 3, 2021 at 10:31 am

#3852778

SQL server 2008 R2 - express edition becomes non-responsive, whenever connection count crosses 12000.
Is it due to any connection limitation on express version ?
or due to overall load ( i.e. 'pending memory grants' was greater than 0) on RAM from increased operations ?

Viewing 12 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply

Erland Sommarskog SSC-Insane Points: 24038 More actions · Answer 1

12000 connections on an Express instance sounds an awfully lot. As a matter of fact, it is an awfully lot even for Enterprise Edition. I've never seen that many.

Does this number reflect actual users? It is not uncommon that when people have a lot of connections, this is due to improper handling on the client side so that connections cannot be reused in the connection pool.

Each connection requires a fixed amount of memory, so you could run out of RAM if you are short on it. How much RAM does the machine have?

[font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]

ScottPletcher SSC Guru Points: 101098 More actions · Answer 2

SQL Express is limited to 1.4GB of RAM. I'm stunned that 12,000 connections could even be initiated in that little RAM. You need to have most of those requests somehow use a different, existing connection to SQL Server rather than create a new one.

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

IT researcher SSCertifiable Points: 7600 More actions · Answer 3

Ok thanks, connections were kept open till application was closed to reuse. Connection string is unique for a DB, so will change to close and open logic.

Whether connection pools are maintained in server(in a LAN) or users machine RAM?

Erland Sommarskog SSC-Insane Points: 24038 More actions · Answer 4

Connection pooling is a client-side concept. When you close a connection, the API keeps the connection and reuses it when the same connection string is used again. SQL Server has support for pooling, so the client can tell SQL Server to clean up the connection. If the connection is not reused within 60 seconds, the application closes the connection for real.

[font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]

IT researcher SSCertifiable Points: 7600 More actions · Answer 5

whether required to restart server pc during SQL server non-responsive state?

i.e.. pending memory grants >0 or restart SQL server ? ( or any other method like DBCC FREEPROCCACHE)

Grant Fritchey SSC Guru Points: 398882 More actions · Answer 6

You're past the edge of what Express is used for. I'd strongly advise you to upgrade to a paid version of SQL Server. You can take advantage of more and bigger hardware that way. 12,000 connections is a huge system.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

Erland Sommarskog SSC-Insane Points: 24038 More actions · Answer 7

To answer your question: you may not have to restart SQL Server. You can connect on the admin connection. You do that by prefixing the server\instance with ADMIN:, for instance

ADMIN:.\SQLEXPRESS

By default the Admin connection is only reachable on the machine itself. It is likely that you will get an error message when you connect, because SSMS wants to make a second connection for Intellisense, but the Admin connection is one single scheduler, and there can only be one connection this way.

Once you are connected, you can start to kill processes, if the problem is a deluge of connections.

That said, many experienced DBAs, including people who monitor this forum, would prefer to take the shortcut to just restart SQL Server.

[font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 8

If that box has 12,000 connections, you may have some software running against it or a stored procedure running that has "Connection Leaks". You should probably investigate what is making all of those connections and how old they may be and what they're actually doing.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

IT researcher SSCertifiable Points: 7600 More actions · Answer 9

In a scenario, where 100 users over LAN open data entry application and edit values(in a grid) from 9 AM to 9 PM, wouldn't there be a speed issue, if connections are opened and closed for each cell edit ?

Thank you

Erland Sommarskog SSC-Insane Points: 24038 More actions · Answer 10

Indeed, there would if the connections would be physically opened and closed for every edit. That is why client APIs implement connection pooling as a default option to prevent this from happening.

In any case, 100 users doing data entry on an application using SQL Server Express sounds somewhat improbable to me.

[font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 11

IT researcher wrote:

In a scenario, where 100 users over LAN open data entry application and edit values(in a grid) from 9 AM to 9 PM, wouldn't there be a speed issue, if connections are opened and closed for each cell edit ?
Thank you

I wasn't suggesting doing anything of the kind. I was assuming that you did like Erland suggested above and that you're code is doing crazy things instead. Google for the definition of "Connection Leak". It sounds similar to people opening global cursors and never closing them. Or, maybe your software is closed to opening a connection for every cell and not closing them than you think.

And, I agree... having a hundred users banging on a seriously under-powered Express instance is very likely to lead to more issues. Probably not a great idea to go ultra cheap on this.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)