Concurrent Batches = Concurrent Connections? 
Author Message
 Concurrent Batches = Concurrent Connections?

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,



Tue, 07 Sep 2004 07:50:59 GMT
 Concurrent Batches = Concurrent Connections?

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,



Tue, 07 Sep 2004 08:24:44 GMT
 Concurrent Batches = Concurrent Connections?
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


Quote:
> 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,



Tue, 07 Sep 2004 09:11:46 GMT
 Concurrent Batches = Concurrent Connections?
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


Quote:
> 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,



Tue, 07 Sep 2004 09:59:48 GMT
 
 [ 4 post ] 

 Relevant Pages 

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

2. Maximum concurrent connections using connection pool

3. problem with "Maximum concurrent user connection"

4. URGENT!! Concurrent Connections

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

6. HELP!! - Max Concurrent Connection Problem

7. SQL Server concurrent connection problem

8. SQL Server Concurrent User Connections

9. # of Concurrent User Connections

10. Connection To Server :Error : More than one concurrent User

11. error with pooled SQL connection and >1 concurrent user

12. How to limit concurrent connections


 
Powered by phpBB® Forum Software