SQL objects that use an index

Question

Is there a way to see what SQL objects from one data base use a certain index. But I do not want to have to see the execution plan for every object because I have a lot of stored procedures and views

PS. It's for SQL 2005

Answer 1

Database objects don't use an index, the queries do.

A table or a view can have an index defined on it. You can see it in the properties tree in the Management Studio.

The index may or may not later be used by a query against this table or view.

A decision of whether to use the index or not is made during the query parsing phase.

Seeing execution plan is the only way to determine whether the query uses the index or not.

Answer 2

you can query the sys.indexes table which:

Contains a row per index or heap of a tabular object, such as a table, view, or table-valued function.

Answer 3

no, you can't...but you can see if indexes are being used by any query

Here is such a query that will give you that info

SELECT
TableName = OBJECT_NAME(s.[OBJECT_ID]),
IndexName = i.name,
s.last_user_seek,
s.user_seeks,
CASE s.user_seeks WHEN 0 THEN 0
ELSE s.user_seeks*1.0 /(s.user_scans + s.user_seeks) * 100.0 END AS SeekPercentage,
s.last_user_scan,
s.user_scans,
CASE s.user_scans WHEN 0 THEN 0
ELSE s.user_scans*1.0 /(s.user_scans + s.user_seeks) * 100.0 END AS ScanPercentage,
s.last_user_lookup,
s.user_lookups,
s.last_user_update,
s.user_updates,
s.last_system_seek,
s.last_system_scan,
s.last_system_lookup,
s.last_system_update,*
FROM
sys.dm_db_index_usage_stats s
INNER JOIN
sys.indexes i
ON
s.[OBJECT_ID] = i.[OBJECT_ID]
AND s.index_id = i.index_id
WHERE
s.database_id = DB_ID()
AND OBJECTPROPERTY(s.[OBJECT_ID], 'IsMsShipped') = 0
AND OBJECT_NAME(s.[OBJECT_ID]) = 'TestIndex';

some more info here Use the sys.dm db index usage stats dmv to check if indexes are being used

your other option would be to parse the query_plan column from the query below

SELECT q.TEXT,cp.usecounts,cp.objtype,p.*,
q.*,
cp.plan_handle
FROM
sys.dm_exec_cached_plans cp
cross apply sys.dm_exec_query_plan(cp.plan_handle) p
cross apply sys.dm_exec_sql_text(cp.plan_handle) AS q
WHERE
cp.cacheobjtype = 'Compiled Plan' 
and q.TEXT  not like '%sys.dm_exec_cached_plans %'

Answer 4

I just wanted to add a comment but it seems everything script driven is broken today in my view of SO and I must add a full answer instead...

In addition to the sys.dm_db_index_usage_stats that shows how indexes are actually used, SQL Server also keeps track of the missing indexes and offers DMVs that show what indexes would had been used by queries, had they been present. See http://msdn.microsoft.com/en-us/library/ms345405.aspx (Using Missing Index Information to Write CREATE INDEX Statements).

You should also download the SQL Server 2005 Performance Dashboard Reports from http://www.microsoft.com/downloads/details.aspx?FamilyId=1d3a4a0d-7e0c-4730-8204-e419218c1efc&displaylang=en that make navigating all this information (used indexes, missing indexes etc) a walk in the park.