1

We have just implemented table partition on a very large table in SQL Server 2014 , we used have filtered stats on the same table before partition not after the table partition we enabled Incremental Statistics but we are thinking to create filtered stats which would allow for a much more granular level of statistics....

At this point, i'm not sure if a partitioned table can have both Filtered stats and Incremental Stats and if we implement would it confuse the optimizer ? Also , can anyone help me on how to implement filtered stats on a partitioned table ?

Thanks in advance...

1

Incremental stats and filtered stats serve completely different purposes. Incremental stats are not used by the query optimizer. They are a feature designed to make maintenance easier for large tables. They can only indirectly help with query performance because they can make it more practical to keep statistics up to date on large tables.

Filtered statistics are used by the query optimizer (if you write your queries carefully enough). They technically add to database maintenance but can significantly improve query performance in some scenarios.

In summary, you should not have to worry about filtered stats and incremental stats confusing the query optimizer. If you need specific help adding filtered stats to a table please post the table DDL and explain what you're trying to do along with what you've already tried.

3
  • Thanks Joe. As i mentioned in my previous post, we already have filtered stats on table before partition. In the old table, filtered stats are created on DateTime column and we implemented the partition on the on the same column. Can we script out the filtered stats from old table and recreate them in new partitioned table ?
    – Robin16
    Nov 21 '16 at 16:20
  • Can anyone please help me on this ?
    – Robin16
    Nov 22 '16 at 16:23
  • @user2344267 Yes, you should be able to create those statistics on a partitioned table. If you need additional help please edit the question to add additional details. What is the relevant table DDL? What code did you try already and what error message did you get?
    – Joe Obbish
    Nov 23 '16 at 3:48

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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