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)
-
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.
Hello! It looks like you're interested in this conversation, but you don't have an account yet.
Getting fed up of having to scroll through the same posts each visit? When you register for an account, you'll always come back to exactly where you were before, and choose to be notified of new replies (either via email, or push notification). You'll also be able to save bookmarks and upvote posts to show your appreciation to other community members.
With your input, this post could be even better 💗
Register Login