All Forums Database
KS42982 137 posts Joined 12/12
15 Jan 2013
Space in the database

Hi,
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.
Thanks.

16 Jan 2013

Use the below sql to find out the remaining space in a Database:

SELECT 
DatabaseName
,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
FROM DBC.DiskSpace
WHERE DatabaseName = <<DatabaseName>>
GROUP BY DatabaseName;
KS42982 137 posts Joined 12/12
17 Jan 2013

It worked fine. Thanks a lot !

dnoeth 4628 posts Joined 11/04
17 Jan 2013

One suggestion:
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.
Dieter

Dieter

KS42982 137 posts Joined 12/12
17 Jan 2013

Thank you.
Do you have any suggesion for my second question ?

dnoeth 4628 posts Joined 11/04
17 Jan 2013

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.
Dieter
 

Dieter

KS42982 137 posts Joined 12/12
17 Jan 2013

That is very helpful. Thanks.

20 Jan 2013

Dieter,

"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?

Qaisar Kiani 337 posts Joined 11/05
21 Jan 2013

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.

dnoeth 4628 posts Joined 11/04
21 Jan 2013

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.
Dieter

Dieter

22 Jan 2013

Thanks Dieter and Qaisar.
Will this wastage be there at a Table level also? 
i.e If I am trying find the Space occpied by a Table (instead of Database) should I use MAX(CurrentPerm) * (HASHAMP()+1) instead of SUM(CurrentPerm) from DBC.TableSize?

Qaisar Kiani 337 posts Joined 11/05
23 Jan 2013

Yes, the wastage is at the Table level and thats why the selection of PI is important to avoid skewness.
MAX(CurrentPerm) * (HASHAMP()+1) should give you a more realistic estimates!

06 Mar 2013

Hi All,
Continuing on the above i have come up with couple of queries to evaluate the space utlisation at Database and table level
Could any one evaluate and let me know of any mistakes?
 
 Database level

sel
databasename,
sum (currentperm)/(1024*1024) Space_Utilised_MB,
(max(currentperm)* (hashamp()+1))/(1024*1024) as Space_Utilised_Incld_Skew_MB,
sum(maxperm)/(1024*1024) Space_Alloted_MB,
avg(currentperm)/(1024*1024) Avg_Space_Per_Amp_MB,
max(currentperm)/(1024*1024) Max_Space_Amp_MB,
(Space_Alloted_MB -  Space_Utilised_MB) Absolut_Space_Left_MB,
(Space_Alloted_MB - Space_Utilised_Incld_Skew_MB ) Space_Left_Consider_Skew_MB,
((1 - (AVG(CURRENTPERM)/MAX(CURRENTPERM)))*100) AS SKEWFACTOR
from
dbc.diskspace
where
databasename = 'XYZ'
group by 1;
 
Table level

sel
databasename,
tablename,
sum (currentperm)/(1024) Space_Utilised_KB,
avg(currentperm)/(1024) Avg_Space_Per_Amp_KB,
max(currentperm)/(1024) Max_Space_Amp_KB,
((1 - (AVG(CURRENTPERM)/MAX(CURRENTPERM)))*100) AS SKEWFACTOR
from
dbc.tablesize
where
databasename = 'XYZ'
group by 1,2;
 
Regards
R.Rajeev

07 Mar 2013

Hi All,
Could anyone review the above two queries and let me know of any mistakes
Thanks
R.Rajeev

09 Mar 2013

Hi All,
Someone please help me in validating the two queries
Thanks in advance
Regards
R.Rajeev
 

elanir2012 1 post Joined 05/13
07 May 2013

hi Rajeev,
 
i dont know about the DB level but for table level use this:
 

SELECT databasename

 

, tablename

 

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

 

, cast (cast (max (currentperm)*(hashamp()+1) AS float) /

 

cast (1024*1024*1024 AS float) AS decimal(18,2)) AS Effective_Perm_Including_Skew

 

, Effective_Perm_Including_Skew - Current_Perm AS Space_Wasted_Due_To_Skew

 

, (100 - (avg (CurrentPerm) / NULLIFZERO(max (CurrentPerm))*100)) AS Skew_Percent

 

FROM dbc.tablesize ts

 

WHERE databasename = 'name of DB'

 

GROUP by 1,2

 

ORDER BY 5 DESC
 
 

mapsiddiqui 2 posts Joined 07/08
26 Sep 2013

Hi elanir2012,
 
I believe your query should look like this:

SELECT

  DATABASENAME

, TABLENAME

, SUM(CURRENTPERM)/1024/1024/1024 AS CURRENT_PERM

, CAST (CAST (MAX (CURRENTPERM)*(HASHAMP()+1) AS FLOAT) / CAST (1024*1024*1024 AS FLOAT) AS DECIMAL(18,2)) AS EFFECTIVE_PERM_INCLUDING_SKEW

, EFFECTIVE_PERM_INCLUDING_SKEW - CURRENT_PERM AS SPACE_WASTED_DUE_TO_SKEW

, (100 - (AVG (CURRENTPERM) / NULLIFZERO(MAX (CURRENTPERM))*100)) AS SKEW_PERCENT

