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.0

    Product: 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:

    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.


  • inedo-engineer

    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 = Y

    Our 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?


  • inedo-engineer

    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 1266

    But 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 Y

    Are those sizes exceptionally large?


  • inedo-engineer

    Definitely not, they are on the low-end of the medium size... and just to be clear you shouldn't be calling dbo.Dashboards_GetDashboardInfo with 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.


Log in to reply
 

Inedo Website HomeSupport HomeCode of ConductForums GuideDocumentation