none
請問如何查資料庫中Table所占空間 RRS feed

  • 問題

  • 請問如何查資料庫中Table所占空間

    透過sql command查

    因從摘要/報表看無法排序或挑選最占空間的Table

    謝謝

    2006年11月1日 上午 05:00

解答

  • SQL 預存程序中好像也沒有可以取得 table 所占空間的方法 .
    你可能要自己 "估算" (estimate) table 可能占的空間 .

    簡單且不精確的算法是各欄位的 size 加總再乘以列數 .
    要詳細的算法的話可能還要考量到 index,這裡有資料:

    http://msdn2.microsoft.com/en-us/library/ms175991.aspx

    2006年11月1日 上午 05:40
    版主
  • 您好:
    範例程式如下:

    SQL Sever 2000/2005 請使用:                                                                  
    DBCC SHOWCONTIG ('我的資料表') WITH TABLERESULTS, ALL_INDEXES

    SQL Sever 2005 建議使用:                                                                              
    SELECT * FROM sys.dm_db_index_physical_stats                                              
      (DB_ID(N'我的資料庫'), OBJECT_ID(N'我的資料表'), NULL, NULL , 'DETAILED');

    查出來的結果所代表的意義請自行參閱線上叢書的說明,然後加以計算才能得到最後的結果

    還有一種比較簡單的方法,在此以 SQL Server 2005 為例進行說明,只需在「查詢編輯器」中,鍵入下列指令:
                                    
    SELECT * FROM 我的資料表

    然後開啟「顯示估計執行計劃」,將「估計的資料列數目」與「估計的資料列大小」相乘就是結果了

    2006年11月1日 上午 06:00
    版主
  • Hi: 您好,

    可用估算資料表大小的方式其實有很多,筆者提供個範例給您做為參考:

    -- 建立預存程序: upspaceused
    CREATE procedure [dbo].[upspaceused]
    @objname nvarchar(776) = null,  
    @updateusage varchar(5) = false  
         
    as

    declare @id int   
      ,@type character(2)
      ,@pages bigint   
      ,@dbname sysname
      ,@dbsize bigint
      ,@logsize bigint
      ,@reservedpages  bigint
      ,@usedpages  bigint
      ,@rowCount bigint


    if @updateusage is not null
     begin
      select @updateusage=lower(@updateusage)

      if @updateusage not in ('true','false')
       begin
        raiserror(15143,-1,-1,@updateusage)
        return(1)
       end
     end

    if @objname IS NOT NULL
    begin

     select @dbname = parsename(@objname, 3)

     if @dbname is not null and @dbname <> db_name()
      begin
       raiserror(15250,-1,-1)
       return (1)
      end

     if @dbname is null
      select @dbname = db_name()

     /*
     **  Try to find the object.
     */
     SELECT @id = object_id, @type = type FROM sys.objects WHERE object_id = object_id(@objname)

     -- Translate @id to internal-table for queue
     IF @type = 'SQ'
      SELECT @id = object_id FROM sys.internal_tables WHERE parent_id = @id and internal_type = 201 --ITT_ServiceQueue

     /*
     **  Does the object exist?
     */
     if @id is null
      begin
       raiserror(15009,-1,-1,@objname,@dbname)
       return (1)
      end

     -- Is it a table, view or queue?
     IF @type NOT IN ('U ','S ','V ','SQ','IT')
     begin
      raiserror(15234,-1,-1)
      return (1)
     end
    end

    if @updateusage = 'true'
     begin
      if @objname is null
       dbcc updateusage(0) with no_infomsgs
      else
       dbcc updateusage(0,@objname) with no_infomsgs
      print ' '
     end

    set nocount on

    if @id is null
    begin
     select @dbsize = sum(convert(bigint,case when status & 64 = 0 then size else 0 end))
      , @logsize = sum(convert(bigint,case when status & 64 <> 0 then size else 0 end))
      from dbo.sysfiles

     select @reservedpages = sum(a.total_pages),
      @usedpages = sum(a.used_pages),
      @pages = sum(
        CASE
         -- XML-Index and FT-Index-Docid is not considered "data", but is part of "index_size"
         When it.internal_type IN (202,204) Then 0
         When a.type <> 1 Then a.used_pages
         When p.index_id < 2 Then a.data_pages
         Else 0
        END
       )
     from sys.partitions p join sys.allocation_units a on p.partition_id = a.container_id
      left join sys.internal_tables it on p.object_id = it.object_id

     select
      database_name = db_name(),
      database_size = ltrim(str((convert (dec (15,2),@dbsize) + convert (dec (15,2),@logsize))
       * 8192 / 1048576,15,2)),
      'unallocated space' = ltrim(str((case when @dbsize >= @reservedpages then
       (convert (dec (15,2),@dbsize) - convert (dec (15,2),@reservedpages))
       * 8192 / 1048576 else 0 end),15,2))


     select
      reserved = ltrim(str(@reservedpages * 8192 / 1024.,15,0)),
      data = ltrim(str(@pages * 8192 / 1024.,15,0)),
      index_size = ltrim(str((@usedpages - @pages) * 8192 / 1024.,15,0)),
      unused = ltrim(str((@reservedpages - @usedpages) * 8192 / 1024.,15,0))
    end


    else
    begin

     SELECT
      @reservedpages = SUM (reserved_page_count),
      @usedpages = SUM (used_page_count),
      @pages = SUM (
       CASE
        WHEN (index_id < 2) THEN (in_row_data_page_count + lob_used_page_count + row_overflow_used_page_count)
        ELSE lob_used_page_count + row_overflow_used_page_count
       END
       ),
      @rowCount = SUM (
       CASE
        WHEN (index_id < 2) THEN row_count
        ELSE 0
       END
       )
     FROM sys.dm_db_partition_stats
     WHERE object_id = @id;


     IF (SELECT count(*) FROM sys.internal_tables WHERE parent_id = @id AND internal_type IN (202,204)) > 0
     BEGIN

      SELECT
       @reservedpages = @reservedpages + sum(reserved_page_count),
       @usedpages = @usedpages + sum(used_page_count)
      FROM sys.dm_db_partition_stats p, sys.internal_tables it
      WHERE it.parent_id = @id AND it.internal_type IN (202,204) AND p.object_id = it.object_id;
     END

     SELECT
      name = OBJECT_NAME (@id),
      rows = convert (char(11), @rowCount),
      reserved = LTRIM (STR (@reservedpages * 8, 15, 0)),
      data = LTRIM (STR (@pages * 8, 15, 0)),
      index_size = LTRIM (STR ((CASE WHEN @usedpages > @pages THEN (@usedpages - @pages) ELSE 0 END) * 8, 15, 0)),
      unused = LTRIM (STR ((CASE WHEN @reservedpages > @usedpages THEN (@reservedpages - @usedpages) ELSE 0 END) * 8, 15, 0))

    end


    return (0)
    GO

    -- 以上是建立預存程序

    -- 以下是依據配置(exntent)來排序
    DECLARE @tbl TABLE (tblname sysname, rows int,
     reserved int, data int,
     index_size int, unused int)
    INSERT @tbl
    EXEC sp_msforeachtable @command1="upspaceused '?'"

    SELECT tblname N'資料表',
     rows '資料列筆數',
     reserved N'配置(extent)的空間總量(KB)',
     data N'資料所用的空間總量(KB)',
     index_size N'索引所用的空間總量(KB)'
    FROM @tbl
    ORDER BY 3 DESC


    希望對您有幫助 ...

    Best Regards
    Derrick Chen 德瑞克

    2006年11月2日 上午 07:02
  • 我有一個很簡單的方式如下:

    -- 取得資料庫空間使用資訊
    use Northwind
    EXEC sp_spaceused

    -- 取得資料庫中某一物件的空間使用資訊
    use Northwind
    EXEC sp_spaceused Customers

     

    希望能解決您的問題。

    2006年11月11日 上午 12:42

