SQL Server clustered index bigger than actual table. Why and how do I fix it?

Viewed 750

I have a few tables in a SQL Server database where the clustered index is really big, up to 50% of the table size, just for the ID's?

For example :

  • Table 1 30 million rows, 10GB of data, clustered index = 10GB
  • Table 2 40 million rows, 2.4GB of data, clustered index = 18GB

The script to get the size of the indexes I am using is : https://blog.sqlauthority.com/2016/11/13/find-size-indexes-database-interview-question-week-097/

SELECT
    OBJECT_SCHEMA_NAME(i.OBJECT_ID) AS SchemaName,
    OBJECT_NAME(i.OBJECT_ID) AS TableName,
    i.name AS IndexName,
    i.index_id AS IndexID,
    8 * SUM(a.used_pages) AS 'Indexsize(KB)'
FROM 
    sys.indexes AS i
JOIN 
    sys.partitions AS p ON p.OBJECT_ID = i.OBJECT_ID AND p.index_i = i.index_id
JOIN 
    sys.allocation_units AS a ON a.container_id = p.partition_id
GROUP BY 
    i.OBJECT_ID, i.index_id, i.name
ORDER BY 
    8 * SUM(a.used_pages)

The index code is as follows (the same for both tables) :

ALTER TABLE [dbo].[my_Table] 
     ADD PRIMARY KEY CLUSTERED ([pkID] ASC)
         WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, 
               SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, 
               ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO

Please can someone assist me in fixing this or help explain why this has happened?

The indexes are not fragmented at all (<5%), surely as the clustered index, it just has the PK in it? Each table has a nvarchar(max) in it, would that be a "hidden" include in the index? Still does not explain as to why it is larger than the actual table though?

I am using SQL Server 2016 Standard.

Thank you have and have a great day. Wade

1 Answers

We usually use for the log file initial more than one GB to avoid more VLF which helps to faster recovery and improve the performance. and also the auto growth should be more than 1 GB which will strict to generate more VLFs.

If you know your load behavior then you can go and allocate maximum initial size which will help to improve the performance because it will not go and initialize the file every time.

Please let me know if it is helpful.

Related