SQL Table Row Size

If you ever designed a table, you must have thought how much space this table will take on disk, memory, how can I estimate the amount of memory for the server etc'. well, I can't help you there, there are too many things to take into consideration to write in a short blog post.


However, I can help you with one thing, estimating the size of a row, just double it with the amount of rows you estimate will be in the table and you have a pretty close number, but you do have to know which indexes you'll have, they might even double the amount of space needed, this article will explain in detail how you can get a more accurate estimate.


Thanks to Ruchir T, who partially implemented this article, I just needed to make a few modifications and voila, you can now start estimating.

---- =============================================
---- Author: Ruchir T (http://www.west-wind.com/weblog/posts/2004/Jan/19/Sql-Server-Row-Size-Limit)
---- Create date: 01/02/2008
---- Update date: 2012-03-14 show all row info
---- Description: returns the number of bytes left to use for creating new columns
---- =============================================
CREATE FUNCTION usfTable_estimates
(    
    @tablename char(50)
)
RETURNS 
    @rettable table
    (
        TableName nvarchar(255),
       num_columns int,
       num_fixed_columns int,
       fixed_data_size int,
       num_var_columns int,
       max_var_size int,
       var_data_size int,
       null_bitmap_size int,
       row_size int,
       bytes_available int
    ) 
AS
begin 


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

insert into @rettable
select @tablename as TableName,
       @num_columns as num_columns,
       @num_fixed_columns as num_fixed_columns,
       @fixed_data_size as fixed_data_size,
       @num_var_columns as num_var_columns,
       @max_var_size as max_var_size,
       @var_data_size as var_data_size,
       @null_bitmap_size as null_bitmap_size,
       @row_size as row_size,
       @result as bytes_available
       
      return
    
end
GO

Read More

Tags: planning, sql

Dror Gluska March 14, 2012  No comment

SQL Table Space

I was doing capacity planning the other day and I needed to know which tables take how much space, how many rows they have etc'.

There's a system stored procedure for that, sp_spaceused, but it only works on the whole database or a specific table and i tried pushing its results into a table and sorting by it, but it converts the numbers to a string so the order didn't exactly worked.

so I looked in the sp's innards and wrote the following:

select stats.name,
      row_count,
       stats.reserved_page_count * 8 as reservedKB,
       stats.pages * 8 as dataKB,
       (CASE WHEN stats.used_page_count > stats.pages THEN (stats.used_page_count - stats.pages) ELSE 0 END) * 8 as index_sizeKB,
       (CASE WHEN stats.reserved_page_count > stats.used_page_count THEN (stats.reserved_page_count - stats.used_page_count) ELSE 0 END) * 8 as unusedKB
from
(
    select name, 
           
            sum(reserved_page_count) as reserved_page_count, 
            sum(used_page_count) as used_page_count, 
            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
                ) as pages
                
    FROM sys.dm_db_partition_stats
    join sys.objects on sys.objects.object_id = sys.dm_db_partition_stats.object_id
        WHERE sys.objects.type = 'U'
    group by name,sys.dm_db_partition_stats.object_id
) as stats
join
(
    select name, max(row_count) as row_count
    FROM sys.dm_db_partition_stats
        join sys.objects on sys.objects.object_id = sys.dm_db_partition_stats.object_id
            WHERE sys.objects.type = 'U'
    group by name
) as rowcountstats
    on rowcountstats.name = stats.name

order by row_count desc

Read More

Tags: dm_db_partition_stats, rows, sp_spaceused, space, sql

Dror Gluska March 06, 2012  No comment

Rebuild database

While still in design and development, a database goes through many changes, tables and columns are added and removed, records are added and deleted, sometimes performance tests are performed, a data structure goes through sanity testing, indexes get fragmented, etc'.


this leaves a lot of junk in the database files, sometimes this space is reclaimed by the SQL engine and sometimes you need to ask for it specifically, that is the purpose of this stored procedure.


I've used portions of it over the years but only this time I've decided to collect all of it into a single stored procedure, the dynamic nature of the current project I'm working on makes me execute this once in a while. I thought I'd share this with you, please be aware that this should not be executed in a production environment, you must read and understand what its doing before you execute it, it might cause some serious data loss in specific cases.


To help you avoid making that mistake, I've commented all the contents of the stored procedure and put a return in the beginning of it. 

-- =============================================
-- Author:  Dror Gluska
-- Create date: 2012-03-05
-- Description: Performs cleanup/rebuild on all indexes, freeing space and accelerating queries
-- =============================================
create PROCEDURE [dbo].[usp_DBCCCleanup]
AS
BEGIN
 -- SET NOCOUNT ON added to prevent extra result sets from
 SET NOCOUNT ON;

    return;

----rebuild tables

--print 'Rebuilding tables...'

--declare @rebuildtables table(name nvarchar(255), cmd nvarchar(max));

