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...

Improve this question

1 Answer 1

Reset to default
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.

Improve this answer
2
  • 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
    Commented Nov 21, 2016 at 16:20
  • @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
    Commented Nov 23, 2016 at 3:48

Your Answer

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

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