Large number of queries to the Octopus SQL server DB?

Legacy Uncategorised
1

Hi!

I just happened to notice that something in Octopus (3.2.10 here) is currently generating hug number of queries to the database (according to Activity monitor).

23000/min of these two:

SELECT COUNT(*) FROM (SELECT * FROM dbo.[Deployment] WHERE (ProjectId in (@projectIds_0)) AND (EnvironmentId in (@environments_0))) ALIAS_SGAQ3 INNER JOIN (SELECT * FROM dbo.[ServerTask] WHERE (State = @taskState)) ALIAS_UQMAJ ON ALIAS_SGAQ3.TaskId = ALIAS_UQMAJ.Id

SELECT * FROM (SELECT RESULT., Row_Number() over (ORDER BY Created desc) as RowNum FROM (SELECT ALIAS_6QPJX. FROM (SELECT * FROM dbo.[Deployment] WHERE (ProjectId in (@projectIds_0)) AND (EnvironmentId in (@environments_0))) ALIAS_6QPJX INNER JOIN (SELECT * FROM dbo.[ServerTask] WHERE (State = @taskState)) ALIAS_DPAXW ON ALIAS_6QPJX.TaskId = ALIAS_DPAXW.Id) RESULT) RS WHERE RowNum >= @_minrow And RowNum <= @_maxrow

This does not seem quite right?

/Ulf

2

Hi Ulf,

Thanks for getting in touch.

During a deployment, the deployment screen polls every second to refresh the screen. If you have more than one deployment happening at once (multiple users all deploying various projects), you can imagine the queries ramp up quickly.

Could you please provide some context on how many deployments (if any) may have been happening during the period when you see so many queries running? How many users might be logged in at the same time and triggering deployments?

Cheers
Mark

3

Hi,

One deployment was running, most likely with two people looking at the deployment screen.

Regards,
Ulf

4

Hi Ulf,

We are unable to replicate this problem on 3.2.10. Would it be possible for you to monitor your SQL activity during your next deployment and see if this is consistently happening? If you notice this happening, can you please also take a screenshot of the ‘Tasks’ screen. That will help us to determine everything that is running and active when the issue is occurring.

Cheers
Mark

5

Ok, will do that!

/Ulf

6

Hi,

Yesterday I asked a colleague to look at this while a deployment was running, he took a measurements at three times during the deploy and got the following values.

1432 transactions/min
1752 transactions/min
2037 transactions/min

I am fairly sure he was the only one looking at the deployment output screen and running any deployment at all so that seems a bit excessive. 60/min would have been expected?

Regards,
Ulf

7

Hi,

The OD server is now upgraded to 3.3.2 and today I am watching a deploy running with just below 60 calls/min against the DB…

I don’t know what really cured it and if the cure is temporary…

/Ulf

From: Ulf Axelsson
Sent: den 11 mars 2016 08:15
To: ‘Mark Siedle’ tender2+d02d698e17@tenderapp.com
Subject: RE: Large number of queries to the Octopus SQL server DB? [Problems #44784]

Hi,

Yesterday I asked a colleague to look at this while a deployment was running, he took a measurements at three times during the deploy and got the following values.

1432 transactions/min
1752 transactions/min
2037 transactions/min

I am fairly sure he was the only one looking at the deployment output screen and running any deployment at all so that seems a bit excessive. 60/min would have been expected?

Regards,
Ulf

8

Hi Ulf,

Thanks for the additional information.

I’m running ongoing tests locally with SQL Profiler to see if I can reproduce this. There can be several polling timers that occur every 4 seconds to update various parts of the interface (depending on where you are within Octopus), each of which can trigger several queries. I’m keeping an eye on these to see if something strange is happening.

Cheers
Mark