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 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy