SQL Sever becoming Non Responsive

  • 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 ?

  • 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]

  • 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) Prosecutor James Blackburn, in closing argument in the Fatal Vision murders trial: "If in the future, you should cry a tear, cry one for them [the murder victims]. If in the future, you should say a prayer, say one for them. And if in the future, you should light a candle, light one for them."

  • 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?

  • 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]

  • 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)

  • 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

    The Scary DBA
    Author of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd Edition
    Product Evangelist for Red Gate Software

  • 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]

  • 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".
    "Dear Lord... I'm a DBA so please give me patience because, if you give me strength, I'm going to need bail money too!"

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

  • 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

  • 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]

  • 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".
    "Dear Lord... I'm a DBA so please give me patience because, if you give me strength, I'm going to need bail money too!"

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

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

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