--insert into @rebuildtables
--SELECT  o.[name],'ALTER TABLE ' + '[' + s.[name] + ']'+'.' + '[' + o.[name] + ']' + ' REBUILD ;'
--FROM sys.objects AS o WITH (NOLOCK)
--INNER JOIN sys.indexes AS i WITH (NOLOCK)
--ON o.[object_id] = i.[object_id]
--INNER JOIN sys.schemas AS s WITH (NOLOCK)
--ON o.[schema_id] = s.[schema_id]
--INNER JOIN sys.dm_db_partition_stats AS ps WITH (NOLOCK)
--ON i.[object_id] = ps.[object_id] AND ps.[index_id] = i.[index_id]
--WHERE o.[type] = 'U' ORDER BY ps.[reserved_page_count]

--declare c_rebuild cursor for select name, cmd from @rebuildtables

--open c_rebuild

--declare @tname nvarchar(255), @tcmd nvarchar(max);

--fetch next from c_rebuild into @tname, @tcmd
--while (@@fetch_status <> -1)
--begin
-- print 'Rebuilding ' + @tname
-- exec sp_executesql @tcmd
 
-- fetch next from c_rebuild into @tname, @tcmd
--end
--close c_rebuild
--deallocate c_rebuild


--print 'Done rebuilding tables'

--print 'Rebuilding indexes...'
----taken from http://technet.microsoft.com/en-us/library/bb838727(v=office.12).aspx

--DECLARE @objectid int;
--DECLARE @indexid int;
--DECLARE @partitioncount bigint;
--DECLARE @schemaname nvarchar(130);
--DECLARE @objectname nvarchar(130);
--DECLARE @indexname nvarchar(130);
--DECLARE @partitionnum bigint;
--DECLARE @partitions bigint;
--DECLARE @frag float;
--DECLARE @command nvarchar(4000);
--DECLARE @dbid smallint;

---- Conditionally select tables and indexes from the sys.dm_db_index_physical_stats function
---- and convert object and index IDs to names.

--SET @dbid = DB_ID();

--SELECT
--    [object_id] AS objectid,
--    index_id AS indexid,
--    partition_number AS partitionnum,
--    avg_fragmentation_in_percent AS frag, page_count
--INTO #work_to_do
--FROM sys.dm_db_index_physical_stats (@dbid, NULL, NULL , NULL, N'LIMITED')
--WHERE
----avg_fragmentation_in_percent > 10.0  -- Allow limited fragmentation
----AND
--index_id > 0 -- Ignore heaps
----AND page_count > 25; -- Ignore small tables
---- Declare the cursor for the list of partitions to be processed.
--DECLARE partitions CURSOR FOR SELECT objectid,indexid, partitionnum,frag FROM #work_to_do;
---- Open the cursor.
--OPEN partitions;
---- Loop through the partitions.
--WHILE (1=1)
--BEGIN
--FETCH NEXT
--FROM partitions
--INTO @objectid, @indexid, @partitionnum, @frag;
--IF @@FETCH_STATUS < 0 BREAK;
--SELECT @objectname = QUOTENAME(o.name), @schemaname = QUOTENAME(s.name)
--FROM sys.objects AS o
--JOIN sys.schemas as s ON s.schema_id = o.schema_id
--WHERE o.object_id = @objectid;
--SELECT @indexname = QUOTENAME(name)
--FROM sys.indexes
--WHERE object_id = @objectid AND index_id = @indexid;
--SELECT @partitioncount = count (*)
--FROM sys.partitions
--WHERE object_id = @objectid AND index_id = @indexid;
---- 30 is an arbitrary decision point at which to switch between reorganizing and rebuilding.
--IF @frag < 30.0
--SET @command = N'ALTER INDEX ' + @indexname + N' ON ' + @schemaname + N'.' + @objectname + N' REORGANIZE';
--IF @frag >= 30.0
--SET @command = N'ALTER INDEX ' + @indexname + N' ON ' + @schemaname + N'.' + @objectname + N' REBUILD';
--IF @partitioncount > 1
--SET @command = @command + N' PARTITION=' + CAST(@partitionnum AS nvarchar(10));
--print 'Rebuilding/Reogranizing index ' + @indexname
--EXEC (@command);
--PRINT N'Executed: ' + @command;
--END
---- Close and deallocate the cursor.
--CLOSE partitions;
--DEALLOCATE partitions;
---- Drop the temporary table.
--DROP TABLE #work_to_do;
--print 'Updating usage'
--DBCC UPDATEUSAGE (0)
--print 'Shrinking database'
--declare @shcmd nvarchar(255) = 'DBCC SHRINKDATABASE (' + DB_NAME() + ',10)'
--exec (@shcmd)
 
END

Read More

Tags: sql

Dror Gluska March 05, 2012  No comment