Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
sqlilliterate
Starting Member
40 Posts |
 Posted - 2007-12-21 : 03:26:54
|
| Hi all,Here’s a question that bugs me on SQL Stored procedure.I’ve a stored procedure which takes 1 sec for its execution when it is hit by single user.When the same Stored procedure is accessed concurrently in a multi user environment with different Inputs to the SP, it’s taking 5-6 secs to execute.I’m totally confused, what might hold up the resources though SQL server provides multi user instances when it is hit by several users.Correct me if I’m wrong).--ash |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2007-12-21 : 03:34:24
|
Are you experiencing any locking problems?
E 12°55'05.25"N 56°04'39.16" |
 |
|
|
harsh_athalye
Master Smack Fu Yak Hacker
5581 Posts |
Posted - 2007-12-21 : 03:34:35
|
It totally depends on what are the contents of your SP. There might be a blocking issue where a process holding lock on the objects referred to in an SP. Harsh AthalyeIndia."The IMPOSSIBLE is often UNTRIED" |
|
|
|
sqlilliterate
Starting Member
40 Posts |
Posted - 2007-12-21 : 04:33:24
|
| Even i afraid it might be due to blocking of the objects referred.But I've used SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTEDand NOLOCKs all through my select statements.Is there any other alternatives, to avoid locking... |
|
|
|
harsh_athalye
Master Smack Fu Yak Hacker
5581 Posts |
Posted - 2007-12-21 : 04:38:02
|
| Using READ UNCOMMITTED isolation level is not at all recommended unless SP is used for reporting purpose as the data fetched can not be reliable enough.Harsh AthalyeIndia."The IMPOSSIBLE is often UNTRIED" |
|
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2007-12-21 : 04:40:35
|
Post the SP code here.
E 12°55'05.25"N 56°04'39.16" |
|
|
|
|
|
|
Execution time of Stored procedure