Performance of session

[ashleyw]

Hi,
Is there any way of increasing the performance of Veeam One session?
This process takes over 2 hours - and the vast majority of time of time is spent updating the objects.
Our environment is not huge - 11 ESXi5.5 hosts, 400 VMs.
Is there any way of speeding up the session run time?
If it makes a difference the Veeam One server has it's database running on a different VM, the Veeam One server itself is a windows2008r2 box running 2gb ram.

thanks
Ashley

7/11/2013 3:00:04 a.m. [info]: Getting containers
...
...
7/11/2013 3:02:46 a.m. [info]: Getting Objects for approval...
7/11/2013 3:02:46 a.m. [info]: Objects have been collected.
7/11/2013 5:18:43 a.m. [info]: Objects have been approved. 873 custom attributes have been updated.
7/11/2013 5:18:43 a.m. [info]: .....: Data collection completed
7/11/2013 5:18:43 a.m. [info]: .....: Data collection completed for all objects.

[ashleyw]

hi,
I decided to get my DBA hat on from a past life and have isolated the issue down to repeated calls of the stored procedure ApproveCustomAttributes.
For example, one of the many calls is via these style of parameters;
@ObjectID=8765,@SessionId=1448,@attributename=N'VMs with Snapshots',@attributevalue=N'No snapshots'

If I look at the performance of that stored proc, I see relatively high CPU and long durations; CPU=12294, Reads=63350,Write=4,Duration=11577

These style of queries are repeated rapidly.
I've taken a quick squizz at the stored procedure itself, but it would take me some time to understand the bottlenecks, but the first place I would look at is the nested sub query - as those can be very bad news depending on how the optimiser deals with them.

cheers

[Vitaliy S.]

Hi Ashley,

Do you use custom attributes to categorize your objects in the VI? Based on the name of the stored procedure I believe it could be something to do with Business View collection engine.

Thanks!

[ashleyw]

hi, Yes this is part of business view collection process - and it is my opinion that over 2 hours is far too long for the collection process.
If this one stored procedure could be tuned, then this time could be significantly reduced.
thanks.

[Vitaliy S.]

I agree, can you please open a support ticket and upload all the logs, so I could ask our dev team to take a look at this SQL procedure?

[ashleyw]

thanks. I've opened up a case (Case # 00488545).
Hopefully the issue will be easily resolvable.

[ashleyw]

just to give some feedback here... The duration was caused by the approval process which has been removed form the UI but left in the workflow code of business view, so the solution provided by Veeam support was to change the stored procedure processing the approval logic to effectively do nothing. Veeam support have said they hope the workflow approval code will be removed completely in a future release.
thanks again.

[Vitaliy S.]

Thanks for coming back and updating the topic with the resolution. Yes, I have just spoken to our project team lead and we are going to update our current workflow of working with custom attributes in the next update.