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')
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')
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…
Here WA Stands for Washington, because SQL team is based in Washington (WA)
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.)
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…
I remember Kimberly or Paul saying something similar at SQLConnections.
The WA is because the SQL team is based in Washington State in the US
Yes, all of you are right 🙂 – thanks for responding !
Nothing new here …. Paul Randal published the information in a blog posting made on 14 August 2009. The link if you want it is http://www.sqlskills.com/BLOGS/PAUL/post/How-are-auto-created-column-statistics-names-generated.aspx By the way I am the fellow who asked Paul that question at our local user group meeting. Now please give credit where creidt is due .. namely to Mr. Paul Randal
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 !