Using a SQL Statement to retrieve maintenance Information of Automic (UC4) System Tables

Discussion created by Daniel_Hausdorf_6982 on Sep 29, 2016
Latest reply on Nov 14, 2016 by Andreas_Sprosec_7439
Running the following SQL Statement against a Automic Database returns maintenance information of the Automic System (MQ) Tables

The returned information from the following statement can be used to help investigate performance issues. This Script delivers the following MQ Table information: 
  • Total Space
  • Data Space
  • Index Space
  • Unused Space

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

t.[name] as tablename,
avg([rows]) as NumRows,
sum(total_pages) as reservedpages,
sum(used_pages) as usedpages,
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;


Once the script has been run, the outcome should look something like this: 


On a SideNote: 

Large 'UnusedSpace' within the MQ Tables can lead to performance issues.