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 ...

Get Microsoft SQL Server 2012 Performance Tuning Cookbook now with O’Reilly online learning.

O’Reilly members experience live online training, plus books, videos, and digital content from 200+ publishers.