SQL Server – Statistics object name _WA_Sys_00000003_1EF99443. Let us decode

Hi Friends,

A light blog for the weekend ! Many of you know that statistics get created automatically provided AUTO_CREATE_STATISTICS is turned on. But have you observed the names of these statistics object that get created automatically? They seem so weird? Names like _WA_Sys_00000003_1EF99443. Are they really weird?

What does 00000003 mean? What does 1EF99443 mean?

Let’s take a simple example:

Create a new table from an existing table.

USE AdventureWorks2008R2
GO

SELECT * INTO PERSON.EmailAddress2
FROM Person.EmailAddress

If you run the following code, you will observe that there is no statistics for this new table.

SELECT * FROM sys.stats
WHERE object_id = object_id('PERSON.EmailAddress2')

Now, let us extract data from this table with a  filter.

SELECT * FROM Person.EmailAddress2
WHERE EmailAddress = N'ken0@adventure-works.com'

And run the stats code again to observe that a new statistics object has been created on EmailAddres column:

SELECT * FROM sys.stats
WHERE object_id = object_id('PERSON.EmailAddress2')

1_SQL_Server_Statistics_object_name _WA_Sys_00000003_1EF99443_Let_us_decode

So what is the logic behind _WA_Sys_00000003_1FEDB87C?

Here it is:

00000003 is the column id of the column on which this statistics is based on. Let us verify with sys.columns.

select * from sys.columns
WHERE object_id = object_id('PERSON.EmailAddress2')

2_SQL_Server_Statistics_object_name _WA_Sys_00000003_1EF99443_Let_us_decode

And 1FEDB87C is the hexadecimal value of the object id. The object id as you can see in the above image is 535672956. The hex of 535672956 is 1FEDB87C. You can verify that using any calculator 🙂

Sys stands for system, I guess.

So, what does WA stand for? 🙂 Comments welcome…

 

 

Amit Bansal

About Amit Bansal

Amit Bansal is always brainstorming around SQL Server. Despite working with SQL since 1997, he is amazed that he keeps learning new things every single day. SQL Server is AB's first love, and his wife does not mind that. He tries to share as much and spreads the SQL goodness. Internals and Performance Tuning excites him, and also gives him sleepless nights at times, simply because he is not a genius, but quite a hard worker and does not give up. It has been a long and exciting journey since 1997, you can read here: http://sqlmaestros.com/amit-bansal/ He is on Twitter: https://www.twitter.com/A_Bansal

View all posts by Amit Bansal →

8 Comments on “SQL Server – Statistics object name _WA_Sys_00000003_1EF99443. Let us decode”

  1. That may sound funny but WA really stands for State of Washington where Microsoft developers team developed this auto create stats. (I believe Paul Randal had mentioned this in one of his blogs, I cant seem to find the exact blog right now.)

  2. Yes both Prince and Prashant are right, WA stands for Washington, I attended an immersion event with Paul in Aug 2010 and he told how WA sneaked into name for system generated stats…

  3. Hi bitbucket,

    “Nothing new here” – There might not be anything new for you in this post; but unfortunately neither do I write only for you, nor are you the only reader of my blog 🙂 – so the content might be new for many others. Thanks for the URL of Paul’s blog and thanks for letting us know that you asked this question to Paul; I hope it doesnt mean that you have a copyright on this question 🙂 –

    “Now please give credit where is due.. namely to Mr. Paul Randal” – Credit goes to Paul for many of my learnings like many other SQL pros who learn from this blog and I greatly appreciate his knowledge and expertise on the subject. But, my source of learning for this piece of information was Ben’s book on optimizer. I had not known about this post from Paul until I clicked the link you provided.

    Hope all your concerns are addressed ! Thanks for reading and thanks for commenting !

Leave a Reply

Your email address will not be published. Required fields are marked *