Tuesday, August 20, 2013

Find Skewed tables

we can find the skewed tables of a system by using below query, we can use the skewed tables for Primary Index revisit for performance enhancement of overall system.

Lock Dbc.TableSize For Access
Lock Dbc.diskspace For Access
SELECT b.databasename,b.TableName AS TableName,c.tablekind,SUM(CurrentPerm) /1024/1024 AS "CurrentPerm(MB)",
SUM(PeakPerm)/1024/1024 AS "PeakPerm(MB)",(100 - (AVG(CurrentPerm)/MAX(CurrentPerm)*100)) AS SkewFactor
FROM Dbc.TableSize b,dbc.tables c, (
Select
  databasename ,zeroifnull((Max(currentperm) - Avg(currentperm))/max(nullifzero(currentperm)) * 100)  As SKW
From dbc.diskspace
Group By 1
Having SKW >=10 ) a
WHERE b.DataBaseName=a.databasename
and c.tablename=b.tablename
and c.tablekind='T'
Group By 1,2,3
Having SkewFactor >=20
Order By "CurrentPerm(MB)" desc;

No comments:

Post a Comment