Contents
What is stored in an index?
Database indexes will also store pointers which are simply reference information for the location of the additional information in memory. Basically the index holds the company_id and that particular row’s home address on the memory disk. The index will actually look like this: company_id. pointer.
Why do we need index on a stored procedure?
The performance of the JOIN condition is increased due to a non clustered index. When the Index created the Index table and the Statistical table, both are updated, so making an index within a stored procedure again takes a long time to create.
Why do we do indexing?
Indexes are used to quickly locate data without having to search every row in a database table every time a database table is accessed. Indexes can be created using one or more columns of a database table, providing the basis for both rapid random lookups and efficient access of ordered records.
Is it possible to find what stored procedures are using what indexes?
As gbn has pointed out a Stored Procedure does not directly use an index, it uses a table that uses an index. Below is an explanation that I hope will clarify what I am trying to ask here. Is it possible for me to determine what SQL was run that caused the above indexes to be used?
How to find stored procedure that inserts into a specific table?
If you are certain of the wording you can search for something like ‘INSERT INTO mytable’ The question asks how to find a stored procedure that inserts into a specific table. Searching for stored procedures containing the name may bring back quite a few false positives if the table is referenced for many selects.
How to find the index of a proc?
Given an index name, this will return entire the definition of each proc (in “entire_query”) and the actual SQL statement in that proc which uses the index (in “query”). Note it runs very slowly! In the SQL management studio, you can see exactly how the query is executed by using the “Display Execution Plan” option under the Query menu.
When to use objectproperty in sys.procedures?
Of course in all cases if you only want a specific type of object (stored procedures for example) in your result set then you have to either write a function (see my previous post here for an example), use OBJECTPROPERTY (IsProcedure, IsView etc) (2000+), OBJECTPROPERTYEX (BaseType) (2005+), or join with sys.objects, sys.procedures, sys.views etc.