Here’s a practical script that I built to view how much space each database object takes up in the database. The script also show you information on how many rows a table (or indexed view) contains, as well as if it’s partitioned and/or compressed.

As usual, this script is strictly as-is, and comes with no warranty.

SELECT --- Schema, type and name of object and index:
       REPLACE(obj.type_desc, '_', ' ') AS objectType,
       sch.[name]+'.'+obj.[name] AS objectName,
       ISNULL(ix.[name], '') AS indexName,
       ix.type_desc AS indexType,

       --- Partition number, if there are partitions:
       (CASE COUNT(*) OVER (PARTITION BY ps.[object_id], ps.index_id)
             WHEN 1 THEN ''
             ELSE CAST(ps.partition_number AS varchar(10))
             END) AS [partition],

       --- Storage properties:
       p.data_compression_desc AS [compression],
       ds.[name]+ISNULL('('+pc.[name]+')', '') AS dataSpace,
       STR(ISNULL(NULLIF(ix.fill_factor, 0), 100), 4, 0)+'%' AS [fillFactor],

       --- The raw numbers:
       ps.row_count AS [rows],
       STR(1.0*ps.reserved_page_count*8/1024, 12, 2) AS reserved_MB,
       STR(1.0*ps.in_row_used_page_count*8/1024, 12, 2) AS inRowUsed_MB,
       STR(1.0*ps.row_overflow_used_page_count*8/1024, 12, 2) AS RowOverflowUsed_MB,
       STR(1.0*ps.lob_used_page_count*8/1024, 12, 2) AS outOfRowUsed_MB,
       STR(1.0*ps.used_page_count*8/1024, 12, 2) AS totalUsed_MB

FROM sys.dm_db_partition_stats AS ps
INNER JOIN sys.partitions AS p ON
    ps.[partition_id]=p.[partition_id]
INNER JOIN sys.objects AS obj ON
    ps.[object_id]=obj.[object_id]
INNER JOIN sys.schemas AS sch ON
    obj.[schema_id]=sch.[schema_id]
LEFT JOIN sys.indexes AS ix ON
    ps.[object_id]=ix.[object_id] AND
    ps.index_id=ix.index_id
--- Data space is either a file group or a partition function:
LEFT JOIN sys.data_spaces AS ds ON
    ix.data_space_id=ds.data_space_id
--- This is the partitioning column:
LEFT JOIN sys.index_columns AS ixc ON
    ix.[object_id]=ixc.[object_id] AND
    ix.index_id=ixc.index_id AND
    ixc.partition_ordinal>0
LEFT JOIN sys.columns AS pc ON
    pc.[object_id]=obj.[object_id] AND
    pc.column_id=ixc.column_id

--- Not interested in system tables and internal tables:
WHERE obj.[type] NOT IN ('S', 'IT')

ORDER BY sch.[name], obj.[name], ix.index_id, p.partition_number;

Pages

SQL Server stores all of its data in pages. A page is the basic storage unit, consisting of 8 kB of consecutive data, on disk and in memory. In this script, we multiply the number of pages by 8, then divide that figure by 1024, which gives us the number of MBs or storage used.

In practice, you won’t know the actual “fill factor” of each page (only the fillfactor setting on the table/index), so in theory, all the pages you see could be half-full. The script, in order words, shows you only the reserved/used pages, really, not the actual amount of data. Though, in most cases, these two will be fairly close.

Compression

Compression allows SQL Server to fit more data in each page. The numbers you see here are compressed sizes, because the size is calculated from the number of actually used pages in the database.

Partitioning

If you have partitioned tables or indexes, the two dynamic management views sys.partitions and sys.dm_db_partition_stats, will contain the number of rows and allocated pages for each partition separately. Unpartitioned objects are treated as single partitions.

In-row, row-overflow and out-of-row

SQL Server stores table data in one of three types of pages, depending on the data:

• The bulk of your data will probably be stored in regular pages, called in-row pages.
• When the total size of all columns on a row exceeds 8000 bytes, the remainder of the row is stored in special row overflow pages.
• text, ntext, image, varchar(max), varbinary(max) and xml columns are known as LOB (“large object”) data, and are stored in “out-of-row pages“. You could think of them as being stored in a separate table right from the start, even though they might fit in-row.

Generally speaking, you get much better performance working with in-row data, so your choice of datatypes and how to store large rows is an important design consideration. There’s much more to be said in different types of pages in data storage. For a great article on the subject, check out this article on aboutsqlserver.com.

That’s it for this week. Leave your comments and questions in the section below!