how to list index corresponding to statistics using select statement

  • ebooklub

    Hall of Fame

    Points: 3873

    Hi ,

    with query bellow I  can list DTA  statistic that is not updated after specific date

    SELECT

    DB_NAME() as 'database name',

    sc.name as 'schema',

    so.name as 'table',

    stat.name as 'name of stat',

    sp.last_updated as 'stat latest update time'

    FROM sys.stats as stat

    CROSS APPLY sys.dm_db_stats_properties (stat.object_id, stat.stats_id) AS sp

    JOIN sys.objects as so on

    stat.object_id=so.object_id

    JOIN sys.schemas as sc on

    so.schema_id=sc.schema_id

    WHERE

    stat.name like '%dta_stat%'

    and

    sp.last_updated as date <'20190918'

    How  to modify query to add column with index corresponding to stats?

    or output result of DBCC SHOW_STATISTICS into  temp table , so  it could be joined with  query above

    Thank you

     

     

     

     

     

  • Jeff Moden

    SSC Guru

    Points: 994677

    If there is an index behind the stats, then stats_id is the same thing as the index_id in sys.indexes.  Do an outer join to sys.stats from sys.indexes to make that determination.  If the stat is backed up by an index, you can get the name of the index from either sys.stats or sys.indexes.  If the stat_id does not appear in sys.indexes, then you have column stats on your hands and the name in sys.stats will be the name of the stat.

    --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.
    "If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
    "Change is inevitable... change for the better is not."
    When you put the right degree of spin on it, the number 3|8 is also a glyph that describes the nature of a DBAs job. 😉

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

  • Jeff Moden

    SSC Guru

    Points: 994677

    p.s.  You might want to check out the OBJECT_NAME(object_id [, database_id]) and OBJECT_SCHEMA_NAME(object_id [, database_id]) functions.  They can greatly reduce the number of joins you have to do for things like this IF you use them correctly.

    --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.
    "If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
    "Change is inevitable... change for the better is not."
    When you put the right degree of spin on it, the number 3|8 is also a glyph that describes the nature of a DBAs job. 😉

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

  • ebooklub

    Hall of Fame

    Points: 3873

    Thanks a lot Jeff

    I modified  query  to provide me overview of  DTA  statistic and indexes I  want/plan  to drop

    SELECT

    DB_NAME() as 'database name',

    sc.name as 'schema',

    so.name as 'table',

    isnull(si.name,'*ORPHAN STAT*') as 'index name',

    (

    SELECT

    USER_SEEKS+ USER_SCANS+ USER_LOOKUPS +USER_UPDATES as x

    FROM SYS.DM_DB_INDEX_USAGE_STATS AS S

    INNER JOIN SYS.INDEXES AS I

    ON I.[OBJECT_ID] = S.[OBJECT_ID]

    AND I.INDEX_ID = S.INDEX_ID

    WHERE OBJECTPROPERTY(S.[OBJECT_ID],'IsUserTable') = 1

    and

    I.[NAME] =si.name

    ) as 'Number of times index used since reboot',

    si.type,

    stat.stats_id,

    stat.name as 'name of stat',

    sp.last_updated as 'stat latest update time'

    FROM

    sys.indexes as si

    right join sys.stats as stat on si.index_id = stat.stats_id

    CROSS APPLY sys.dm_db_stats_properties (stat.object_id, stat.stats_id) AS sp

    JOIN sys.objects as so on

    stat.object_id=so.object_id

    JOIN sys.schemas as sc on

    so.schema_id=sc.schema_id

    WHERE

    stat.name like '%dta_stat%'

     

     

     

Viewing 4 posts - 1 through 4 (of 4 total)

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