Testing Stored Procedure performance

Question

I did some operations on my tables to get faster SP operations against to time-out problem on a web service method which uses that problematic SP.

I was trying to compare SP perfomance but i could not get on well on that sie. See 1 please.

Clearly, I want to see if I get time-out problem after all SP modifications on development scope. If everything goes well, I will put my new indexes to PRODUCTION ENVIRONMENT, the indexes which helps the SP to run faster against to time-out problem.

1- How can I be sure Web.Config file does let the SP run itself until the operation will be completed successfully. this configuration eneough ?

<configuration>

<location path="~/WebService.asmx">

<system.web>

<httpRuntime timeout="700"/>

</system.web>

</location>

</configuration>

OR, Should i edit the connection string for time-out duration?

Connect Timeout=30

Regards BK

References:

Comparing SP performance

Edit:

Nina : Sql fora profiler result How shold I consider the results of the Profiler..?

Answer 1

If you are looking at testing your stored procedure performance you should use the profiling tools of the RDBMS (E.G., the query execution plan, profiler, etc) instead of using the a web service.