FROM DBC.TABLESIZE TS

WHERE DATABASENAME = 'XYZ'

GROUP BY 1,2

ORDER BY 6 DESC

 

changed the expression for current_perm.

 

Thanks,

-Anjum.

drmkd17 54 posts Joined 10/12
26 Jan 2014

Hi ,
Please help me with this question. 
Which two methods are available with Teradata to limit data access? (Choose two.)
A. set a user's PERM space to zero
B. restrict access via database profiles
C. require users to access data via macros
D. require users to access data via stored procedures
 
 
I guess the answer should be A, C.
Nt sure. 
What happens if the PERM space of any user is zero.
Please suggest.
 
 
Thanks

Adeel Chaudhry 773 posts Joined 04/08
02 Feb 2014

If the perm-space is zero, user usually fly off the planet.
 
Answer is B & C, as perm-space has nothing to do with data-access rather its for data-storage.

-- If you are stuck at something .... consider it an opportunity to think anew.

dnoeth 4628 posts Joined 11/04
02 Feb 2014

A: wrong, most users will have zero Perm Space, this only restricts the possibility to create objects.
B: a profile doesn't specify any access rights, even setting spool to zero will still allow access which doesn't need spool (PI/USI).
C: yes
D: if C is correct, of course this is correct, too.

Dieter

Adeel Chaudhry 773 posts Joined 04/08
02 Feb 2014

Absolutely correct Dieter .... i was sleepy while mentioning B i guess :)

-- If you are stuck at something .... consider it an opportunity to think anew.

sayaksitex 24 posts Joined 09/12
26 Mar 2014

I do not have data in my table still I see the Current Perm on the higher side. 
Why is this happening? since there is no data in the table the skew factor is 0.
My table is Multiset table with NUPI defined on it.
Please clarify.....
Thanks in advance...!!!
Sayak G

26 Mar 2014

---- Database level Space Check

SELECT 

DatabaseName

,SUM(CurrentPerm)/1024/1024 AS USEDSPACE_IN_MB ---  includes wasted perm due to skew

,MAX(CurrentPerm) * (HASHAMP()+1)/1024/1024 AS USEDSPACE_IN_MB_Skew ---includes wasted perm due to skew

,SUM(MaxPerm)/1024/1024 AS MAXSPACE_IN_MB

,SUM(MaxPerm)/1024/1024/1024 AS MAXSPACE_IN_GB

,SUM(CurrentPerm)/ NULLIFZERO (SUM(MaxPerm)) *100 (FORMAT 'zz9.99%') AS Percentage_Used 

,MAXSPACE_IN_MB- USEDSPACE_IN_MB_Skew AS REMAININGSPACE_IN_MB

FROM DBC.DiskSpace

WHERE DatabaseName =  'databasename' 

GROUP BY DatabaseName

ORDER BY Percentage_Used DESC

HAVING Percentage_Used IS NOT NULL;

 

 

----- Check maximum sized Table

SELECT 

tbl.DatabaseName,

tbl.TableName,

tbl.CreatorName AS CREATOR,

SUM(tsize.currentperm) asPermSize,

MAX(tsize.currentperm)/AVG(tsize.currentperm) (DECIMAL (18,2)) (NAMED SkewRatio),

MAX(tsize.currentperm) (NAMED BytesUsedTopAmp)

FROM dbc.tables tbl

INNER JOIN

dbc.tablesize tsize

ON

tbl.Tablename = tsize.Tablename AND tbl.Databasename = tsize.Databasename AND tbl.Tablekind = 'T'

WHERE tbl.databasename IN ('databasename')

GROUP BY 1,2;

 

 

--- Tables To delete due to Skewness

 

SELECT databasename

,tablename

,100 * (1 - (AVG(currentperm) / MAX(currentperm))) skew_factor

FROM dbc.tablesize

WHERE databasename = 'databasename'

GROUP BY 1,2

ORDER BY 3  DESC

 

--- Table Level  Space Check

SELECT

  DATABASENAME

, TABLENAME

, SUM(CURRENTPERM)/1024/1024/1024 AS CURRENT_PERM

, CAST (CAST (MAX (CURRENTPERM)*(HASHAMP()+1) AS FLOAT) / CAST (1024*1024*1024 AS FLOAT) AS DECIMAL(18,2)) AS EFFECTIVE_PERM_INCLUDING_SKEW

, EFFECTIVE_PERM_INCLUDING_SKEW - CURRENT_PERM AS SPACE_WASTED_DUE_TO_SKEW

, (100 - (AVG (CURRENTPERM) / NULLIFZERO(MAX (CURRENTPERM))*100)) AS SKEW_PERCENT

FROM DBC.TABLESIZE TS

WHERE DATABASENAME  LIKE  '<DataBaeName>%' AND TABLENAME = '<TableName>'

GROUP BY 1,2

ORDER BY 6 DESC;

krishaneesh 140 posts Joined 04/13
26 Mar 2014

@Sayak, at the time of table creation by default, the header is created on all amps. Though the table does not have any data, the table occupies some currentperm due to the header that was created.

You must sign in to leave a comment.