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?
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:
,MAX(CurrentPerm) * (HASHAMP()+1)/1024/1024 AS USEDSPACE_IN_MB
GROUP BY DatabaseName
ORDER BY USEDSPACE_IN_MB DESC;
Use SQL below to find TOP Tables by space occupied:
,SUM(CurrentPerm)/1024/1024 AS TABLESIZE_IN_MB
GROUP BY DATABASENAME,TABLENAME
ORDER BY TABLESIZE_IN_MB DESC;
I am confused by the formula you used in the upper query
(CurrentPerm) * (HASHAMP()+1)/1024/1024
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?
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.
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?