Implementing Physical Database Structure - Microsoft SQL Server 2012 Performance Tuning - page 340

We then verify the location of our tables by executing a query that makes use of several
joins. We fetch details from sys.indexes , sys.tables , sys.filegroups , sys.
database_files . We join sys.indexes and sys.tables on the object_id column.
data_space_id is the ID of the filegroup in sys.indexes , sys.filegroups , and sys.
database_files ; then we join these system views, based on data_space_id . We retrieve
table name by using the OBJECT_NAME() function, name of the filegroup from the sys.
filegroups system view, and path of the data files from the sys.database_files system
view. Remember that for a table that is on heap, the value of index_id is always 0 . index_
id for a clustered index is always 1 . Clustered index means that the data of the table itself is
attached to it. This is the reason why we have specified the condition I.index_id <= 1 , so
that we get details only for a clustered table or a heap table. Any non-clustered index entries
are filtered. Note that as we have not partitioned the table, we have not included the sys.
partitions system catalog view in our query. In case the table was partitioned and you had
wanted to return a list of locations for each partition on the table, you would also have needed
to join sys.partitions in the query, to retrieve the partition-specific location information.
Moving the existing large table to separate
physical disk
In the previous recipe, Using Files and Filegroups , we saw that we can create a filegroup
and create a table that is expected to become large and place it on different physical disks
using filegroup.
But what if there is already an existing large table in an existing database that is
extensively used by queries? Let's say that you are responsible for the AdventureWorks2012
database in your production environment, and there is one large table named Sales.
SalesOrderDetail , which is located on the primary filegroup. You observe that the table
is very large, I/O operations with a large volume of data made on this table are taking more
time to be completed causing blocking issues, and other transactions have to wait for I/O
operations on the same resources, resulting in bad I/O response time. You realise that there
is a need to move this large table ( Sales.SalesOrderDetail ) containing billions of rows
onto a dedicated physical disk to improve the I/O response time. How would you achieve this
task of moving a large table to another disk?
In this recipe, you will learn how to move an existing large table to a different physical disk.
Placing two large tables used frequently in join queries on two different
physical disks can also help in improving performance by allowing SQL Server
to perform parallel read operations on two tables specified join queries.