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 the O’Reilly learning platform.
O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.
