Concurrent Batches = Concurrent Connections?

Concurrent Batches = Concurrent Connections?

Post by Jeff Steven » Sat, 23 Mar 2002 08:50:59



I have read the manual about the 5 concurrent batches:

A concurrent workload governor limits the performance of the database engine
in these two editions. The performance of individual Transact-SQL batches is
decreased when more than five batches are executed concurrently. The amount
each batch is slowed down depends on how many batches over the five-batch
limit are executing concurrently, and the amount of data retrieved by the
individual batches. As more batches are executed concurrently, and as more
data is retrieved by each batch, the more the governor slows down the
individual batches. You can use the DBCC CONCURRENCYVIOLATION statement to
report how often the concurrent workload governor is activated. For more
information, see DBCC CONCURRENCYVIOLATION

Does this have anything at all to do with concurrent db connections?

If you share one connection among 5 or more threads in ADO are your still
going to run into this if they execute something all at once.

Thank You,

 
 
 

Concurrent Batches = Concurrent Connections?

Post by Greg Linwoo » Sat, 23 Mar 2002 09:24:44


Hi Jeff..

It has very much to do with concurrent db connections.

Although, to answer your qn directly, if 5 threads share a pointer to the
same COM ADO Connection, their batches (more accurately - "Commands" in this
context) will be serialized - one after the other.

You can observe this behaviour using the profiler.

If you can live with this design in your VB app, you should be ok..

HTH

Cheers,
Greg Linwood


Quote:> I have read the manual about the 5 concurrent batches:

> A concurrent workload governor limits the performance of the database
engine
> in these two editions. The performance of individual Transact-SQL batches
is
> decreased when more than five batches are executed concurrently. The
amount
> each batch is slowed down depends on how many batches over the five-batch
> limit are executing concurrently, and the amount of data retrieved by the
> individual batches. As more batches are executed concurrently, and as more
> data is retrieved by each batch, the more the governor slows down the
> individual batches. You can use the DBCC CONCURRENCYVIOLATION statement to
> report how often the concurrent workload governor is activated. For more
> information, see DBCC CONCURRENCYVIOLATION

> Does this have anything at all to do with concurrent db connections?

> If you share one connection among 5 or more threads in ADO are your still
> going to run into this if they execute something all at once.

> Thank You,


 
 
 

Concurrent Batches = Concurrent Connections?

Post by Jeff Steven » Sat, 23 Mar 2002 10:11:46


Greg,

    That is good that they are serialized so my design will work.
    So just to clarify, idle connections should not have any bearing.  For
instance, you could have 100 connections but as long as only 5 batches (in 5
separate connections) are run simultaneously, there should not be any
performance degradateion.

Thanks,
Jeff


> Hi Jeff..

> It has very much to do with concurrent db connections.

> Although, to answer your qn directly, if 5 threads share a pointer to the
> same COM ADO Connection, their batches (more accurately - "Commands" in
this
> context) will be serialized - one after the other.

> You can observe this behaviour using the profiler.

> If you can live with this design in your VB app, you should be ok..

> HTH

> Cheers,
> Greg Linwood



> > I have read the manual about the 5 concurrent batches:

> > A concurrent workload governor limits the performance of the database
> engine
> > in these two editions. The performance of individual Transact-SQL
batches
> is
> > decreased when more than five batches are executed concurrently. The
> amount
> > each batch is slowed down depends on how many batches over the
five-batch
> > limit are executing concurrently, and the amount of data retrieved by
the
> > individual batches. As more batches are executed concurrently, and as
more
> > data is retrieved by each batch, the more the governor slows down the
> > individual batches. You can use the DBCC CONCURRENCYVIOLATION statement
to
> > report how often the concurrent workload governor is activated. For more
> > information, see DBCC CONCURRENCYVIOLATION

> > Does this have anything at all to do with concurrent db connections?

> > If you share one connection among 5 or more threads in ADO are your
still
> > going to run into this if they execute something all at once.

> > Thank You,

 
 
 

Concurrent Batches = Concurrent Connections?

Post by Greg Linwoo » Sat, 23 Mar 2002 10:59:48


That's right.. so actually, you COULD use 5 different instances of COM ADO
Connections and be quite sure about it..

Another thing is that performance is only degraded, when you exceed the
threshold. So you might not have to be so shy about going over the limit
from time to time!

Don't forget that there are other constraints though - such as the 2Gb limit
which you might want to keep in mind!

Cheers,
Greg Linwood


> Greg,

>     That is good that they are serialized so my design will work.
>     So just to clarify, idle connections should not have any bearing.  For
> instance, you could have 100 connections but as long as only 5 batches (in
5
> separate connections) are run simultaneously, there should not be any
> performance degradateion.

> Thanks,
> Jeff



> > Hi Jeff..

> > It has very much to do with concurrent db connections.

> > Although, to answer your qn directly, if 5 threads share a pointer to
the
> > same COM ADO Connection, their batches (more accurately - "Commands" in
> this
> > context) will be serialized - one after the other.

> > You can observe this behaviour using the profiler.

> > If you can live with this design in your VB app, you should be ok..

> > HTH

> > Cheers,
> > Greg Linwood



> > > I have read the manual about the 5 concurrent batches:

> > > A concurrent workload governor limits the performance of the database
> > engine
> > > in these two editions. The performance of individual Transact-SQL
> batches
> > is
> > > decreased when more than five batches are executed concurrently. The
> > amount
> > > each batch is slowed down depends on how many batches over the
> five-batch
> > > limit are executing concurrently, and the amount of data retrieved by
> the
> > > individual batches. As more batches are executed concurrently, and as
> more
> > > data is retrieved by each batch, the more the governor slows down the
> > > individual batches. You can use the DBCC CONCURRENCYVIOLATION
statement
> to
> > > report how often the concurrent workload governor is activated. For
more
> > > information, see DBCC CONCURRENCYVIOLATION

> > > Does this have anything at all to do with concurrent db connections?

> > > If you share one connection among 5 or more threads in ADO are your
> still
> > > going to run into this if they execute something all at once.

> > > Thank You,

 
 
 

1. Count of concurrent users, max concurrent users, more...

Hi all:

Siebel wants some performance benchmarks from our SQL server...  How do
I get a count of concurrent users + max concurrent users (mobile AND
conencted), et cetera?  Do I use profiler for this or is there a better
way, like a sp I don't know about?  Thanks much for your response...

Sent via Deja.com http://www.deja.com/
Before you buy.

2. bde on nt3.5

3. Maximum concurrent connections using connection pool

4. SQL assistance

5. problem with "Maximum concurrent user connection"

6. static function ODCIGetInterfaces(ifclist OUT sys.ODCIObjectList)

7. URGENT!! Concurrent Connections

8. Bspxcom-13 with psql-2000

9. Concurrent User connection to SQL 7 or SQL 2K

10. HELP!! - Max Concurrent Connection Problem

11. SQL Server concurrent connection problem

12. SQL Server Concurrent User Connections

13. # of Concurrent User Connections