January 30, 2010 at 3:25 pm
Can anyone give me a generic script to find store procedure using by indexes. I have bunch of indexes need to find which sp using them.
Thanks in advance
January 30, 2010 at 5:03 pm
Your question is rather vague. Do you mean stored procedures (SP) using a query hint? Or those that reference in a WHERE clause a column of a table and that column is indexed?
Remember without a query/table hint in a SP, the use of an index(s) is determined by the database engine creating what it has evaluated as the optimum execution plan.
If you can be more specific I am sure someone will be able to assist you.
January 30, 2010 at 5:10 pm
I'm looking for scirpt which gives list of procs used by indexes. I have bunch of indexes need to find procs used by them
January 30, 2010 at 5:28 pm
I believe this might be of assistance to you
SQL Server 2005 Books Online (November 2008)
sys.dm_db_index_usage_stats
http://msdn.microsoft.com/en-us/library/ms188755(SQL.90).aspx
It will give you Returns counts of different types of index operations and the time each type of operation was last performed.
Hope this helps if not return and ask again.
January 30, 2010 at 7:45 pm
No I'm looking for script to find procs in the database based on indexes. For example I have index idx_foote need to find which proc is using it.
January 30, 2010 at 8:27 pm
Finally .... but can I ask why do you want to find the SP that "is using it"
Indexes are not used by SP's rather are used by the Query optimizer To gain an understanding of that please read
http://msdn.microsoft.com/en-us/library/ms190457(SQL.90).aspx
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply