When a database, user, or table is created, allocated disk space is divided evenly among all AMPs. The AllSpace view returns one row of usage information for each AMP in the system configuration (or for all AMPs if the SUM aggregate is used).
When a database is created, a space row is added to each AMP, with the processor field in each row initialized to 0. The first time the space row is updated (such as when a table is created in the database, or when the system is restarted), the processor field in each row is updated to indicate the actual processor number.
When a query applies a SUM aggregate to either the MaxPerm or CurrentPerm column without a WHERE clause, or with a WHERE clause that references only one TableName or DatabaseName, the returned values are double the desired result.
For example, the following query, which returns the correct amount of space allocated to Peterson, also returns twice the amount of space currently being used by Peterson (see DiskSpaceV[X] and TableSizeV[X]).
SELECT SUM(MaxPerm), SUM(CurrentPerm) FROM DBC.AllSpaceV WHERE DatabaseName = ’Peterson’;
You can use the DBC.ClearPeakDisk macro to reset PeakPerm, PeakSpool, PeakPersistentSpool, and PeakTemp.
The X view references these additional tables:
AppProxyUser and TrustUserName
These columns are not returned in the X or VX views.