Update Statistics at the partition level - by Data Realized

Status : 

 


209
0
Sign in
to vote
ID 468517 Comments
Status Active Workarounds
Type Suggestion Repros 3
Opened 6/19/2009 2:24:51 PM
Access Restriction Public

Description

I'd like to see Update Statistics modified to include partitionID in combination with the existing table or indexed view name.

With the improvements of 2k8 and partitioning, very large result sets are being seen at one of my clients that utilizes partitioning.  Having to update statistics against partitions that haven't changed in order to achieve the change on the partition that actually needs it, creates additional overhead that is not required.  It also forces a change to plans that wouldn't otherwise be altered which causes for memory churn in the proc cache.

One other related note, is that you can defrag an individual parition index, but it will not update the statistics.  

Sign in to post a comment.
Posted by Wim SQL Server on 2/19/2015 at 7:40 AM
In SQL Server 2014 u have the incremental statistics, as I understand this is statistics at the partition level:
http://sqlperformance.com/2014/02/sql-statistics/2014-incremental-statistics
https://msdn.microsoft.com/en-us/library/ms188038.aspx
Posted by Neugebauer on 8/4/2014 at 2:37 PM
Wasn't this resolved with the filtered statistics for SQL Server 2014 ?
Posted by Victor_Co on 6/13/2012 at 1:32 AM
Hello,

I have 2 points to make about this issue.

First, it's a feature that I would need as well at one of my client sites - currently running SQL 2008 R2.
We have an SSIS-based solution that loads a large volume of data daily. While loading several days worth of data in sequence we encountered a strange behaviour of SQL Server, where the upload that would generally be performed in less than 2 minutes was taking between 45 minutes and a few hours. After a fair amount of pain, we could only assume that it's the Query Optimizer that goes wrong, so we got the statistics updated after in our SSIS packages after inserting large number of records into tables. This solved the issue. However, as tables grow larger, the time for updating the statistics is continuously increasing. Performance can therefore only get worse - reducing this way the benefits of partitioning.

My second point is that I believe it's not good practice to simply pay more attention to the issues that get more votes. "Get your friends to vote on it" sounds like a lame recommendation to me. I'm sure there are enough bright people at Microsoft able to analyze the importance of an issue, even if it was raised by someone with fewer friends!

Thanks,

Victor

Posted by Boris [MSFT] on 1/31/2012 at 4:32 PM
Hello,

I have resolved this item as a duplicate of another in our internal database. This may result in notifications sent by Connect. The feedback item should remain Active after Connect picks up changes from our internal bug database.

This is just FYI.

Regards,
Boris.
Posted by Cobalt Software on 11/7/2011 at 3:04 AM
When I've got a 500gb+ table for a data warehouse that needs it's statistics updating every month as new partitions are being added and growing, this strikes me as a pretty glaring omission.

There's no reason to go changing the internals of file structures - surely it's possible, where statistics are related to a partitioning key, to be able to just update the statistics for a given value range? Then I can just update the stats with a command like:

i.e.
UPDATE STATISTICS SomeFactTableName
    WHERE DateKey BETWEEN 20111001 AND 20111031

No new file structure required, surely? It's just updating a portion of the histogram, and solves the majority of issues people have in this area. It also has the advantage of remaining storage agnostic (it's syntax that works irrespective of whether the table is physically partitioned or not).
Posted by Boris [MSFT] on 7/13/2011 at 4:34 PM
Hi Jeremy,

It is sad for me to say that this item will not make into SQL Server "Denali" RTM release. We do see this as the top customer-voted DCR item in the area of Query Processor and understand that delaying its delivery has a number of negative effects.

Overall, this is a relatively large item with a lot of consequences (such as change in statistics format, backward compatibility, consequential changes in query execution plans, performance implications of it, etc.) so it will take time before it is completed.

On this public forum I can only share information about features that are guaranteed to make a release. Given this, you will see minimal or no updates until we reach that state for this feature.

Thank you,
Boris.
Posted by Data Realized on 7/6/2011 at 9:45 AM
Hello,

Is there any chance that this will make it into a version of SQL Server?

This item now has 90 votes. A related item (328093) has 66 votes. Combined there are over 150 votes for this functionality. Additionally, if you look at who has voted for these items I'm confident that you will find them to be some of the larger SQL Server installs.

