Connection established in DBMS

PramodHarithsa1 · ‎09-04-2013

Hi All,
We have about 40 ArcGIS Server instance (ArcSOC.exe) set using SDE direct connection.
ArcSDE version: 9.3.1
RDBMS: SQL Server 2005

As and when the map services(18 services-->total 40 instances) restart i see about 140 connections getting established in SQL server.
Can anybody help me understand how the ArcSOC instances and SQL server connections are related.

This increased number of connection is resulting in 'Maximum number of connections to instance exceeded' message to popup.
What would be the best way to handle this?
We did think of changing the connection value in giomgr.defs file. But i am unable to relate the creation of 140 connections for 40 instances. Is ther something i am missing out?

JakeSkinner · ‎09-04-2013

Hi Pramod,

When you start your services, an SDE connection will be made for each number of minimum instances you have set for that service. For example, if the minimum is set to 2, 2 SDE connections will be made.

More instances are created when simultaneous requests are made to the same service, and the amount of requests exceeds the available instances. For example, if 3 requests were made to the same service, and only 2 instances were available, another instance will be created. If there a no instances available, i.e. the max number of instances has been reached, the client will have to wait until an instance is returned to the pool before their request is returned.

What is the minimum number of instances set to for each of your 18 services? If no other users are connecting through any other clients, i.e. ArcMap/ArcCatalog, and you start your 18 services that each have a minimum number of instances set to 2, you should have 36 SDE connections.

Also, when all the users are disconnected from the SDE geodatabase, and all services are stopped, query the sde.sde_process_information table. Check to make sure there are no orphaned connections listed here.

PramodHarithsa1 · ‎09-04-2013

Hi Pramod,

When you start your services, an SDE connection will be made for each number of minimum instances you have set for that service. For example, if the minimum is set to 2, 2 SDE connections will be made.

More instances are created when simultaneous requests are made to the same service, and the amount of requests exceeds the available instances. For example, if 3 requests were made to the same service, and only 2 instances were available, another instance will be created. If there a no instances available, i.e. the max number of instances has been reached, the client will have to wait until an instance is returned to the pool before their request is returned.

What is the minimum number of instances set to for each of your 18 services? If no other users are connecting through any other clients, i.e. ArcMap/ArcCatalog, and you start your 18 services that each have a minimum number of instances set to 2, you should have 36 SDE connections.

Also, when all the users are disconnected from the SDE geodatabase, and all services are stopped, query the sde.sde_process_information table. Check to make sure there are no orphaned connections listed here.

Hi Jake ,
Thanks for the reply. The 40 instances i mentioned are the maximum number of instances that will be created for the 18 map services( each map service has a varied number of max instances ).
This should result in 40 connections made to SQL server. But i see multiple connections getting created for the same instance.
( I varified the process ID of instance and the application establishing connection on database ) .
I did check for orphaned connections. They are none. The 140 connections seen on SQL are when the map services are restarted and there are no applications consuming them.

ShannonShields · ‎09-05-2013

Hi Jake ,
Thanks for the reply. The 40 instances i mentioned are the maximum number of instances that will be created for the 18 map services( each map service has a varied number of max instances ).
This should result in 40 connections made to SQL server. But i see multiple connections getting created for the same instance.
( I varified the process ID of instance and the application establishing connection on database ) .
I did check for orphaned connections. They are none. The 140 connections seen on SQL are when the map services are restarted and there are no applications consuming them.

There may be 3-4 SQL Server connections established for one connection from ArcGIS. This is for performance reasons as it allows operations to occur in parallel. The error message you are seeing about the maxiumum number of connections being reached is from ArcGIS, and not SQL Server (it's connection maxiumum is 32,000+ connections). You can increase the number of geodatabase connections by using the sdeconfig command to update the CONNECTIONS parameter. It defaults to 64. A number larger than this is probably due to ArcGIS server creating additional instances.

-Shannon

PramodHarithsa1 · ‎09-06-2013

There may be 3-4 SQL Server connections established for one connection from ArcGIS. This is for performance reasons as it allows operations to occur in parallel. The error message you are seeing about the maxiumum number of connections being reached is from ArcGIS, and not SQL Server (it's connection maxiumum is 32,000+ connections). You can increase the number of geodatabase connections by using the sdeconfig command to update the CONNECTIONS parameter. It defaults to 64. A number larger than this is probably due to ArcGIS server creating additional instances.

-Shannon

Yes shannon, we were able to resolve the maximum connection issue by altering the connection parameter.
Ya there are about 3 to 4 SQL server connection for every map service. Is there a way we can control them. A few map services are rarely accessed so we don't want their connection to affect the overall performance.

VinceAngelo · ‎09-06-2013

How are you able to conclude that unused services are impacting overall performance?
Have you turned off the underutilized services and seen significant performance gains?

- V

ShannonShields · ‎09-06-2013

No, the number of SQL Server connections launched by an ArcGIS connection is not configurable. An idle connection does not take up a lot of resources - maybe 50K of the memory allocated to connections. Having a few connections that are not being used consistently is probably not going to make or break your server performance.

-Shannon

PramodHarithsa1 · ‎09-12-2013

@Vince/@Shannon : Thanks for the reply.
There is no actual performance issue due to idle connections but it did affect the map services as we had the connection parameter of arcgis set to default value(64). Increasing the connection parameter resolved the issue.
We also found that the minimum and maximum instances in arcgis server were set to same value which resulted in creation of redundant instances even when not required.