sp execution

[nirmalsp]

Hi

i am having a problem like this
that is,
there are 3 systems running using database as sql server in one sql server
all these 3 systems use stored procedures in all prcesses & trigers.

when there are sp's running in one database
at the same time if i do execute another sp from a another
database permormance drops & some times it take long time to execute it.
even odbc time out since most we run from front end.

how could i solve this.
i don't want to go for a another server as a alternative
since it's going to cost the compay.

thanks

 

[tlbroadbent]

Are the applications blocking each other? Have you monitored locking in the database when the apps run? Do the tables involved have appropriate indexes?

There are literally dozens of questions that we could ask in order to find the reason for the long runs. Most of the time, poor performance is the result of poor application design, inadequate indexing or poor table design.

I recommend a visit to the SQL Performance web site.

http://www.sql-server-performance.com/articles.asp

I also have some additional links you may find helpful at my web site.

http://tlbroadbent.home.attbi.com/sqlarticles.htm

You can find plenty of additional information in SQL BOL or the Microsoft web site by searching on the keyword "tuning." Terry L. Broadbent
FAQ183-874 contains tips for posting questions in these forums.
NOTE: Reference to the FAQ is not directed at any individual.