Update Statistics : _WA_Sys_0000000 ... Type

Question

Post reply

Update Statistics : _WA_Sys_0000000 ... Type

river1

SSC-Insane

Points: 24309
More actions
January 17, 2018 at 7:08 am

#409965

Dear all,
I was looking at the statistics of OLA jobs related with Update Statistics command.
BElow you can find the result of them for table ETL.LoadLog:
ETL.LoadLog - _WA_Sys_00000003_4AD81681
ETL.LoadLog - _WA_Sys_00000004_4AD81681
ETL.LoadLog - _WA_Sys_00000005_4AD81681
ETL.LoadLog - _WA_Sys_00000006_4AD81681
ETL.LoadLog - _WA_Sys_00000007_4AD81681
ETL.LoadLog - _WA_Sys_00000008_4AD81681
ETL.LoadLog - IDX_FailedLoads
ETL.LoadLog - IDX_SuccessfulLoads
ETL.LoadLog - PK_ETL_LoadLog
The last 3 ones I understand. They are index related. But what about the first 6 ? They are related with what?
Thank you

Viewing 10 posts - 1 through 9 (of 9 total)

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

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 1

They're the ones auto-generated when you use a column that doesn't already have statistics on it in a query.

John

Thomas Rushton SSC-Insane Points: 22649 More actions · Answer 2

The _00000002_ etc is the column number in the table to which those statistics belong; the last eight characters are the object_id of the table (in hex).

Thomas Rushton
blog: https://thelonedba.wordpress.com

Steve Jones - SSC Editor SSC Guru Points: 736270 More actions · Answer 3

Ignore these stats. If they are useful and always created, you could drop them and recreate them with better names, but it's really not worth the effort.

Jeff Moden SSC Guru Points: 1004363 More actions · Answer 4

Steve Jones - SSC Editor - Wednesday, January 17, 2018 7:49 AM
Ignore these stats. If they are useful and always created, you could drop them and recreate them with better names, but it's really not worth the effort.

I STRONGLY DISAGREE!!!! These are "column stats" that SQL Server made because the column had no index associate with it. It could and will absolutely kill performance, especially on large tables, while SQL Server dutifully rebuilds the stats because a given query needs the stats. We have such stats on a fair number of large tables and dropping such a stat would (and has) taken up to 15 minutes to rebuild.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Steve Jones - SSC Editor SSC Guru Points: 736270 More actions · Answer 5

Steve Jones - SSC Editor

SSC Guru

Points: 736270

January 17, 2018 at 10:28 am

#1976160

Did you manually recreate them?

Jeff Moden SSC Guru Points: 1004363 More actions · Answer 6

Steve Jones - SSC Editor - Wednesday, January 17, 2018 10:28 AM
Did you manually recreate them?

Nope. The system did when someone ran a query that needed them. Usually (at least for our systems), they exist because a new production stored procedure came into being and there were no indexes to support the new proc.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

river1 SSC-Insane Points: 24309 More actions · Answer 7

Jeff Moden - Wednesday, January 17, 2018 11:44 AM
Steve Jones - SSC Editor - Wednesday, January 17, 2018 10:28 AM
Did you manually recreate them?
Nope. The system did when someone ran a query that needed them. Usually (at least for our systems), they exist because a new production stored procedure came into being and there were no indexes to support the new proc.

Thank you very much. This means that update statistics should only ( in normal situation run against indexes) and so, if we have update statistics others then again indexes, it means that we are missing index? If so, how can we discover what were the sp that generate this out of the box statistics?

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 8

No, it does not mean any of those.
Update statistics absolutely must run against column statistics. More so than against the stats associated with indexes, since they also get updated with index rebuilds.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Jeff Moden SSC Guru Points: 1004363 More actions · Answer 9

river1 - Wednesday, January 17, 2018 12:40 PM
Jeff Moden - Wednesday, January 17, 2018 11:44 AM
Steve Jones - SSC Editor - Wednesday, January 17, 2018 10:28 AM
Did you manually recreate them?
Nope. The system did when someone ran a query that needed them. Usually (at least for our systems), they exist because a new production stored procedure came into being and there were no indexes to support the new proc.
Thank you very much. This means that update statistics should only ( in normal situation run against indexes) and so, if we have update statistics others then again indexes, it means that we are missing index? If so, how can we discover what were the sp that generate this out of the box statistics?

Just to echo what Gail has already stated, the system generated statistics are just as important as the index generated statistics and must also be maintained. It's not possible to determine which query generated the system generated statistics but it IS possible to determine which system generated statistics are being used over time by using TF 866 and some long running code to examine queries available in cache. See the following link for a bit more on that.

https://www.sqlservercentral.com/Forums/1878360/Need-some-help-with-some-XML-Hell-please

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)