Share all details related to your problem, including any error messages you may have received.
Recently we are noticing issue in our Production environment, where an session getting created in DB(MSSQL) and its going to sleeping state and because of that, all upcoming sessions are not processing and SailPoint is not behaving as expected, none of the tabs which are related to quering were not working(Taskresults, Reports, Advanced Analytics) etc.
From DB side, we collected few data and based on session created time, we couldnt find any relevant tasks or much information.
Requesting support to provide clues or heads-up to troubleshoot this issue further. The issue is intermitent.
The sleep sessions occur when a connection is waiting for the next command from the client after a previous command execution. In your case, these sleep sessions are holding locks. The intermittent nature of the issue suggests it could be related to workload spikes on the VM or the application server.
The Sleeping status is going to occur with an open transaction holding locks between the .SaveEntity calls of the objects completing and the final commit in the code generated code behind for the objects. If the VM/App server is under pressure or load, then this could be delayed and lead to issues with blocking, but the problem isn’t in SQL Server, it is doing exactly what it should within the scope of the transaction. The problem is ultimately the result of the delay in processing the application side commit point.
Moving/dividing the application load across servers in a NLB or hardware load balanced configuration using hosts that aren’t over committed on CPU usage would quickly restore the immediate commit of the transactions and remove the Sleeping sessions holding locks in SQL Server.
Possible resolutions:
Look for any long-running queries or transactions that could be causing locks to be held unnecessarily.
Check MSSQL configuration settings such as lock timeout values, isolation levels, and connection pooling settings. Ensure that these settings are optimized for your application’s workload and resource requirements.
Monitor CPU, memory, and disk usage on both the MSSQL server and the application server running SailPoint. If resource contention is identified as a root cause, consider distributing the workload across multiple servers using load balancing or scaling techniques. This can help alleviate the strain on individual servers and reduce the likelihood of locking issues occurring.