January 4, 2011 at 7:58 am
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
January 4, 2011 at 8:11 am
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
January 4, 2011 at 8:15 am
Thank you John 😀
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)
VinayIf 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
January 4, 2011 at 8:23 am
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
January 4, 2011 at 10:22 am
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
ONS.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
January 4, 2011 at 10:53 am
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
October 18, 2012 at 7:47 am
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
December 30, 2013 at 7:42 am
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/
---------------------------------------------------------------------
November 16, 2016 at 12:38 am
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 :).
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.... 🙂
November 16, 2016 at 8:32 am
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.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply