Sql Statement to retrieve maintenance information of UC4 System Tables.

Products

• CA Automic Workload Automation - Automation Engine

Issue/Introduction

Running the following SOL Statement against a Automic Database returns maintenance information of the Automic System (MQ) Tables:

The information returned by this statement can help investigate the root cause of performance issues:

It delivers the Total Space, Data Space, Index Space and Unused Space of the MQ tables.

SELECT
TableName, NumRows,
 reservedpages *8192/1024 as TotalSpace,
 pages * 8192/1024 as DataSpace,
 (usedpages-pages)*8192/1024 as IndexSpace,
 (reservedpages-usedpages)*8192/1024 as UnusedSpace

FROM (SELECT
t.[name] as tablename,
avg([rows]) as NumRows,
sum(total_pages) as reservedpages,
sum(used_pages) as usedpages,
sum(
CASE
When it.internal_type IN (202,204) Then 0
When a.type < 1 Then a.used_pages
When p.index_id < 2 Then a.data_pages
Else 0
END) as pages
from sys.allocation_units as a Join sys.partitions as p on p.partition_id = a.container_id
left join sys.internal_tables it on p.object_id = it.object_id
JOIN sys.tables as t on p.object_id=t.object_id WHERE t.name like 'MQ%'
group by t.[name]) as subselect;

 

Environment

Release: AOATAM99000-9.0-Automic-One Automation Tools-Application Manager
Component:

Resolution

The Information returned by the Database will look like this:

<Please see attached file for image>

align="bottom" class="documentation" border="0" alt=".png" src="http://ecmstage.ca.com/KB%20Asset%20Library/000008514_Embeded/.png" width="471" height="293">

 

!Note: A large unused space on the MQ tables can lead to performance issues.