1

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.

2 Answers 2

4

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.

6
  • I supposed partitioned tables should have less value in Reads column while viewing statistics in SQL Server Profiler. What do you mean by useful index on DateColumn?
    – AA.SC
    Apr 18, 2016 at 12:23
  • What I mean by useful index here is that SQL Server will use the index for a seek predicate in the execution plan. In the case of a non-clustered index, the optimizer may choose to scan the table instead of use the index depending on the estimated qualifying rows.
    – Dan Guzman
    Apr 18, 2016 at 12:37
  • 1
    I think "AA, SC" might be trying to use the function of partition elimination to reduce searches. Paul White has several good articles on this: sqlblog.com/blogs/paul_white/archive/2012/09/12/…
    – Arthur D
    Apr 18, 2016 at 13:42
  • 1
    Actually 90% of our queries hit 6 months data and never go for cold data; I thought here partition comes. Now I am checking how partition facilitate queries ? do I need to create all ( Nonclustered ) indexes on same partition scheme which contain DateColumn? because my major query always hit a index which is Nonclustered contain 2 key columns and 6 include columns.
    – AA.SC
    Apr 18, 2016 at 14:45
  • @AA.SC, is the index you mention partitioned? I assume the partitioning column is specified in the WHERE clause?
    – Dan Guzman
    Apr 19, 2016 at 12:40
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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.