I was having performance issues with the SQL Monitor Base monitor, so I had the resources on the base monitor server increased. Now the base monitor is frequently timing out.
The significant part of the error message I am receiving is:
"The timeout period elapsed prior to obtaining a connection from the pool. This may have occurred because all pooled connections were in use and max pool size was reached."
I have many database instances that this base monitor is connecting to, so beyond the obvious answer of scaling out with another base monitor and repository, are there any configuration options to increase these values.
Here is full message:
/OverviewSideNavigation: 500 Internal Server Error
#5jv.#mkv: NHibernate.ADOException was thrown by method GroupedQuery on service DataPresenterService:
NHibernate.ADOException: While preparing SELECT machines0_.[GroupId] as GroupId1_1_, machines0_.[ClusterId] as ClusterId2_1_, cluster1_.[Id] as Id1_0_0_, cluster1_.[CreatedDate] as CreatedD2_0_0_, cluster1_.[ModifiedDate] as Modified3_0_0_, cluster1_.[IsValid] as IsValid4_0_0_, cluster1_.[IsSuspended] as IsSuspen5_0_0_, cluster1_.[CredentialsDiscriminator] as Credenti6_0_0_, cluster1_.[User] as User7_0_0_, cluster1_.[Domain] as Domain8_0_0_, cluster1_.[Password] as Password9_0_0_, cluster1_.[Name] as Name10_0_0_, cluster1_.[IsCluster] as IsCluster11_0_0_, cluster1_.[IsAddressDetected] as IsAddre12_0_0_, cluster1_.[NodeCount] as NodeCount13_0_0_, cluster1_.[RequestedLicenceLevel] as Request14_0_0_, cluster1_.[EffectiveLicenceLevel] as Effecti15_0_0_, cluster1_.[MW_IsEnabled] as MW16_0_0_, cluster1_.[MW_Start] as MW17_0_0_, cluster1_.[MW_Duration] as MW18_0_0_, cluster1_.[MW_Monday] as MW19_0_0_, cluster1_.[MW_Tuesday] as MW20_0_0_, cluster1_.[MW_Wednesday] as MW21_0_0_, cluster1_.[MW_Thursday] as MW22_0_0_, cluster1_.[MW_Friday] as MW23_0_0_, cluster1_.[MW_Saturday] as MW24_0_0_, cluster1_.[MW_Sunday] as MW25_0_0_ FROM settings.[GroupMachines] machines0_ left outer join settings.[Clusters] cluster1_ on machines0_.[ClusterId]=cluster1_.[Id] WHERE machines0_.[GroupId]=@p0 an error occurred ---> System.InvalidOperationException: Timeout expired. The timeout period elapsed prior to obtaining a connection from the pool. This may have occurred because all pooled connections were in use and max pool size was reached.
at System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnection owningConnection)
at System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory)
at System.Data.SqlClient.SqlConnection.Open()
at NHibernate.Connection.DriverConnectionProvider.GetConnection()
at NHibernate.AdoNet.ConnectionManager.GetConnection()
at NHibernate.AdoNet.AbstractBatcher.Prepare(IDbCommand cmd)
--- End of inner exception stack trace ---
at NHibernate.AdoNet.AbstractBatcher.Prepare(IDbCommand cmd)
at NHibernate.AdoNet.AbstractBatcher.ExecuteReader(IDbCommand cmd)
at NHibernate.Loader.Loader.GetResultSet(IDbCommand st, Boolean autoDiscoverTypes, Boolean callable, RowSelection selection, ISessionImplementor session)
at NHibernate.Loader.Loader.DoQuery(ISessionImplementor session, QueryParameters queryParameters, Boolean returnProxies)
at NHibernate.Loader.Loader.DoQueryAndInitializeNonLazyCollections(ISessionImplementor session, QueryParameters queryParameters, Boolean returnProxies)
at NHibernate.Loader.Loader.LoadCollection(ISessionImplementor session, Object id, IType type)
at NHibernate.Persister.Collection.AbstractCollectionPersister.Initialize(Object key, ISessionImplementor session)
at NHibernate.Event.Default.DefaultInitializeCollectionEventListener.OnInitializeCollection(InitializeCollectionEvent event)
at NHibernate.Impl.SessionImpl.InitializeCollection(IPersistentCollection collection, Boolean writing)
at NHibernate.Collection.AbstractPersistentCollection.Initialize(Boolean writing)
at NHibernate.Collection.Generic.PersistentGenericSet`1.System.Collections.Generic.IEnumerable<T>.GetEnumerator()
at System.Linq.Enumerable.WhereSelectEnumerableIterator`2.MoveNext()
at System.Collections.Generic.List`1.InsertRange(Int32 index, IEnumerable`1 collection)
at System.Collections.Generic.List`1.AddRange(IEnumerable`1 collection)
at #hiv.#KXK..ctor(IEnumerable`1 )
at #Zmv.#Vnv.GetGroups()
at #Zmv.#jg6c.#9Jw(#CJv , #VJw )
at #Zmv.#jg6c.#9Jw(#CJv )
at #Zmv.#Vnv.#9Jw(#CJv )
at #Zmv.#6Jw.GroupedQuery(#Kru , #Aru , #hjv , #hjv )
at #Zmv.#6Jw.GroupedQuery(#Kru , #Aru , #hjv )
at #Mlv.#bmv.GroupedQuery(ChannelTreeMessage`1 )
at #Zjv.#jkv.#cxv(String , MethodInfo , Object[] )
at RedGate.Response.Common.Networking.Client.RpcSynchronousTcpChannel.#cxv(String , MethodInfo , Object[] )
at RedGate.Response.Common.Networking.Client.RpcProxyGenerator.#lkv.Intercept(IInvocation )
at Castle.DynamicProxy.AbstractInvocation.Proceed()
at Castle.Proxies.IDataPresenterServiceProxy.GroupedQuery(ChannelTreeMessage`1 rootQueryTreeMessage)
at #Mlv.#1lv.GroupedQuery(Unit , IStatusLogger , #hjv )
at RedGate.Response.UI.Website.Controllers.OverviewSideNavigationController.Index(Nullable`1 date, String clusterName, String machineName, String sqlServerName, String groupName)
at lambda_method(ExecutionScope , ControllerBase , Object[] )
at System.Web.Mvc.ActionMethodDispatcher.Execute(ControllerBase controller, Object[] parameters)
at System.Web.Mvc.ReflectedActionDescriptor.Execute(ControllerContext controllerContext, IDictionary`2 parameters)
at System.Web.Mvc.ControllerActionInvoker.InvokeActionMethod(ControllerContext controllerContext, ActionDescriptor actionDescriptor, IDictionary`2 parameters)
at System.Web.Mvc.ControllerActionInvoker.<>c__DisplayClassd.<InvokeActionMethodWithFilters>b__a()
at System.Web.Mvc.ControllerActionInvoker.InvokeActionMethodFilter(IActionFilter filter, ActionExecutingContext preContext, Func`1 continuation)
at System.Web.Mvc.ControllerActionInvoker.<>c__DisplayClassd.<>c__DisplayClassf.<InvokeActionMethodWithFilters>b__c()
at System.Web.Mvc.ControllerActionInvoker.InvokeActionMethodFilter(IActionFilter filter, ActionExecutingContext preContext, Func`1 continuation)
at System.Web.Mvc.ControllerActionInvoker.<>c__DisplayClassd.<>c__DisplayClassf.<InvokeActionMethodWithFilters>b__c()
at System.Web.Mvc.ControllerActionInvoker.InvokeActionMethodFilter(IActionFilter filter, ActionExecutingContext preContext, Func`1 continuation)
at System.Web.Mvc.ControllerActionInvoker.<>c__DisplayClassd.<>c__DisplayClassf.<InvokeActionMethodWithFilters>b__c()
at System.Web.Mvc.ControllerActionInvoker.InvokeActionMethodFilter(IActionFilter filter, ActionExecutingContext preContext, Func`1 continuation)
at System.Web.Mvc.ControllerActionInvoker.<>c__DisplayClassd.<>c__DisplayClassf.<InvokeActionMethodWithFilters>b__c()
at System.Web.Mvc.ControllerActionInvoker.InvokeActionMethodFilter(IActionFilter filter, ActionExecutingContext preContext, Func`1 continuation)
at System.Web.Mvc.ControllerActionInvoker.<>c__DisplayClassd.<>c__DisplayClassf.<InvokeActionMethodWithFilters>b__c()
at System.Web.Mvc.ControllerActionInvoker.InvokeActionMethodWithFilters(ControllerContext controllerContext, IList`1 filters, ActionDescriptor actionDescriptor, IDictionary`2 parameters)
at System.Web.Mvc.ControllerActionInvoker.InvokeAction(ControllerContext controllerContext, String actionName)
at System.Web.Mvc.Controller.ExecuteCore()
at System.Web.Mvc.ControllerBase.Execute(RequestContext requestContext)
at System.Web.Mvc.ControllerBase.System.Web.Mvc.IController.Execute(RequestContext requestContext)
at System.Web.Mvc.MvcHandler.<>c__DisplayClass8.<BeginProcessRequest>b__4()
at System.Web.Mvc.Async.AsyncResultWrapper.<>c__DisplayClass1.<MakeVoidDelegate>b__0()
at System.Web.Mvc.Async.AsyncResultWrapper.<>c__DisplayClass8`1.<BeginSynchronous>b__7(IAsyncResult _)
at System.Web.Mvc.Async.AsyncResultWrapper.WrappedAsyncResult`1.End()
at System.Web.Mvc.MvcHandler.EndProcessRequest(IAsyncResult asyncResult)
at System.Web.Mvc.MvcHandler.System.Web.IHttpAsyncHandler.EndProcessRequest(IAsyncResult result)
at System.Web.HttpApplication.CallHandlerExecutionStep.System.Web.HttpApplication.IExecutionStep.Execute()
at System.Web.HttpApplication.ExecuteStep(IExecutionStep step, Boolean& completedSynchronously)
The connection pool is a finite number of connections that the SQL Server instance is allowed to handle. In some cases these connections can all be used up, either because they are all activate, or because they have not been correctly released and returned to the pool.
The significant part of the error message I am receiving is:
"The timeout period elapsed prior to obtaining a connection from the pool. This may have occurred because all pooled connections were in use and max pool size was reached."
I have many database instances that this base monitor is connecting to, so beyond the obvious answer of scaling out with another base monitor and repository, are there any configuration options to increase these values.
Here is full message:
/OverviewSideNavigation: 500 Internal Server Error
#5jv.#mkv: NHibernate.ADOException was thrown by method GroupedQuery on service DataPresenterService:
NHibernate.ADOException: While preparing SELECT machines0_.[GroupId] as GroupId1_1_, machines0_.[ClusterId] as ClusterId2_1_, cluster1_.[Id] as Id1_0_0_, cluster1_.[CreatedDate] as CreatedD2_0_0_, cluster1_.[ModifiedDate] as Modified3_0_0_, cluster1_.[IsValid] as IsValid4_0_0_, cluster1_.[IsSuspended] as IsSuspen5_0_0_, cluster1_.[CredentialsDiscriminator] as Credenti6_0_0_, cluster1_.[User] as User7_0_0_, cluster1_.[Domain] as Domain8_0_0_, cluster1_.[Password] as Password9_0_0_, cluster1_.[Name] as Name10_0_0_, cluster1_.[IsCluster] as IsCluster11_0_0_, cluster1_.[IsAddressDetected] as IsAddre12_0_0_, cluster1_.[NodeCount] as NodeCount13_0_0_, cluster1_.[RequestedLicenceLevel] as Request14_0_0_, cluster1_.[EffectiveLicenceLevel] as Effecti15_0_0_, cluster1_.[MW_IsEnabled] as MW16_0_0_, cluster1_.[MW_Start] as MW17_0_0_, cluster1_.[MW_Duration] as MW18_0_0_, cluster1_.[MW_Monday] as MW19_0_0_, cluster1_.[MW_Tuesday] as MW20_0_0_, cluster1_.[MW_Wednesday] as MW21_0_0_, cluster1_.[MW_Thursday] as MW22_0_0_, cluster1_.[MW_Friday] as MW23_0_0_, cluster1_.[MW_Saturday] as MW24_0_0_, cluster1_.[MW_Sunday] as MW25_0_0_ FROM settings.[GroupMachines] machines0_ left outer join settings.[Clusters] cluster1_ on machines0_.[ClusterId]=cluster1_.[Id] WHERE machines0_.[GroupId]=@p0 an error occurred ---> System.InvalidOperationException: Timeout expired. The timeout period elapsed prior to obtaining a connection from the pool. This may have occurred because all pooled connections were in use and max pool size was reached.
at System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnection owningConnection)
at System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory)
at System.Data.SqlClient.SqlConnection.Open()
at NHibernate.Connection.DriverConnectionProvider.GetConnection()
at NHibernate.AdoNet.ConnectionManager.GetConnection()
at NHibernate.AdoNet.AbstractBatcher.Prepare(IDbCommand cmd)
--- End of inner exception stack trace ---
at NHibernate.AdoNet.AbstractBatcher.Prepare(IDbCommand cmd)
at NHibernate.AdoNet.AbstractBatcher.ExecuteReader(IDbCommand cmd)
at NHibernate.Loader.Loader.GetResultSet(IDbCommand st, Boolean autoDiscoverTypes, Boolean callable, RowSelection selection, ISessionImplementor session)
at NHibernate.Loader.Loader.DoQuery(ISessionImplementor session, QueryParameters queryParameters, Boolean returnProxies)
at NHibernate.Loader.Loader.DoQueryAndInitializeNonLazyCollections(ISessionImplementor session, QueryParameters queryParameters, Boolean returnProxies)
at NHibernate.Loader.Loader.LoadCollection(ISessionImplementor session, Object id, IType type)
at NHibernate.Persister.Collection.AbstractCollectionPersister.Initialize(Object key, ISessionImplementor session)
at NHibernate.Event.Default.DefaultInitializeCollectionEventListener.OnInitializeCollection(InitializeCollectionEvent event)
at NHibernate.Impl.SessionImpl.InitializeCollection(IPersistentCollection collection, Boolean writing)
at NHibernate.Collection.AbstractPersistentCollection.Initialize(Boolean writing)
at NHibernate.Collection.Generic.PersistentGenericSet`1.System.Collections.Generic.IEnumerable<T>.GetEnumerator()
at System.Linq.Enumerable.WhereSelectEnumerableIterator`2.MoveNext()
at System.Collections.Generic.List`1.InsertRange(Int32 index, IEnumerable`1 collection)
at System.Collections.Generic.List`1.AddRange(IEnumerable`1 collection)
at #hiv.#KXK..ctor(IEnumerable`1 )
at #Zmv.#Vnv.GetGroups()
at #Zmv.#jg6c.#9Jw(#CJv , #VJw )
at #Zmv.#jg6c.#9Jw(#CJv )
at #Zmv.#Vnv.#9Jw(#CJv )
at #Zmv.#6Jw.GroupedQuery(#Kru , #Aru , #hjv , #hjv )
at #Zmv.#6Jw.GroupedQuery(#Kru , #Aru , #hjv )
at #Mlv.#bmv.GroupedQuery(ChannelTreeMessage`1 )
at #Zjv.#jkv.#cxv(String , MethodInfo , Object[] )
at RedGate.Response.Common.Networking.Client.RpcSynchronousTcpChannel.#cxv(String , MethodInfo , Object[] )
at RedGate.Response.Common.Networking.Client.RpcProxyGenerator.#lkv.Intercept(IInvocation )
at Castle.DynamicProxy.AbstractInvocation.Proceed()
at Castle.Proxies.IDataPresenterServiceProxy.GroupedQuery(ChannelTreeMessage`1 rootQueryTreeMessage)
at #Mlv.#1lv.GroupedQuery(Unit , IStatusLogger , #hjv )
at RedGate.Response.UI.Website.Controllers.OverviewSideNavigationController.Index(Nullable`1 date, String clusterName, String machineName, String sqlServerName, String groupName)
at lambda_method(ExecutionScope , ControllerBase , Object[] )
at System.Web.Mvc.ActionMethodDispatcher.Execute(ControllerBase controller, Object[] parameters)
at System.Web.Mvc.ReflectedActionDescriptor.Execute(ControllerContext controllerContext, IDictionary`2 parameters)
at System.Web.Mvc.ControllerActionInvoker.InvokeActionMethod(ControllerContext controllerContext, ActionDescriptor actionDescriptor, IDictionary`2 parameters)
at System.Web.Mvc.ControllerActionInvoker.<>c__DisplayClassd.<InvokeActionMethodWithFilters>b__a()
at System.Web.Mvc.ControllerActionInvoker.InvokeActionMethodFilter(IActionFilter filter, ActionExecutingContext preContext, Func`1 continuation)
at System.Web.Mvc.ControllerActionInvoker.<>c__DisplayClassd.<>c__DisplayClassf.<InvokeActionMethodWithFilters>b__c()
at System.Web.Mvc.ControllerActionInvoker.InvokeActionMethodFilter(IActionFilter filter, ActionExecutingContext preContext, Func`1 continuation)
at System.Web.Mvc.ControllerActionInvoker.<>c__DisplayClassd.<>c__DisplayClassf.<InvokeActionMethodWithFilters>b__c()
at System.Web.Mvc.ControllerActionInvoker.InvokeActionMethodFilter(IActionFilter filter, ActionExecutingContext preContext, Func`1 continuation)
at System.Web.Mvc.ControllerActionInvoker.<>c__DisplayClassd.<>c__DisplayClassf.<InvokeActionMethodWithFilters>b__c()
at System.Web.Mvc.ControllerActionInvoker.InvokeActionMethodFilter(IActionFilter filter, ActionExecutingContext preContext, Func`1 continuation)
at System.Web.Mvc.ControllerActionInvoker.<>c__DisplayClassd.<>c__DisplayClassf.<InvokeActionMethodWithFilters>b__c()
at System.Web.Mvc.ControllerActionInvoker.InvokeActionMethodFilter(IActionFilter filter, ActionExecutingContext preContext, Func`1 continuation)
at System.Web.Mvc.ControllerActionInvoker.<>c__DisplayClassd.<>c__DisplayClassf.<InvokeActionMethodWithFilters>b__c()
at System.Web.Mvc.ControllerActionInvoker.InvokeActionMethodWithFilters(ControllerContext controllerContext, IList`1 filters, ActionDescriptor actionDescriptor, IDictionary`2 parameters)
at System.Web.Mvc.ControllerActionInvoker.InvokeAction(ControllerContext controllerContext, String actionName)
at System.Web.Mvc.Controller.ExecuteCore()
at System.Web.Mvc.ControllerBase.Execute(RequestContext requestContext)
at System.Web.Mvc.ControllerBase.System.Web.Mvc.IController.Execute(RequestContext requestContext)
at System.Web.Mvc.MvcHandler.<>c__DisplayClass8.<BeginProcessRequest>b__4()
at System.Web.Mvc.Async.AsyncResultWrapper.<>c__DisplayClass1.<MakeVoidDelegate>b__0()
at System.Web.Mvc.Async.AsyncResultWrapper.<>c__DisplayClass8`1.<BeginSynchronous>b__7(IAsyncResult _)
at System.Web.Mvc.Async.AsyncResultWrapper.WrappedAsyncResult`1.End()
at System.Web.Mvc.MvcHandler.EndProcessRequest(IAsyncResult asyncResult)
at System.Web.Mvc.MvcHandler.System.Web.IHttpAsyncHandler.EndProcessRequest(IAsyncResult result)
at System.Web.HttpApplication.CallHandlerExecutionStep.System.Web.HttpApplication.IExecutionStep.Execute()
at System.Web.HttpApplication.ExecuteStep(IExecutionStep step, Boolean& completedSynchronously)