none
两段计算行大小的脚本 有差异 RRS feed

  • 问题

  • 脚本1:

    --数据分析
    
    CREATE TABLE #tablespaceinfo
        (
          nameinfo VARCHAR(50) ,
          rowsinfo BIGINT ,
          reserved VARCHAR(20) ,
          datainfo VARCHAR(20) ,
          index_size VARCHAR(20) ,
          unused VARCHAR(20)
        )  
     
    DECLARE @tablename VARCHAR(255);  
     
    DECLARE Info_cursor CURSOR
    FOR
        SELECT  '[' + [name] + ']'
        FROM    sys.tables
        WHERE   type = 'U';  
     
    OPEN Info_cursor  
    FETCH NEXT FROM Info_cursor INTO @tablename  
     
    WHILE @@FETCH_STATUS = 0
        BEGIN 
            INSERT  INTO #tablespaceinfo
                    EXEC sp_spaceused @tablename  
            FETCH NEXT FROM Info_cursor  
        INTO @tablename  
        END 
     
    CLOSE Info_cursor  
    DEALLOCATE Info_cursor  
     
    --创建临时表
    CREATE TABLE [#tmptb]
        (
          TableName VARCHAR(50) ,
          DataInfo BIGINT ,
          RowsInfo BIGINT ,
          Spaceperrow  AS ( CASE RowsInfo
                             WHEN 0 THEN 0
                             ELSE CAST(DataInfo AS decimal(18,2))/CAST(RowsInfo AS decimal(18,2))
                           END ) PERSISTED
        )
    
    --插入数据到临时表
    INSERT  INTO [#tmptb]
            ( [TableName] ,
              [DataInfo] ,
              [RowsInfo]
            )
            SELECT  [nameinfo] ,
                    CAST(REPLACE([datainfo], 'KB', '') AS BIGINT) AS 'datainfo' ,
                    [rowsinfo]
            FROM    #tablespaceinfo
            ORDER BY CAST(REPLACE(reserved, 'KB', '') AS INT) DESC  
    
    
    --汇总记录
    SELECT  [tbspinfo].* ,
            [tmptb].[Spaceperrow] AS '每行记录大概占用空间(KB)'
    FROM    [#tablespaceinfo] AS tbspinfo ,
            [#tmptb] AS tmptb
    WHERE   [tbspinfo].[nameinfo] = [tmptb].[TableName]
    ORDER BY [nameinfo]
    
    DROP TABLE [#tablespaceinfo]
    DROP TABLE [#tmptb]


    脚本2:

    USE [sss]
    SELECT  SCHEMA_NAME(t.schema_id) AS [Schema] ,
            t.name AS TableName ,
            i.rows AS [RowCount],
    		i.[minlen] AS 'rowlen'
    FROM    sys.tables AS t ,
            sysindexes AS i
    WHERE   t.object_id = i.id
            AND i.indid <= 1
    ORDER BY t.[name]

    测试环境:sqlserver2008r2

    不知道minlen的单位是什么,在sqlserver技术内幕里也没有提到相关的资料

    2014年8月19日 7:29

全部回复

  • minlen 联机帮助上写的, 行的最小大小

    你自己算出来的那个每行记录, 是用 size/行数, 这个是平均值

    平均值和最小值不一样是很正常的事吧

    2014年8月19日 8:01
  • 另外一个, sp_spaceused 是根据 page 算的, 一个 page 8K, 就算你只有一条升为为 1字节的记录, 那么也占用一个 page, sp_spaceused 出来的是 8 KB

    而 sysindexs 的 minlen 是实际长度, 只有一个字节, 那么结果就是 1 (这个长度跟表结构有关, 不是纯粹的字段值长度, 具体你可以看下联机帮助中,“估计表的大小”相关的话题)

    所以就算只有一条记录, 这两个值也没有可比性

    2014年8月19日 8:06
  • 周建的意思是minlen的单位是字节?

    其实我一直疑惑的是想比较准确的得出表的每条记录的长度

    如果单位是字节的话,那么aa表里的行长最大的那条记录是412字节?

    http://msdn.microsoft.com/zh-cn/library/ms190283.aspx

    sql6.5技术内幕


    2014年8月19日 9:25
  • 而且我也好奇,既然数据页中的minlen会记录到sysindexes表,那么应该是取表中某一个数据页面的行长最大的那个数据页面的minlen放到sysindexes吧
    2014年8月19日 9:40
  • 而且我也好奇,既然数据页中的minlen会记录到sysindexes表,那么应该是取表中某一个数据页面的行长最大的那个数据页面的minlen放到sysindexes吧

    关于 minlen, 建议还是以联机帮助上的说明为准, sysindexes 里面,除了  minlen, 还有 xmaxlen 的

    http://msdn.microsoft.com/zh-cn/library/ms190283.aspx


    2014年8月20日 1:02
  • 你给出的联机帮助跟我给出的联机帮助是一样的,估计现在已经改名了,之前是叫maxlen的

    资料太少

    2014年8月20日 1:17
  • 在 2012 中实际测试了一下, 也查了一些 2008R2 的库, 我前面的说法是不正确的

    sysindex 中的 minlen 与数据无关, 只与表结构有关

    minlen 表示的应该只是定长字段的长度, 4+sum( 所有定长字段升序, bit 字段 /8 )

    xmaxlen 的没看出来是怎么算的, 查询了一些库, 最大的值是 2048, maxlen 是固定 8000

    2014年8月20日 1:59
  • len 的计算应该还考虑了字段的 null able 设置, 另外,  minlen 会有大于 xmaxlen 的情况

    sysindex 在 2005 之后就被代替了,里面的东东不知道适用性还有多少, 不打算再进一步研究了

    2014年8月20日 2:17
  • sysindex只是封装了,不是没有用,新建表的时候,信息依然会保存到sysindex

    2014年8月20日 2:27
  • 建表测试了, 也查了现在的库中的表,  minlen 绝大部分是定长+4, 有的表不是, 是小于定长之和,出现的情况少

    maxlen 查出来是固定的 8000, xmaxlen 查查出来的也不是定长+变长,而且最大的值是 2048

    没有2005/2000, 不知道这些版本如何, 另外,可以看sysindexes的定义,它取的 minlen和 maxlen是用 indexproperty函数,这两个函数的说明中, 并没有 minlen和maxlen的说法 (2008R2的帮助)

    所以我说的是不知道适用性还有多大

    2014年8月20日 3:40
  • 这些基本都是undocument的,而且一般我们只是用数据库的人不会用到这些系统表,只需要用到封装好的视图

    我自己再研究一下吧,谢谢周建大侠。。。

    2014年8月20日 3:46
  • “那些只包含定长数据的行”,你的表全是定长数据类型?

    想不想时已是想,不如不想都不想。

    2014年8月24日 6:16
    版主
  • 不是全部定长,现在还是使用脚本1来大概估计行占用空间

    minlen和maxlen还是无法得出来

    2014年8月24日 12:43