Conncurrent access to the same stored procedure

Hi all DB guys, I am curious of the performance of the database system on
concurrent access to a single stored procedure. What I mean is, when I have
a
stored procedure that is access by multiple connections/clients, what is the
impact on the system?
Apart from that, if I am not mistaken, we can set the isolation level to
prevent concurrent access to the same stored procedure, is it? So, this is
actually to prevent concurrent read/write to the same data
(locking/blocking), or is it because of any other problem?
Would really appreciate if you people can share your experience or thought.
Thanks
EugeneHi Eugene
Multiple users can execute the same proc at the same time. Any performance
impact would be because of the data being accessed, not because the commands
were in a procedure. If multiple processes are trying to access the same
data, there are always possible blocking issues to be aware of, whether or
not you are going through a stored procedure.
Your isolation level doesn't control anything about stored procedures. It
only controls how long shared locks are held, no matter how those locks are
acquired. Have you read everything about isolation levels, locking and
blocking in the Books Online?
--
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"Eugene" <Eugene@.discussions.microsoft.com> wrote in message
news:C08DF3A2-52F7-4471-9284-7C9089E1292A@.microsoft.com...
> Hi all DB guys, I am curious of the performance of the database system on
> concurrent access to a single stored procedure. What I mean is, when I
> have a
> stored procedure that is access by multiple connections/clients, what is
> the
> impact on the system?
> Apart from that, if I am not mistaken, we can set the isolation level to
> prevent concurrent access to the same stored procedure, is it? So, this is
> actually to prevent concurrent read/write to the same data
> (locking/blocking), or is it because of any other problem?
> Would really appreciate if you people can share your experience or
> thought.
> Thanks
> Eugene
>|||Your isolation level will manage how data is read or written to across
connections and clients. You need to worry/plan for this.
There are other things that can affect the performance of your stored
procedure, some of these include your stored procedure recompilations,
indices, table design, etc.
"Eugene" <Eugene@.discussions.microsoft.com> wrote in message
news:C08DF3A2-52F7-4471-9284-7C9089E1292A@.microsoft.com...
> Hi all DB guys, I am curious of the performance of the database system on
> concurrent access to a single stored procedure. What I mean is, when I
> have a
> stored procedure that is access by multiple connections/clients, what is
> the
> impact on the system?
> Apart from that, if I am not mistaken, we can set the isolation level to
> prevent concurrent access to the same stored procedure, is it? So, this is
> actually to prevent concurrent read/write to the same data
> (locking/blocking), or is it because of any other problem?
> Would really appreciate if you people can share your experience or
> thought.
> Thanks
> Eugene|||Hi Kalen, Kingsley
thanks for the explanation. Kalen, I didn't read up clearly on the Isolation
level, I asked does Isolation prevent concurrent access to the stored
procedure because I am told so by my friend. To get myself clear, since
isolation level does not prevent concurrent access to the same sp, so does
Isolation prevent concurrent access to the same Transaction block code in th
e
same sp?
As I said with Isolation level, I am told concurrent access to the same sp
would be terrible to the performance - which you two have explained that it
is not a concern, the main concern is the underlying data, thanks a lot :)
I guess I can better explain to my friends next time such discussion pops
up. Thanks.
"Kalen Delaney" wrote:

> Hi Eugene
> Multiple users can execute the same proc at the same time. Any performance
> impact would be because of the data being accessed, not because the comman
ds
> were in a procedure. If multiple processes are trying to access the same
> data, there are always possible blocking issues to be aware of, whether or
> not you are going through a stored procedure.
> Your isolation level doesn't control anything about stored procedures. It
> only controls how long shared locks are held, no matter how those locks ar
e
> acquired. Have you read everything about isolation levels, locking and
> blocking in the Books Online?
> --
> HTH
> --
> Kalen Delaney
> SQL Server MVP
> www.SolidQualityLearning.com
>
> "Eugene" <Eugene@.discussions.microsoft.com> wrote in message
> news:C08DF3A2-52F7-4471-9284-7C9089E1292A@.microsoft.com...
>
>