Details on Index usage by SPs

[RajD]

Hi,

Is there a way I can get a list of stored procedures using one particular index in my database?

I have table which is used by many SPs(over 100 in #). This table has 7 indexes on various columns. I feel one of the 7 indexes is useless and want to drop it. But before doing so, I wanted to confirm if it is being used by any SP.

I am on ASE 11.9.2

Thanks,
RajD

 

[qyllr]

hello,

there may be a system procedure that can give you this info... i just have not come across it...

i would approach it like this...

write a sql script that does the following for each sp:

set showplan on
set noexec on
go

sp_{name} {parameter?}
go

if you know perl... you can use the following script to generate the sql script:

prerequisite - create a .dat file containing all your sp's

#!/usr/local/bin
$In_File = "RAJ.dat";
$Out_File = "RAJ.sql";
open (IN_FILE,"$In_File&quot || die "Ooops: Cannot Open Input File: $!\n";
open (OUT_FILE,">$Out_File&quot || die "Two for two: Cannot Open Output File: $!\n";

print OUT_FILE "USE {database}\n";
print OUT_FILE "GO\n";
print OUT_FILE "--------------------------\n";

while ($line = <IN_FILE>)
{
if ($line =~ m/\w+/)
{
$line =~ s/^\s+//g;
$line =~ s/\s+$//g;
$sp = $line;

print OUT_FILE &quot;SET showplan on\n&quot;;
print OUT_FILE &quot;GO\n&quot;;
print OUT_FILE &quot;SET noexec on&quot;;
print OUT_FILE &quot;GO\n&quot;;
print OUT_FILE &quot;$sp\n&quot;;
print OUT_FILE &quot;GO\n&quot;;
print OUT_FILE &quot;-------------------------------\n&quot;;

}
}

close (OUT_FILE);
close (IN_FILE);

...run the sql script through isql...
...grep for the index from the output file...

check also the sybase website... for alternative solutions...

hope this helps,
q.

 

[Guest_imported]

I am assuming you are trying to identify if an index has been forced in a sp.

you can query syscomments

select distinct object_name(id)
from syscomments
where
text like '%INDEXNAMEINQUESTIONHERE%'

if that indexname is in the stored procedure it will come up

 

[RajD]

Hi qyllr,

I didn't find any stored proc for this purpose yet.

The solution you suggest do not seem to work due to following reason:

When &quot;Set noexec on&quot; is set, it does not parse through the stored procedure. Just returns a line saying
STEP 1
The type of query is EXECUTE.

That's it. But if I run a single query, it does returns the query plan.

RajD

 

[RajD]

Hi damien_k ,

No. I am not trying to identify if an index has been forced in a sp.

I need to know, if I create an index on a table, will it be used by the query plan during execution. If yes, then which other procedures makes use of it.

This I want to know for the reverse purpose.

I have an active table (inventory) which has 7 indexes on it. There are around 500 stored procedures using this table(as listed by sp_depends too). My question is:
If I drop one of these indexes(say index on inv_date), I need to know which stored procedures are going to get affected(ofcourse negative impact).

I am sorry if I am confussing you more. Anyway, thanks.

RajD

 

[qyllr]

hello,

try it without setting noexec on...

just do...

set showplan on
go
sp_{name}
go

q.

 

[RajD]

Hi qyllr,

That would be my last option, and its very expensive too.

These procedures have certain DMLs which would alter the data. They run in batch based on certain parameters like date, some_flag etc.

Anyway, I am getting a feel there is no escape. I guess Sybase 12.0 has some system tables (sysqueryplans) to store query plans, which could help in future.

RajD

 

[qyllr]

raj,

sounds like a plan... good luck...

q.