SQL Server – Auto Statistics – Naming Convention ( Day 2 )

Background

Quick follow up to our last post.

In this post, we track back and see if we can decipher the roots of the stats name.

Lineage

  1. SQL Server – Auto Created Stats – Naming Convention
    Link

 

SSMS

Here are the stats shown us via SQL Server Management Studio.

stats.raw.bible.kjv.20190302.0626AM

Goal

Let us relate the data to actual objects and columns.

 

Outline

  1. isAutoGenerated
    • Auto Generated Statistics have names starting with
      • _WA_Sys_
  2. statAsPeriodDelimeted
    • Replace underscore ( _ ) with period ( . )
    • Doing so allows us to use the parsename function
  3. objectIDToken
    • read statAsPeriodDelimeter and get first Octet from right
  4. columnIDToken
    • read statAsPeriodDelimeter and get second Octet from right
  5. objectIDAsHex
    • ObjectIDToken is an obvious number, but it is missing the 0x
    • Let us prefix it with 0x
  6. objectIDAsBinary
    • Convert ObjectIDAsHex to Binary’
    • Use convert/int
  7. objectIDAsInt
    • Convert objectIDAsBinary to objectIDAsInt
    • Use convert/int
  8. object Name
    • Get schema :- object_schema_name
    • Get object :- object_name
  9. Column Name
    • Join sys.objects to sys.columns
    • Map object_id and column_id

Code

SQL


set nocount on
go

use [bible]
go

declare @object sysname

set @object = '[dbo].[kjv]'

declare @tblStat table
(
      [id] tinyint not null
        identity(1,1)

    , [stat] sysname not null
    , [statKey] sysname not null

    , [isAutoGenerated]
        as  cast
            (
                case
                    when ( ltrim([stat]) like '_WA_Sys_%') then 1
                    else 0
                end
                as bit
            )

    , [statAsPeriodDelimited] sysname null

    , [objectIDToken]         varchar(20) null
    , [columnIDToken]	      varchar(8) null

    , [objectIDAsHex]		  varchar(30)
    , [objectIDAsBinary]	  varbinary(8)
    , [objectIDAsInt]         int null
    , [objectName]            varchar(512) null

    , [columnIDAsInt]		  int null
    , [columnName]            sysname null

)

insert into  @tblStat
(
      [stat]
    , [statKey]
)
exec sp_helpstats
           @objname =  @object
 --        , @results = 'ALL'

/*
    a) To allow us to use parsename as tokenizer, let us replace _ with period(.)
*/
update tblS

set    tblS.[statAsPeriodDelimited]
            = replace
              (
                  substring
                   (
                       [stat]
                     , 2
                     , 255
                    )
                  , '_'
                  , '.'
              )

from   @tblStat tblS

where  [isAutoGenerated] = 1

/*
    a) Get objectIDAsHex as first octet from right
    b) Get columnIDAsVarchar as second octet from right
*/
update tblS

set    tblS.[objectIDToken]
            = parseName
              (
                  [statAsPeriodDelimited]
                  , 1
              )

        , tblS.[columnIDToken]
            = parseName
              (
                  [statAsPeriodDelimited]
                  , 2
              )

from   @tblStat tblS

where  [isAutoGenerated] = 1

/*
    Get objectIDAsBinary
        a) Prefix ObjectIDAshex with 0x, this makes it Hex
*/
update tblS

set    tblS.[objectIDAsHex]
            =  '0x' + tblS.[objectIDToken]

from   @tblStat tblS

where  [isAutoGenerated] = 1

/*
    Get objectIDAsBinary
        a) Prefix ObjectIDAshex with 0x, this makes it Hex
*/
update tblS

set    tblS.[objectIDAsBinary]
            =  convert
                (
                      varbinary
                    , tblS.[objectIDAsHex]
                    , 1
                )

from   @tblStat tblS

where  [isAutoGenerated] = 1

/*
    Get ObjectIDAsInt
        a) Convert objectIDAsHex to Int
*/
update tblS

set    tblS.[objectIDAsInt]
            =  convert
                    (
                          int
                        , tblS.[objectIDAsBinary]
                    )
from   @tblStat tblS

/*
    Get Column ID
*/
update tblS

set    tblS.[columnIDAsInt]
            = tblS.columnIDToken

from   @tblStat tblS

where  ISNUMERIC(tblS.[columnIDToken]) = 1

/*
    Get Object Name
*/
update tblS

set tblS.[objectName]
            = object_schema_name(tblS.[objectIDAsInt])
                + '.'
                + object_name(tblS.[objectIDAsInt])

from   @tblStat tblS

where (

         ( tblS.[objectIDAsInt] is not null )

      )

/*
    Get Column Name
*/
update tblS

set tblS.[columnName] = tblSC.[name]

from   @tblStat tblS

inner join sys.columns tblSC

        on  tblS.[objectIDAsInt] = tblSC.[object_id]
        and tblS.[columnIDAsInt] = tblSC.[column_id]

select *

from   @tblStat

 

Output

stats.interpreted.bible.kjv.20190302.0621AM

Summary

The data shown via SSMS is not so opaque after all.

For instance :- _WA_Sys_00000002_3D5E1FD2

  1. _WA
  2. _Sys
  3. _00000002
    • Column ID 2
  4. _3D5E1FD2
    • Object ID in Hex
    • Convert Hex to Int to determine actual number

 

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s