Performance Testing: Handling maximum database user connection

Performance Testing: Handling maximum database user connection

By Pankaj Goel

While working on performance testing assignments, many a times, I came across situation where following error was reported while applying load on the system
“The Maximum Connection Limit Has Been Reached”
On further analysis, I found that the error was being returned by the database (MS SQL Server in this case). Here is in this post, I have tried to detail what all to do when you get this error
So for MS SQL Server (2000 or 2005), if you have identified that the number of user connections is high, you can control the number of user connections by doing one of the following:
• Enable Microsoft SQL Server to automatically add user connections as needed. Do this through the Microsoft SQL Server SQL Server Properties window by setting the user connections configuration option to 0. If you specify a value other than 0, Microsoft SQL Server allocates memory for those connections at startup, which burns up portions of the memory pool.

• Specify a maximum number of user connections. Set this amount to the expected maximum number of concurrent users. Microsoft SQL Server rejects new login requests after reaching the maximum number of user connections when this value is set. After changing the maximum number of user connections, you must restart Microsoft SQL Server.

To re-configure the maximum number of user connection, In SQL Server 2000, you can use the sp_configure stored procedure. When you install SQL Server, the default value for user connections is 0 (32767 concurrent connections).
a) Start SQL server from command prompt
sqlservr.exe -c -f
b) Issue the following command
sp_configure ‘user connections’, 0
go
reconfigure with override
This code sets user connections back to the default setting.
c) Restart SQL server normally

For further details about the optimum SQL server settings, resources @ the following link contain some good information
http://support.microsoft.com/kb/319942/EN-US/
http://support.microsoft.com/kb/224587/



http://www.qacampus.com
http://www.crestech.in
http://www.crestechsoftware.com.au