1

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)
9
  • Is your primary key a clustered index? Jun 24, 2017 at 7:52
  • Yes, just setup as a default SQL Server PK
    – RemarkLima
    Jun 24, 2017 at 7:56
  • 1
    "How do I stop the PK including all the columns" - define it as non-clustered. You deployed the defaults, which makes it clustered, which creates a B+ Tree index for the PK, which includes all columns. Jun 24, 2017 at 9:50
  • 5
    Defining the PK as non-clustered would convert the table from clustered to a heap. You'll still need the same (or around) amount of disk space for the table to be stored. It's not that a clustered index duplicates the data. The clustered index is the table. Jun 24, 2017 at 10:28
  • 2
    What is your table definition?
    – Joe Obbish
    Jun 24, 2017 at 16:24

1 Answer 1

2

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.

1
  • With everyone's help and the comments that the cluster index IS the table allowed me to find the source - the message queue table was storing the entire MIME message with all attachments encoded in there... Each message generally having 10 - 50 MB of attachments. The vast growth makes sense in this context, and it wasn't an index, but the raw data.
    – RemarkLima
    Jul 10, 2017 at 9:35

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.