how to list index corresponding to statistics using select statement

Question

how to list index corresponding to statistics using select statement

ebooklub

Hall of Fame

Points: 3905
More actions
September 19, 2019 at 9:37 pm

#3681603

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

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

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

Jeff Moden SSC Guru Points: 1004340 More actions · Answer 1

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.

Change is inevitable... Change for the better is not.

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

Jeff Moden SSC Guru Points: 1004340 More actions · Answer 2

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.

Change is inevitable... Change for the better is not.

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

ebooklub Hall of Fame Points: 3905 More actions · Answer 3

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%'