Wednesday 30 December 2009

Sybase Fragmentation Report SQL

From below sqls , we can have a very good idea about the fragmentation going on in Sybase user tables. Based on the reports you can think of reorg, rebuilds, etc.


ASE 12.5 Compatible

select object_name(i.id) AS OBJECT_NAME, i.name AS INDEXNAME,CASE WHEN convert(varchar(8),lockscheme(i.id)) = "allpages" THEN "APL"WHEN convert(varchar(8),lockscheme(i.id)) = "datapages" THEN "DPG"ELSE "DRW" END AS "SCHEME",CASEWHEN i.status2 & 512 = 512 OR i.status & 16 = 16THEN "CLI"WHEN i.indid = 0THEN "TBL"ELSE "NCL"END AS "IDXTYPE","DPCR"=convert(decimal(6,2),isnull(derived_stat(i.id,i.indid,"dpcr"),0.00)),"DRCR"=convert(decimal(6,2),isnull(derived_stat(i.id,i.indid,"drcr"),0.00)),"SPUT"=convert(decimal(6,2),isnull(derived_stat(i.id,i.indid,"sput"),0.00)),"LGIO"=convert(decimal(6,2),isnull(derived_stat(i.id,i.indid,"lgio"),0.00)),"IPCR"=convert(decimal(6,2),isnull(derived_stat(i.id,i.indid,"ipcr"),0.00)),"FROW"=ltrim(convert(varchar(8),str(round(convert(double precision,t.forwrowcnt),16),8,2))),"DROW"=ltrim(convert(varchar(8),str(round(convert(double precision,t.delrowcnt),16),8,2))),"NROW"=ltrim(convert(varchar(8),str(round(convert(double precision,t.rowcnt),16),8,2))),"SPACEKB"=convert(numeric(15, 2), (reserved_pgs(i.id, i.doampg) + reserved_pgs(i.id, i.ioampg))*2)from sysindexes i, sysobjects o,systabstats twhere o.id=i.id and o.type = 'U'and o.id=t.id and i.id=t.id and i.indid=t.indidand object_name(o.id) = 'tPrice'order by lockscheme(i.id)


ASE 15.0 Compatible

select object_name(i.id) AS OBJECTNAME, i.name AS INDEXNAME,CASE WHEN convert(varchar(8),lockscheme(i.id)) = "allpages" THEN "APL"WHEN convert(varchar(8),lockscheme(i.id)) = "datapages" THEN "DPG"ELSE "DRW" END AS "SCHEME",CASEWHEN i.status2 & 512 = 512 OR i.status & 16 = 16THEN "CLI"WHEN i.indid = 0THEN "TBL"ELSE "NCL"END AS "IDXTYPE","DPCR"=convert(decimal(6,2),isnull(derived_stat(i.id,i.indid,"dpcr"),0.00)),"DRCR"=convert(decimal(6,2),isnull(derived_stat(i.id,i.indid,"drcr"),0.00)),"SPUT"=convert(decimal(6,2),isnull(derived_stat(i.id,i.indid,"sput"),0.00)),"LGIO"=convert(decimal(6,2),isnull(derived_stat(i.id,i.indid,"lgio"),0.00)),"IPCR"=convert(decimal(6,2),isnull(derived_stat(i.id,i.indid,"ipcr"),0.00)),"FROW"=ltrim(convert(varchar(8),str(round(convert(double precision,t.forwrowcnt),16),8,2))),"DROW"=ltrim(convert(varchar(8),str(round(convert(double precision,t.delrowcnt),16),8,2))),"NROW"=ltrim(convert(varchar(8),str(round(convert(double precision,t.rowcnt),16),8,2))),"SPACEKB"=reserved=convert(numeric(15, 2), (reserved_pages(db_id(),i.id, i.indid))*2)from sysindexes i, sysobjects o,systabstats twhere o.id=i.id and o.type = 'U'and o.id=t.id and i.id=t.id and i.indid=t.indid

No comments:

Post a Comment