Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Find all queries which use a perticular index.


Find all queries which use a perticular index.

Author
Message
bhushanvinay
bhushanvinay
Old Hand
Old Hand (339 reputation)Old Hand (339 reputation)Old Hand (339 reputation)Old Hand (339 reputation)Old Hand (339 reputation)Old Hand (339 reputation)Old Hand (339 reputation)Old Hand (339 reputation)

Group: General Forum Members
Points: 339 Visits: 196
If i want to delete my IX_Unused_Index

but before that i want to identify which queries will be affected if i do drop this index.

how do i go about this.

Tried the follwoing, but no luck, i belive i am not the first person to go through this issue, i hope somebody has already got a script which does this.


select object_name(objectid),
query_plan.query('count[//@Index=''[IX_Unused_Index]'']') as result,
query_plan
from sys.dm_exec_cached_plans pl
cross apply sys.dm_exec_query_plan(pl.plan_handle)
where dbid = 14

but looking at the

select *
from sys.syscacheobjects co
where dbid = 14

gives me all prepared and compiled plans but does not give the plan itself.
may be i am getting confused here with syscacheobjects and dm_exec_query_plan function.

please help.

Kind Regards
Vinay

Regards
Vinay
John Mitchell-245523
John Mitchell-245523
SSCrazy Eights
SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)

Group: General Forum Members
Points: 8846 Visits: 15471
Vinay

If your index lives up to its name and is indeed unused, then no queries will be affected if you drop it. Therefore it is sufficient to use the dm_db_index_usage_stats DMV over a period of time to verify that the index is never used, before you drop it.

John
bhushanvinay
bhushanvinay
Old Hand
Old Hand (339 reputation)Old Hand (339 reputation)Old Hand (339 reputation)Old Hand (339 reputation)Old Hand (339 reputation)Old Hand (339 reputation)Old Hand (339 reputation)Old Hand (339 reputation)

Group: General Forum Members
Points: 339 Visits: 196
Thank you John :-D

That was indeed not what i expected, the idea is that its an index which i think is not used in the query stats only some amount of seeks are there, but wnated to make sure the amount of seeks it does will not impact the major queries and there are 100's of procs in the db.

i dont want some odd process to start slowing down and not hit my SLA's.

Just wanted to know if the index in question is beeing used in any query plans.

Kind Regards
Vinay



John Mitchell-245523 (1/4/2011)
Vinay

If your index lives up to its name and is indeed unused, then no queries will be affected if you drop it. Therefore it is sufficient to use the dm_db_index_usage_stats DMV over a period of time to verify that the index is never used, before you drop it.

John


Regards
Vinay
John Mitchell-245523
John Mitchell-245523
SSCrazy Eights
SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)

Group: General Forum Members
Points: 8846 Visits: 15471
Vinay

What do you mean by the query stats? I would recommend that you monitor dm_db_index_usage_stats over a period of time. That way you'll find out how often the index is used, and get an idea of whether the regularity of its use justifies the expense of maintaining it.

John
bhushanvinay
bhushanvinay
Old Hand
Old Hand (339 reputation)Old Hand (339 reputation)Old Hand (339 reputation)Old Hand (339 reputation)Old Hand (339 reputation)Old Hand (339 reputation)Old Hand (339 reputation)Old Hand (339 reputation)

Group: General Forum Members
Points: 339 Visits: 196
Thank you for the reply,

Ok let me kind of get this right,

Used the index usage dmv to figure out what indexes are not used very often.

found few indexes which run very infrequently used but they are seeks,

The amount of seeks will not help becuse the query may run once a week or a end of day process.

so i wanted to know if drop an index which i have identified by the query stats. then what queries will be affected when i drop this perticular index.

just to make sure the infrequent queries dont impact the SLA's which are setup for the EOD or EOM processes.

To Re-iterate
the steps i followed

1) use Sys.DM_DB_INDEX_USAGE_STATS


SELECT
OBJECTNAME = OBJECT_NAME(I.OBJECT_ID),
INDEXNAME = I.NAME,
I.INDEX_ID,
S.*
FROM
SYS.INDEXES I
INNER JOIN SYS.OBJECTS O
ON I.OBJECT_ID = O.OBJECT_ID
INNER JOIN SYS.DM_DB_INDEX_USAGE_STATS S
ON S.OBJECT_ID = I.OBJECT_ID
AND I.INDEX_ID = S.INDEX_ID
AND DATABASE_ID = @dbid
WHERE
OBJECTPROPERTY(O.OBJECT_ID,'IsUserTable') = 1
AND I.NAME IS NOT NULL and
OBJECT_NAME(I.OBJECT_ID) not like 'sys%'
AND I.NAME not like 'PK%' -- Ignore Pk's
and I.NAME not like 'UC%' -- Ignore unique constraints.
ORDER BY (s.user_seeks+s.user_scans+s.user_lookups+s.user_updates) asc





2) Use the Indexes which are not frequently used. Do an impact analysys.



select object_name(objectid),
query_plan.query('count[//@Index=''[IDX_SettledMonthToDate]'']') as result,
query_plan
from sys.dm_exec_cached_plans pl
cross apply sys.dm_exec_query_plan(pl.plan_handle)
where dbid = 14



