Breaking point for SQL server database based on connections from app to SQL server We have been asked to confirm on some capacity test for one of our SQL server.

Application team is building their new app servers with better infrastructure to connect to SQL server.

Currently with X app servers connecting to our SQL server with over 500 Gb of RAM and 80 logical processors Numa enabled. Below is the current load

Total sessions: 


used and not sleeping - on avg 100;
used and sleeping - 5000;
not used and sleeping around 500.


Above shows 40K batch requests / sec followed by approx 10k transactions per sec during the peak day. The described load consumes between 70-80 percent of CPU through peak time on SQL Server.




How can I calculate the max load in terms of additional app servers
this SQL Server can handle without causing any outages or breaking
like slow SQL Server causing issues?
Does it matter if volume remains same and just more user
connections are made or that's never going to be the case?
How can I calculate the max capacity of user connections this
SQl Server can handle without breaking apart?
You can use a load testing tool which supports the protocol which your application servers use for the database communication (OLE DB, ODBC, JDBC, etc.) in order to mimic the increased amount of application servers talking to the database instance, this way you will be able to collect metrics and correlate increasing load with the increasing query execution times, eventual deadlocks and so on. 

Just make sure to configure the load testing tool to use the same connection pattern: connection pool settings, transaction isolation level, etc. so your load test would accurately represent the network footprint of the real application. 

Check out The Real Secret to Building a Database Test Plan With JMeter article for more information on the concept of databases load testing using a performance testing tool, in the above case Apache JMeter