How to evaluate the storage space, that is used by the Dynamics CRM database tables

  • Article

 

If you have ever needed a query that shows you in detail the amount of storage space, that every single CRM database table is using, the SQL query in this article will surely be of great value for you.  

The query returns for each table in your database the following information:

ObjectID:        The SQL Server’s internal object id for the specified table
ObjectName:   The name of the table
Reserved_MB: The total amount of space, that the table object does allocate on the disk drive in megabytes.
                       The Reserved_MB value a sum out of the storage space for data, indexes, and unused but allocated space.
Data_MB:        The total amount of storage space in megabytes, that is used for storing data.
Index_MB:      The total amount of storage space in megabytes, that is occupied by the table’s indexes.
RowCount:      The total amount of data rows in the specified table

 

To retrieve this information from the CRM database, please execute the following T-SQL query using your CRM database:

SELECT
so.object_id AS ObjectID,
so.name AS ObjectName,       
(CONVERT(decimal(20,4),(SUM (ps.reserved_page_count) * 8))) / 1024 As Reserved_MB,
(CONVERT(decimal(20,4),SUM (
            CASE
                  WHEN (ps.index_id < 2) THEN (ps.in_row_data_page_count + ps.lob_used_page_count + ps.row_overflow_used_page_count)
                  ELSE ps.lob_used_page_count + ps.row_overflow_used_page_count
            END
            ) * 8)) / 1024 As Data_MB,
(CONVERT(decimal(20,4),(CASE WHEN (SUM(used_page_count)) >
            (SUM(CASE
                  WHEN (ps.index_id < 2) THEN (ps.in_row_data_page_count + ps.lob_used_page_count + ps.row_overflow_used_page_count)
                  ELSE ps.lob_used_page_count + ps.row_overflow_used_page_count
            END
            )) THEN (SUM(used_page_count) -          
            (SUM(CASE
                  WHEN (ps.index_id < 2) THEN (ps.in_row_data_page_count + ps.lob_used_page_count + ps.row_overflow_used_page_count)
                  ELSE ps.lob_used_page_count + ps.row_overflow_used_page_count
            END
            ))) ELSE 0 END) * 8)) / 1024 As Index_MB,
(SUM (
            CASE
                  WHEN (ps.index_id < 2) THEN ps.row_count
                  ELSE 0
            END
            )) AS [RowCount]
FROM sys.dm_db_partition_stats AS ps
INNER JOIN sys.objects AS so ON so.object_id = ps.object_id
WHERE so.object_id > 100
GROUP BY so.object_id, so.name
ORDER BY [Reserved_MB]Desc 

 

Greetings, 
Alex Leu