Welcome to the Inedo Forums! Check out the Forums Guide for help getting started.
If you are experiencing any issues with the forum software, please visit the Contact Form on our website and let us know!
ProGet 5.1.x upgrade broke ProGet (SQL timeout)
-
Suddenly our ProGet installation is getting SQL timeouts. I've seen that the configuration has been moved to a central configuration file: https://inedo.com/support/documentation/proget/reference/config-files
The SQL connectionstring is still valid, but it seems the application is not reading the connectionstring from the shared config file?
I tried putting ConnectionString and EncryptionKey inside Web_appSettings.config and App_appSettings.config but still get the same error.Don't I need a customconfiguration section declaration for "InedoAppConfig" inside Web.config and Proget.Service.exe.config ?
Is anyone else experiencing this issue?
Server Error in '/' Application.
The wait operation timed out
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.
Exception Details: System.ComponentModel.Win32Exception: The wait operation timed out
Source Error:
An unhandled exception was generated during the execution of the current web request. Information regarding the origin and location of the exception can be identified using the exception stack trace below.
Stack Trace:
[Win32Exception (0x80004005): The wait operation timed out]
[SqlException (0x80131904): Execution Timeout Expired. The timeout period elapsed prior to completion of the operation or the server is not responding.]
System.Data.SqlClient.<>c.<ExecuteDbDataReaderAsync>b__174_0(Task1 result) +1161090 System.Threading.Tasks.ContinuationResultTaskFromResultTask2.InnerInvoke() +108
System.Threading.Tasks.Task.Execute() +71
System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task) +14139120
System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task) +62
Inedo.Data.<CreateAsync>d__10.MoveNext() +341
System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task) +14139120
System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task) +62
Inedo.Data.<ExecuteInternalAsync>d__33.MoveNext() +1239
System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task) +14139120
System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task) +62
Inedo.ProGet.Data.<Dashboards_GetDashboardInfoAsync>d__91.MoveNext() +731
System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task) +14139120
System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task) +62
System.Runtime.CompilerServices.TaskAwaiter.ValidateEnd(Task task) +14139265
Inedo.ProGet.WebApplication.Pages.<CreateChildControlsAsync>d__2.MoveNext() +411
System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task) +14139120
System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task) +62
Inedo.ProGet.WebApplication.Pages.<InitializeAsync>d__1.MoveNext() +348
System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task) +14139120
System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task) +62
Inedo.Web.PageFree.<ProcessRequestAsync>d__46.MoveNext() +283
System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task) +14139120
System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task) +62
System.Web.TaskAsyncHelper.EndTask(IAsyncResult ar) +69
Inedo.Web.Handlers.AsyncHandlerWrapper.EndProcessRequest(IAsyncResult result) +34
System.Web.CallHandlerExecutionStep.OnAsyncHandlerCompletion(IAsyncResult ar) +178
Version Information: Microsoft .NET Framework Version:4.0.30319; ASP.NET Version:4.6.1590.0Product: ProGet
Version: 5.1.7
-
The connection string is in a different place yes, but unless you changed it from the value it was, that shouldn't affect timeouts.
My guess is that the SQL Server service just needs to be restarted.
-
I already tried restarting SQL Server and also uninstalling ProGet and even with a clean installation (which checks the database connection) we're getting the timeout error.
Other applications using other databases on the same SQL Server instance are still working fine.
-
I've narrowed down the issue a bit: the database connection is working on most pages of ProGet, except for the pages that make a call to the stored procedure dbo.Dashboards_GetDashboardInfo which causes the SQL timeout:
- www.example.com/ (root url)
- www.example.com/feeds (all feeds)
- www.example.com/feeds/<containerregistryname> (Docker container registry)
Increasing the command timeout of SQL Server didn't help.
When I execute the stored procedure dbo.Dashboards_GetDashboardInfo manually then it gets really slow depending on how high value I pass to parameter @Packages_Count (I don't know which values is actually being passed by ProGet). When I pass for example 5000 then the query takes 2 minutes and 35 seconds to complete.
-
ProGet calls the procedure on those pages 10 and 1 for Packages_Count. If it's really slow at 10 packages, you may want to try rebuilding/defragmenting your SQL Server indexes.
-
Hi Alana,
After defragmenting the indexes and updating the statistics, we are still getting the timeout...
Running the stored proc like this takes 1'45":
EXEC @return_value = [dbo].[Dashboards_GetDashboardInfo]
@Packages_Count = 10,
@IncludeInactive_Indicator = YOur database is really not spectacular looking at the row counts.
Perhaps the nuget.org connector could be the culprit?TableName RowCount
dbo.EventOccurrences 198648
dbo.PackageDownloads 16878
dbo.ScopedExecutionLogEntries 7100
dbo.NuGetPackageVersionsV2 4950
dbo.NuGetPackagesV2 2530
dbo.LogMessages 2004
dbo.DockerBlobs 1706
dbo.LicenseUrls 1394
dbo.Executions 1228
dbo.NuGetPackageSymbolsV2 690
dbo.Licenses 654
dbo.ScopedExecutionLogs 614
dbo.DockerImages 512
dbo.__BuildMaster_DbSchemaChanges2 406
dbo.__BuildMaster_DbSchemaChanges 328
dbo.__StoredProcInfo 254
dbo.DockerImageTags 143
dbo.RoleTasks 38
dbo.Tasks 32
dbo.Feeds 32
dbo.Privileges 27
dbo.Configuration 27
dbo.EventTypes 23
dbo.ScheduledTasks 21
dbo.Roles 18
dbo.FeedConnectors 12
dbo.Users 8
dbo.UserGroups 7
dbo.Connectors 6
dbo.UserDirectories 4
dbo.Groups 3
dbo.AssessmentTypes 3
dbo.VsixPackages 2
dbo.Webhooks 1
-
Could you please improve the performance of the stored procedure and provide a new ProGet release to include this?
-
Based on the table sizes and distribution, there's no reason it should be performing so slowly. Actually it works fast even on instances with 100x the packages that you have.
Maybe there's something else with your data that we can't see based on the tables you sent us. You should be able to view/edit the code of the procedure and underlying database views; perhaps you can find something by investigating the queries and data relationships?
-
I did some database cleanup (deleted old packages) and ran the Feed Cleanup tasks:
NuGetPackageVersionsV2 2477
NuGetPackagesV2 1266But the first query in view dbo.DashboardFeeds is still very slow (1 minute and 38 seconds), particularly the subqueries Feed_Size and Cache_Size:
Feed_Id Feed_Name FeedType_Name Package_Download_Count Package_Count Feed_Size Cache_Size Active_Indicator
1 nuget.org-proxy NuGet 3581 628 1371022356 1370053686 Y
2 tobania NuGet 555 36 364376870 3685794 Y
3 chocolatey-proxy Chocolatey 0 0 0 0 Y
5 nuget.org-microsoft.net-proxy NuGet 3130 577 1053810421 1053810421 Y
7 anton2 NuGet 4 7 102825284 0 Y
9 powershell-tobania PowerShell 0 0 0 0 Y
22 digipolis NuGet 186 16 8250831 0 Y
23 febelfin-academy NuGet 69 2 238575 0 Y
25 zorgplan-devexpress NuGet 0 0 0 0 YAre those sizes exceptionally large?
-
Definitely not, they are on the low-end of the medium size... and just to be clear you shouldn't be calling
dbo.Dashboards_GetDashboardInfowith more than 10 packages. It's only designed/tested for 0, 1, and 10.
-
I'm directly executing the DashboardFeeds view, so I'm not using the parameter.
It seems that the SUM([Package_Size] for [Feed_Size] and [Cache_Size] are the culprit.
When I replace the query with [Feed_Size] = 0, [Cache_Size] = 0 then the timeouts are gone! They are just missing from the feed disk usage graph. Adding an index to NuGetPackageVersionsV2.Package_Size didn't help.The strange thing is that some pages that are not using the graph are also executing the DashboardInfo stored procedure.
