Top 10 Databases And Tables According To Size?

Database
Enthusiast

Top 10 Databases And Tables According To Size?

In an environment, how can you find the top 10 space consuming Databases and Tables?

Also, when you calculate the space in an environment, would you use the MAXPERM or CURRENTPERM?

4 REPLIES
Enthusiast

Re: Top 10 Databases And Tables According To Size?

MAXPERM IS the MAX Permanant space that has been allocated. CURRENTPERM IS the Permanant space occupied currently. So use CURRENTPERM TO calculate space consumed.

Use SQL below to find TOP Databases by space occupied:

SELECT 
DatabaseName
,MAX(CurrentPerm) * (HASHAMP()+1)/1024/1024 AS USEDSPACE_IN_MB
FROM DBC.DiskSpace
GROUP BY DatabaseName
ORDER BY USEDSPACE_IN_MB DESC;

 Use SQL below to find TOP Tables by space occupied:

SELECT 
DATABASENAME
,TABLENAME
,SUM(CurrentPerm)/1024/1024 AS TABLESIZE_IN_MB
FROM DBC.TableSize
GROUP BY DATABASENAME,TABLENAME
ORDER BY TABLESIZE_IN_MB DESC;

Enthusiast

Re: Top 10 Databases And Tables According To Size?

Thanks Sachin,

I am confused by the formula you used in the upper query

",MAX(CurrentPerm) * (HASHAMP()+1)/1024/1024 AS USEDSPACE_IN_MB "

Can you tell me what is being done here?

I think that by using the SUM(CURRENTPERM) formula would give me the same result.

Can you shed any light on this?

Thanks

Teradata Employee

Re: Top 10 Databases And Tables According To Size?

SUM(CurrentPerm) is the actual space.

MAX(CurrentPerm)*(HashAmp()+1) = MAX(CurrentPerm)*NumberOfAMPs can be regarded as the "effective space" allowing for skew.

Both values can be calculated at either database level or table level. But at the database level, the latter value is particularly relevant because the PermSpace limit must be at least this large.

Enthusiast

Re: Top 10 Databases And Tables According To Size?

Thanks Fred,

I think I get what you are trying to say.

If I don't have skewed data on my system then the results of both cases would be the same?

And if I do have skewed data then the result of my query would be different and in this case it would be better if I used the second query?

Cheers!