SkewFactor in WinDDI

[Lysen]

Hi folks,

When you look at table space in WinDDI, what does SKEWFACTOR represent? What is the
highest acceptable value there? Most tables have skewfactor 0 (which is good?) and some have as high as 25... How can you lower this skewfactor?

Greetings, Lysen

 

[dnoeth]

Highlight the "Table Space" command and press F1 to open the help file.

And then press F12 to open the SQL History window and look for a row with function "Table Space" to see the calculation:
(100 - (AVG(CurrentPerm)/MAX(CurrentPerm)*100)) AS SkewFactor

The only way to lower the skewfactor is to create a new table with a different (hopefully better) primary index and insert/select the data.

Dieter

 

[tdatgod]

You can check the hash distribution ( number of rows per amp ) on any table by executing.



sel hashamp(hashbucket(hashrow(<pk>))),count(*)
from your_table
order by 1
group by 1;


<pk> are your prime Key fields of
your_table.



I think the number of rows per AMP is a better judge of SKEW than current perm.


Rows with Compressible or nullable fields may contribute to SKEW if some rows with real values are propotionatly bigger than the rows where the fileds have been compressed or Nulled.


In addition a SMALL table with the number of rows < Number of AMPS will have a HUGE SKEW Factor because the CURRPERM for a table on an amp with no rows will be the size of a table header.