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