August 14, 2025 at 9:55 am
I am trying consolidate indexes in the production environment and whilst I have a good idea of what or how to about the actual index consolidation, my challenge is how to test this process to ensure that stored procedures or processes calling on tables are using the indexes they would normally have used so for example a process using Index IX_Name1_Name3 INCLUDE Name2 will still use the same index if consolidated index if changed to IX_Name1_Name2 INCLUDE Name3_Name4. I got this script which is supposed to show what stored procedures use a particular index and the purpose for me doing this is to test if I amend that index that stored procedure will still utilize the index however I am not sure if the below script is giving me accurate results.
SELECT
OBJECT_NAME(st.objectid) AS ProcedureName,
st.text AS [SQLText]
qp.query_plan
FROM sys.dm_exec_cached_plans AS cp
CROSS APPLY sys.dm_exec_query_plan(cp.plan_handle) AS qp
CROSS APPLY sys.dm_exec_sql_text(cp.plan_handle) AS st
WHERE CAST (qp.query_plan AS NVARCHAR(MAX)) Like'%IndexName%'
Is someone able to help analyse this to confirm it will provide what I need?
August 14, 2025 at 12:13 pm
only key columns of an index are used to filter directly on index access
Filters using included columns are only applied after key columns have been processed ( stage 3 )
Included columns should only be in your index ( leaf level ) to avoid rid-lookups or clustered index access to get to the actual data.
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
August 14, 2025 at 4:03 pm
What Johan said. You would need to track the plans across time for a proc to see if the same index is used or not used. Plans clear out of the cache over time, or with changes to procs, so you need a way to track this to see if the new index is being used by the proc instead of the old one.
I assume you're using this query to see which items use an index, which I think it will do, but then you have a lot of info to process.
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply