"Underutilized Software" Report versus "Metered Software > Usage Tracking" view

[Jose Roberto]

Hello,
These views provide details about software usage (computers with software installed), but unfortunately how many devices the NS has worse is the performance. 



Based on Broadcom recommendations, we enable "Optimize for ad-hoc Workloads" to solve the issue.

The "Underutilized Software" Report shows some improvement in the response. However, the "computers with software installed" view on the Metered Software panel still consumes more than a half-hour to present results.
Does someone know how to increase performance for those specific views?
Relevant information:
NS - 8.6RU3
devices - 20k
managed applications - 19
SQL DB Version - 14.0.3381
#tmpSoftComponent- 80k rows

[Igor Perevozchikov]

Looks very strange.. Please raise a support ticket about this problem.

Just in case:

I see that you have SQL Server 2017 (14.0.3381) which is a Cumulative Update version 23 https://support.microsoft.com/en-us/topic/kb5000685-cumulative-update-23-for-sql-server-2017-22b653c7-8487-4564-9db2-b5c1bd465145 and it was released in February 24, 2021 year

but I have a latest SQL Server 2017 (14.0.3460.9) with latest Cumulative Update version 31 released in 2023-02-14.
https://support.microsoft.com/en-us/topic/kb5021126-description-of-the-security-update-for-sql-server-2017-cu31-february-14-2023-2867280f-e66f-4598-a2f1-3d301e367683

Your database has SQL Compatibility level 2017 (140)?


Best regards,
IP.

[Igor Perevozchikov]

Hi Jose Roberto !

1. By default, App metering data isn't purged, so user can decide what time period to keep such data.

This scheduled task "NS.Application Metering Settings.{e87c4bf1-5612-438a-85ef-4019a9e2b67e}" will be running every day and purge unnecessary App metering data

2. There is a mention about your environment

NS - 8.6RU3
devices - 20k
managed applications - 19
SQL DB Version - 14.0.3381
#tmpSoftComponent- 80k rows

But no information about CPU, Memory, Disk (SQL Server is on-box with NS or SQL is remote?)

Best regards,
IP.

[Jose Roberto]

Thanks for your quick response!
Adding more details:
CPU- 16 
 Memory- 64GB
 Our SQL is remote. It is an Amazon EC2 instance.

[Jose Roberto]

I enabled app metering data to purge the past month, but didn't detect improvements after that.
regards,

[Igor Perevozchikov]

I checked same place in Console and I see that it takes 33 seconds to return 10k rows
(Using ITMS 8.7 release with on-box SQL server and totally have 34k computers in database)

Rechecked same query in SQL studio as well

EXECUTE spAC_GetComputersBySoftwareProduct @SoftwareComponentGuids=N'69c0a9e1-b8a8-47ee-8dc2-634cdd131959', 
@ComputerName=N'%', 
@Trustee=N'{196ECBB8-B414-471D-90E1-101CFCBA6675},{1B523156-A34C-49CA-A4DF-80425470744A},
{1B70E344-4CCA-4884-83B2-BDA754C149AB},{1C27725B-1FEF-40E7-BC53-F095E01F171D},{20D94388-49BE-4572-A1D8-02C39DF0769A},
{247ABCF4-0F42-4D7D-AB2C-99B0B546B1E2},{2693F908-42A5-41DC-B148-D499A7ECD580},{2E1F478A-4986-4223-9D1E-B5920A63AB41},
{2F0C0A0E-3B04-4FE1-955C-8A12B2AFB808},{30C2939F-B223-4FBC-BE4E-765922648CA8},{37528845-0CCE-4EA7-950A-FACBA9F484F4},
{3CCF3241-71C0-4782-A830-8ABB045AC6E2},{44D83C34-7436-4502-9819-A78A266E2E4B},{4E7B66E1-9208-4EA1-BE48-28E5AB288163


Please check what amount of software components you have for this Software Product because in my case there is only 7 software components included

And for usage tracking, only 4 rules
Best regards,
IP.

[Jose Roberto]

Thanks again Igor!
Unfortunately, the query results don't return rows in my case.
The same results with 8,6RU3 and 8.7(with the last fix)
I changed the trustees, too, without success. 
Best regards,
JR

[Igor Perevozchikov]

Previously mentioned SQL Query doesn't return any data because there is my SoftwareComponentGuid and Trustees specified.

Try this one
EXECUTE spAC_GetComputersBySoftwareProduct @SoftwareComponentGuids=N'Put here Guid of required Software', 
@ComputerName=N'%', 
@Trustee=N'{2e1f478a-4986-4223-9d1e-b5920a63ab41}'

Copy GUID of required software product and put in SQL Query above

How much Software components you have in this Software product and "Usage tracking" rules?
I mentioned my example in previous comment, so interesting to know amount that you have currently for this affected Software product which loads half hour.

Thanks,
IP.

[Jose Roberto]

Ups, Friday morning in hell mode!
You are always right.
The most painful is Microsoft Office Standard 2016:
software components - 13 
usage tracking - 25 rules
00:41:28 minutes with all trustees - 13722 devices 
00:40:50 minutes only with Symantec Administrators trustee - 13732 devices
Regards,

[Jose Roberto]

It sounds crazy, but iTunes 6.0.4.2, with only one track file (itunes.exe) and two inventory files (iTunes 6.0.4.2), took the same 40 minutes.
Without more details about how that SP works, I think one temporary table (#scope or #usage) could be responsible for this delay.
Regards
JR

[Jose Roberto]

Hi Igor,
You have eagle eyes.
Before opening a ticket, we will attempt to apply your recommendations:
I will request the same SQL patch and change the option "compatible level" to 140 (currently 130)
I found a lot of improvements changing from 130 to 140 option.
https://learn.microsoft.com/en-us/sql/t-sql/statements/alter-database-transact-sql-compatibility-level?view=sql-server-ver16
regards,
JR,