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
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:
On a SideNote:
Large 'UnusedSpace' within the MQ Tables can lead to performance issues.
Would there be an Oracle version as well for completeness sake?