Reduce the size of a primary key Index

Question

I'm using Azure SQL DB and noticed that one of my largest databases appears to have a primary key index with a total size of 6Gb. It's a clustered index on a uniqueidentifier. However, the total rows in the table only amount to around 78k rows at any given point in time.

This table stores outbound email and sms messages, so it fills up and get's emptied out on a regular basis. We also run weekly index maintenance across the database, so I'm trying to understand why this PK index appears to be far larger than it has any reason to be.

The email sizes are almost 50KB in some cases for the HTML ones.

With my own local testing, it seems removing rows from this table does reduce the size of the PK, but this does not appear to be happening on the production database.

And then what options do I have to reduce this index size and free up what I imagine is a lot of unused space.

Answer 1

The leaf level of the clustered index is the in-row data, logically ordered by the clustering key.

78,000 rows of ~50KB emails comes to around 3.6GB data in total.

With other row data, and overhead (row headers, unused space within pages), your situation is within the bounds of what would be expected.

See Estimate the Size of a Clustered Index in the documentation.

Presumably, your local testing was done with smaller, non-production data.

Answer 2

Tables that have a pattern of many inserts and deletes tend to have a problem with accumulating ghost records (a row which is marked as deleted but not actually removed, there is a later asynchronous process that is supposed to come along and clean them up - but it can fall behind).

If you run this query and post the results we should be able to see if this is what is causing your issue.

    select 
       sum(record_count) as records,
       SUM(PAGE_COUNT) AS pages,
       sum(ghost_record_count) as ghost_records,
       sum(version_ghost_record_count) as version_ghost_records
from sys.dm_db_index_physical_stats(
       db_id(), object_id('dbo.yourtable'), default, default, 'detailed')
 where index_id = 1