How to find index usage by stored procedure?

• SQL Guy 1

  SSCoach

  Points: 15981

  More actions

  January 27, 2021 at 5:57 pm

  #3838077

  Hi all,

  We have 2 DMV's:

  sys.dm_db_index_usage_stats

  sys.dm_exec_procedure_stats

  And they don't look they can be joined between each other. One shows index usage, and another shows procedures executions statistics. But is there a way to find index usage by procedure?

  Thanks

   

• ScottPletcher

  SSC Guru

  Points: 101192

  More actions

  January 27, 2021 at 6:02 pm

  #3838090

  No.  SQL doesn't really care which proc is using the index, and even trying to capture that kind of data about index usage would be huge overhead.  Indexes need tuned for all uses of the table, not just for specific proc(s).

  SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

• Johan Bijnens

  SSC Guru

  Points: 135536

  More actions

  January 28, 2021 at 9:10 am

  #3838285

  FWIW the execution plan will show the indexes it plans use ( and estimates or actual usage values )

  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