SessionManagementService QuerySessionsAsync SQL performance

[MikeJonesRVU]

Which version of Duende IdentityServer are you using?
7.0.6

Which version of .NET are you using?
.NET 8

Describe the bug

We had an issue recently whereby we were experiencing SQL timeouts when closing accounts in Identity Server. As part of our closure function, we use the session management service to query for all of the server side session related to the identity, which we then iterate through and remove using SessionManagementService.RemoveSessionsAsync method.

Upon investigating the SQL trace, we noticed that when querying user sessions, the resulting SQL is running a LIKE filter, something like

WHERE SubjectId LIKE @param0 AND SessionId LIKE @param1

Because of the LIKE operator, the query is unable to leverage the indexes on the table as it forced to perform a full table scan.

This is coming from the following library code, which is using a "contains" filter. I can't see why it would need to do this for subjectId or sessionId as they are both GUID fields, so a partial match wouldn't be useful functionality.

I was able to work around the issue by instead calling ServerSideTicketStore.GetSessionsAsync directly, which uses an equality check as expected.

https://github.com/DuendeSoftware/IdentityServer/blob/e9860c6488f90e8fbc11a4452b9dd111dbfae933/src/EntityFramework.Storage/Stores/ServerSideSessionStore.cs#L248

To Reproduce
Set up a SQL trace
Call the SessionManagementService.QuerySessionsAsync method with a subjectID or sessionID filter set
Inspect the resulting SQL query

Expected behavior
The resulting query uses an equality filter on the subjectID and sessionID GUID fields.

Log output/exception with stacktrace

{"severityLevel":"Error","outerId":"0","message":"Execution Timeout Expired. The timeout period elapsed prior to completion of the operation or the server is not responding.","type":"Microsoft.Data.SqlClient.SqlException","id":"52969240","parsedStack":[{"assembly":"System.Private.CoreLib, Version=8.0.0.0, Culture=neutral, PublicKeyToken=7cec85d7bea7798e","method":"System.Threading.Tasks.ContinuationResultTaskFromResultTask`2.InnerInvoke","level":0,"line":0},{"assembly":"System.Private.CoreLib, Version=8.0.0.0, Culture=neutral, PublicKeyToken=7cec85d7bea7798e","method":"System.Threading.ExecutionContext.RunInternal","level":1,"line":0},{"assembly":"System.Private.CoreLib, Version=8.0.0.0, Culture=neutral, PublicKeyToken=7cec85d7bea7798e","method":"System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw","level":2,"line":0},{"assembly":"System.Private.CoreLib, Version=8.0.0.0, Culture=neutral, PublicKeyToken=7cec85d7bea7798e","method":"System.Threading.ExecutionContext.RunInternal","level":3,"line":0},{"assembly":"System.Private.CoreLib, Version=8.0.0.0, Culture=neutral, PublicKeyToken=7cec85d7bea7798e","method":"System.Threading.Tasks.Task.ExecuteWithThreadLocal","level":4,"line":0},{"assembly":"System.Private.CoreLib, Version=8.0.0.0, Culture=neutral, PublicKeyToken=7cec85d7bea7798e","method":"System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw","level":5,"line":0},{"assembly":"System.Private.CoreLib, Version=8.0.0.0, Culture=neutral, PublicKeyToken=7cec85d7bea7798e","method":"System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess","level":6,"line":0},{"assembly":"System.Private.CoreLib, Version=8.0.0.0, Culture=neutral, PublicKeyToken=7cec85d7bea7798e","method":"System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification","level":7,"line":0},{"assembly":"Microsoft.EntityFrameworkCore.Relational, Version=8.0.6.0, Culture=neutral, PublicKeyToken=adb9793829ddae60","method":"Microsoft.EntityFrameworkCore.Storage.RelationalCommand+<ExecuteReaderAsync>d__18.MoveNext","level":8,"line":0},{"assembly":"System.Private.CoreLib, Version=8.0.0.0, Culture=neutral, PublicKeyToken=7cec85d7bea7798e","method":"System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw","level":9,"line":0},{"assembly":"Microsoft.EntityFrameworkCore.Relational, Version=8.0.6.0, Culture=neutral, PublicKeyToken=adb9793829ddae60","method":"Microsoft.EntityFrameworkCore.Storage.RelationalCommand+<ExecuteReaderAsync>d__18.MoveNext","level":10,"line":0},{"assembly":"System.Private.CoreLib, Version=8.0.0.0, Culture=neutral, PublicKeyToken=7cec85d7bea7798e","method":"System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw","level":11,"line":0},{"assembly":"System.Private.CoreLib, Version=8.0.0.0, Culture=neutral, PublicKeyToken=7cec85d7bea7798e","method":"System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess","level":12,"line":0},{"assembly":"System.Private.CoreLib, Version=8.0.0.0, Culture=neutral, PublicKeyToken=7cec85d7bea7798e","method":"System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification","level":13,"line":0},{"assembly":"Microsoft.EntityFrameworkCore.Relational, Version=8.0.6.0, Culture=neutral, PublicKeyToken=adb9793829ddae60","method":"Microsoft.EntityFrameworkCore.Query.Internal.SingleQueryingEnumerable`1+AsyncEnumerator+<InitializeReaderAsync>d__21.MoveNext","level":14,"line":0},{"assembly":"System.Private.CoreLib, Version=8.0.0.0, Culture=neutral, PublicKeyToken=7cec85d7bea7798e","method":"System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw","level":15,"line":0},{"assembly":"System.Private.CoreLib, Version=8.0.0.0, Culture=neutral, PublicKeyToken=7cec85d7bea7798e","method":"System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess","level":16,"line":0},{"assembly":"System.Private.CoreLib, Version=8.0.0.0, Culture=neutral, PublicKeyToken=7cec85d7bea7798e","method":"System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification","level":17,"line":0},{"assembly":"Microsoft.EntityFrameworkCore, Version=8.0.6.0, Culture=neutral, PublicKeyToken=adb9793829ddae60","method":"Microsoft.EntityFrameworkCore.Storage.ExecutionStrategy+<>c__DisplayClass30_0`2+<<ExecuteAsync>b__0>d.MoveNext","level":18,"line":0},{"assembly":"System.Private.CoreLib, Version=8.0.0.0, Culture=neutral, PublicKeyToken=7cec85d7bea7798e","method":"System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw","level":19,"line":0},{"assembly":"System.Private.CoreLib, Version=8.0.0.0, Culture=neutral, PublicKeyToken=7cec85d7bea7798e","method":"System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess","level":20,"line":0},{"assembly":"System.Private.CoreLib, Version=8.0.0.0, Culture=neutral, PublicKeyToken=7cec85d7bea7798e","method":"System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification","level":21,"line":0},{"assembly":"Microsoft.EntityFrameworkCore, Version=8.0.6.0, Culture=neutral, PublicKeyToken=adb9793829ddae60","method":"Microsoft.EntityFrameworkCore.Storage.ExecutionStrategy+<ExecuteImplementationAsync>d__31`2.MoveNext","level":22,"line":0},{"assembly":"System.Private.CoreLib, Version=8.0.0.0, Culture=neutral, PublicKeyToken=7cec85d7bea7798e","method":"System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw","level":23,"line":0},{"assembly":"Microsoft.EntityFrameworkCore, Version=8.0.6.0, Culture=neutral, PublicKeyToken=adb9793829ddae60","method":"Microsoft.EntityFrameworkCore.Storage.ExecutionStrategy+<ExecuteImplementationAsync>d__31`2.MoveNext","level":24,"line":0},{"assembly":"System.Private.CoreLib, Version=8.0.0.0, Culture=neutral, PublicKeyToken=7cec85d7bea7798e","method":"System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw","level":25,"line":0},{"assembly":"System.Private.CoreLib, Version=8.0.0.0, Culture=neutral, PublicKeyToken=7cec85d7bea7798e","method":"System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess","level":26,"line":0},{"assembly":"System.Private.CoreLib, Version=8.0.0.0, Culture=neutral, PublicKeyToken=7cec85d7bea7798e","method":"System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification","level":27,"line":0},{"assembly":"Microsoft.EntityFrameworkCore, Version=8.0.6.0, Culture=neutral, PublicKeyToken=adb9793829ddae60","method":"Microsoft.EntityFrameworkCore.Storage.ExecutionStrategy+<ExecuteAsync>d__30`2.MoveNext","level":28,"line":0},{"assembly":"System.Private.CoreLib, Version=8.0.0.0, Culture=neutral, PublicKeyToken=7cec85d7bea7798e","method":"System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw","level":29,"line":0},{"assembly":"System.Private.CoreLib, Version=8.0.0.0, Culture=neutral, PublicKeyToken=7cec85d7bea7798e","method":"System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess","level":30,"line":0},{"assembly":"System.Private.CoreLib, Version=8.0.0.0, Culture=neutral, PublicKeyToken=7cec85d7bea7798e","method":"System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification","level":31,"line":0},{"assembly":"Microsoft.EntityFrameworkCore.Relational, Version=8.0.6.0, Culture=neutral, PublicKeyToken=adb9793829ddae60","method":"Microsoft.EntityFrameworkCore.Query.Internal.SingleQueryingEnumerable`1+AsyncEnumerator+<MoveNextAsync>d__20.MoveNext","level":32,"line":0},{"assembly":"System.Private.CoreLib, Version=8.0.0.0, Culture=neutral, PublicKeyToken=7cec85d7bea7798e","method":"System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw","level":33,"line":0},{"assembly":"System.Private.CoreLib, Version=8.0.0.0, Culture=neutral, PublicKeyToken=7cec85d7bea7798e","method":"System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess","level":34,"line":0},{"assembly":"System.Private.CoreLib, Version=8.0.0.0, Culture=neutral, PublicKeyToken=7cec85d7bea7798e","method":"System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification","level":35,"line":0},{"assembly":"Microsoft.EntityFrameworkCore, Version=8.0.6.0, Culture=neutral, PublicKeyToken=adb9793829ddae60","method":"Microsoft.EntityFrameworkCore.EntityFrameworkQueryableExtensions+<ToListAsync>d__67`1.MoveNext","level":36,"line":0},{"assembly":"System.Private.CoreLib, Version=8.0.0.0, Culture=neutral, PublicKeyToken=7cec85d7bea7798e","method":"System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw","level":37,"line":0},{"assembly":"Microsoft.EntityFrameworkCore, Version=8.0.6.0, Culture=neutral, PublicKeyToken=adb9793829ddae60","method":"Microsoft.EntityFrameworkCore.EntityFrameworkQueryableExtensions+<ToListAsync>d__67`1.MoveNext","level":38,"line":0},{"assembly":"System.Private.CoreLib, Version=8.0.0.0, Culture=neutral, PublicKeyToken=7cec85d7bea7798e","method":"System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw","level":39,"line":0},{"assembly":"System.Private.CoreLib, Version=8.0.0.0, Culture=neutral, PublicKeyToken=7cec85d7bea7798e","method":"System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess","level":40,"line":0},{"assembly":"System.Private.CoreLib, Version=8.0.0.0, Culture=neutral, PublicKeyToken=7cec85d7bea7798e","method":"System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification","level":41,"line":0},{"assembly":"Microsoft.EntityFrameworkCore, Version=8.0.6.0, Culture=neutral, PublicKeyToken=adb9793829ddae60","method":"Microsoft.EntityFrameworkCore.EntityFrameworkQueryableExtensions+<ToArrayAsync>d__68`1.MoveNext","level":42,"line":0},{"assembly":"System.Private.CoreLib, Version=8.0.0.0, Culture=neutral, PublicKeyToken=7cec85d7bea7798e","method":"System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw","level":43,"line":0},{"assembly":"System.Private.CoreLib, Version=8.0.0.0, Culture=neutral, PublicKeyToken=7cec85d7bea7798e","method":"System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess","level":44,"line":0},{"assembly":"System.Private.CoreLib, Version=8.0.0.0, Culture=neutral, PublicKeyToken=7cec85d7bea7798e","method":"System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification","level":45,"line":0},{"assembly":"System.Private.CoreLib, Version=8.0.0.0, Culture=neutral, PublicKeyToken=7cec85d7bea7798e","method":"System.Runtime.CompilerServices.TaskAwaiter`1.GetResult","level":46,"line":0},{"assembly":"Duende.IdentityServer.EntityFramework.Storage, Version=7.0.6.0, Culture=neutral, PublicKeyToken=null","method":"Duende.IdentityServer.EntityFramework.Stores.ServerSideSessionStore+<NextPage>d__15.MoveNext","level":47,"line":413,"fileName":"/_/src/EntityFramework.Storage/Stores/ServerSideSessionStore.cs"},{"assembly":"System.Private.CoreLib, Version=8.0.0.0, Culture=neutral, PublicKeyToken=7cec85d7bea7798e","method":"System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw","level":48,"line":0},{"assembly":"System.Private.CoreLib, Version=8.0.0.0, Culture=neutral, PublicKeyToken=7cec85d7bea7798e","method":"System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess","level":49,"line":0},{"assembly":"System.Private.CoreLib, Version=8.0.0.0, Culture=neutral, PublicKeyToken=7cec85d7bea7798e","method":"System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification","level":50,"line":0},{"assembly":"Duende.IdentityServer.EntityFramework.Storage, Version=7.0.6.0, Culture=neutral, PublicKeyToken=null","method":"Duende.IdentityServer.EntityFramework.Stores.ServerSideSessionStore+<QuerySessionsAsync>d__12.MoveNext","level":51,"line":347,"fileName":"/_/src/EntityFramework.Storage/Stores/ServerSideSessionStore.cs"},{"assembly":"System.Private.CoreLib, Version=8.0.0.0, Culture=neutral, PublicKeyToken=7cec85d7bea7798e","method":"System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw","level":52,"line":0},{"assembly":"System.Private.CoreLib, Version=8.0.0.0, Culture=neutral, PublicKeyToken=7cec85d7bea7798e","method":"System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess","level":53,"line":0},{"assembly":"System.Private.CoreLib, Version=8.0.0.0, Culture=neutral, PublicKeyToken=7cec85d7bea7798e","method":"System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification","level":54,"line":0},{"assembly":"System.Private.CoreLib, Version=8.0.0.0, Culture=neutral, PublicKeyToken=7cec85d7bea7798e","method":"System.Runtime.CompilerServices.TaskAwaiter`1.GetResult","level":55,"line":0},{"assembly":"Duende.IdentityServer, Version=7.0.6.0, Culture=neutral, PublicKeyToken=null","method":"Duende.IdentityServer.Stores.ServerSideTicketStore+<QuerySessionsAsync>d__12.MoveNext","level":56,"line":190,"fileName":"/_/src/IdentityServer/Stores/Default/ServerSideTicketStore.cs"},{"assembly":"System.Private.CoreLib, Version=8.0.0.0, Culture=neutral, PublicKeyToken=7cec85d7bea7798e","method":"System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw","level":57,"line":0},{"assembly":"System.Private.CoreLib, Version=8.0.0.0, Culture=neutral, PublicKeyToken=7cec85d7bea7798e","method":"System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess","level":58,"line":0},{"assembly":"System.Private.CoreLib, Version=8.0.0.0, Culture=neutral, PublicKeyToken=7cec85d7bea7798e","method":"System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification","level":59,"line":0},{"assembly":"System.Private.CoreLib, Version=8.0.0.0, Culture=neutral, PublicKeyToken=7cec85d7bea7798e","method":"System.Runtime.CompilerServices.TaskAwaiter`1.GetResult","level":60,"line":0},{"assembly":"REDACTED, Version=1.0.0.0, Culture=neutral, PublicKeyToken=null","method":"REDACTED+<RemoveSessions>d__8.MoveNext","level":61,"line":68,"fileName":"/home/vsts/work/1/s/src/REDACTED/SessionManagement/SessionManagementServiceFacade.cs"}]}

Additional context
N/A

[RolandGuijt]

The code you're referring to was written to accommodate searches from the UI. ApplyFilter is called from QuerySessionsAsync which is ultimately called from the UI. We used Contains to support partial matches as users type and we chose convenience over performance in this case.
Good that you found a more suitable method for your needs.
And thanks for reporting this: we're now discussing internally if this behavior should change. If we decide to do that I will link to the issue here.

[RolandGuijt]

@MikeJonesRVU Would you like to add anything? If not I'd like to close the issue.