Post reply

Filtered statistics

  • December 14, 2016 at 7:34 am

    #401210

    Hi,

    Does anyone have any links / good information about creating filtered statistics on partitioned tables? Tantalisingly, I've found references to a SQLCAT blog on the subject, but can't find the actual article 🙁

    Why I am interested - I have a few tables that have between 1billion - 20billion rows each. They're monthly partitioned and I am considering creating filtered statistics along the same line as the partitioning key. Queries generally select data from the latest partition. I am considering filtered statistics because:

    a) Concentrated sampling of the partition rather than the whole table, giving a better picture of data distribution

    b) Maintenance - I'm currently updating stats with a 1% sample on these big tables due to the time taken and i/o caused. Hopefully filtered stats will make maintenance a bit easier, for example, update filtered stats daily and regular stats less frequently. Auto update stats is off on these tables.

    It's SQL Server 2008 R2 Enterprise Edition. We only do reorg's of the partitioned indexes (aligned) as we can't afford for them to be offline, plus you can't do an online index rebuild of a single partition in our version. This of course means that stats need to be done manually, as opposed to index stats being done with an index rebuild.

    Thanks

  • December 15, 2016 at 10:04 am

    #1918241

    Anyone have any thoughts?

  • December 15, 2016 at 1:23 pm

    #1918278

    Not sure if you already looked at this article in MSDN.

    https://msdn.microsoft.com/en-us/library/ms188038.aspx

  • December 16, 2016 at 7:28 am

    #1918395

    Yes, thanks. I have seen that one...

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply