I have couple of questions regarding database space.
1) how to find out how much space has been left in an existing database ? I know how to find out the total allocated space to the database but not sure how to find out the remaining space.
2) I have a query which inserts the data into one table. Before I insert, I would like to find out how much space it would occupy in the database once the data gets inserted into the the table.
Use the below sql to find out the remaining space in a Database:
,SUM(CurrentPerm)/1024/1024 AS USEDSPACE_IN_MB
,SUM(MaxPerm)/1024/1024 AS MAXSPACE_IN_MB
,SUM(CurrentPerm)/ NULLIFZERO (SUM(MaxPerm)) *100 (FORMAT 'zz9.99%') AS Percentage_Used
,MAXSPACE_IN_MB- USEDSPACE_IN_MB AS REMAININGSPACE_IN_MB
WHERE DatabaseName = <<DatabaseName>>
GROUP BY DatabaseName;
instead of "SUM(CurrentPerm)" you should consider using "MAX(CurrentPerm) * (HASHAMP()+1)" because this includes wasted perm due to skew. This the minimum perm space needed to store the existing tables.
If it's a large number of rows you calculate the average rowsize based on datatypes, add the row overhead (14+ bytes) and multiply times the number of rows.
This is the approximate size of the uncompressed table, after applying the various compression techniques it might be anywhere between 10% and 100% of this size.
In the Databse Design manual "Chapter 14: Database-Level Capacity Planning Considerations" there's a lot about sizing.
"MAX(CurrentPerm) * (HASHAMP()+1)" because this includes wasted perm due to skew
Does this mean that even if data is not physically stored, Perm space is wasted due to Skew?
Taking an extreme case scenario: Lets say there are 50 AMPs and there is only 1 table in a Database. And all the rows are on only 1 AMP, occupying 1 MB. Would it waste 1MB per all other 49 AMPs also, causing space occupied to be 50MB instead of expected 1 MB?
Yes thats the correct understanding. If the database has been allocated 50 MB of space on a 50 AMP system, this means each AMP can store max of 1 MB, so if the space on one AMP is exhausted then the database space will also be exhausted.
The used perm space according to dbc.TableSize would still be 1 MB, but the remaining 49 MB in this database can't be assigned to another database, effectively wasting it.
All space (perm(temp/spool) assigned on database level is divided by the number of AMPs, thus each AMP gets exactly the same maximum size.