data_engine 'Status' button shows no results for MS SQL Server 2019

Products

• DX Unified Infrastructure Management (Nimsoft / UIM)
• CA Unified Infrastructure Management On-Premise (Nimsoft / UIM)
• CA Unified Infrastructure Management SaaS (Nimsoft / UIM)

Issue/Introduction

To check where the bulk of the QOS is being generated from (which probes' QOS objects), you can click the data_engine "Status" button but it is not delivering any results in the data_engine probe GUI Status window. It is empty/blank.

This works just fine in MS SQL Server v2016 in the lab but not in the customer's environment which is running MS SQL Server v2019:

Microsoft SQL Server 2019 (RTM-CU20) (KB5024276) - 15.0.4312.2 (X64)   Apr  1 2023 12:10:46   Copyright (C) 2019 Microsoft Corporation  Enterprise Edition: Core-based Licensing (64-bit) on Windows Server 2019 Standard 10.0 <X64> (Build 17763: ) (Hypervisor) 

SMSS error message result:
Msg 245, Level 16, State 1, Line 25
Conversion failed when converting the nvarchar value 'sysrscols' to data type int.

>>>sysrscols is a hidden system table which is used for tracking the column modification.

Environment

• Release: 20.4
• Microsoft SQL Server 2019 (RTM-CU20) (KB5024276) - 15.0.4312.2 (X64)

Resolution

Confirmed this updated version of the underlying query for the data_engine Status button is working as expected in MS SQL Server 2019 in the UIM monitoring environment.

When run manually, you must run the create table first then the select.

create table #t (id bigint, name varchar(max), rname varchar

(max), hname varchar(max), dname varchar(max), bname

varchar(max),

rrows bigint, hrows int, drows int, brows int, rsize

int, hsize int, dsize int, bsize int)

insert #t (name,rname,rrows,id, hname, dname, bname)

 

SELECT sqd.name, object_name(i.object_id), sum(p.rows)

as rowCnt, sqd.qos_def_id,

replace(object_name(i.object_id),'RN','HN'), replace

(object_name(i.object_id),'RN','DN'), replace(object_name

(i.object_id),'RN','BN')

FROM

sys.indexes i INNER JOIN sys.partitions p ON i.object_id =

p.object_id AND i.index_id = p.index_id

INNER JOIN s_qos_definition sqd on

sqd.qos_def_id = try_cast(replace(object_name

(i.object_id),'RN_QOS_DATA_','') AS int)

WHERE object_name(i.object_id) like 'RN_QOS_DATA_%'

AND i.index_id <= 1

GROUP BY sqd.name,i.object_id, i.[name], sqd.qos_def_id