-2

I have a stored procedure in SQL Server 2005. When it's executed by one user, it takes 5 seconds to run. But when it's executed by several users, it takes a lot longer, even more than 15 seconds.

How to ameliorate it to be executed not depend on number of user?

Thanks.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
lizarazou
  • 73
  • 7
  • 5
    Add code snippet, table structure, sample data for your scenario and execution plan – Kannan Kandasamy May 10 '17 at 13:20
  • why not try indexing. – shishir May 10 '17 at 13:22
  • 3
    It is unclear what you are asking as it's akin to: "When I run one complex process on my computer it runs fast, but when I have many complex processes running, it runs slower. How to make all complex processes run fast regardless of number of processes?" – JNevill May 10 '17 at 13:22
  • Also probably should include your server hardware when asking performance questions. Putting 12 people in a Prius will reduce it's top speed if you catch my drift. – Jacob H May 10 '17 at 13:32

1 Answers1

2

5 seconds is relatively slow for a SQL Query, indicating that its doing a fair amount of work (e.g. a lot of disk IO). A query involving a lot of disk IO is likely to run slower when executed by several concurrent users because of increased load on the SQL Server. You should get an execution plan and look at whether the 5s query can be optimized, e.g. with additional indexes.

Its also possible that locking is to blame, e.g. if you query exclusive locks the entire table and takes 5s to run, a second query run concurrently would need to wait 5s for the original query to finish executing, and would then lock the table itself and take an additional 5s to execute resulting in a 10s execution time. 3 concurrent queries would take 15s etc... See How to identify blocking in SQL Server for a bunch of methods to check whether or not this is the case.

Community
  • 1
  • 1
Justin
  • 84,773
  • 49
  • 224
  • 367
  • i already add all the index needed. my problem is why when only one user take 5 seconde but when more two user execute the same procedure takes more time. is it problem of performance server or other thing. – lizarazou May 10 '17 at 14:32