This code is not returning all the query plans used by sql server. and unable to see what processes/procedures/AdhocSql are using this perticular index

Could you please help me with a query so that i can isolate the sql text which is going to be using this perticular index or may be the procedure name which will be impacted.

This is what i want to know. plese help.

Regards
Vinay
GilaMonster
GilaMonster
SSC Guru
SSC Guru (54K reputation)SSC Guru (54K reputation)SSC Guru (54K reputation)SSC Guru (54K reputation)SSC Guru (54K reputation)SSC Guru (54K reputation)SSC Guru (54K reputation)SSC Guru (54K reputation)

Group: General Forum Members
Points: 54049 Visits: 44625
The only way you can do that is to query the plan cache, read through the xml of the query plan and see if the index is there. If there are no plans that have reference to that index, it just means that there's no such plans in cache, nothing more.

I know there have been blog posts written on this, hit google and see what turns up.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass


stephen.kratowicz
stephen.kratowicz
Grasshopper
Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)

Group: General Forum Members
Points: 14 Visits: 114
I know this thread has been quiet for a while, but I came across it while trying to do a similar thing myself. If sys.dm_db_index_usage_stats tells you that an index you want to drop is being used, how do you find what is using it?

Vinay, your logic is sound, it's just that your XPath query wasn't quite right. I settled on the following query to get me info on what plans use my index. It differentiates between Seeks, Scans, and Updates (the latter of which shouldn't prevent you from dropping your index).

Of course, as GilaMonster indicates, this only tells you about plans in the cache. There could be a rarely-used proc not in the cache still lurking out there that wouldn't show-up on this query.

Here is the SQL... just replace 'MyDatabase' and 'MyIndex' with your own values. This works on SQL 2008 (v10.0.5768.0). I haven't tested it on other versions.


WITH XMLNAMESPACES (DEFAULT 'http://schemas.microsoft.com/sqlserver/2004/07/showplan')
SELECT
DB_NAME(E.dbid) AS [DBName],
object_name(E.objectid, dbid) AS [ObjectName],
P.cacheobjtype AS [CacheObjType],
P.objtype AS [ObjType],
E.query_plan.query('count(//RelOp[@LogicalOp = ''Index Scan'' or @LogicalOp = ''Clustered Index Scan'']/*/Object[@Index=''[MyIndex]''])') AS [ScanCount],
E.query_plan.query('count(//RelOp[@LogicalOp = ''Index Seek'' or @LogicalOp = ''Clustered Index Seek'']/*/Object[@Index=''[MyIndex]''])') AS [SeekCount],
E.query_plan.query('count(//Update/Object[@Index=''[MyIndex]''])') AS [UpdateCount],
P.refcounts AS [RefCounts],
P.usecounts AS [UseCounts],
E.query_plan AS [QueryPlan]
FROM sys.dm_exec_cached_plans P
CROSS APPLY sys.dm_exec_query_plan(P.plan_handle) E
WHERE
E.dbid = DB_ID('MyDatabase') AND
E.query_plan.exist('//*[@Index=''[MyIndex]'']') = 1


george sibbald
george sibbald
SSCertifiable
SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)

Group: General Forum Members
Points: 6992 Visits: 13687
also aware this is an old post but it came top in a google search, so check this out as well

http://www.sqlskills.com/blogs/jonathan/finding-what-queries-in-the-plan-cache-use-a-specific-index/

---------------------------------------------------------------------
arunleoprakash
arunleoprakash
Forum Newbie
Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)

Group: General Forum Members
Points: 7 Visits: 1
Great!!! this script works for me - and I use the query plan column from the output which opens up with SSMS and I am able to find which query exactly using the index - note the query plan for procedure, function calls gives the entire query plan - however it quite easy to locate the object. with SQL Sentry Plan explorer it becomes easy to locate the index object. Another thing I have noticed is the query returns all the queries that impact the index, DML's impacting the indexes is also listed, but still it helps to understand the extra load during DML's on the index Smile.

The seek count and other extracts were not correct though for which I used

SELECT OBJECT_NAME(S.[OBJECT_ID]) AS [OBJECT NAME],
I.[NAME] AS [INDEX NAME],
USER_SEEKS,
USER_SCANS,
USER_LOOKUPS,
USER_UPDATES
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 S.database_id = DB_ID()
AND i.name = 'MYINDEX'


Also my thanks to Vinay bringing up the question I had in mind.... Smile
stephen.kratowicz
stephen.kratowicz
Grasshopper
Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)

Group: General Forum Members
Points: 14 Visits: 114
The seek count and other extracts were not correct though for which I used...


@arunleoprakash, our queries are looking for different things. The ScanCount column in my query tells you the number of times that a scan operation on the index appears in the query plan. If a plan only has one scan on your index, then the count will always show 1, even if the plan has been executed many times and your index has been scanned many times. When looking for total times a scan/seek/update has occurred, then yes your query is the right approach.

My reasoning for including it is that if you get -- for example -- a scan count of 3 for some query plan, then you know you need to look for 3 places in that query/procedure if you want to find all instances of it.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search