High memory usage caused by vAC_ComputerLicenseAndUsageByProductNoAsset
search cancel

High memory usage caused by vAC_ComputerLicenseAndUsageByProductNoAsset

book

Article ID: 276449

calendar_today

Updated On:

Products

IT Management Suite

Issue/Introduction

Sporadically, the SQL Server seems to be spiking on memory usage when there is a process using "vAC_ComputerLicenseAndUsageByProductNoAsset".

It has been noticed high TempDB usage on the SQL server.
While looking at the SQL logs, some of the queries seems to be triggering this behavior are like these ones:

SELECT c.ResourceGuid FROM vAC_ComputerLicenseAndUsageByProductNoAsset c WHERE  c.SoftwareProductGuid in ('43902158-f5ce-4447-8c59-bc0560c6d25a')  AND  c.[Usage Status Text] in ('UNTRACKED')

INSERT INTO #CurrentCollectionEval SELECT DISTINCT [ResourceGuid] FROM (
SELECT c.ResourceGuid FROM vAC_ComputerLicenseAndUsageByProductNoAsset c WHERE  c.SoftwareProductGuid in ('43902158-f5ce-4447-8c59-bc0560c6d25a')  AND  c.[Usage Status Text] in ('UNTRACKED')
) dsQuery

Environment

ITMS 8.6 RU3, 8.7, 8.7.1

Cause

Known issue. "vAC_ComputerLicenseAndUsageByProductNoAsset" needed to be optimized.

Resolution

This issue has been reported to our Broadcom Development team. A fix has been added to our next release, ITMS 8.7.2

A workaround is available. An updated version of this "vAC_ComputerLicenseAndUsageByProductNoAsset" is available:

1. Open SQL management studio > paste there content below and execute this query for "Symantec_CMDB" database.

 
--Start--
 
/*Dropin_vAC_ComputerLicenseAndUsageByProductNoAsset
CRE-16190*/
 
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
 
IF (OBJECT_ID('dbo.fn_GetSoftwareProductUsage') IS NOT NULL) AND (OBJECTPROPERTY(OBJECT_ID('dbo.fn_GetSoftwareProductUsage'), 'IsTableFunction') = 1)
DROP FUNCTION dbo.fn_GetSoftwareProductUsage
GO
 
CREATE FUNCTION [dbo].[fn_GetSoftwareProductUsage] ()
RETURNS @results TABLE
(
ResourceGuid         uniqueidentifier,
SoftwareProductGuid  uniqueidentifier,
RunCount             int,
LastStart            datetime,
InstallDate          datetime
)
AS
BEGIN
INSERT @results
SELECT iis._ResourceGuid,
pcc.ParentResourceGuid,
SUM(mdu.[Run Count]),
MAX(mdu.[Last Start]),
MAX(iis.[InstallDate])
FROM Inv_InstalledSoftware  iis
JOIN ResourceAssociation    pcc ON pcc.ChildResourceGuid = iis._SoftwareComponentGuid
                          AND pcc.ResourceAssociationTypeGuid = '9D67B0C6-BEFF-4FCD-86C1-4A40028FE483' -- product contains component
OUTER APPLY
(
SELECT _ResourceGuid,
component,
SUM([Run Count])  as [Run Count],
MAX([Last Start]) as [Last Start]
FROM     vAC_MeteringData
WHERE    FileMetered   = 1
AND      _ResourceGuid = iis._ResourceGuid
AND      component     = iis._SoftwareComponentGuid
GROUP BY _ResourceGuid, component
)  AS mdu
WHERE iis.InstallFlag = 1
GROUP BY iis._ResourceGuid, pcc.ParentResourceGuid
RETURN
END
GO
 
IF EXISTS ( SELECT TOP(1) 1 FROM sys.views WHERE object_id = OBJECT_ID(N'[dbo].[vAC_ComputerLicenseAndUsageByProductNoAsset]'))
DROP VIEW [dbo].[vAC_ComputerLicenseAndUsageByProductNoAsset]
GO
 
CREATE VIEW [dbo].[vAC_ComputerLicenseAndUsageByProductNoAsset]
AS
    SELECT    rpu.ResourceGuid,
          vcn.Name,
          rpu.SoftwareProductGuid,
          spi.Name  AS [Software Product Name],
          DATEADD(MINUTE, DATEDIFF(MINUTE, GETUTCDATE(), GETDATE()), rpu.[LastStart])  AS [Last Used],
          rpu.[InstallDate] AS [Install Date],
          CASE WHEN ISNULL(spu.IsUsageTracked,0) = 0 OR rpu.[RunCount] IS NULL OR rpu.[LastStart] IS NULL
               THEN 'UNTRACKED'
               ELSE 
               CASE WHEN rpu.[RunCount] > 0 AND DATEDIFF(dd, DATEADD(dd, -ISNULL(spu.UsageCount, 90), DATEADD(DAY, DATEDIFF(DAY, 0, GETUTCDATE()), 0)), rpu.[LastStart]) >= 0
                    THEN 'USED'
                    ELSE 'UNUSED'
               END
          END       AS [Usage Status Text],
          ISNULL(ugd.[Display Name], ISNULL(NULLIF(LTRIM(RTRIM(ISNULL(ugd.[Given Name],'') + ' ' + ISNULL(ugd.Surname,''))),''), usr.Name)) AS [User]
FROM      fn_GetSoftwareProductUsage()     rpu
JOIN      vComputer                        vcn  ON vcn.Guid               = rpu.ResourceGuid
JOIN      vRM_Software_Product_Item        spi  ON spi.Guid               = rpu.SoftwareProductGuid
LEFT JOIN Inv_Software_Product_Usage       spu  ON spu._ResourceGuid      = rpu.SoftwareProductGuid
LEFT JOIN vAssetMaster                     pua  ON pua.ParentResourceGuid = rpu.ResourceGuid
LEFT JOIN RM_ResourceUser                  usr  ON usr.Guid               = pua.ChildResourceGuid
LEFT JOIN Inv_Global_User_General_Details  ugd  ON ugd._ResourceGuid      = usr.Guid
GO

--END--

Attachments

Dropin_vAC_ComputerLicenseAndUsageByProductNoAsset-Updated.txt get_app