Incorrect Plans on Partitioned Tables

Question

Hi All,

I am new to the company and here we have several tables being partitioned via a creation_date column on a weekly basis.  The tables are relatively narrow being less than 20 columns mostly integer data types. 

When I do a simple query on the tables to pull back rows between lets say the last two weeks based on both a creation_date column (which the partition is based on) and an updated_date column (which has a nonclustered index) I notice that the estimated number of rows is 1 and the actual number of rows is 565501. These statistics are causing the plan to use an Index Seek instead of scan, a Key Lookup and then combine the with a Nested Loops operator instead of a hash match.

I have done a decent amount of optimization in the past, just nothing dealing with Partitioned Tables.  I manually update the statistics on the tables which took a long time but yielded no benefits.

Should we be doing index partitioning as well or are there additional maintenance tasks which we should be performing.  Any help would be greatly appreciated.

 

Thank you in advance,

Paul

Answer 1

What version of SQLServer you are using? Also, its not clear on where / what kind of indexes you are using. It will help if you can share all details like: table spec, partition spec, index spec, and any maintenance plan on top of them.

Thanks.

---

Vikas S. Rajput