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

Confused with a change in t-sql behavior

I am really curious to know why a simple query like throws an error in SQL Server 2005 and not in SQL Server 2000?

On SQL Server 2005
-
select RowNumber, RowNumber, TextData from dbo.[Tempdb Profile]
Order By RowNumber

error

Msg 209, Level 16, State 1, Line 1
Ambiguous column name 'RowNumber'.

You can have two columns with the same name, try to give them an alias and the query will execute withtout any errors.

HTH, jens Suessmeyer.

|||

yeah right

select RowNumber, RowNumber as rownumberx, TextData from dbo.[Tempdb Profile]
Order By 1 --<< referreing to first column

|||Hi,

I wouldn′t rely on the numbered order, what if you have 25 columns, where you want to order for the 16 column, do you really want to count the columns in your select statement. OK you might say, therefore you would name the column.. you are right.. but regarding the other examples you would produce a inconcistency in your own SQL Syntax, so I would prefer better to stick to ONE style, rather numbering OR naming.

Just my 0.02$, Jens Suessmeyer.|||

Hi Jens

This is part of an update to the behaviour of ordering by aliases and can be found in the Upgrade Wizard: http://www.microsoft.com/downloads/details.aspx?familyid=451FBF81-AB07-4CCB-A18B-DA38F6BCF484&displaylang=en

I've pasted the relevant documentation below:

In SQL Server 2005, column aliases in the ORDER BY clause cannot be prefixed by the table alias.

Component

Database Engine

Description

For example, the following query executes in SQL Server 2000, but returns an error in SQL Server 2005:

USE AdventureWorks;

GO

SELECT FirstName AS f, LastName AS l

FROM Person.Contact p

ORDER BY p.l

The SQL Server 2005 Database Engine does not match p.l in the ORDER BY clause to a valid column in the table.

Exception

If the prefixed column alias that is specified in the ORDER BY clause is a valid column name in the specified table, the query executes without error; in SQL Server 2005, the semantics of the statement might be different. For example, the column alias (id) specified in the following statement is a valid column name in the sysobjects table. In SQL Server 2000, when the statement executes, the CAST operation is performed after the result set is sorted. This means the name column is used in the sort operation. In SQL Server 2005, the CAST operation occurs before the sort operation. This means the id column in the table is used in the sort operation and returns the result set in an unexpected order.

SELECT CAST (o.name AS char(128)) AS id

FROM sysobjects AS o

ORDER BY o.id;

Corrective Action

Modify queries that use column aliases prefixed by table aliases in the ORDER BY clause in either of the following ways:

· Do not prefix the column alias in the ORDER BY clause, if possible.

· Replace the column alias with the column name.

For example, both of the following queries execute without error in SQL Server 2005:

USE AdventureWorks;

GO

SELECT FirstName AS f, LastName AS l

FROM Person.Contact p

ORDER BY l

USE AdventureWorks;

GO

SELECT FirstName AS f, LastName AS l

FROM Person.Contact p

ORDER BY p.LastName

|||

Hi. I think you found it !

I have the exact same problem with a client with hundreds of ASP pages, with a lot of selects with this order by clause problem.

Is there any workaround for SQL 2005 behave as SQL 2000 and the queries return to work without having to change all the source code?

Thanks in advance.

MG

|||

Just in case this is useful to someone else, you can right click your database, choose Properties, select (left) the "Options" page and you can change the Compatibility Level of your database to whaterever you need (e.g SQL 2000). Pretty good for old code...

Rgds. MG

Marco Gouveia wrote:

Hi. I think you found it !

I have the exact same problem with a client with hundreds of ASP pages, with a lot of selects with this order by clause problem.

Is there any workaround for SQL 2005 behave as SQL 2000 and the queries return to work without having to change all the source code?

Thanks in advance.

MG