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;
The Information returned by the Database will look like this:
<Please see attached file for image>
!Note: A large unused space on the MQ tables can lead to performance issues.