I've seen a significant amount of clients utilizing partitioning in the past year and I believe, along with 150 of my peers, that this needs to be included sooner rather than later.

The last update was 10 months ago by Boris and I'm hopeful that you can share a bit more information now.

Thank you,

Jeremy Lowell
Posted by Data Realized on 12/21/2010 at 2:03 PM
Is there any chance that this will make it into Denali?
Posted by SirMarco on 9/21/2010 at 2:38 AM
Hello Guys

do we have any update on this ?
I can't go around trying to convince my clients to get rid of Oracle
and then find myself stucked on such a issue !
I have billion row tables with 90% of data frozen and read only,
it does not make any sense that I must process 90% of the data meaningless.
Also this column within sys.sysindexes apparently does not store any meaningful information
on changes against partitioned tables; how can we have this nonsense ?!
Do I miss anything ?

From BOL
////////
Counts the total number of inserted, deleted, or updated rows since the last time statistics were updated for the table.

0 = Index is partitioned when indid > 1.

0 = Table is partitioned when indid is 0 or 1.

In SQL Server 2005 and later, rowmodctr is not fully compatible with earlier versions. For more information, see Remarks.
////////

I love MSSQL, 2008 particulary, so please let's make it even better.
Many thanks to everybody involved with MSSQL for your great efforts.

Looking forward to a solution
Thanks again

             Marco
Posted by Boris [MSFT] on 9/10/2010 at 6:02 PM
Hi Jeremy,

Writing to acknowledge that I have seen your request but unfortunately at the moment I cannot provide more info. I do get a notification every time someone posts a comment or votes on this item.

I will update it as soon as I have a definitive action on this item. Thank you for understanding!

Regards,
Boris.

p.s. I have also resolved duplicates to this item (there were reasons why the duplicates were kept active). MS Connect status is different from status of the items in our bug database and hence you sometime do not see changes as they happen on the items internally.
Posted by Data Realized on 9/6/2010 at 12:13 PM
Boris,

It has been 6 months since the last update. Can you confirm if this will be included in the next major release?

Yavor also mentioned (just over a year ago) that he would know more in 12 months.

Please advise,

Thanks,

Jeremy
Posted by Franz Robeller on 9/3/2010 at 12:31 PM
To me it loks like that this is also a duplicate of item 328093 Title: "Statistics are saved on table level and not on partition level"
Posted by Boris [MSFT] on 3/1/2010 at 5:24 PM
Hi Jeremy,

Thank you for your patience! We understand the pain this causes, esp. given customers put more and more data in partitioned tables.

This item is still active for the next major release (even if it was resolved internally as a duplicate of something else, the feedback you see gets transferred to the new defect in internal database). So no feedback/comment is lost during this process.

This item will get resolved once some action happens on it (we implement functionality, postpone it, etc.). To set expectations, this item will be staying active for another few months before you see any action on it. If you feel this item is not receiving attention it deserves, have your friends vote on it. This helps us define priority. We tend to proceed with items with high vote count first.

Functionality you are asking for _usually_ is not put into service packs but in major releases and hence delay in action for this item. This item was not planned for SQL Server 2008 R2.

Hope that clarifies situation a bit.

Thank you and best regards,
Boris Baryshnikov.
SQL Server Engine
Posted by Data Realized on 3/1/2010 at 3:27 PM
It's been about 8 months... any update on this? Also, it used to show up as being resolved as it was a dupliacte, but I don't see that status on it now. If it is a duplicate can you please supply this post, via a comment, on how I can find what it's a duplicate of so that I can track it?

It's still an issue... Now it's an issue for two of my clients, not just one.

Thank you,

Jeremy Lowell
Posted by Data Realized on 7/23/2009 at 8:10 PM
Can you please tell me which DCR this is a duplicate of? I'd like to keep tabs on resolution to this issues. As you can imagine, it's becoming a more critical need as more and more clients see the value of the 2008 and 2008 R2 releases.
Posted by Microsoft on 7/7/2009 at 4:27 PM
Dear Customer,

Thank you for taking the time and raising the issue of per-partition statitics. It is currently on list of customer pain points and we hope to address it for SQL11, but we haven't committed to do it yet. I will know more in 12 months. We are resolving this DCR as "duplicate".


Thanks,
Yavor Angelov
SQL Engine - Program Management