Validation of Partitioned table Performance

Question

We are testing partitioning on a table which contains a datetime column (DateColumn).

Partition function I am using is:

-- 138623  +  16774 records
CREATE PARTITION FUNCTION pf_TableName_DateColumn (datetime)
AS RANGE LEFT 
FOR VALUES ('20160101', '99991231');

1. How do I verify that partitioning the table is improving query performance?
2. There is one Clustered index on PARTITION SCHEME, do remaining non clustered indexes get statistics of partition?

I am testing it by running the same stored procedure with date parameters on two databases (real and replica). One contains a partitioned table (two partitions) whereas the other is simple. Profiler doesn't show any difference in CPU, Duration, Rows, or Reads.

Answer 1

Why would you expect performance to be different with partitioning? Partitioning improves manageability but performance depends greatly on the workload and queries. For example, if you have a useful index on the DateColumn, you will likely get the same performance with and without partitioning. With no index on the column, performance may be better with partitioning as long as only the needed partitions are scanned due to elimination. But if an index can prevent the scan, that would provide the greatest performance benefit.

Answer 2

So, to test query performance for SELECT statements you'll want to leverage statistics and look at the query plan. Your statistics (using SET STATISTICS IO ON, and SET STATISTICS TIME ON) will show you the number of logical, physical, lob reads and the CPU and elapsed times of the query.

When you compare partitioned versus non-partitioned results look for fewer reads in your statistics, and look for partition elimination in the query plan. You can verify partition elimination by looking (presumably) at your clustered index seek and see that the optimizer has narrowed the partition search to the partition that contains the date (or your partition column). See 'Actual Partition Count' and 'Actual Partitions Accessed' for this information.

When you're comparing your CPU and elapsed times though, make sure you're making a fair comparison by looking to see that the physical and logical reads are (for lack of a better word at the moment) symmetrical. Eliminating physical reads on both queries is an example of that.