June 21, 2009 at 1:18 am
Comments posted to this topic are about the item Estimating the table size...
Regards,
[font="Verdana"]Sqlfrenzy[/font]
July 2, 2009 at 7:41 am
After 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.
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
Bill Soranno
MCP, MCTS, MCITP DBA
Database Administrator
Winona State University
Maxwell 143
"Quality, like Success, is a Journey, not a Destination" - William Soranno '92
July 2, 2009 at 10:41 am
do you mean that it is not working....
Regards,
[font="Verdana"]Sqlfrenzy[/font]
July 2, 2009 at 11:36 am
The 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 --
July 2, 2009 at 12:33 pm
well... I just scripted the function thru ssms copied them to the editor... I will try to remove the characters.....
Regards,
[font="Verdana"]Sqlfrenzy[/font]
July 2, 2009 at 12:37 pm
For some reason I'm getting loads of "Incorrect syntax" errors while trying to compile this
edit:
what version of SQL were you using to write this?
--
Thiago Dantas
@DantHimself
July 2, 2009 at 1:09 pm
dant12 (7/2/2009)
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?
u r getting the syntax errors because the characters added by the editor.....
Regards,
[font="Verdana"]Sqlfrenzy[/font]
July 2, 2009 at 1:11 pm
I have attached the updated script...once published it will be available for viewing....
Regards,
[font="Verdana"]Sqlfrenzy[/font]
July 2, 2009 at 1:18 pm
ah, yes.
pasted it in notepad and got to see it
quick replace fixed it
--
Thiago Dantas
@DantHimself
July 2, 2009 at 1:20 pm
Here is a cleaned up set of 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'
END
GO
CREATE FUNCTION [dbo].[fn_ClusteredIndexSize]
(
@Tablename VARCHAR(100)
)
RETURNS BIGINT
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
,@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
END
GO
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 BIGINT
AS 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 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
END
GO
CREATE FUNCTION [dbo].[fn_GetNonClusteredIndexSize]
(
@TableName VARCHAR(100)
)
RETURNS BIGINT
AS 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))
END
GO
CREATE 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
Bill Soranno
MCP, MCTS, MCITP DBA
Database Administrator
Winona State University
Maxwell 143
"Quality, like Success, is a Journey, not a Destination" - William Soranno '92
July 2, 2009 at 1:24 pm
according to your script one of my tables has 440 MBs but SSMS says it has around 46 MBs
u sure u did the math right?
--
Thiago Dantas
@DantHimself
July 2, 2009 at 1:30 pm
thanks william...
Regards,
[font="Verdana"]Sqlfrenzy[/font]
July 2, 2009 at 1:42 pm
dant12 (7/2/2009)
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?
I tested the script against the 7 tables of adventureworks database...5 tables gave me result with a margin +1/-1..
Regards,
[font="Verdana"]Sqlfrenzy[/font]
June 9, 2010 at 6:25 pm
Wouldn't it be incorrect to compare this script to an existing table, since the script is meant to estimate a table's size
Sqlfrenzy (7/2/2009)
dant12 (7/2/2009)
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?
I tested the script against the 7 tables of adventureworks database...5 tables gave me result with a margin +1/-1..
February 25, 2011 at 1:35 pm
I 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.doc
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply