Daniel_Hausdorf_6982

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
Script:

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;

Outcome

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

sjwvf700h7xy.png

On a SideNote: 

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

Outcomes