SQL Server Performance

space used summary for tables

Discussion in 'Contribute Your SQL Server Scripts' started by joechang, Jun 8, 2006.

  1. joechang New Member

    /*
    ** Copyright Joe Chang, Elemental Inc. 2003 - 2006
    ** this is the script i use to get summary info on table size etc
    ** i recall that i started with the code for sp-spaceused,
    ** set it to list all tables,
    ** then i added stuff like average row size, has clustered index, number of nonclustered ** indexes, number of stats (not on indexes), max index depth, the rowmodctr from
    ** sysindexes,

    ** i suppose i could add whether there are text/image entries
    */

    set nocount on

    if exists (select * from tempdb..sysobjects where name LIKE '#spt_space%' and xtype = 'U')
    drop table #spt_space

    create table #spt_space (
    idint,
    uidsmallint,
    namevarchar(255),
    rowsbigint null,
    reservedfloat null,
    datafloat null,
    indexpfloat null,
    unusedfloat null,
    Clust int,
    IX_Cnt int,
    Stat_Cntint,
    IX_Depthint,
    RowModint
    )
    GO
    insert into #spt_space (id, uid, name, rows, reserved, data, indexp, Clust,IX_Cnt,Stat_Cnt, IX_Depth, RowMod)
    select o.id, o.uid, o.name,
    rows = (select rows from sysindexes i WITH (NOLOCK) where i.id = o.id and indid < 2)
    ,reserved = (select sum(reserved) from sysindexes i WITH (NOLOCK) where i.id = o.id and indid in (0, 1, 255))
    ,data = (select sum(dpages) from sysindexes i WITH (NOLOCK) where i.id = o.id and indid < 2) +
    (select isnull(sum(used), 0) from sysindexes i WITH (NOLOCK) where i.id = o.id and indid =255)
    ,indexp = (select sum(used) from sysindexes i WITH (NOLOCK) where i.id = o.id and indid in (0, 1, 255))
    ,Clust = CASE WHEN (select count(*) from sysindexes i WITH (NOLOCK) where i.id = o.id and indid =1)=1 THEN 1 ELSE 0 END
    ,IX_Cnt = (select count(*) from sysindexes i WITH (NOLOCK) where i.id = o.id and indid BETWEEN 2 AND 254 AND status&64 =0)
    ,Stat_Cnt = (select count(*) from sysindexes i WITH (NOLOCK) where i.id = o.id and indid BETWEEN 2 AND 254 AND status&64 =64)
    ,IX_Depth = (select ISNULL(MAX(indexProperty(id, i.name, 'indexDepth')),0) from sysindexes i WITH (NOLOCK) where i.id = o.id and indid BETWEEN 1 AND 254 AND status&64 =0)
    ,RowMod = (select sum(rowmodctr) from sysindexes i WITH (NOLOCK) where i.id = o.id and indid in (0, 1))
    from sysobjects o WITH (NOLOCK)
    where xtype IN ('U') -- add 'S' for system tables
    GO
    update #spt_space set unused = reserved - indexp
    GO
    update #spt_space set indexp = indexp - data
    GO
    select --[id] = 0,
    [User] = 'User'
    , [Table] = ' Total'
    , [rows] = SUM(rows)
    , [reserved KB] = CONVERT(bigint,SUM(reserved * d.low / 1024.))
    , [data KB] = CONVERT(bigint,SUM(data * d.low / 1024.))
    , [index KB]= CONVERT(bigint,SUM(indexp * d.low / 1024.))
    , [unused] = CONVERT(bigint,SUM(unused * d.low / 1024.))
    , [A.RowSz]= str(SUM(data * d.low)/SUM(rows),8,1)
    , [Clust] = SUM(Clust)
    , [IX's] = SUM(IX_Cnt)
    , [Stat's] = SUM(Stat_Cnt)
    , [IX_Depth] = 0
    --, [dpages] = CONVERT(int,SUM(data))
    , [RowMod] = 0
    from #spt_space, master.dbo.spt_values d
    where d.number = 1 and d.type = 'E' --AND rows > 0
    UNION ALL
    select --[id],
    [User] = USER_NAME(uid)
    , [Table] = substring(#spt_space.name, 1, 40)
    , [rows] = rows
    , [reserved KB]= CONVERT(bigint,reserved * d.low / 1024.)
    , [data KB]= CONVERT(bigint,data * d.low / 1024.0)
    , [index KB] = CONVERT(bigint,indexp * d.low / 1024.)
    , [unused] = CONVERT(bigint,unused * d.low / 1024.)
    , [A.RowSz] = str((d.low*data)/(rows+1),8,1)
    , [Clust] = Clust
    , [IX's] = IX_Cnt
    , [Stat's] = Stat_Cnt
    , [IX_Depth]= IX_Depth
    --, [dpages] = convert(int,data)
    , [RowMod]
    from #spt_space, master.dbo.spt_values d
    where d.number = 1 and d.type = 'E' --AND rows > 0
    order by [reserved KB] DESC
    --order by rows DESC
    --order by [Table] --#spt_space.nam e
    GO

    drop table #spt_space
    GO

Share This Page