How do I determine the row size of all tables in a particular DB?

  • Greg J

    SSCarpal Tunnel

    Points: 4288

    I have a feeling I'm exceeding 8060 on a lot of tables. I'd like a definitive list of tables I should be looking into.

    Does anyone have a script that will show me this? Any ideas on system sprocs or extended sprocs that might get me started?

    tx

  • Lee Dise

    SSCarpal Tunnel

    Points: 4384

    I don't believe any row *can* exceed the 8K page length in SQL Server 2000.  There is only a potential for an error, should any row *try* to exceed that length.

    There may be a more precise method, but try this:

    SELECT OBJECT_NAME (id) tablename

         , COUNT (1)        nr_columns

         , SUM (length)     maxrowlength

    FROM   syscolumns

    GROUP BY OBJECT_NAME (id)

    ORDER BY OBJECT_NAME (id)

     

     

  • Greg J

    SSCarpal Tunnel

    Points: 4288

    nice. that did the trick. and you're right, I was looking for the tables that *could* have exceeded the 8060 limit.

    I was getting some system tables (some are over 8060!) as well as stored procedures the way you had it, here is my modified version:

    SELECT OBJECT_NAME (sc.[id]) tablename

    , COUNT (1) nr_columns

    , SUM (sc.length) maxrowlength

    FROM syscolumns sc

    join sysobjects so

    on sc.[id] = so.[id]

    WHERE so.xtype = 'U'

    GROUP BY OBJECT_NAME (sc.[id])

    ORDER BY SUM (sc.length) desc

  • Lee Dise

    SSCarpal Tunnel

    Points: 4384

    I'm glad that helped.  If you want to find the largest *actual* row lengths for a table, something like the following would work in most cases:

      DECLARE @sql        VARCHAR (8000)

            , @tablename  VARCHAR (255)

            , @delim      VARCHAR (3)

            , @q          CHAR (1)

      SELECT @tablename = '{table name}'

           , @q         = CHAR (39)

      SELECT @delim = ''

           , @sql   = 'SELECT '

      SELECT @sql   = @sql

                    + @delim

                    + 'DATALENGTH ([' + name + '])'

           , @delim = ' + '

      FROM   syscolumns

      WHERE  id = OBJECT_ID (@tablename)

      ORDER BY colid

      SELECT @sql = @sql + ' rowlength'

                  + ' FROM [' + @tablename + ']'

           , @sql =  'SELECT MAX (rowlength)'

                  + ' FROM (' + @sql + ') rowlengths'

      PRINT @sql

      EXEC (@sql)

     

    I say "in most cases", because if the number of columns or the lengths of their names add up to more the 8K characters, you won't get a complete query.  You can code around that, though, by executing more than one T-SQL string and using "overflow" variables.

  • Michael Lee-169622

    SSCommitted

    Points: 1721

    Lee's script is great as long as you do not have a row with nulls. I modified the query as follows to eliminate that problem:

    DECLARE @sql        VARCHAR (8000)

            , @tablename  VARCHAR (255)

            , @delim      VARCHAR (3)

            , @q          CHAR (1)

      SELECT @tablename = 'uspsorderbase'

           , @q         = CHAR (39)

      SELECT @delim = ''

           , @sql   = 'SELECT '

      SELECT @sql   = @sql

                    + @delim

                    + 'ISNULL(DATALENGTH ([' + name + ']),0)'

           , @delim = ' + '

      FROM   syscolumns

      WHERE  id = OBJECT_ID (@tablename)

      ORDER BY colid

      SELECT @sql = @sql + ' rowlength'

                  + ' FROM [' + @tablename + ']'

           , @sql =  'SELECT MAX (rowlength)'

                  + ' FROM (' + @sql + ') rowlengths'

      PRINT @sql

      EXEC (@sql)

     

     


    Shalom!,

    Michael Lee

  • ruchir-628038

    SSC Veteran

    Points: 230

    Hi guys,

    I had written a small sql scalar function that takes a table name and returns available bytes for column expansion

    Notes:

    1. The maximum size in bytes that a table can hold in SQL Server is 8060 including internal book keeping bytes

    2. If there is even one variable column 2 bytes are added as internal bytes

    3. A header of 4 bytes is always added to a row

    4. Each variable column contribute 2 bytes towards the total internal bytes

    5. Only the maximum bytes of all variable columns in the row are kept in the same table page – anything exceeding it is rolled out to an external page

    Hope it helps

    -- ================================================

    -- Template generated from Template Explorer using:

    -- Create Scalar Function (New Menu).SQL

    --

    -- Use the Specify Values for Template Parameters

    -- command (Ctrl-Shift-M) to fill in the parameter

    -- values below.

    --

    -- This block of comments will not be included in

    -- the definition of the function.

    -- ================================================

    SET ANSI_NULLS ON

    GO

    SET QUOTED_IDENTIFIER ON

    GO

    -- =============================================

    -- Author: Ruchir T

    -- Create date: 01/02/2008

    -- Description: returns the number of bytes left to use for creating new columns

    -- =============================================

    CREATE FUNCTION available_tablerowsize

    (

    -- Add the parameters for the function here

    @tablename char(50)

    )

    RETURNS int

    AS

    BEGIN

    -- variables to track fixed and variable column sizes

    DECLARE @num_columns int

    DECLARE @result int

    DECLARE @num_fixed_columns int

    DECLARE @fixed_data_size int

    DECLARE @var_data_size int

    DECLARE @num_var_columns int

    DECLARE @max_var_size int

    DECLARE @null_bitmap_size int

    DECLARE @row_size int

    -- Find the total number of columns

    select @num_columns = count(*)

    from syscolumns,systypes

    where syscolumns.id=object_id(@tablename)

    and syscolumns.xtype=systypes.xtype

    -- Find the size occupied by fixed length columns (Note: not possible to exist outside the 8060 bytes limit)

    select @num_fixed_columns = count(*)

    from syscolumns,systypes

    where syscolumns.id=object_id(@tablename)

    and syscolumns.xtype=systypes.xtype and systypes.variable=0

    select @fixed_data_size = sum(syscolumns.length)

    from syscolumns,systypes

    where syscolumns.id=object_id(@tablename)

    and syscolumns.xtype=systypes.xtype and systypes.variable=0

    -- Find the size occupied by variable length columns within the 8060 page size limit

    -- number of variable length columns

    select @num_var_columns=count(*)

    from syscolumns, systypes

    where syscolumns.id=object_id(@tablename)

    and syscolumns.xtype=systypes.xtype and systypes.variable=1

    -- max size of all variable length columns

    select @max_var_size =max(syscolumns.length)

    from syscolumns,systypes

    where syscolumns.id=object_id(@tablename)

    and syscolumns.xtype=systypes.xtype and systypes.variable=1

    -- calculate variable length storage

    begin

    if @num_var_columns>0

    set @var_data_size=2+(@num_var_columns*2)+@max_var_size

    --set @var_data_size = @num_var_columns*24

    else

    set @var_data_size=0

    end

    -- If there are fixed-length columns in the table, a portion of the row, known as the null bitmap, is reserved to manage column nullability.

    select @null_bitmap_size = 2 + ((@num_columns+7)/8)

    -- Calculate total rowsize

    select @row_size = @fixed_data_size + @var_data_size + @null_bitmap_size + 4

    -- Return the available bytes in the row available for expansion

    select @result = 8060 - @row_size

    RETURN @result

    END

    GO

  • markanderson

    SSC-Addicted

    Points: 402

    Guys:

    This is an alternate look at the large tables in a database. Thought it may help.:D

    --drop table #tmp

    create table #tmp(

    [name] varchar(100),

    [rows] int,

    [reserved] varchar(20),

    [data] varchar(20),

    [index size] varchar(20),

    [unused] varchar(20)

    )

    insert into #tmp

    exec sp_msforeachtable'

    exec sp_spaceused ''?'''

    select * from #tmp

    order by rows desc

  • Scalability Doug

    SSCertifiable

    Points: 5291

    Mark has the best solution with sp_spaceused. Just beware if your database has multiple schemas. It will return schema-less results. For example

    exec sp_spaceused 'dbo.summarydata'

    exec sp_spaceused 'test.summarydata'

    returns the following results...

    name rows reserved Data index unused

    SummaryData 918 272 KB 104 KB 152 KB 16 KB

    summarydata 1 24 KB 16 KB 8 KB 0 KB

    DAB

  • RBarryYoung

    SSC Guru

    Points: 143327

    SQLServerLifer (3/12/2008)


    Mark has the best solution with sp_spaceused. Just beware if your database has multiple schemas. It will return schema-less results. For example

    exec sp_spaceused 'dbo.summarydata'

    exec sp_spaceused 'test.summarydata'

    returns the following results...

    name rows reserved Data index unused

    SummaryData 918 272 KB 104 KB 152 KB 16 KB

    summarydata 1 24 KB 16 KB 8 KB 0 KB

    I can fix that problem AND get rid of the Cursor in sp_MSForEach as follows:declare @PageSize float

    select @PageSize=v.low/1024.0 from master.dbo.spt_values v where v.number=1 and v.type='E'

    SELECT SCHEMA_NAME(tbl.schema_id) as [Schema], tbl.*, idx.index_id,

    CAST(CASE idx.index_id WHEN 1 THEN 1 ELSE 0 END AS bit) AS [HasClusteredIndex],

    ISNULL( ( select sum (spart.rows) from sys.partitions spart where spart.object_id = tbl.object_id and spart.index_id < 2), 0) AS [RowCount],

    , ISNULL((select @PageSize

    * SUM(a.used_pages - CASE WHEN a.type <> 1 THEN a.used_pages WHEN p.index_id < 2 THEN a.data_pages ELSE 0 END)

    FROM sys.indexes as i

    JOIN sys.partitions as p ON p.object_id = i.object_id and p.index_id = i.index_id

    JOIN sys.allocation_units as a ON a.container_id = p.partition_id

    where i.object_id = tbl.object_id )

    , 0.0) AS [IndexSpaceUsed]

    , ISNULL((select @PageSize

    * SUM(CASE WHEN a.type <> 1 THEN a.used_pages WHEN p.index_id < 2 THEN a.data_pages ELSE 0 END)

    FROM sys.indexes as i

    JOIN sys.partitions as p ON p.object_id = i.object_id and p.index_id = i.index_id

    JOIN sys.allocation_units as a ON a.container_id = p.partition_id

    where i.object_id = tbl.object_id)

    , 0.0) AS [DataSpaceUsed]

    FROM sys.tables AS tbl

    INNER JOIN sys.indexes AS idx ON idx.object_id = tbl.object_id and idx.index_id < 2

    [font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
    Proactive Performance Solutions, Inc.
    [/font]
    [font="Verdana"] "Performance is our middle name."[/font]

  • Ahmad Osama

    SSCertifiable

    Points: 7676

    Hi All,

    what effect will i have on the performance if a table exceeds 8062 size?

    Once I received an error which said that any update to the table will not take place as row size has exceeded 8062 bytes.How ever i was easily able to update and insert into the table.

    Regards,

    Ahmad

    Regards,
    [font="Verdana"]Sqlfrenzy[/font]

  • dokledon

    SSC-Addicted

    Points: 489

    Hi,

    On RBarryYoung's script, Final comma on line 8 must be deleted.

Viewing 11 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply