High memory usage caused by vAC_ComputerLicenseAndUsageByProductNoAsset

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 that seem to be triggering this behavior are like these:

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:

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--