February 27, 2015 at 1:40 pm
In my database there is a big table and format is something like this:
CREATE TABLE [dbo].[MyTable](
[aaa] [uniqueidentifier] NULL,
[bbb] [uniqueidentifier] NULL,
[ccc] [nvarchar](max) NULL,
[ddd] [nvarchar](100) NULL,.......etc.........
There are some more columns with more 'nvarchar' (max) and other INT data types. Anyway, I know a page is 8K size. How do I find out how much space does A ROW takes with above datatypes? If users add 5000 Rows per day, how do I figure out how much size the table will increase?
Thank you.
February 27, 2015 at 1:52 pm
Just let SQL tell you :-).
Create an empty table and then run this command on it:
SELECT *
FROM sys.dm_db_index_physical_stats(DB_ID(),OBJECT_ID('<your_table_name>'),NULL,NULL,'DETAILED')
It will give you the min and max row sizes.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
February 27, 2015 at 2:06 pm
I created a table using all same column and datatype and ran as you said but it came out 0s !!!!
February 27, 2015 at 2:12 pm
D'OH!
Here's my script, based on Books Online's description of how to calc max row size. I think it will give you a reasonably good estimate of the max possible row size. Note: It uses 16 for (max) columns, assuming they're stored off-page, with just a row pointer on the data page.
DECLARE @table_name varchar(128)
SET @table_name = '<your_table_name>' --Edit: Changed table name to generic entry
SELECT
SUM(CASE WHEN c.max_length = -1 THEN 16 ELSE c.max_length END) +
MAX(ca1.count_columns_var_length * 2) + 2 +
MAX((ca1.count_columns_all + 7) / 8) +
11 AS total_max_row_length,
SUM(CASE WHEN c.max_length = -1 THEN 16 ELSE c.max_length END) AS data_length, + --column data, fixed and/or variable
MAX(ca1.count_columns_var_length * 2) + 2 AS var_lengths, + --var lengths / offset pointer to var lengths
MAX((ca1.count_columns_all + 7) / 8) AS bit_map, + --NULL bit map
11 AS general_row_overhead --general row overhead
FROM sys.columns c
CROSS APPLY (
--DECLARE @table_name varchar(128) SET @table_name = 'dbs'
SELECT
SUM(CASE WHEN t2.name LIKE '%var%' THEN 1 ELSE 0 END) AS count_columns_var_length,
SUM(1) AS count_columns_all
FROM sys.columns c2
INNER JOIN sys.types t2 ON
t2.system_type_id = c2.system_type_id AND
t2.user_type_id = c2.user_type_id
LEFT OUTER JOIN sys.computed_columns cc2 ON
cc2.object_id = c2.object_id AND
cc2.column_id = c2.column_id
WHERE
c2.object_id = OBJECT_ID(@table_name) AND
(c2.is_computed = 0 OR cc2.is_persisted = 1)
) AS ca1
INNER JOIN sys.types t ON
t.system_type_id = c.system_type_id AND
t.user_type_id = c.user_type_id
LEFT OUTER JOIN sys.computed_columns cc ON
cc.object_id = c.object_id AND
cc.column_id = c.column_id
WHERE
c.object_id = OBJECT_ID(@table_name) AND
(c.is_computed = 0 OR cc.is_persisted = 1)
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
February 28, 2015 at 8:17 am
Hi Scott,
I got the following results, but didn't get it. Would you mind explaining please?
total_max_row_length
1461
data_length
1415
var_lengths
32
bit_map
3
general_row_overhead
11
February 28, 2015 at 10:12 am
Tac11 (2/27/2015)
In my database there is a big table and format is something like this:CREATE TABLE [dbo].[MyTable](
[aaa] [uniqueidentifier] NULL,
[bbb] [uniqueidentifier] NULL,
[ccc] [nvarchar](max) NULL,
[ddd] [nvarchar](100) NULL,.......etc.........
There are some more columns with more 'nvarchar' (max) and other INT data types. Anyway, I know a page is 8K size. How do I find out how much space does A ROW takes with above datatypes? If users add 5000 Rows per day, how do I figure out how much size the table will increase?
Thank you.
Wouldn't it be easier to just check the size of the whole table using something like sp_SpaceUsed and then divide that by the number of the rows in the table to come up with an average row size?
--Jeff Moden
Change is inevitable... Change for the better is not.
March 1, 2015 at 4:46 am
Hi Jeff,
After i divided size (KB) with Rows it came out 30 KB (around) size for each row. So each page contains 8 KB. So a row on my table contains almost 4 pages?
March 1, 2015 at 4:30 pm
Tac11 (3/1/2015)
Hi Jeff,After i divided size (KB) with Rows it came out 30 KB (around) size for each row. So each page contains 8 KB. So a row on my table contains almost 4 pages?
Yep... Blobs (MAX datatypes, XML, etc) live in other "tables" and are handled differently than "in-row" data but that's what it boils down to and that probably includes the weight of indexes, which are also important in capacity planning.
--Jeff Moden
Change is inevitable... Change for the better is not.
March 2, 2015 at 8:09 am
Thanks you so much guys!!!!!
March 2, 2015 at 9:07 am
As I noted earlier, once you have data loaded you can do:
SELECT *
FROM sys.dm_db_index_physical_stats(DB_ID(),OBJECT_ID('<your_table_name>'),NULL,NULL,'DETAILED')
And SQL will give you the average and max rows lengths for the data you have.
My script gives you the theoretical max row size of the row in the main table (not in LOB overflow).
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
March 9, 2015 at 5:48 am
Hi Jeff,
Since BLOBs live in different table, if I want to know 'what's the size of each row?' does the 'Data' contains (from sp_spacesued 'tablename') BLOBs too? And what about the 'Index_size' column? should I add this column too with 'Data' to get exact size of a row?
Another word, what are the columns should I add and divide from 'rows'? Does 'Data' column also include BLOBs?
March 11, 2015 at 7:13 pm
Tac11 (3/9/2015)
Hi Jeff,Since BLOBs live in different table, if I want to know 'what's the size of each row?' does the 'Data' contains (from sp_spacesued 'tablename') BLOBs too? And what about the 'Index_size' column? should I add this column too with 'Data' to get exact size of a row?
Another word, what are the columns should I add and divide from 'rows'? Does 'Data' column also include BLOBs?
Just look at the reserved size. Blobs are still a "part" of the table for planning purposes.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 12 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply