AI OnAI Off
Intermittent performance issues and database deadlocks
Machine specs:
The server is a Windows Server 2012 R2
The database server is an Sql Server 2008 (SP4), not officially supported from EPiServer 9 and up, but by looking at the server logs from before upgrading to EPi 9 we can see that the intermittent long response times were an issue even before upgrading. Sadly though, we kept no server logs before the performance issues began, so we can´t connect the performance issues to a particular commit in our version control.
Blobs are stored on the same server/drive as the website.
Feb 24, 2017 13:47
Deadlock error in error log:
ERROR EPiServer.Data.Providers.SqlTransientErrorsRetryPolicy: Failing because of transient error: Transaction (Process ID 58) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.
System.Data.SqlClient.SqlException (0x80131904): Transaction (Process ID 58) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.
at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose)
at System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady)
at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString)
at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async, Int32 timeout, Task& task, Boolean asyncWrite, SqlDataReader ds, Boolean describeParameterEncryptionRequest)
at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, TaskCompletionSource`1 completion, Int32 timeout, Task& task, Boolean asyncWrite)
at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(TaskCompletionSource`1 completion, String methodName, Boolean sendToPipe, Int32 timeout, Boolean asyncWrite)
at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()
at EPiServer.Data.Providers.SqlDatabaseHandler.<>c__DisplayClass29_0.<ExecuteTransaction>b__0()
at EPiServer.Data.Providers.SqlDatabaseHandler.<>c__DisplayClass30_0`1.<ExecuteTransaction>b__0()
at EPiServer.Data.Providers.SqlTransientErrorsRetryPolicy.Execute[TResult](Func`1 method)
ClientConnectionId:b4374fbe-f419-4b50-b792-75f055576822
Error Number:1205,State:51,Class:13
System.Data.SqlClient.SqlException (0x80131904): Transaction (Process ID 58) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.
at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose)
at System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady)
at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString)
at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async, Int32 timeout, Task& task, Boolean asyncWrite, SqlDataReader ds, Boolean describeParameterEncryptionRequest)
at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, TaskCompletionSource`1 completion, Int32 timeout, Task& task, Boolean asyncWrite)
at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(TaskCompletionSource`1 completion, String methodName, Boolean sendToPipe, Int32 timeout, Boolean asyncWrite)
at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()
at EPiServer.Data.Providers.SqlDatabaseHandler.<>c__DisplayClass29_0.<ExecuteTransaction>b__0()
at EPiServer.Data.Providers.SqlDatabaseHandler.<>c__DisplayClass30_0`1.<ExecuteTransaction>b__0()
at EPiServer.Data.Providers.SqlTransientErrorsRetryPolicy.Execute[TResult](Func`1 method)
ClientConnectionId:b4374fbe-f419-4b50-b792-75f055576822
Error Number:1205,State:51,Class:13
2017-02-23 08:42:16,774 [30] ERROR EPiServer.Global: 1.2.5 Unhandled exception in ASP.NET
System.Data.DataException: A transient error occured in the database: 'Transaction (Process ID 58) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.', retried 5 times before giving up ---> System.Data.SqlClient.SqlException: Transaction (Process ID 58) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.
at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose)
at System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady)
at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString)
at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async, Int32 timeout, Task& task, Boolean asyncWrite, SqlDataReader ds, Boolean describeParameterEncryptionRequest)
at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, TaskCompletionSource`1 completion, Int32 timeout, Task& task, Boolean asyncWrite)
at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(TaskCompletionSource`1 completion, String methodName, Boolean sendToPipe, Int32 timeout, Boolean asyncWrite)
at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()
at EPiServer.Data.Providers.SqlDatabaseHandler.<>c__DisplayClass29_0.<ExecuteTransaction>b__0()
at EPiServer.Data.Providers.SqlDatabaseHandler.<>c__DisplayClass30_0`1.<ExecuteTransaction>b__0()
at EPiServer.Data.Providers.SqlTransientErrorsRetryPolicy.Execute[TResult](Func`1 method)
--- End of inner exception stack trace ---
at EPiServer.Data.Providers.SqlTransientErrorsRetryPolicy.Execute[TResult](Func`1 method)
at EPiServer.Security.SynchronizingUserService.<>c__DisplayClass25_0.<SynchronizeUserAndClaims>b__0()
at EPiServer.Security.WindowsRoleProvider.GetRolesForUser(String username)
at System.Web.Security.RolePrincipal.GetRoles()
at System.Web.Security.RoleClaimProvider.<get_Claims>d__4.MoveNext()
at System.Security.Claims.ClaimsIdentity.<get_Claims>d__51.MoveNext()
at System.Security.Principal.WindowsIdentity.<get_Claims>d__95.MoveNext()
at System.Security.Claims.ClaimsIdentity.HasClaim(String type, String value)
at System.Security.Claims.ClaimsPrincipal.IsInRole(String role)
at EPiServer.Security.VirtualRolePrincipal.IsInRole(String role, SecurityEntityType type, Object context)
at EPiServer.Security.VirtualRolePrincipal.IsInRole(String role)
at System.Web.Configuration.AuthorizationRule.IsTheUserInAnyRole(StringCollection roles, IPrincipal principal)
at System.Web.Configuration.AuthorizationRule.IsUserAllowed(IPrincipal user, String verb)
at System.Web.Configuration.AuthorizationRuleCollection.IsUserAllowed(IPrincipal user, String verb)
at System.Web.Security.UrlAuthorizationModule.CheckUrlAccessForPrincipal(String virtualPath, IPrincipal user, String verb)
at EPiServer.Security.PrincipalInfo.HasPathAccess(String path)
at EPiServer.Web.DisplayChannelService.GetActiveChannels(HttpContextBase context)
at EPiServer.Web.TemplateResolverImplementation.ResolveCore(HttpContextBase httpContext, ContentType contentType, Type itemType, Object itemToRender, TemplateTypeCategories category, String tag)
at EPiServer.Web.TemplateResolver.Resolve(HttpContextBase httpContext, Object itemToRender, TemplateTypeCategories templateTypeCategory, ContextMode contextMode)
at EPiServer.Web.Mvc.ExistingActionRouteConstraint.Match(Route route, SegmentContext routingContext, String parameterName)
at EPiServer.Web.Routing.ContentRoute.MatchConstraints(SegmentContext segmentContext, HttpContextBase context)
at EPiServer.Web.Routing.ContentRoute.GetRouteData(HttpContextBase httpContext)
at System.Web.Routing.RouteCollection.GetRouteData(HttpContextBase httpContext)
at EPiServer.Web.Routing.RouteCollectionExtensions.HandleRouteData(RouteCollection routes, HttpContextBase context)
at EPiServer.Global.DefaultDocumentHandling(Object sender, EventArgs e)
at System.Web.HttpApplication.SyncEventExecutionStep.System.Web.HttpApplication.IExecutionStep.Execute()
at System.Web.HttpApplication.ExecuteStep(IExecutionStep step, Boolean& completedSynchronously)
System.Data.DataException: A transient error occured in the database: 'Transaction (Process ID 58) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.', retried 5 times before giving up ---> System.Data.SqlClient.SqlException: Transaction (Process ID 58) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.
at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose)
at System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady)
at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString)
at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async, Int32 timeout, Task& task, Boolean asyncWrite, SqlDataReader ds, Boolean describeParameterEncryptionRequest)
at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, TaskCompletionSource`1 completion, Int32 timeout, Task& task, Boolean asyncWrite)
at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(TaskCompletionSource`1 completion, String methodName, Boolean sendToPipe, Int32 timeout, Boolean asyncWrite)
at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()
at EPiServer.Data.Providers.SqlDatabaseHandler.<>c__DisplayClass29_0.<ExecuteTransaction>b__0()
at EPiServer.Data.Providers.SqlDatabaseHandler.<>c__DisplayClass30_0`1.<ExecuteTransaction>b__0()
at EPiServer.Data.Providers.SqlTransientErrorsRetryPolicy.Execute[TResult](Func`1 method)
--- End of inner exception stack trace ---
at EPiServer.Data.Providers.SqlTransientErrorsRetryPolicy.Execute[TResult](Func`1 method)
at EPiServer.Security.SynchronizingUserService.<>c__DisplayClass25_0.<SynchronizeUserAndClaims>b__0()
at EPiServer.Security.WindowsRoleProvider.GetRolesForUser(String username)
at System.Web.Security.RolePrincipal.GetRoles()
at System.Web.Security.RoleClaimProvider.<get_Claims>d__4.MoveNext()
at System.Security.Claims.ClaimsIdentity.<get_Claims>d__51.MoveNext()
at System.Security.Principal.WindowsIdentity.<get_Claims>d__95.MoveNext()
at System.Security.Claims.ClaimsIdentity.HasClaim(String type, String value)
at System.Security.Claims.ClaimsPrincipal.IsInRole(String role)
at EPiServer.Security.VirtualRolePrincipal.IsInRole(String role, SecurityEntityType type, Object context)
at EPiServer.Security.VirtualRolePrincipal.IsInRole(String role)
at System.Web.Configuration.AuthorizationRule.IsTheUserInAnyRole(StringCollection roles, IPrincipal principal)
at System.Web.Configuration.AuthorizationRule.IsUserAllowed(IPrincipal user, String verb)
at System.Web.Configuration.AuthorizationRuleCollection.IsUserAllowed(IPrincipal user, String verb)
at System.Web.Security.UrlAuthorizationModule.CheckUrlAccessForPrincipal(String virtualPath, IPrincipal user, String verb)
at EPiServer.Security.PrincipalInfo.HasPathAccess(String path)
at EPiServer.Web.DisplayChannelService.GetActiveChannels(HttpContextBase context)
at EPiServer.Web.TemplateResolverImplementation.ResolveCore(HttpContextBase httpContext, ContentType contentType, Type itemType, Object itemToRender, TemplateTypeCategories category, String tag)
at EPiServer.Web.TemplateResolver.Resolve(HttpContextBase httpContext, Object itemToRender, TemplateTypeCategories templateTypeCategory, ContextMode contextMode)
at EPiServer.Web.Mvc.ExistingActionRouteConstraint.Match(Route route, SegmentContext routingContext, String parameterName)
at EPiServer.Web.Routing.ContentRoute.MatchConstraints(SegmentContext segmentContext, HttpContextBase context)
at EPiServer.Web.Routing.ContentRoute.GetRouteData(HttpContextBase httpContext)
at System.Web.Routing.RouteCollection.GetRouteData(HttpContextBase httpContext)
at EPiServer.Web.Routing.RouteCollectionExtensions.HandleRouteData(RouteCollection routes, HttpContextBase context)
at EPiServer.Global.DefaultDocumentHandling(Object sender, EventArgs e)
at System.Web.HttpApplication.SyncEventExecutionStep.System.Web.HttpApplication.IExecutionStep.Execute()
at System.Web.HttpApplication.ExecuteStep(IExecutionStep step, Boolean& completedSynchronously)
Edited,
Feb 24, 2017 13:53
So this was actually a bug in EPiServer:
http://world.episerver.com/support/bug-list/bug/CMS-4795
(the spelling of netSynchedUserRoleUpdate is incorrect in the bug report, so it doesn´t show when googling the stored procedure)
Apr 25, 2017 12:32
I have corrected the sproc name in the bug now (might take a few hours before the update is synced to Episerver World). Thanks for pointing it out!
Apr 25, 2017 12:55
This topic was created over six months ago and has been resolved. If you have a similar question, please create a new topic and refer to this one.
First of all, I would like to apologize for the amount of text below :)
Where I work, we have an internal website running EPiServer 9.7.3.0 (originally developed for EPiServer 7) that has been experiencing performance issues even with a low number of simultaneous users.
The long reponse times relate to both slow initial page loads and random media on a page loading slowly. Most of the time the website renders just fine, but every so often, delays anywhere between two and thirty seconds are seen.
The requests that pile up on the IIS seem to be in whichever state but mostly AuthorizeRequest and ResolveRequestCache.
My initial thought was that this somehow related to the way .NET handles session state, however, disabling session state did not improve performance.
In the error log we started seeing database deadlocks. These are not as regularly occurring as the performance issues, but I figured they might be signs of database blocks not resolving in due time. So I tried running this script:
https://blog.sqlauthority.com/2010/10/06/sql-server-quickest-way-to-identify-blocking-query-and-resolution-dirty-solution/
This led me to believe that there is a blocking on the calls to the table tblSynchedUserRole which contains all our active directory groups. The stored procedure is netSynchedUserRoleUpdate.
We have roughly 8000 active directory groups/EPiServer roles.
Our default role/membership providers are WindowsMembershipProvider and WindowsRoleProvider.
I do not know if it is connected, but we do quite a lot of checks and updates against the user´s EPiServer Profile, which contains a number of additional properties.
Any thoughts on what might be causing this behavior are greatly appreciated!
/Daniel