Sql Statement to retrieve maintenance information of UC4 System Tables.

Document ID : KB000088460
Last Modified Date : 14/04/2018
Show Technical Document Details
Issue:

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;


 

Resolution:

The Information returned by the Database will look like this:

.png

 

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