SQL Server 2014 - Optimizer does not use partitions on nonclustered index -


i'm using sql server 2014, have huge table of 120 columns , more 30 million rows. clustered index on column of type date.

i created partitioned function:

create partition function [20yearsdate](date)  range right values (n'2015-07-07t00:00:00.000', n'2015-08-07t00:00:00.000', n'2015-09-07t00:00:00.000', n'2015-10-07t00:00:00.000', n'2015-11-07t00:00:00.000', n'2015-12-07t00:00:00.000', n'2016-01-07t00:00:00.000', n'2016-02-07t00:00:00.000', n'2016-03-07t00:00:00.000', n'2016-04-07t00:00:00.000', n'2016-05-07t00:00:00.000', n'2016-06-07t00:00:00.000'........etc............ 

(its partition per month)

now, when i'm running query where statement on date column (the last 3 month), uses nonclustered index partitioned on partition scheme:20yearsdate, on column: date.

but problem optimizer uses 241 partitions!

why doesn't use 3 or 4 partitions if statement on last 3 months?


Comments