locked
SQL Server performance RRS feed

  • Question

  • I have a new sql server 2014 installation :

    Database server Configuration
    RAM : 128GB
    PROCESSOR : Intel(R) Xeon(R) CPU E7-4870 @2.40Ghz 4 Pocessors
    SQL server 2014

    The dev team is doing Load testing, there are 16 application servers, which are hitting 2 database servers with the databases in Always ON Availability group.

    When the number of concurrent users is below 3000, the home page response time is 2secs but when the number of concurrent users exeeds more than 3000 the response reduces to 5-6 secs, which is not acceptibl, it should be 2sec.

    I have read that SQL server supports more than 36k concurrent connections, but the max number of connections during peak load testing in my system is between 400-450.

    I have checked the maximum number of connections with the below query,
    SELECT DB_NAME(dbid) as DBName, COUNT(dbid) as NumberOfConnections, loginame as LoginName, nt_domain as NT_Domain, nt_username as NT_UserName, hostname as HostName FROM sys.sysprocesses WHERE dbid > 0 GROUP BY dbid, hostname, loginame,nt_domain,nt_username order by NumberOfConnections DESC


    I checked the CPU and memory utilization during the load testing and its in acceptible range.
    The profiler also doesnot give me any leads..

    How can i identify the reason for this latency, OR is this normal, if yes, I need to prove it.. how do i prove that.
    If you can give me some hints on how to investigate further, and any query to check how much load can my machine take while giving good performance... that would be very helpfull.

    Monday, September 22, 2014 6:32 PM

Answers

  • Oh sorry, I should have read properly "max number of connections during peak load testing in my system is between 400-450". So there is a chance that waits occur in the app servers as with >3000 users a lot of them will be queued.

    Try finding out about waits within SQL Server querying

    sys.dm_os_wait_stats

    or use Perfmon SQL Server: Wait Statistics.


    Bodo Michael Danitz - MCT, MCITP - free consultant - performance specialist - www.sql-server.de

    Monday, September 22, 2014 7:15 PM

All replies

  • With so many concurrent connections you may run out of workers.

    To find out what's configured:

    select max_workers_count from sys.dm_os_sys_info

    To find out about worker usage:

    select SUM(current_workers_count), SUM(active_workers_count) from sys.dm_os_schedulers 

    Maybe you want to adjust "max worker threads" to a higher value using sp_configure.

    Keep in mind that this increases the overhead memory needs (2 MB / worker).


    Bodo Michael Danitz - MCT, MCITP - free consultant - performance specialist - www.sql-server.de


    Monday, September 22, 2014 6:56 PM
  • Are you sure this is a SQL Server issue?  Did you look at the application server performance?  Most likely you are seeing exceeding the connection pool on the application side.

    Normally you would never reach 1000s of simultaneous connection from a web server to SQL Server.  Normally a web server would use "Connection Pooling" to connect to the SQL Server and 50K users would only be ~200 actual SQL connections required.

    See:

    http://msdn.microsoft.com/en-us/library/8xx3tyca(v=vs.110).aspx

    Monday, September 22, 2014 6:56 PM
    Answerer
  • Ah, I forgot "how to monitor":

    There's a counter in Perfmon called "SQL Server: Wait Statistics Wait for the worker" which can tell you if there's waiting tasks and wait time - which would be my first guess considering the high number of connections.

    You can also execute this query to find out about waiting workers:

    select * from sys.dm_os_wait_stats where wait_type = 'THREADPOOL'


    Bodo Michael Danitz - MCT, MCITP - free consultant - performance specialist - www.sql-server.de

    Monday, September 22, 2014 7:05 PM
  • But there are 16 app servers... so it's easy to get over a 1000 concurrent connections each demanding a worker.


    Bodo Michael Danitz - MCT, MCITP - free consultant - performance specialist - www.sql-server.de

    Monday, September 22, 2014 7:08 PM
  • Oh sorry, I should have read properly "max number of connections during peak load testing in my system is between 400-450". So there is a chance that waits occur in the app servers as with >3000 users a lot of them will be queued.

    Try finding out about waits within SQL Server querying

    sys.dm_os_wait_stats

    or use Perfmon SQL Server: Wait Statistics.


    Bodo Michael Danitz - MCT, MCITP - free consultant - performance specialist - www.sql-server.de

    Monday, September 22, 2014 7:15 PM