Index Analysis - Unused Include Columns

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

SQL Server Administration (2008)

Index Analysis - Unused Include Columns

Author

Topic

Bustaz Kool
Master Smack Fu Yak Hacker

1834 Posts

Posted - 2014-05-13 : 13:11:31

I am looking for a script or information on how to determine which include columns within an index are not being used. I can find which indexes are not being used using the DMVs. Some of the indexes that I have been given contain a large number of include columns and I'd like to prune them if possible.
Also, I'm trying to find the code that uses a specific index. All of our code is in stored procedures so I'm running this script:[CODE]declare @index sysname = N'MyIndex';
select *,
substring(
qp.query_plan,
charindex(
'CREATE PROCEDURE',
qp.query_plan
) + 17,
charindex(
'#',
qp.query_plan,
charindex(
'CREATE PROCEDURE',
qp.query_plan
) + 17
) -
(charindex(
'CREATE PROCEDURE',
qp.query_plan
) + 17)
) procName
from
sys.dm_exec_procedure_stats s
outer apply
sys.dm_exec_text_query_plan(s.plan_handle, default, default) qp
where
1=1
and s.database_ID = db_id()
and qp.query_plan like '%' + @index + '%'[/CODE]It isn't returning the results that I expect. Sometimes it yields nothing. Other times it returns a procedure that doesn't seem to reference the index. All suggestions are welcome.

===============================================================================
“Opportunity is missed by most people because it is dressed in overalls and looks like work.” - T.A.Edison

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2014-05-13 : 17:55:20

I tweeted your question out to the SQL Community (#sqlhelp) as I knew the people that watch that hashtag would be the best to ask.

Robert Davis (@SQLSoldier) replied:

quote:

There is nothing that tracks index usage to that degree. You'd have to parse the query plans using the index.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

Bustaz Kool
Master Smack Fu Yak Hacker

1834 Posts

Posted - 2014-05-14 : 17:27:23

Thanx for your efforts...

===============================================================================
“Opportunity is missed by most people because it is dressed in overalls and looks like work.” - T.A.Edison

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources