/scripts/ahub.form.attachments.js
0

What does sp_configure "update statistics hashing" do? (ASE 16)

May 04, 2017 at 02:58 PM

203

avatar image

For ASE 16, I don't have this config option set, but I can still run update statistics with a explicit "with hashing" clause.

The docs for this just say "enables SAP ASE to gather hash-based statistics"

10 |10000 characters needed characters left characters exceeded
* Please Login or Register to Answer, Follow or Comment.

2 Answers

Best Answer
Mark A Parsons May 04, 2017 at 04:04 PM
2

Documentation needs some work ...

By default ASE does not use hashing when running update (index) statistics.

update statistics hashing allows the DBA to decide if hashing is used by default for all update (index) statistics commands that do not explicitly state a hashing option.

You can see this behavior by running a series of tests with different config settings and update (index) statistics commands, and searching optdiag output for indications of hashing use.

NOTE: Make sure you run delete statistics before each test to insure you're starting with a clean slate.

NOTE: Not sure if there's another way to verify use of hashing other than watching for tempdb usage for non-hashed operations.

NOTE: Explicitly providing a hashing option for update (index) statistics also sets some sticky attributes for column stats ... this doesn't seem to be the case (in my testing) when relying on the server-wide config setting; not sure if this is by design or it was overlooked.

Show 2 Share
10 |10000 characters needed characters left characters exceeded

Ok, got it. The "update statistics hashing" controls the default behavior. It would be nice if the docs actually said that...

Re: You can see this behavior by running a series of tests with different config settings and update (index) statistics commands, and searching optdiag output for indications of hashing use.

When using the "update index statistics" or "update all statistics" command with the "print_progress=1" option, you can see hashing based stats updates:

[110] MYSERVER.mydb-11:27:24-1> update index statistics mytable with hashing, print_progress=1;
Update Statistics STARTED.
Update Statistics index scan started on index 'myindex'.
...It is using existing scan to hash column 'year' (column id = 2).
...It is using existing scan to hash column 'volume' (column id = 3).
...It is using existing scan to hash column 'issue' (column id = 4).
...It is using existing scan to hash column 'month' (column id = 5).
Update Statistics table scan started on table 'mytable' for summary statistics.
Update Statistics FINISHED.

Note that if you use the "update index statistics <table> with partial_hashing, sampling=10 percent" (for example), it will use hashing for low ordinality columns (low number of distinct values) and sampling with sorting for high ordinality columns.

0

Cool Ben

Noted and will try to use the ""print_progress=1" option" for our index stats runs.

Thanks for sharing the tip.

Avinash

0
Avinash Kothare May 04, 2017 at 03:15 PM
0

Hi Ben

Is it a case of syntax you used is valid and therefore allowed to run

but the actual gathering based on hashing is silently ignored ?

It will be interesting if we have inputs from SAP/Sybase

on checks/tests to run before and after the update stats with hashing option to verify that it did what it claimed to do.

Avinash

Show 1 Share
10 |10000 characters needed characters left characters exceeded

Re: Is it a case of syntax you used is valid and therefore allowed to run but the actual gathering based on hashing is silently ignored ?

No, as discussed above, the "update statistics hashing" config parameter just controls the default behavior (of whether to use hashing for update stats commands). If you explicitly specify a "with hashing" option, it is used.

0
Skip to Content