Moving non-clustered indexes on separate physical disk
If you have few large tables and some non-clustered indexes on these tables, which are frequently used in queries, you can consider placing the non-clustered indexes on a separate physical drive. By having non-clustered indexes on a separate physical disk, SQL Server can perform bookmark lookups in parallel and can simultaneously read data pages and index pages. This parallelism improves the performance of queries.
In this recipe, we will move all non-clustered indexes of table
Sales.SalesOrderDetail to a separate physical disk (the table
Sales.SalesOrderDetail that we moved to
F: drive in the previous recipe, Moving Existing Large Table to Separate Physical Disk).
This example ...