Mostly indexes are encouraged to boost the performance. But in case of very large tables we should consider another dimension also. Incorrect use of indexing is most common root cause of memory pressure. Along with growing size of database, DBA should look forward to remove clustered indexes from large tables.The negative result is that by changing the clustered primary keys to nonclustered primary keys, you’ll increase the amount of load on the disk subsystem. But main achievement will be in case of memory.
As clustered index contains the entire table, when the clustered index is loaded into memory, much more memory is used than loading a non-clustered version of the index. This will dramatically reduce your memory requirements for this table object.
Consider a very wide table (average of 250 bytes per row) that has a total of 65 GB of data. It means that the clustered index is also 65 GB in size. By changing the primary key from a clustered index to a nonclustered index, the index space requirements drop from 65 GB to approx 1 GB (including padding). This greatly reduces the amount of storage required, and when doing index scans against the primary key of the table, only 1 GB of data has to be loaded into memory – which means the data can be left in memory much longer.In common case if a production server has only 16 GB of RAM installed, so having a 63 GB index in memory is not an effective use of memory. By making these changes to the primary key, disk load increased a small percentage. But memory load will decrease dramatically allowing SQL Server to keep data in cache much longer, while also allowing SQL Server to cache other objects into memory as well.