InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comFri, 24 May 2013 05:10:14 GMT20http://www.sqlservercentral.com/Forums/Topic738998-1407-1.aspxI like this, but Richard Ding's creation "sp_sos" is a little more thorough, and is a PROC instead of a group of Functions.http://media.techtarget.com/searchSQLServer/downloads/URL_for_SQL_Script_download_Tip_on_Sp_SOS.docFri, 25 Feb 2011 13:35:28 GMTSQL_ME_RICHhttp://www.sqlservercentral.com/Forums/Topic738998-1407-1.aspxWouldn't it be incorrect to compare this script to an existing table, since the script is meant to [i]estimate[/i] a table's size[quote][b]Sqlfrenzy (7/2/2009)[/b][hr][quote][b]dant12 (7/2/2009)[/b][hr]according to your script one of my tables has 440 MBs but SSMS says it has around 46 MBsu sure u did the math right?[/quote]I tested the script against the 7 tables of adventureworks database...5 tables gave me result with a margin +1/-1..[/quote]Wed, 09 Jun 2010 18:25:42 GMTLangston Montgomeryhttp://www.sqlservercentral.com/Forums/Topic738998-1407-1.aspx[quote][b]dant12 (7/2/2009)[/b][hr]according to your script one of my tables has 440 MBs but SSMS says it has around 46 MBsu sure u did the math right?[/quote]I tested the script against the 7 tables of adventureworks database...5 tables gave me result with a margin +1/-1..Thu, 02 Jul 2009 13:42:34 GMTSqlfrenzyhttp://www.sqlservercentral.com/Forums/Topic738998-1407-1.aspxthanks william...Thu, 02 Jul 2009 13:30:28 GMTSqlfrenzyhttp://www.sqlservercentral.com/Forums/Topic738998-1407-1.aspxaccording to your script one of my tables has 440 MBs but SSMS says it has around 46 MBsu sure u did the math right?Thu, 02 Jul 2009 13:24:31 GMTdant12http://www.sqlservercentral.com/Forums/Topic738998-1407-1.aspxHere is a cleaned up set of code.[code]CREATE FUNCTION [dbo].[fn_CalculateHeapSize] ( @Tablename VARCHAR(100) )RETURNS VARCHAR(100)AS BEGIN-- Calculate the space used taken at leaf level DECLARE @Num_Rows FLOAT ,@Num_Cols INT ,@Fixed_data_size INT ,@Num_var_Cols INT ,@Max_var_size INT ,@Null_Bitmap INT ,@Variable_Data_Size INT ,@Heap_size BIGINT ,@Row_Size INT ,@Rows_per_page FLOAT ,@Num_Pages FLOAT SET @Num_Rows = ( SELECT [rows] FROM sys.sysindexes WHERE indid = 1 AND id = OBJECT_ID(@Tablename) ) SET @Num_Cols = ( SELECT COUNT(*) FROM sys.columns WHERE object_id = OBJECT_ID(@Tablename) ) SET @Fixed_data_size = ( SELECT SUM(max_lenGth) FROM sys.columns WHERE object_id = OBJECT_ID(@Tablename) AND system_type_id NOT IN (165, 167, 231, 34, 35, 99) ) SET @Num_var_Cols = ( SELECT COUNT(*) FROM sys.columns WHERE object_id = OBJECT_ID(@Tablename) AND system_type_id IN (165, 167, 231, 34, 35, 99) ) SET @Max_var_size = ( SELECT SUM(max_lenGth) FROM sys.columns WHERE object_id = OBJECT_ID(@Tablename) AND system_type_id IN (165, 167, 231, 34, 35, 99) ) SET @Null_Bitmap = 2 + (@Num_Cols + 7) / 8 IF (@Num_var_Cols = 0) BEGIN SET @Variable_Data_Size = 0 END ELSE BEGIN SET @Variable_Data_Size = 2 + (@Num_var_Cols * 2) + @Max_var_size END SET @Row_Size = @Fixed_data_size + @Variable_Data_Size + @Null_Bitmap + 4 -- Row header info SET @Rows_per_page = 8096 / (@Row_Size + 2)-- No. of pages needed to store rows SET @Num_Pages = CEILING(@Num_Rows / @Rows_per_page) SET @Heap_size = (8192 * @Num_Pages) / 1024-- Space used to store index info RETURN LTRIM(STR(@Heap_size))-- + ' KB' ENDGOCREATE FUNCTION [dbo].[fn_ClusteredIndexSize] ( @Tablename VARCHAR(100) )RETURNS BIGINTAS BEGIN-- Calculate the space used taken at leaf level DECLARE @Num_Rows FLOAT ,@Num_Cols INT ,@Fixed_data_size INT ,@Num_var_Cols INT ,@Max_var_size INT ,@fill_factor INT ,@uniquifier SMALLINT ,@uniquefiersize SMALLINT ,@Null_Bitmap INT ,@Variable_Data_Size INT ,@Total_Space VARCHAR(100) ,@Row_Size INT ,@Rows_per_page FLOAT ,@Free_rows_per_page FLOAT ,@level FLOAT ,@Num_Pages FLOAT ,@Leaf_level_space INT SET @uniquifier = 1 SET @uniquefiersize = 4 SET @Num_Rows = ( SELECT [rows] FROM sys.sysindexes WHERE indid = 1 AND id = OBJECT_ID(@Tablename) ) SET @Num_Cols = ( SELECT COUNT(*) FROM sys.columns WHERE object_id = OBJECT_ID(@Tablename) ) SET @Fixed_data_size = ( SELECT SUM(max_lenGth) FROM sys.columns WHERE object_id = OBJECT_ID(@Tablename) AND system_type_id NOT IN (165, 167, 231, 34, 35, 99) ) SET @Num_var_Cols = ( SELECT COUNT(*) FROM sys.columns WHERE object_id = OBJECT_ID(@Tablename) AND system_type_id IN (165, 167, 231, 34, 35, 99) ) SET @Max_var_size = ( SELECT SUM(max_lenGth) FROM sys.columns WHERE object_id = OBJECT_ID(@Tablename) AND system_type_id IN (165, 167, 231, 34, 35, 99) ) IF (( SELECT is_unique FROM sys.indexes WHERE type = 1 AND object_id = OBJECT_ID(@Tablename) ) = 0) BEGIN SET @Num_Cols = @Num_Cols + @uniquifier SET @Num_var_Cols = @Num_var_Cols + @uniquifier SET @Max_var_size = @Max_var_size + @uniquefiersize END SET @Null_Bitmap = 2 + (@Num_Cols + 7) / 8 SET @Variable_Data_Size = 2 + (@Num_var_Cols * 2) + @Max_var_size SET @Row_Size = @Fixed_data_size + @Variable_Data_Size + @Null_Bitmap + 4 -- Row header info SET @Rows_per_page = 8096 / (@Row_Size + 2) SET @fill_factor = ( SELECT fill_factor FROM sys.indexes WHERE object_id = OBJECT_ID(@Tablename) AND type = 1 )-- No. of reserved free rows per page SET @Free_rows_per_page = 8096 * (((100 - @Fill_Factor) / 100) / (@Row_Size + 2))-- No. of pages needed to store rows SET @Num_Pages = CEILING((@Num_Rows / (@Rows_per_page - @Free_rows_per_page))) SET @Leaf_level_space = 8192 * @Num_Pages-- Space used to store index info DECLARE @Num_Key_cols INT ,@Fixed_key_size INT ,@Num_var_key_cols INT ,@Max_var_key_size INT ,@Index_Null_Bitmap INT ,@Variable_Key_size INT ,@Index_row_size INT ,@Index_row_per_page FLOAT ,@levels INT ,@Num_Index_pages INT ,@Index_level_space INT ,@Null_Cols INT SET @Num_Key_cols = ( SELECT Keycnt FROM sys.sysindexes WHERE id = OBJECT_ID(@Tablename) AND indid = 1 ) SET @Fixed_key_size = ( SELECT SUM(max_length) FROM sys.index_columns a ,sys.indexes b ,sys.columns c WHERE a.index_id = b.index_id AND b.object_id = OBJECT_ID(@Tablename) AND type = 1 AND a.object_id = b.object_id AND c.object_id = b.object_id AND a.column_id = c.column_id AND system_type_id NOT IN (165, 167, 231, 34, 35, 99) ) SET @Num_var_key_cols = ( SELECT COUNT(c.name) FROM sys.index_columns a ,sys.indexes b ,sys.columns c WHERE a.index_id = b.index_id AND b.object_id = OBJECT_ID(@Tablename) AND type = 1 AND a.object_id = b.object_id AND c.object_id = b.object_id AND a.column_id = c.column_id AND system_type_id IN (165, 167, 231, 34, 35, 99) ) SET @Max_var_key_size = ( SELECT ISNULL(SUM(max_length), 0) FROM sys.index_columns a ,sys.indexes b ,sys.columns c WHERE a.index_id = b.index_id AND b.object_id = OBJECT_ID(@Tablename) AND type = 1 AND a.object_id = b.object_id AND c.object_id = b.object_id AND a.column_id = c.column_id AND system_type_id IN (165, 167, 231, 34, 35, 99) ) IF (( SELECT is_unique FROM sys.indexes WHERE type = 1 AND object_id = OBJECT_ID(@Tablename) ) = 0) BEGIN SET @Num_Key_cols = @Num_Key_cols + @uniquifier SET @Num_var_key_cols = @Num_var_key_cols + @uniquifier SET @Max_var_key_size = @Max_var_key_size + @uniquefiersize END SET @Null_Cols = ( SELECT ISNULL(COUNT(c.name), 0) FROM sys.index_columns a ,sys.indexes b ,sys.columns c WHERE a.index_id = b.index_id AND b.object_id = OBJECT_ID(@Tablename) AND type = 1 AND a.object_id = b.object_id AND c.object_id = b.object_id AND a.column_id = c.column_id AND c.is_nullable = 1 ) SELECT @Index_level_space = dbo.fn_getIndexSpace(@Null_Cols, @Num_var_key_cols, @Max_var_key_size, @Fixed_key_size, @Num_Rows) SET @Total_space = LTRIM(STR((@Index_level_space + @Leaf_level_space) / (1024))) RETURN @Total_space ENDGO CREATE FUNCTION [dbo].[fn_getIndexSpace] ( @Num_Null_key_cols INT ,@Num_var_key_cols INT ,@Max_var_key_size INT ,@Fixed_key_size INT ,@Num_Rows FLOAT )RETURNS BIGINTAS BEGIN DECLARE @Index_Null_Bitmap INT ,@Var_Key_Size INT ,@Index_row_Size INT ,@Index_Row_per_Page FLOAT ,@level FLOAT ,@Num_Index_pages INT ,@Index_Space_Used BIGINT IF (@Num_Null_key_cols > 0) BEGIN SET @Index_Null_Bitmap = 2 + ((@Num_Null_key_cols + 7) / 8) END ELSE BEGIN SET @Index_Null_Bitmap = 0 END IF (@Num_var_key_cols > 0) BEGIN SET @Var_Key_Size = 2 + (@Num_var_key_cols * 2) + @Max_var_key_size END ELSE BEGIN SET @Var_Key_Size = 0 END SET @Index_row_Size = @Fixed_key_size + @Var_Key_Size + @Index_Null_Bitmap + 1 + 6 SET @Index_Row_per_Page = 8096 / (@Index_row_Size + 2) SET @level = 1 + FLOOR(ABS((LOG10(@Num_Rows / @Index_row_per_page) / LOG10(@Index_row_per_page)))) SET @Num_Index_pages = 0 DECLARE @i INT IF (@level > 0) BEGIN SET @i = 1 WHILE(@i <= @Level) BEGIN SET @Num_Index_pages = @Num_Index_pages + POWER(@Index_row_per_page, @level - @i) SET @i = @i + 1 END END SET @Index_Space_Used = (8192 * @Num_Index_pages) RETURN @Index_Space_Used ENDGOCREATE FUNCTION [dbo].[fn_GetLeafLevelIndexSpace] ( @Num_Leaf_Cols INT ,@Num_Var_leaf_Cols INT ,@Max_var_leaf_size INT ,@Fixed_Leaf_Size INT ,@Fill_Factor INT ,@Num_Rows FLOAT )RETURNS BIGINTAS BEGIN DECLARE @Leaf_Null_Bitmap INT ,@Variable_leaf_size INT ,@Leaf_Row_Size INT ,@Leaf_Rows_per_page INT ,@Free_Rows_Per_Page INT ,@Num_Leaf_Pages FLOAT ,@Leaf_Space_Used INT SET @Leaf_Null_Bitmap = 2 + ((@Num_Leaf_Cols + 7) / 8) IF (@Num_Var_leaf_Cols > 0) BEGIN SET @Variable_leaf_size = 2 + (@Num_Var_leaf_Cols * 2) + @Max_var_leaf_size END ELSE BEGIN SET @Variable_leaf_size = 0 END SET @Leaf_Row_Size = @Fixed_Leaf_Size + @Variable_leaf_size + @Leaf_Null_Bitmap + 1 + 6 SET @Leaf_Rows_per_page = 8096 / (@Leaf_Row_Size + 2) SET @Free_Rows_Per_Page = 8096 * (((100 - @Fill_Factor) / 100) / (@Leaf_Row_Size + 2)) SET @Num_Leaf_Pages = CEILING((@Num_Rows / (@Leaf_Rows_per_page - @Free_Rows_Per_Page))) SET @Leaf_Space_Used = 8192 * @Num_Leaf_Pages RETURN @Leaf_Space_Used ENDGOCREATE FUNCTION [dbo].[fn_GetNonClusteredIndexSize] ( @TableName VARCHAR(100) )RETURNS BIGINTAS BEGIN DECLARE @Num_Rows FLOAT ,@Num_Key_cols INT ,@Fixed_key_size INT ,@Num_var_key_cols INT ,@Max_var_key_size INT ,@is_clustered INT ,@index_id INT ,@is_unique BIT ,@Num_Diff_cols INT ,@Num_Null_key_cols INT ,@Num_Index_pages INT ,@Index_Space_Used INT ,@Total_Index_space BIGINT ,@Num_Leaf_Cols INT ,@Num_Included_Cols INT ,@Leaf_Level_Space INT ,@Fill_Factor INT -- CALCULATE THE SPACE USED TO SAVE INDEX INFORMATION AT NON-LEAF LEVEL-- No of Rows in a table SET @Total_Index_space = 0 SET @Leaf_Level_Space = 0-- insert info intom temp table SET @Num_Rows = ( SELECT [rows] FROM sys.sysindexes WHERE indid = 1 AND id = OBJECT_ID(@TableName) ) DECLARE @Tmp_Info TABLE ( Index_id INT ,Num_key_cols INT ,type INT ,is_unique BIT ,is_included SMALLINT ,fill_factor INT ,Num_Var_Key_cols INT ,Fixed_Key_Size INT ,Max_Var_Key_Size INT ) DECLARE @tmp_Index_info TABLE ( sno INT IDENTITY(1, 1) ,index_id INT ,Num_key_cols INT ,type INT ,is_unique BIT ,Num_Var_Key_cols INT ,Fixed_Key_Size INT ,Max_Var_Key_Size INT ,Num_Included_Col INT ,fill_factor INT ) INSERT INTO @Tmp_Info SELECT b.Index_id ,COUNT(c.name) Num_key_cols ,b.type ,b.is_unique ,is_included_column ,fill_factor ,ISNULL(( SELECT COUNT(c.name) FROM sys.columns e WHERE e.object_id = c.object_id AND a.column_id = e.column_id AND c.system_type_id IN (165, 167, 231, 34, 35, 99) ), 0) AS Num_Var_Key_cols ,ISNULL(( SELECT SUM(max_length) FROM sys.indexes d WHERE d.index_id = b.index_id AND d.object_id = c.object_id AND c.system_type_id NOT IN (165, 167, 231, 34, 35, 99) ), 0) AS Fixed_Key_Size ,ISNULL(( SELECT SUM(max_length) FROM sys.indexes d WHERE d.index_id = b.index_id AND d.object_id = c.object_id AND c.system_type_id IN (165, 167, 231, 34, 35, 99) ), 0) AS Max_Var_Key_Size--into @Tmp_Info FROM sys.index_columns a ,sys.columns c ,sys.indexes b WHERE a.column_id = c.column_id AND a.index_id = b.index_id AND b.object_id = c.object_id AND a.object_id = b.OBJECT_ID AND b.object_id = OBJECT_ID(@TableName) --and b.type>1 GROUP BY c.name ,b.index_id ,c.object_id ,c.system_type_id ,a.column_id ,b.type ,b.is_unique ,is_included_column ,fill_factor ORDER BY b.index_id INSERT INTO @tmp_Index_info SELECT index_id AS Index_id ,SUM(num_key_cols) AS num_key_cols ,type ,is_unique ,SUM(Num_var_key_cols) AS Num_var_key_cols ,SUM(Fixed_key_size) AS Fixed_key_size ,SUM(max_var_key_size) AS max_var_key_size ,SUM(is_included) ,fill_factor--into @tmp_Index_info FROM @Tmp_Info WHERE type > 1 GROUP BY index_id ,type ,is_unique ,fill_factor IF EXISTS ( SELECT 1 FROM @Tmp_Info WHERE type = 1 ) BEGIN SET @is_clustered = 1 END ELSE BEGIN SET @is_clustered = 0 END DECLARE @row_Count INT SET @row_Count = ( SELECT COUNT(*) FROM @tmp_Index_info WHERE type > 1 ) WHILE (@row_Count > 0) BEGIN SELECT @index_id = index_id ,@Num_Key_cols = num_key_cols ,@Fixed_key_size = fixed_key_size ,@Num_var_key_cols = Num_var_key_cols ,@Max_var_key_size = Max_var_key_size ,@is_unique = is_unique ,@Num_Included_Cols = Num_Included_Col ,@Fill_Factor = fill_factor FROM @tmp_Index_info WHERE sno = @row_Count IF (@is_clustered = 0) BEGIN SET @Num_Key_cols = @Num_Key_cols + 1 SET @Num_Leaf_Cols = @Num_Key_cols + @Num_Included_Cols + 1 END ELSE BEGIN SELECT @Num_Diff_cols = COUNT(column_id) FROM sys.index_columns x ,sys.indexes y WHERE column_id NOT IN ( SELECT column_id FROM sys.index_columns a ,sys.indexes b WHERE a.index_id = b.index_id AND type > 1 AND a.object_id = b.object_id AND a.object_id = OBJECT_ID(@TableName) AND a.index_id = @index_id) AND x.object_id = y.object_id AND y.type = 1 AND x.object_id = OBJECT_ID(@TableName) AND x.index_id = y.index_id SET @Num_Key_cols = @Num_Key_cols + @Num_Diff_cols + @is_unique SET @Num_Leaf_Cols = @Num_Key_cols + @Num_Included_Cols + @Num_Diff_cols + @is_unique END SELECT @Num_Null_key_cols = ISNULL(COUNT(x.column_id), 0) FROM sys.index_columns x ,sys.columns y WHERE x.column_id = y.column_id AND x.index_id = @index_id AND y.is_nullable = 1 AND x.object_id = OBJECT_ID(@TableName) AND x.object_id = y.object_id DECLARE @index_name VARCHAR(100) SELECT @index_name = name FROM sys.indexes WHERE object_id = OBJECT_ID(@TableName) AND index_id = @index_id SELECT @Index_Space_Used = dbo.fn_getIndexSpace(@Num_Null_key_cols, @Num_var_key_cols, @Max_var_key_size, @Fixed_key_size, @Num_Rows) SELECT @Leaf_Level_Space = dbo.fn_GetLeafLevelIndexSpace(@Num_Leaf_Cols, @Num_var_key_cols, @Max_var_key_size, @Fixed_key_size, @Fill_Factor, @Num_Rows) SET @Total_Index_space = @Total_Index_space + @Index_Space_Used + @Leaf_Level_Space SET @row_Count = @row_count - 1 END RETURN LTRIM(STR((@Total_Index_space)) / (1024)) ENDGOCREATE FUNCTION [dbo].[fn_GetTableSize] ( @TableName VARCHAR(100) )RETURNS VARCHAR(25)AS BEGIN DECLARE @TableSize VARCHAR(25) IF EXISTS ( SELECT 1 FROM sys.indexes WHERE object_id = OBJECT_ID(@TableName) AND type = 1 ) BEGIN SELECT @TableSize = dbo.fn_ClusteredIndexSize(@TableName) + dbo.fn_GetNonClusteredIndexSize(@TableName) END ELSE BEGIN SELECT @TableSize = (ISNULL(dbo.fn_CalculateHeapSize(@TableName), 0) + ISNULL(dbo.fn_GetNonClusteredIndexSize(@TableName), 0)) END SET @TableSize = LTRIM(STR(@TableSize)) + ' KB' RETURN @TableSize END[/code]Thu, 02 Jul 2009 13:20:04 GMTWilliam Sorannohttp://www.sqlservercentral.com/Forums/Topic738998-1407-1.aspxah, yes.pasted it in notepad and got to see itquick replace fixed itThu, 02 Jul 2009 13:18:19 GMTdant12http://www.sqlservercentral.com/Forums/Topic738998-1407-1.aspxI have attached the updated script...once published it will be available for viewing....Thu, 02 Jul 2009 13:11:50 GMTSqlfrenzyhttp://www.sqlservercentral.com/Forums/Topic738998-1407-1.aspx[quote][b]dant12 (7/2/2009)[/b][hr]For some reason I'm getting loads of "Incorrect syntax" errors while trying to compile thisedit:what version of SQL were you using to write this?[/quote]u r getting the syntax errors because the characters added by the editor.....Thu, 02 Jul 2009 13:09:08 GMTSqlfrenzyhttp://www.sqlservercentral.com/Forums/Topic738998-1407-1.aspxFor some reason I'm getting loads of "Incorrect syntax" errors while trying to compile thisedit:what version of SQL were you using to write this?Thu, 02 Jul 2009 12:37:29 GMTdant12http://www.sqlservercentral.com/Forums/Topic738998-1407-1.aspxwell... I just scripted the function thru ssms copied them to the editor... I will try to remove the characters.....Thu, 02 Jul 2009 12:33:10 GMTSqlfrenzyhttp://www.sqlservercentral.com/Forums/Topic738998-1407-1.aspxThe uploaded text is full of gargage characters that will prevent a quick copy and paste from compiling.Any way to clean up the script.-- Mark D Powell --Thu, 02 Jul 2009 11:36:22 GMTMark D Powellhttp://www.sqlservercentral.com/Forums/Topic738998-1407-1.aspxdo you mean that it is not working....Thu, 02 Jul 2009 10:41:34 GMTSqlfrenzyhttp://www.sqlservercentral.com/Forums/Topic738998-1407-1.aspxAfter cleaning up the text of the functions so it would create them, the function fn_GetTableSize only returns NULL. I changed the function so the calculations handle a NULL return.[code]ALTER FUNCTION [dbo].[fn_GetTableSize] ( @TableName VARCHAR(100) )RETURNS VARCHAR(25)AS BEGIN DECLARE @TableSize VARCHAR(25) IF EXISTS ( SELECT 1 FROM sys.indexes WHERE object_id = OBJECT_ID(@TableName) AND type = 1 ) BEGIN SELECT @TableSize = ISNULL(dbo.fn_ClusteredIndexSize(@TableName), 0) + ISNULL(dbo.fn_GetNonClusteredIndexSize(@TableName), 0) END ELSE BEGIN SELECT @TableSize = (ISNULL(dbo.fn_CalculateHeapSize(@TableName), 0) + ISNULL(dbo.fn_GetNonClusteredIndexSize(@TableName), 0)) END SET @TableSize = LTRIM(STR(@TableSize)) + ' KB' RETURN @TableSize END[/code]Thu, 02 Jul 2009 07:41:15 GMTWilliam Sorannohttp://www.sqlservercentral.com/Forums/Topic738998-1407-1.aspxComments posted to this topic are about the item [B]<A HREF="/scripts/Table/67302/">Estimating the table size...</A>[/B]Sun, 21 Jun 2009 01:18:32 GMTSqlfrenzy