Thursday, January 3, 2013

Defragmentation in Teradata


This activity is automatically handled by Teradata File System when there is lot of fragmented sectors across cylinders.

Whenever a new table is created and data is loaded into it, the data is stored in Cylinders on a particular AMP. Data is always pushed into AMP cylinders as DATA BLOCKS.
IF the table is abused so much by doing DML operations like Update, delete and inserts on regular basis the data would have scattered across various cylinders as fragments.  When data blocks are scattered across cylinder lot of empty sectors occur. Well this space might be sufficient for small updates or inserts happening on table.
What if we needed large amount of Data to be inserted into table? The data blocks may not fit into cylinder as there are no continuous sectors available.

To overcome this problem Teradata File system (FS) runs the defragmentation to push all the data into continuous blocks.  This will create all the data blocks to be packed towards top of the cylinder making way for free sectors towards the bottom.   

Defragmentation does not free up cylinders, it only groups empty sectors and push towards bottom of cylinder
Can I run defragmentation manually?
Yes indeed!! By using ferret --> defrag   y ;
We should note that defragmentation will not run unless 25% of sectors are fragmented on cylinder.

By using “FORCE” option we can override this 25% factor and defragmentation is initiated.

Please note

  • Defragmentation is entirely controlled by DefragLowCylprod parameter in DBSControl . Setting this parameter high will result defragmentation to run frequently.  We can set up-to 65535 as it is maximum limit for cylinders per AMP.
  • Defragmentation consumes lot of CPU resources and hence should be planned out !! 


  1. what is the difference b/w packdisk and Defragmentation ?

  2. Hi ashok,

    Defragmentation will try to remove empty spaces in a cylinder so that cylinder can accomodate more data.

    Please refer to my post of defragmentation.

    Where as packdisk will help to cleanup/ freeup cylinders in a given amp so amp does not run out of free space.