테이블의 크기 계산 스크립트 > db

출처 : http://www.swynk.com

내용 : 테이블의 이름과 테이블상의 ROW의 갯수를 넘겨주면

바로 테이블의 크기, 페이지수, 데이터페이지, 클러스터드 색인 페이지, 넌클러스터드 색인 페이지의 수를

리턴해주는 쓸만한 쿼리 입니다.

북스 온라인상의 테이블 크기 계산 공식(저도 조금 봐밨는데.. 약간 복잡합니다.)

의 인자들을 얻어 처리하는 방식 입니다.

보통 테이블의 크기를 예측할때 사용하시면 많은 도움 되실 겁니다.

PS : 작성자는 샤먼 둘리(진짜임) 라는 사람으로... 상당히 C 스타일의 프로그램을 좋아하는듯 합니다.

 

if exists (select * from sysobjects where id = object_id(N'[dbo].[calcspace]')
and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 drop procedure [dbo].[calcspace]
GO

create procedure CalcSpace

-- A procedure to estimate the disk space requirements of a table.
-- Refer to Books OnLine topic "Estimating the size of a table..."
-- for a detailed description

 (@table_name varchar(30)=null,-- name of table to estimate
  @num_rows int = 0)  -- number of rows in the table
as

declare @msg varchar(120)

-- Give usage statement if @table_name is null

if @table_name = null or @num_rows = 0
begin
 print 'Usage is:'
 print '   calcspace table_name, no_of_rows'
 print 'where table_name is the name of the table,'
 print '      no_of_rows is the number of rows in the table,'
 print ' '
 return
end

declare @num_fixed_col    int,
 @fixed_data_size   int,
 @num_variable_col   int,
 @max_var_size    int,
 @null_bitmap    int,
 @variable_data_size   int,
 @table_id    int,
 @num_pages    int,
 @table_size_in_bytes   int,
 @table_size_in_meg   real,
 @table_size_in_kbytes   real,
 @sysstat    tinyint,
 @row_size    int,
 @rows_per_page    int,
 @free_rows_per_page   int,
 @fillfactor    int,
  @num_fixed_ckey_cols    int,
 @fixed_ckey_size    int,
 @num_variable_ckey_cols   int,
 @max_var_ckey_size    int,
 @cindex_null_bitmap   int,
 @variable_ckey_size   int,
 @cindex_row_size   int,
 @cindex_rows_per_page   int,
 @data_space_used   int,
 @num_pages_clevel_0   int,
 @num_pages_clevel_1   int,
 @num_pages_clevel_x   int,
 @num_pages_clevel_y   int,
 @Num_CIndex_Pages   int,
 @clustered_index_size_in_bytes  int,
 @num_fixed_key_cols    int,
 @fixed_key_size    int,
 @num_variable_key_cols    int,
 @max_var_key_size    int,
 @index_null_bitmap   int,
 @variable_key_size   int,
 @nl_index_row_size   int,
 @nl_index_rows_per_page   int,
 @index_row_size    int,
 @index_rows_per_page   int,
 @free_index_rows_per_page  int,
 @num_pages_level_0   int,
 @num_pages_level_1   int,
 @num_pages_level_x   int,
 @num_pages_level_y   int,
 @num_index_pages   int,
 @nonclustered_index_size  int,
 @total_num_nonclustered_index_pages int,
 @free_cindex_rows_per_page  int,
 @tot_pages    int

-- initialize variables
select @num_fixed_col    =0,
 @fixed_data_size   =0,
 @num_variable_col   =0,
 @max_var_size    =0,
 @null_bitmap    =0,
 @variable_data_size   =0,
 @table_id    =0,
 @num_pages    =0,
 @table_size_in_bytes   =0,
 @table_size_in_meg   =0,
 @table_size_in_kbytes   =0,
 @sysstat    =0,
 @row_size    =0,
 @rows_per_page    =0,
  @num_fixed_ckey_cols    =0,
 @fixed_ckey_size    =0,
 @num_variable_ckey_cols   =0,
 @max_var_ckey_size    =0,
 @cindex_null_bitmap   =0,
 @variable_ckey_size   =0,
 @cindex_row_size   =0,
 @cindex_rows_per_page   =0,
 @data_space_used   =0,
 @num_pages_clevel_0   =0,
 @num_pages_clevel_1   =0,
 @Num_CIndex_Pages   =0,
 @clustered_index_size_in_bytes  =0,
 @num_fixed_key_cols    =0,
 @fixed_key_size    =0,
 @num_variable_key_cols    =0,
 @max_var_key_size    =0,
 @index_null_bitmap   =0,
 @variable_key_size   =0,
 @nl_index_row_size   =0,
 @nl_index_rows_per_page   =0,
 @index_row_size    =0,
 @index_rows_per_page   =0,
 @free_index_rows_per_page  =0,
 @num_pages_level_0   =0,
 @num_pages_level_1   =0,
 @num_pages_level_x   =0,
 @num_pages_level_y   =0,
 @num_index_pages   =0,
 @nonclustered_index_size  =0,
 @total_num_nonclustered_index_pages =0,
 @free_cindex_rows_per_page  =0,
 @tot_pages    =0

set nocount on

--*********************************************
-- MAKE SURE TABLE EXISTS
--*********************************************

select  @sysstat = sysstat,
 @table_id = id
  from sysobjects where name = @table_name

if @sysstat & 7 not in (1,3)
begin
 select @msg = 'I can not find the table '+@table_name
 print @msg
 return
end

--*********************************************
-- ESTIMATE SIZE OF TABLE
--*********************************************

-- get total number and total size of fixed-length columns

select  @num_fixed_col = count(name),
 @fixed_data_size = sum(length)
 from  syscolumns
 where  id= @table_id and xtype in
  (
  select xtype from systypes where variable=0
  )

 if @num_fixed_col= 0 --@fixed_data_size is null.  change to 0
  select @fixed_data_size=0

-- get total number and total maximum size of variable-length columns

select  @num_variable_col=count(name),
 @max_var_size= sum(length)
 from  syscolumns
 where  id= @table_id and xtype in
  (
  select xtype from systypes where variable=1
  )
 if @num_variable_col= 0 --@max_var_size is null.  change to 0
  select @max_var_size=0

-- get portion of the row used to manage column nullability

select @null_bitmap=2+((@num_fixed_col+7)/8)

-- determine space needed to store variable-length columns
-- this assumes all variable length columns will be 100% full
if @num_variable_col = 0
 select @variable_data_size=0
else
 select @variable_data_size =  2 + (@num_variable_col *2 )+ @max_var_size

-- get row size

select @row_size=  @fixed_data_size +
   @variable_data_size +
   @null_bitmap + 4  -- 4 represents the data row header

-- get number of rows per page

select @rows_per_page = (8096) / (@row_size+2)

-- If a clustered index is to be created on the table,
-- calculate the number of reserved free rows per page,
-- based on the fill factor specified.
-- If no clustered index is to be created, specify Fill_Factor as 100.

select  @fillfactor = 100 -- initialize it to the maximum
select  @free_rows_per_page = 0  --initialize to no free rows/page
select  @fillfactor=OrigFillFactor
from  sysindexes
where  id = @table_id and indid=1  -- indid of 1 means the index is clustered

if @fillfactor<>0
 -- a 0 fill factor ALMOST fills up the entire page, but not quite.
 --The doc says that fill factor zero leaves 2 empty rows (keys)
 --in each index page and no free rows in data pages of clustered
 --indexes and leaf pages of non-clustered.
 --We are working on the data pages in this section
 select @free_rows_per_page=8096 * ((100-@fillfactor)/100)/@row_size

-- get number of pages needed to store all rows

select @num_pages = ceiling(convert(dec,@num_rows) / (@rows_per_page-@free_rows_per_page))

-- get storage needed for table data

select @data_space_used=8192*@num_pages

--*********************************************
-- COMPUTE SIZE OF CLUSTERED INDEX IF ONE EXISTS
--*********************************************

-- create a temporary table to contain columns in clustered index. System table
-- sysindexkeys has a list of the column numbers contained in the index

select colid into #col_list
from sysindexkeys where id= @table_id and indid=1  -- indid=1 means clustered

if (select count(*) from #col_list) >0 -- do the following only if clustered index exsists
begin
 -- get total number and total maximum size of fixed-length columns in clustered index

 select  @num_fixed_ckey_cols=count(name),
  @fixed_ckey_size= sum(length)
  from  syscolumns
  where  id= @table_id and xtype in
  (
  select xtype from systypes where variable=0
  )
  and colid in (select * from #col_list)

 if @num_fixed_ckey_cols= 0 --@fixed_ckey_size is null.  change to 0
  select @fixed_ckey_size=0

 -- get total number and total maximum size of variable-length columns in clustered index

 select  @num_variable_ckey_cols=count(name),
  @max_var_ckey_size= sum(length)
  from  syscolumns
  where  id= @table_id and xtype in
  (
  select xtype from systypes where variable=1
  )
  and colid in (select * from #col_list)

 if @num_variable_ckey_cols= 0 --@max_var_ckey_size is null.  change to 0
  select @max_var_ckey_size=0

 -- If there are fixed-length columns in the clustered index,
 -- a portion of the index row is reserved for the null bitmap. Calculate its size:
 if @num_fixed_ckey_cols <> 0
  select @cindex_null_bitmap=2+((@num_fixed_ckey_cols + 7)/8)
 else
  select @cindex_null_bitmap=0

 -- If there are variable-length columns in the index, determine how much
 -- space is used to store the columns within the index row:

 if @num_variable_ckey_cols <> 0
  select @variable_ckey_size=2+(@num_variable_ckey_cols *2)+@max_var_ckey_size
 else
  select @variable_ckey_size=0

 -- Calculate the index row size

 select @cindex_row_size=@fixed_ckey_size +@variable_ckey_size+@cindex_null_bitmap+1+8

 --Next, calculate the number of index rows per page (8096 free bytes per page):

 select @cindex_rows_per_page=(8096)/(@cindex_row_size+2)

 -- consider fillfactor
 if @fillfactor=0
  select @free_cindex_rows_per_page = 2
 else
   select @free_cindex_rows_per_page= 8096 * ((100-@fillfactor)/100)/@cindex_row_size

 -- Next, calculate the number of pages required to store
 -- all the index rows at each level of the index.

 select @num_pages_clevel_0=ceiling(convert(decimal,(@data_space_used/8192))/(@cindex_rows_per_page-@free_cindex_rows_per_page))
 select @Num_CIndex_Pages=@num_pages_clevel_0
 select @num_pages_clevel_x=@num_pages_clevel_0

 while @num_pages_clevel_x <> 1
 begin
  select @num_pages_clevel_y=ceiling(convert(decimal,@num_pages_clevel_x)/(@cindex_rows_per_page-@free_cindex_rows_per_page))
  select @Num_CIndex_Pages=@Num_CIndex_Pages+@num_pages_clevel_y
  select @num_pages_clevel_x=@num_pages_clevel_y
 end
end

--*********************************************
-- END CLUSTERED INDEX SECTION
--*********************************************

--*********************************************
-- BEGIN NON-CLUSTERED INDEX SECTION
--*********************************************

-- create temp table with non-clustered index info

select indid, colid into #col_list2
from sysindexkeys where id= @table_id and indid<>1 -- indid=1 means clustered

if (select count(*) from #col_list2) >0 -- do the following only if non-clustered indexes exsist
begin
 declare @i int  -- a counter variable
 select @i=1 -- initilize to 2, because 1 is id of clustered index

 while @i< 249 -- max number of non-clustered indexes
 begin
  select @i=@i+1 -- look for the next non-clustered index
  -- reinitialize all numbers
  select  @num_fixed_key_cols = 0,
   @fixed_key_size = 0,
   @num_variable_key_cols = 0,
   @max_var_key_size = 0,
   @index_null_bitmap = 0,
   @variable_key_size = 0,
   @nl_index_row_size = 0,
   @nl_index_rows_per_page = 0,
   @index_row_size = 0,
   @index_rows_per_page = 0,
   @free_index_rows_per_page = 0,
   @num_pages_level_0 = 0,
   @num_pages_level_x = 0,
   @num_pages_level_y = 0,
   @Num_Index_Pages = 0

  -- get total number and total maximum size
  -- of fixed-length columns in nonclustered index
  select  @num_fixed_key_cols=count(name),
   @fixed_key_size= sum(length)
   from  syscolumns
   where  id= @table_id and xtype in
   (
   select xtype from systypes where variable=0
   )
   and colid in (select colid from #col_list2 where indid=@i)
  if @num_fixed_key_cols= 0 --@fixed_key_size is null.  change to 0
   select @fixed_key_size=0

  -- get total number and total maximum size of variable-length columns in index

  select  @num_variable_key_cols=count(name),
   @max_var_key_size= sum(length)
   from  syscolumns
   where  id= @table_id and xtype in
   (
   select xtype from systypes where variable=1
   )
   and colid in  (select colid from #col_list2 where indid=@i)
  if @num_variable_key_cols= 0 --@max_var_key_size is null.  change to 0
   select @max_var_key_size=0

  if @num_fixed_key_cols = 0 and @num_variable_key_cols = 0 --there is no index
   continue
  -- If there are fixed-length columns in the non-clustered index,
  -- a portion of the index row is reserved for the null bitmap. Calculate its size:
  if @num_fixed_key_cols <> 0
   select @index_null_bitmap=2+((@num_fixed_key_cols + 7)/8)
  else
   select @index_null_bitmap=0

  -- If there are variable-length columns in the index, determine how much
  -- space is used to store the columns within the index row:

  if @num_variable_key_cols <> 0
   select @variable_key_size=2+(@num_variable_key_cols *2)+@max_var_key_size
  else
   select @variable_key_size=0

  -- Calculate the non-leaf index row size
  select @nl_index_row_size=@fixed_key_size +@variable_key_size+@index_null_bitmap+1+8

  --Next, calculate the number of non-leaf index rows per page (8096 free bytes per page):

  select @nl_index_rows_per_page=(8096)/(@nl_index_row_size+2)

  -- Next, calculate the leaf index row size

  select @index_row_size=@cindex_row_size + @fixed_key_size + @variable_key_size+@index_null_bitmap+1

  -- Next, calculate the number of leaf level index rows per page

  select @index_rows_per_page = 8096/(@index_row_size + 2)

  -- Next, calcuate the number of reserved free index rows/page based on fill factor

  if @fillfactor=0
  -- a 0 fill factor ALMOST fills up the entire page, but not quite.
  --The doc says that fill factor zero leaves 2 empty rows (keys)
  --in each index page and no free rows in data pages of clustered
  --indexes and leaf pages of non-clustered.
  --We are working on the non-clustered index pages in this section
   select @free_index_rows_per_page=0
  else
   select @free_index_rows_per_page= 8096 * ((100-@fillfactor)/100)/@index_row_size

  -- Next, calculate the number of pages required to store
  -- all the index rows at each level of the index.

  select @num_pages_level_0=ceiling(convert(decimal,@num_rows)/@index_rows_per_page-@free_index_rows_per_page)

  select @Num_Index_Pages=@num_pages_level_0
  select @num_pages_level_x=@num_pages_level_0

  while @num_pages_level_x <> 1
  begin
   select @num_pages_level_y=ceiling(convert(decimal,@num_pages_level_x)/@nl_index_rows_per_page)
   select @Num_Index_Pages=@Num_Index_Pages+@num_pages_level_y
   select @num_pages_level_x=@num_pages_level_y
  end

  select @total_num_nonclustered_index_pages=@total_num_nonclustered_index_pages+@Num_Index_Pages
 end
end
--*********************************************
-- END NON-CLUSTERED INDEX SECTION
--*********************************************
-- display numbers

select @tot_pages=@num_pages + @Num_CIndex_Pages + @total_num_nonclustered_index_pages
select @table_size_in_bytes= 8192*@tot_pages
select @table_size_in_kbytes= @table_size_in_bytes/1024.0
select @table_size_in_meg= str(@table_size_in_kbytes/1000.0,17,2)

select  substring(@table_name,1,20) as 'Table Name',
 convert(varchar(10),@table_size_in_meg) as 'MB Estimate',
 @tot_pages as 'Total Pages',
 @num_pages as '#Data Pgs',
 @Num_CIndex_Pages as '#Clustered Idx Pgs',
 @total_num_nonclustered_index_pages as '#NonClustered Idx Pgs'

 

go

 

--실행 방법
--pubs DB에서 테스트 테이블명과 로우의 수를 넘겨줘야 한다.
--테이블수를 체크하기위한 변수 선언후 같이 넘겨 준다.
declare @rownum int
set @rownum = (select count(*) from titles)
exec calcspace titles, @rownum
go