Data Partitioning - Pro SQL Server Internals - page 336

Now let's partition the table on a monthly basis, as shown in Listing 15-31.
Listing 15-31. Potential issues with data partitioning: Partitioning the table
create partition function pfData(datetime)
as range right for values
('2014-02-01', '2014-03-01','2014-04-01','2014-05-01'
,'2014-06-01','2014-07-01','2014-08-01');
create partition scheme psData
as partition pfData
all to ([FG1]);
create unique clustered index IDX_Data_DateCreated_Id
on dbo.Data(DateCreated,ID)
on psData(DateCreated);
create unique nonclustered index IDX_Data_DateModified_Id_DateCreated
on dbo.Data(DateModified, ID, DateCreated)
on psData(DateCreated);
If you run the code from Listing 15-30 again, the execution plan would change as shown in Figure 15-17 . As you
can see, SQL Server decides to use a Clustered Index Scan , which dramatically decreases the performance of the query.
Figure 15-17. Execution plan with partitioned table
The root cause of the problem is related to the fact that the data in clustered and nonclustered indexes are now
sorted on a partition-by-partition basis rather than across the entire table. You can think about each partition as an
individual table with its own set of data and indexes. SQL Server decides that, in such a situation, a clustered index
scan is the cheapest option with which to proceed.
Let's look what happens if you force SQL Server to use a nonclustered index with an index hint, as shown in
Listing 15-32.
Listing 15-32. Potential issues with data partitioning: Using a nonclustered index with a hint
declare
@LastDateModified datetime = '2014-05-25'
select top 100 Id, DateCreated, DateModified, PlaceHolder
from dbo.Data with (index=IDX_Data_DateModified_Id_DateCreated)
where DateModified > @LastDateModified
order by DateModified, Id
As you can see in Figure 15-18 , the execution plan is even less efficient than before. SQL Server located and read
all of the rows with a DateModified greater than @ LastDateModified from every partition, and it performed a Key
Lookup operation for all of them, sorting the data afterwards.