Poor performance on Microsoft SQL Server with parallel access by multiple users

Problem:

When two or more users access the same tables and feature classes in the same Microsoft SQL Server database, performance degrades noticeably compared to access by just one user. This is already understandable with simple functions such as moving the map section, but it affects all functions with database access.

Cause:

This behavior is registered as Known Limit at Esri under "#BUG-000132672 Performance decreases while users simultaneously access the same data.". The cause is a Microsoft SQL Server behavior that Esri describes as follows:

This issue is caused by a Microsoft behavior where Cursor + SELECT operations forces some strong name validations and causes mismatch between cached SCHEMAID + current user's default SCHEMAID. A recompilation is triggered based on that mismatch and is the cause of the performance issue.

Workaround:

The behavior occurs when each user has their own schema in Microsoft SQL Server. If all users use a common schema (e.g. DBO or SDE), the behavior does not occur.

Customization in existing installation:

In Microsoft SQL Server Management Studio you can adjust this afterwards.

Customization of the scripts for creating users:

If users are to be created using the scripts provided by UT for ArcGIS, the script UT.create_user.MSSQL.sql has to be adapted. For this purpose, the following lines have to be removed or commented out.

ALTER USER [$(USER_NAME)] WITH DEFAULT_SCHEMA=[$(USER_NAME)];
GO

Customization when using SSO:

When using Single Sign On (SSO), the UT.utusradm_procedures.MSSQL.sql script must be adjusted. The following lines must be removed from the ARCFMUT_CREATE_USER procedure.

 set @CreateStatement = N'ALTER USER [' + @UserName + N'] WITH DEFAULT_SCHEMA=[' + @UserName + N']';
EXEC [$(USER_NAME)].ArcFMUT_EXEC_ANYSQL @CreateStatement;

The procedure changed in this way must then be transferred to the database, either via the script UT.setup_SDE_MSSQL.bat or manually via selcmd.

sqlcmd -S %SQLSERVER% -H %SQLHOST% -d %SQLDB% %SQLADMIN% -i UT.sde_procedures.MSSQL.sql -o %SQLDBPATH%UT.sde_procedures.MSSQL.sql.log -v SQLDB=%SQLDB% SDE_OWNER=%SDE_OWNER% SQLADMIN_USER=%SQLADMIN_USER%