所有回覆

  • SQL 預存程序中好像也沒有可以取得 table 所占空間的方法 .
    你可能要自己 "估算" (estimate) table 可能占的空間 .

    簡單且不精確的算法是各欄位的 size 加總再乘以列數 .
    要詳細的算法的話可能還要考量到 index,這裡有資料:

    http://msdn2.microsoft.com/en-us/library/ms175991.aspx

    2006年11月1日 上午 05:40
    版主
  • Try try!

     

    sp_databases

     

     

    2006年11月1日 上午 05:40
  •  梁東尼 寫信:

    sp_databases 

    sp_databases 取得的是 "資料庫" 所佔空間,不是 "資料表" 所佔空間 ...

    2006年11月1日 上午 05:42
    版主
  • 明白!明白! 多謝大哥!
    2006年11月1日 上午 05:48
  • 您好:
    範例程式如下:

    SQL Sever 2000/2005 請使用:                                                                  
    DBCC SHOWCONTIG ('我的資料表') WITH TABLERESULTS, ALL_INDEXES

    SQL Sever 2005 建議使用:                                                                              
    SELECT * FROM sys.dm_db_index_physical_stats                                              
      (DB_ID(N'我的資料庫'), OBJECT_ID(N'我的資料表'), NULL, NULL , 'DETAILED');

    查出來的結果所代表的意義請自行參閱線上叢書的說明,然後加以計算才能得到最後的結果

    還有一種比較簡單的方法,在此以 SQL Server 2005 為例進行說明,只需在「查詢編輯器」中,鍵入下列指令:
                                    
    SELECT * FROM 我的資料表

    然後開啟「顯示估計執行計劃」,將「估計的資料列數目」與「估計的資料列大小」相乘就是結果了

    2006年11月1日 上午 06:00
    版主
  • Hi: 您好,

    可用估算資料表大小的方式其實有很多,筆者提供個範例給您做為參考:

    -- 建立預存程序: upspaceused
    CREATE procedure [dbo].[upspaceused]
    @objname nvarchar(776) = null,  
    @updateusage varchar(5) = false  
         
    as

    declare @id int   
      ,@type character(2)
      ,@pages bigint   
      ,@dbname sysname
      ,@dbsize bigint
      ,@logsize bigint
      ,@reservedpages  bigint
      ,@usedpages  bigint
      ,@rowCount bigint


    if @updateusage is not null
     begin
      select @updateusage=lower(@updateusage)

      if @updateusage not in ('true','false')
       begin
        raiserror(15143,-1,-1,@updateusage)
        return(1)
       end
     end

    if @objname IS NOT NULL
    begin

     select @dbname = parsename(@objname, 3)

     if @dbname is not null and @dbname <> db_name()
      begin
       raiserror(15250,-1,-1)
       return (1)
      end

     if @dbname is null
      select @dbname = db_name()

     /*
     **  Try to find the object.
     */
     SELECT @id = object_id, @type = type FROM sys.objects WHERE object_id = object_id(@objname)

     -- Translate @id to internal-table for queue
     IF @type = 'SQ'
      SELECT @id = object_id FROM sys.internal_tables WHERE parent_id = @id and internal_type = 201 --ITT_ServiceQueue

     /*
     **  Does the object exist?
     */
     if @id is null
      begin
       raiserror(15009,-1,-1,@objname,@dbname)
       return (1)
      end

     -- Is it a table, view or queue?
     IF @type NOT IN ('U ','S ','V ','SQ','IT')
     begin
      raiserror(15234,-1,-1)
      return (1)
     end
    end

    if @updateusage = 'true'
     begin
      if @objname is null
       dbcc updateusage(0) with no_infomsgs
      else
       dbcc updateusage(0,@objname) with no_infomsgs
      print ' '
     end

    set nocount on

    if @id is null
    begin
     select @dbsize = sum(convert(bigint,case when status & 64 = 0 then size else 0 end))
      , @logsize = sum(convert(bigint,case when status & 64 <> 0 then size else 0 end))
      from dbo.sysfiles

     select @reservedpages = sum(a.total_pages),
      @usedpages = sum(a.used_pages),
      @pages = sum(
        CASE
         -- XML-Index and FT-Index-Docid is not considered "data", but is part of "index_size"
         When it.internal_type IN (202,204) Then 0
         When a.type <> 1 Then a.used_pages
         When p.index_id < 2 Then a.data_pages
         Else 0
        END
       )
     from sys.partitions p join sys.allocation_units a on p.partition_id = a.container_id
      left join sys.internal_tables it on p.object_id = it.object_id

     select
      database_name = db_name(),
      database_size = ltrim(str((convert (dec (15,2),@dbsize) + convert (dec (15,2),@logsize))
       * 8192 / 1048576,15,2)),
      'unallocated space' = ltrim(str((case when @dbsize >= @reservedpages then
       (convert (dec (15,2),@dbsize) - convert (dec (15,2),@reservedpages))
       * 8192 / 1048576 else 0 end),15,2))


     select
      reserved = ltrim(str(@reservedpages * 8192 / 1024.,15,0)),
      data = ltrim(str(@pages * 8192 / 1024.,15,0)),
      index_size = ltrim(str((@usedpages - @pages) * 8192 / 1024.,15,0)),
      unused = ltrim(str((@reservedpages - @usedpages) * 8192 / 1024.,15,0))
    end


    else
    begin

     SELECT
      @reservedpages = SUM (reserved_page_count),
      @usedpages = SUM (used_page_count),
      @pages = SUM (
       CASE
        WHEN (index_id < 2) THEN (in_row_data_page_count + lob_used_page_count + row_overflow_used_page_count)
        ELSE lob_used_page_count + row_overflow_used_page_count
       END
       ),
      @rowCount = SUM (
       CASE
        WHEN (index_id < 2) THEN row_count
        ELSE 0
       END
       )
     FROM sys.dm_db_partition_stats
     WHERE object_id = @id;


     IF (SELECT count(*) FROM sys.internal_tables WHERE parent_id = @id AND internal_type IN (202,204)) > 0
     BEGIN

      SELECT
       @reservedpages = @reservedpages + sum(reserved_page_count),
       @usedpages = @usedpages + sum(used_page_count)
      FROM sys.dm_db_partition_stats p, sys.internal_tables it
      WHERE it.parent_id = @id AND it.internal_type IN (202,204) AND p.object_id = it.object_id;
     END

     SELECT
      name = OBJECT_NAME (@id),
      rows = convert (char(11), @rowCount),
      reserved = LTRIM (STR (@reservedpages * 8, 15, 0)),
      data = LTRIM (STR (@pages * 8, 15, 0)),
      index_size = LTRIM (STR ((CASE WHEN @usedpages > @pages THEN (@usedpages - @pages) ELSE 0 END) * 8, 15, 0)),
      unused = LTRIM (STR ((CASE WHEN @reservedpages > @usedpages THEN (@reservedpages - @usedpages) ELSE 0 END) * 8, 15, 0))

    end


    return (0)
    GO

    -- 以上是建立預存程序

    -- 以下是依據配置(exntent)來排序
    DECLARE @tbl TABLE (tblname sysname, rows int,
     reserved int, data int,
     index_size int, unused int)
    INSERT @tbl
    EXEC sp_msforeachtable @command1="upspaceused '?'"

    SELECT tblname N'資料表',
     rows '資料列筆數',
     reserved N'配置(extent)的空間總量(KB)',
     data N'資料所用的空間總量(KB)',
     index_size N'索引所用的空間總量(KB)'
    FROM @tbl
    ORDER BY 3 DESC


    希望對您有幫助 ...

    Best Regards
    Derrick Chen 德瑞克

    2006年11月2日 上午 07:02
  • 我有一個很簡單的方式如下:

    -- 取得資料庫空間使用資訊
    use Northwind
    EXEC sp_spaceused

    -- 取得資料庫中某一物件的空間使用資訊
    use Northwind
    EXEC sp_spaceused Customers

     

    希望能解決您的問題。

    2006年11月11日 上午 12:42