DBA question: "sticky" statistic?

[Ganimede-Dignan]

Hi,

what is it a "sticky" statistic? In DBACOCKPIT I see several message as:

Column SETNAME is sticky on histogram steps, partial hashing

What can I do to solve it?

PS: of course, Number of Tables with Incomplete Index Statistics = 0

Thank you.

[former_member188958 · Accepted solution]

See sp_modifystats

[Ganimede-Dignan · Accepted solution]

Such as...

use PRD

sp_modifystats "PRD.SAPSR3.ALCLASTOOL", SETNAME, REMOVE_STICKINESS

is it correct?

Thank you.

[former_member182259 · Accepted solution]

Careful.   The concept of "stickiness" is not necessarily a problem that needs solved.   What "sticky" means from index statistics is that certain statistics attributes are retained vs. the defaults when update statistics is run without any attribute values specified.    For example, 'histogram steps' - not sure what your values are - but let's say they are 100 (a good value for SAP applications) for a table such as COVP.   If I then run:

update index statistics "SAPSR3"."COVP"

I retain the 100 value for histogram steps even though the default for the server likely is 20.   However, if you remove the stickiness, then run the above command, you will reset the number of histogram steps to 20 - which could lead to query issues with large IN() clauses, etc.   Another way to reset/override the value is to do something like:

update index statistics "SAPSR3"."COVP" using 50 values

....which then sets the 'histogram steps' for all column histograms to use 50 values instead of the previous 100 (or whatever).

Point I am making is that you would not normally remove 'stickiness' from index statistics unless you have a VERY good reason and you fully understand what it is that you are changing and how it will impact query optimization.