Search code examples
sqlsql-serverdatabaseindexingnon-clustered-index

Index Size not reachable

I have created a non-clustered index on table columns and it contains 5GB of index data but I am not able to see this +5GB data size change on my disk.

Any suggestions?

Thanks

Solution

  • You can see how much space is being used within a SQL mdf or ndf file with this query:

    SELECT  df.name ,
        df.size / 128 AS FileSize ,
        df.size / 128 - fsu.unallocated_extent_page_count / 128 AS UsedSpace
FROM    DatabaseName.sys.dm_db_file_space_usage fsu
        INNER JOIN DatabaseName.sys.database_files df ON fsu.file_id = df.file_id

    If the file had enough free space before, it would not have to grow when you created the index.