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...
>
>

Confliction between SQL Server 2005 and SQL Server 2005 Express Edition

Hi,

My objective is using a single DB file (mdf file) + SQL Server Express 2005 to run my program

I got two softwares here, Visual Studio 2005 and SQL Server 2005. Firstly, I installed Visual Studio 2005, normally the SQL Server 2005 Express Edition will be installed with this software. After finishing the installation. I opened a new project and try to open a DB file (.mdf) from Visual Studio 2005, it works. The problem is that I can't restore the mdf file from an old database file since it lose a lot of functions that appear in SQL Server 2000 / SQL Server 2005. And it is too troublesome and inconvenient to edit a DB file in Visual Stuio 2005.

Therefore, I have to install SQL Server 2005 for me to edit the mdf file, so that I can modify, backup, or even restore the mdf file with another DB file. After the installation of SQL Server 2005, I got a tool, SQL Server Managment Studio, to edit the mdf file. I can open the mdf file but when I double-clicked on the mdf file in Visual Studio 2005, it gives me the following error message:

Connections to SQL Server files (*.mdf) require SQL Server Express 2005 to function properly. Please verify the installation of the component or download from the URL:http://go.microsoft.com/fwlink/?LinkId=49251

It seems the SQL Server 2005 modify some of the files in SQL Server 2005 Express Edition. Is that SQL Server 2005 and SQL Server 2005 Express Edition cannot be exist at the same time?

The issue is I'm working on my computer, but I dun want to install SQL Server 2005 in the server that I will transfer to. It will be much like using Access DB file, without the Microsoft Access software, the program can still work well. However, when I'm still in programming stage, I do hope to have a DB file as the source file for my program, but I can do some editing works with SQL Server 2005.

Does anyone can give me a suggestion? Thanks for your kindly reply.

Calvin

On my machine I have SQL2005 and SQL Express installed at the same time, and both of them work fine. I guess the problem lays in connection string of the database. You can check as following:

Go to database explorer-data connections.
Right click your mdf database and click "modify connection"...
Click the advanced button
Search for the "data source" string
Set the string to refer to your SQL Server2005 instance.

You can take a look at this link:

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=124596&SiteId=1.

BTW, if you just want to operate on the database file more directly, you do not need to install SQL Server2005. You can intall "SQL Server Management Studio Express " from here:

http://msdn.microsoft.com/vstudio/express/sql/download/

.