We were doing some capacity estimation for one of the clients today to forecast their storage requirements for SQL Server database on Azure. While this estimation involves various business factors as well but technically the very basic thing that you would like to know specially towards your existing biggest table is the row size. Further, not just the actual row size at random but a consideration to minimum, maximum and average row size also so that you can predict the future needs in a more statistical way. Let’s see how we can determine the actual row size in SQL Server database.
Note that we are talking about actual row size here, not the sizes defined by the schema of the table. Also, note that we are just trying to get an idea here not a robust calculation to a precise level of accuracy.
You can run the following query that gives you good level of details –
DBCC SHOWCONTIG ('dbo.p_Test') WITH TABLERESULTS
However, this method has become deprecated and there is another way using DMV to get the same information –
SELECT * FROM sys.dm_db_index_physical_stats ( DB_ID() , OBJECT_ID(N'dbo.p_Test') , NULL , NULL , 'DETAILED' )
If you don’t specify “DETAILED” in the query then the size fields are returned as NULL –
SELECT * FROM sys.dm_db_index_physical_stats ( DB_ID() , OBJECT_ID(N'dbo.p_Test') , NULL , NULL , '' )
After knowing no. of records over a period of time you can estimate it for the next n number of years and hence choose the right amount of storage needed for the same.
You can get your hands dirty with more granular details if you want to get to the root of each number calculation. Microsoft explains this well to estimate the size of heap.
For a thorough details of the DMV (sys.dm_db_index_physical_stats) used here, visit this page.