Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Find all queries which use a perticular index. Expand / Collapse
Author
Message
Posted Tuesday, January 4, 2011 7:58 AM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Wednesday, October 2, 2013 9:39 AM
Points: 329, Visits: 195
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
Post #1042434
Posted Tuesday, January 4, 2011 8:11 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 2:33 AM
Points: 5,437, Visits: 10,131
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
Post #1042445
Posted Tuesday, January 4, 2011 8:15 AM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Wednesday, October 2, 2013 9:39 AM
Points: 329, Visits: 195
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)
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
Post #1042451
Posted Tuesday, January 4, 2011 8:23 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 2:33 AM
Points: 5,437, Visits: 10,131
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
Post #1042455
Posted Tuesday, January 4, 2011 10:22 AM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Wednesday, October 2, 2013 9:39 AM
Points: 329, Visits: 195
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
Post #1042573
Posted Tuesday, January 4, 2011 10:53 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 2:37 AM
Points: 40,431, Visits: 36,883
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 2008, MVP
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

Post #1042589
Posted Thursday, October 18, 2012 7:47 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, September 25, 2014 7:38 AM
Points: 4, Visits: 72
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

Post #1374368
Posted Monday, December 30, 2013 7:42 AM
SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 1:35 AM
Points: 5,886, Visits: 13,044
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/


---------------------------------------------------------------------

Post #1526499
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse