How to I determine the maximum row size for a table? I'm looking for a tool or script that does this so I don't have to add up each column's size by hand.
My goal is to produce a report of tables that are too wide so we can look into restructing them. I know we have several that are so wide that only 1 row fits on each 8K page, but I want to find the rest.
Another way, run this then look at MaximumRecordsize
dbcc showcontig ('YourTableNameHere') with tableresults
As the DBCC SHOWCONTIG is going to be deprecated, Microsoft advises to use the sys.dm_db_index_physical_stats instead. You can get the maximum, minimum and average row size for each index and other stats, too:
SELECT * FROM sys.dm_db_index_physical_stats (DB_ID(N'AdventureWorks2014'), OBJECT_ID(N'[sales].[SalesOrderHeader]'), NULL, NULL , 'DETAILED')
Output:
database_id object_id index_id partition_number index_type_desc alloc_unit_type_desc index_depth index_level avg_fragmentation_in_percent fragment_count avg_fragment_size_in_pages page_count avg_page_space_used_in_percent record_count ghost_record_count version_ghost_record_count min_record_size_in_bytes max_record_size_in_bytes avg_record_size_in_bytes forwarded_record_count compressed_page_count
----------- ----------- ----------- ---------------- ------------------------------------------------------------ ------------------------------------------------------------ ----------- ----------- ---------------------------- -------------------- -------------------------- -------------------- ------------------------------ -------------------- -------------------- -------------------------- ------------------------ ------------------------ ------------------------ ---------------------- ---------------------
6 1266103551 1 1 CLUSTERED INDEX IN_ROW_DATA 3 0 0.439882697947214 28 24.3571428571429 682 98.8644304423029 31465 0 0 154 195 171.487 NULL 0
6 1266103551 1 1 CLUSTERED INDEX IN_ROW_DATA 3 1 0 2 1 2 54.7442550037064 682 0 0 11 11 11 NULL 0
6 1266103551 1 1 CLUSTERED INDEX IN_ROW_DATA 3 2 0 1 1 1 0.296515937731653 2 0 0 11 11 11 NULL 0
6 1266103551 2 1 NONCLUSTERED INDEX IN_ROW_DATA 2 0 0 4 25.5 102 99.0670990857425 31465 0 0 24 24 24 NULL 0
6 1266103551 2 1 NONCLUSTERED INDEX IN_ROW_DATA 2 1 0 1 1 1 31.4801087225105 102 0 0 23 23 23 NULL 0
6 1266103551 3 1 NONCLUSTERED INDEX IN_ROW_DATA 2 0 0 3 36.3333333333333 109 99.8363355572029 31465 0 0 26 26 26 NULL 0
6 1266103551 3 1 NONCLUSTERED INDEX IN_ROW_DATA 2 1 0 1 1 1 36.3355572028663 109 0 0 25 25 25 NULL 0
6 1266103551 4 1 NONCLUSTERED INDEX IN_ROW_DATA 2 0 0 2 27.5 55 98.9285025945145 31465 0 0 12 12 12 NULL 0
6 1266103551 4 1 NONCLUSTERED INDEX IN_ROW_DATA 2 1 0 1 1 1 11.527057079318 55 0 0 15 15 15 NULL 0
6 1266103551 5 1 NONCLUSTERED INDEX IN_ROW_DATA 2 0 0 2 27.5 55 98.9285025945145 31465 0 0 12 12 12 NULL 0
6 1266103551 5 1 NONCLUSTERED INDEX IN_ROW_DATA 2 1 0 1 1 1 13.5656041512231 55 0 0 18 18 18 NULL 0
Here is another query I got, but like Tomalak's it is broken because it doesn't take into account things like the overhead for variable length columns.
SELECT OBJECT_NAME (id) tablename
, COUNT (1) nr_columns
, SUM (length) maxrowlength
FROM syscolumns
GROUP BY OBJECT_NAME (id)
ORDER BY OBJECT_NAME (id)
Might not the best but this is showing the number of rows + for columns with datatype date,time and so on
SELECT
t.NAME AS TableName,
s.Name AS SchemaName,
p.rows AS RowCounts,
SUM(a.total_pages) * 8 AS TotalSpaceKB,
CAST(ROUND(((SUM(a.total_pages) * 8) / 1024.00), 2) AS NUMERIC(36, 2)) AS TotalSpaceMB,
SUM(a.used_pages) * 8 AS UsedSpaceKB,
CAST(ROUND(((SUM(a.used_pages) * 8) / 1024.00), 2) AS NUMERIC(36, 2)) AS UsedSpaceMB,
(SUM(a.total_pages) - SUM(a.used_pages)) * 8 AS UnusedSpaceKB,
CAST(ROUND(((SUM(a.total_pages) - SUM(a.used_pages)) * 8) / 1024.00, 2) AS NUMERIC(36, 2)) AS UnusedSpaceMB
FROM
sys.tables t
INNER JOIN
sys.indexes i ON t.OBJECT_ID = i.object_id
INNER JOIN
sys.partitions p ON i.object_id = p.OBJECT_ID AND i.index_id = p.index_id
INNER JOIN
sys.allocation_units a ON p.partition_id = a.container_id
LEFT OUTER JOIN
sys.schemas s ON t.schema_id = s.schema_id
INNER JOIN sysobjects so on t.object_id = so.id
INNER JOIN syscolumns SC on (so.id = sc.id)
INNER JOIN systypes st on (st.type = sc.type)
WHERE
t.NAME NOT LIKE 'dt%'
AND t.is_ms_shipped = 0
AND i.OBJECT_ID > 255
AND so.type = 'U'
and st.name IN ('DATETIME', 'DATE', 'TIME')
GROUP BY
t.Name, s.Name, p.Rows
ORDER BY
p.rows DESC
Have a look at INFORMATION_SCHEMA.COLUMNS. You can add the size of each column in a table to give you an idea which tables have the potential to overshoot the 8k/row rule.
SELECT * FROM INFORMATION_SCHEMA.COLUMNS
You will figure out the rest when you see the query result.
Better option to get table size
SELECT
t.NAME AS TableName,
s.Name AS SchemaName,
p.rows AS RowCounts,
SUM(a.total_pages) * 8 AS TotalSpaceKB,
CAST(ROUND(((SUM(a.total_pages) * 8) / 1024.00), 2) AS NUMERIC(36, 2)) AS TotalSpaceMB,
SUM(a.used_pages) * 8 AS UsedSpaceKB,
CAST(ROUND(((SUM(a.used_pages) * 8) / 1024.00), 2) AS NUMERIC(36, 2)) AS UsedSpaceMB,
(SUM(a.total_pages) - SUM(a.used_pages)) * 8 AS UnusedSpaceKB,
CAST(ROUND(((SUM(a.total_pages) - SUM(a.used_pages)) * 8) / 1024.00, 2) AS NUMERIC(36, 2)) AS UnusedSpaceMB
FROM
sys.tables t
INNER JOIN
sys.indexes i ON t.OBJECT_ID = i.object_id
INNER JOIN
sys.partitions p ON i.object_id = p.OBJECT_ID AND i.index_id = p.index_id
INNER JOIN
sys.allocation_units a ON p.partition_id = a.container_id
LEFT OUTER JOIN
sys.schemas s ON t.schema_id = s.schema_id
WHERE
t.NAME NOT LIKE 'dt%'
AND t.is_ms_shipped = 0
AND i.OBJECT_ID > 255
GROUP BY
t.Name, s.Name, p.Rows
ORDER BY
t.Name
