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).
Getting ready
This example refers the I drive to place non-clustered indexes.
The following are the pre-requisites to completing this recipe:
An instance of SQL Server 2012 Developer or Enterprise Evaluation edition.
Sample
AdventureWorks2012database on the instance of SQL server...
