All Forums Database
usmans 39 posts Joined 01/13
22 Jan 2013
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?
 

--Regards Usmans
22 Jan 2013

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; 
usmans 39 posts Joined 01/13
07 Feb 2013

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

--Regards
Usmans

Fred 1096 posts Joined 08/04
09 Feb 2013

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.

usmans 39 posts Joined 01/13
10 Feb 2013

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!

--Regards
Usmans

You must sign in to leave a comment.