You're confusing a few terms, I think. The ndf file is not an "index file", it is just another database data file to house the data.
It sounds like you have two database data files (the MDF and NDF). With the size of your MDF being so small, my guess would be that your database has two different filegroups (the PRIMARY filegroup using the MDF, and the "other" filegroup using the NDF file).
You need to do a little discovery here.
What are the filegroups in your database?
Which database files belong to which filegroups?
file_name = df.name,
filegroup_name = f.name
from sys.database_files df
inner join sys.filegroups f
on df.data_space_id = f.data_space_id;
And which data space do all of the allocation units live in, and how much are they?
data_space_name = ds.name,
total_pages = sum(au.total_pages),
convert(decimal(11, 2), sum(au.total_pages) * 8.0 / 1024)
from sys.allocation_units au
inner join sys.data_spaces ds
on au.data_space_id = ds.data_space_id
group by ds.name;
My guess is that you have two filegroups,
PRIMARY and another one. The other filegroup probably has the NDF file that you are looking at linked up with it. And maybe even the "other" filegroup is the default filegroup. This is actually a prudent approach, so that you keep user objects off of the
Why do you think this is a problem? Maybe you just have 57.3 GB worth of data in this database now. Don't be thrown off by the differing sizes of the MDF and NDF (unless they are in the same filegroup, in which case update your question with the specifics from my diagnostic queries above).
Every time I try to reorganise or rebuild indexes on this database, all available RAM is eaten up for this operation, the server becomes almost unresponsive and IO activity is incredibly high
You should only be selectively rebuilding/reorganizing your indexes based off of their fragmentation levels. As for the memory consumption, there are ways to minimize that impact such as limiting by altering the
max server memory server configuration so that SQL Server doesn't over consume memory and then battle with the OS.