Partitioning a large table has not improved performance, why?

Question

In SQL Server 2014 I have partitioned one of my large tables weekly and defined a sliding window scenario to switch the oldest week's data to the archive DB and create a new partition for the next week.

This is the result:

This is for an AVL System (Vehicle Tracking). I have partitioned on PositionDate (datetime). All our queries have PositionDate in the WHERE clause and in many cases we have VehicleId in the WHERE clause too. So I created two aligned indexes on VehicleId (int):

• one on (PositionDate,VehicleId);
• one on just (VehicleId).

But in every query that contains VehicleId in its WHERE clause neither of these two non-clustered indexes is used (according to the query plan).

I have a performance problem now.

I compared the query plans between partitioned and non-partitioned table for queries like below:

Select * from MyNonPart_Table Where PositionDate between '2016-05-01' AND '2016-06-01'

Select * from PartitinedTable Where PositionDate between '2016-05-01' AND '2016-06-01'

and awesomely I see that the first query costs 30% but the second, 70%.

I have one file group with two files for the partitioned table.

My questions:

1. Is the number of rows in each partition more than the optimal number of rows for partitioning? If I partition by day and hold last 60 days' data live, will that help me improve performance?

2. Are my non-clustered indexes well defined or I should remove them? We have PositionDate in the WHERE clause of all queries and VehicleId in many of them.

3. Am I misusing partitioning for this scenario? If I define good indexes on my non-partitioned table and move oldest data (more than 2 month old) to the archive table, will that work well for my case?

DDL for my indexes:

ALTER TABLE [dbo].[MyTable] ADD  CONSTRAINT [PK_Primary] PRIMARY KEY CLUSTERED 
(
    [PositionDate] ASC,
    [Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
GO

CREATE NONCLUSTERED INDEX [NonClusteredIndex-VehicleId] ON [dbo].[MyTable]
(
    [VehicleId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
GO

CREATE NONCLUSTERED INDEX [NCIX_VehicleId_PositionDate] ON [dbo].[MyTable]
(
    [VehicleId] ASC,
    [PositionDate] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
GO

This is an example, my queries are in SPs that receive datetime type parameters.

Answer 1

You generally followed a delusion - that partitioning will give you a significant performance boost for queries compared to a standard index.

This is not the case. There is little difference between filtering with an index and with a partition.

Partitions are not there to make your queries faster, but to allow fast DELETE - by swapping out a partition with an empty version of the table. This allows "truncate" style performance for deleting parts of a table - and that is significant. Brutally significant, if you ever experience the long time it may take to delete dozens of gigabytes of data.

There are also insert scenarios where a partition can help.

But for queries - no, the partition will not be better than proper indices. In fact, it will be slower - as there the work is more complex (management of which partitions to access).

Answer 2

TomTom has a great answer with which I completely agree. sp_BlitzErik rightly cites Kendra Little as a good source for acknowledging that partitioning is not a performance feature:

Why Table Partitioning Doesn’t Speed Up Query Performance

Partitioning is, in fact, a data management feature. As noted by TomTom, bulk DELETE operations that encompass the data set within a partition are accomplished in a matter of microseconds with a simple metadata switch. Additionally, you can do some things with files and filegroups that will allow you to roll "colder" data off to slower storage and free up your faster storage for "hot" data.

Where some people are fooled into thinking that partitioning is a performance feature is that sometimes you can get partition elimination, which reduces the amount of data scanned to one, or a few partitions. However, the conditions need to be just right and you need to have a controlled query development environment in order to get partition elimination.

For example, our partitioned database struggles to get partition elimination on complex queries and with implicit conversions. Finally, on a partitioned table where partition elimination is not used, queries can actually be slower than on those that are not partitioned, especially as the number of partitions grows.

Partitioning is not something to just toss on a large table and hope it works "faster." You need to be thoughtful in how you approach all aspects of it, from your partition key, to your partition grain, to your retention strategy, to your storage strategy.

Finally, please don't just use the cost estimates. Run the queries with SET STATISTICS TIME ON ... and even SET STATISTICS IO ON to really get a sense of what it's doing.