Very large Primary Key index with few rows

Question

I have a table with a GUID as the primary key.

The default is newsequentialid() to keep the GUID's in order.

With only 65,000 rows, the primary key is 1.5 GB! There's a similar table which was 2GB with only 3,000 rows.

I've truncated the second table in a test environment and it has reduced the table size and DB size by that amount.

How do I troubleshoot this? What causes the indexes to be so large with so little data?

It's a SQL Server 2016 standard database on a Windows 2012 R2 server.

---

The data is from running the script below.

;with SpaceInfo(ObjectId, IndexId, TableName, IndexName
    ,Rows, TotalSpaceMB, UsedSpaceMB)
as
( 
    select  
        t.object_id as [ObjectId]
        ,i.index_id as [IndexId]
        ,s.name + '.' + t.Name as [TableName]
        ,i.name as [Index Name]
        ,sum(p.[Rows]) as [Rows]
        ,sum(au.total_pages) * 8 / 1024 as [Total Space MB]
        ,sum(au.used_pages) * 8 / 1024 as [Used Space MB]
    from    
        sys.tables t with (nolock) join 
            sys.schemas s with (nolock) on 
                s.schema_id = t.schema_id
            join sys.indexes i with (nolock) on 
                t.object_id = i.object_id
            join sys.partitions p with (nolock) on 
                i.object_id = p.object_id and 
                i.index_id = p.index_id
            cross apply
            (
                select 
                    sum(a.total_pages) as total_pages
                    ,sum(a.used_pages) as used_pages
                from sys.allocation_units a with (nolock)
                where p.partition_id = a.container_id 
            ) au
    where   
        i.object_id > 255
    group by
        t.object_id, i.index_id, s.name, t.name, i.name
)
select 
    ObjectId, IndexId, TableName, IndexName
    ,Rows, TotalSpaceMB, UsedSpaceMB
    ,TotalSpaceMB - UsedSpaceMB as [ReservedSpaceMB]
from 
    SpaceInfo       
order by
    TotalSpaceMB desc
option (recompile)

Answer 1

A clustered index in MSSQL causes sequential data rows to be stored together in the data file, including when data in a row is updated. You don't have to have one, but, unless you make it different, a primary key will be created with a clustered index. As we've already said, really you're changing your entire table into the clustered index. Basically a tree whose leaves are the data rows. You can also have a clustered index which isn't the primary key.

A nonclustered index is a separate copy of specified table column data, organised in a different key order. It includes the clustered index key, if there is a clustered index, to link back to the table row. This makes it economical to use a small size clustered index key. GUID isn't ideal, but there's worse. Data can be fetched from a nonclustered index without touching the stored table at all. Of course, when table data is updated, the server has the job of automatically updating the data in a nonclustered index as well.