Decoded Statistics Names and More

Every now and again I am asked about the meaning behind the automatically generated names for statistics in SQL Server. The quick answer is short, sweet and really easy. I give them a quick explanation and then often refer them to the blog post by Paul Randal on the topic.

The better answer is to show them what the auto-generated names really mean, alongside the great explanation from Paul. Finally, after years of the topic being on my backlog, I am sharing a script that will help decode those names and help to prove out fully what’s in a statistic name.

Fun with Stats

First let’s look at an example for an auto-generated statistic name.

_WA_Sys_0000000A_0000001B

It is important to understand the naming convention used here and that there are four distinct parts to the name. Each part is separated by an underscore (_). The leading underscore is unnecessary, so just ignore it. Here is a quick breakdown of the four parts

  1. WA – as Paul explained, this is directly related to where the SQL team is based – WAshington state.
  2. Sys – also as Paul explained, this just means that the stat was auto-created. We will be able to see more of that later in the script results.
  3. 0000000A – This value is a Hex value converted to a string and represents the Column ID for the statistic.
  4. 0000001B – This value is a Hex value converted to a string and represents the Object ID for the object upon which the statistic was created.

Given that parts 3 and 4 of the auto-generated name are Hex strings, we have to do a little trickery in order to convert them to the exact hexadecimal format so we can then properly convert the values to their integer counterparts. Once getting those conversions performed, the rest of the script is fairly easy. Let’s take a look.

As you can see, I am parsing the name into the various parts using the PARSENAME function. After splitting the parts out, I use some conversion techniques to try and get the hexadecimal representation (using VARBINARY). In the result set, I have included the object_id from the sys.stats view as well as the column_id from the sys.stats_columns view to demonstrate that the conversion is accurate. Here is an example result set. Also of note, I have filtered the results strictly down to those stats that have been auto created.

Looking at the results, the columns with “Parsed” in the name are those that have been decoded direct from the stats name, while the rest have come from the various views. Shown here, I have the ColumnID from the two sources in red, and the ObjectID in blue to help correlate the values.

That is great, now let’s step it up a notch to see just a few more results.

This extended query will give us similar output to the following.

If you are looking closely at the code, you may be wondering why I have near identical versions of the query connected via a UNION statement. Well, if the stat is not an auto generated stat, then the parsing statements will error, and I would like to be able to retrieve extended information regarding all statistics so I can better understand what is going on as a whole with my statistics.

Wrapping it Up

Auto-generated statistics names can seem like they are entirely random, but there is a method to the madness. With a little effort and a bit of TSQL trickery, we can decode those names and reveal what the names really mean.

This article helps to decode those names and as a bonus, I show you how to pull additional pertinent information about your statistics to help you on your path to being an expetional data professional.

Interested in learning about some deep technical information instead? Check these out!

Want to learn more about your indexes? Try this index maintenance article or this index size article.

No Comments - Leave a comment

Leave a comment

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

This site uses Akismet to reduce spam. Learn how your comment data is processed.



Calendar
April 2020
M T W T F S S
« Mar   May »
 12345
6789101112
13141516171819
20212223242526
27282930  

Welcome , today is Sunday, May 31, 2020