Tech Tip: How to find the TOP 10 largest tables in your UIM Database

Document created by Marco_Ippati Employee on Jan 31, 2017
Version 1Show Document
  • View in full screen mode

Document ID:  TEC1998675
Last Modified Date:  01/30/2017
Show Technical Document Details

Introduction:

In some cases it may be necessary to verify which are the tables that have grown the most in your UIM backend database.  How can I find the TOP 10 largest tables?

 

Environment:

CA UIM 8.x.x Microsoft SQL Server MySql Server

Instructions:

-- The following query provides TOP 10 largest tables in UIM Database on Microsoft SQL Server:

 

SELECT TOP 10

    t.NAME AS TableName,

    i.name as indexName,

    sum(p.rows) as RowCounts,

    sum(a.total_pages) as TotalPages,

    sum(a.used_pages) as UsedPages,

    sum(a.data_pages) as DataPages,

    (sum(a.total_pages) * 8) / 1024 as TotalSpaceMB,

    (sum(a.used_pages) * 8) / 1024 as UsedSpaceMB,

    (sum(a.data_pages) * 8) / 1024 as DataSpaceMB

FROM

    sys.tables t

INNER JOIN     

    sys.indexes i ON t.OBJECT_ID = i.object_id

INNER JOIN

    sys.partitions p ON i.object_id = p.OBJECT_ID AND i.index_id = p.index_id

INNER JOIN

    sys.allocation_units a ON p.partition_id = a.container_id

WHERE

    t.NAME NOT LIKE 'dt%' AND

    i.OBJECT_ID > 255 AND  

    i.index_id <= 1

GROUP BY

    t.NAME, i.object_id, i.index_id, i.name

ORDER BY

 

       SUM(p.rows) DESC

 

 

 

 

-- The following query provides TOP 10 largest tables in UIM/ information_schema schema on MySQL Server:

 

SELECT CONCAT(table_schema, '.', table_name),

CONCAT(ROUND(table_rows / 1000000, 2), 'M') rows,

CONCAT(ROUND(data_length / ( 1024 * 1024 * 1024 ), 2), 'G') DATA,

CONCAT(ROUND(index_length / ( 1024 * 1024 * 1024 ), 2), 'G') idx,

CONCAT(ROUND(( data_length + index_length ) / ( 1024 * 1024 * 1024 ), 2), 'G') total_size,

ROUND(index_length / data_length, 2) idxfrac

FROM   information_schema.TABLES

ORDER  BY data_length + index_length DESC

 

LIMIT  10;

 

 

 

https://www.ca.com/us/services-support/ca-support/ca-support-online/knowledge-base-articles.TEC1998675.html  

5 people found this helpful

Attachments

    Outcomes