Optimizing connection pooling

Hi,

I'm trying to optimize an ASP.NET 2.0 app where, with a load of 100 users, the CPU usage on the web server is a reasonable <20% but the db server (SQL Server 2005) often goes up to 100% and never below 20%. When viewing the Performance Monitor counter SQL Server :General Statistics/User Connections this shows that there are 50-65 connections. When the number of connections go up the db server's CPU usage also goes up along with web page response times.

When viewing the Sql server Activity Monitor it, for all connections, always says that the Login Time and the Last Batch are the same. This has got me wondering if no connections are pooled and that all connections always are closed and reopened and that would explain why the db server is so jammed.

Could someone give me some pointers as what to check to find optimization points.

Regards,

Mathias

Which operating system is running on your server?|||

Hi Mathias,

Please check in your connection string to see if pooling has been turned off.

If it is on, you can try to increase the connection lifetime and the min pool size, to make sure that the connections are available for using.

sql

Optimization problem

Hi,
I got a problem when run optimization. In SQL 7, transaction log usage is
very limit but after upgraded to SQL 2000, found the optimization job ate 9G
transaction (the db size is 21G). How can I limit the transaction log usage?
Thanks & regards,
henryHi,
you can set maximum size of the log. But then set an alert to notify you
when log is nearly full.
Other good way to keep tran log small is to regulary backup it...
Danijel
"Monster Henry" <henry@.monster.com> wrote in message
news:eJnzn3cAFHA.3592@.TK2MSFTNGP09.phx.gbl...
> Hi,
> I got a problem when run optimization. In SQL 7, transaction log usage is
> very limit but after upgraded to SQL 2000, found the optimization job ate
> 9G transaction (the db size is 21G). How can I limit the transaction log
> usage?
> Thanks & regards,
> henry
>|||Hi
The transaction log growing due to a Index Re-Builds are expected. Since the
operation is fully logged, you need as much space (1.2 times) as the biggest
table when a clustered index is re-built.
Regards
Mike
"Monster Henry" wrote:
> Hi,
> I got a problem when run optimization. In SQL 7, transaction log usage is
> very limit but after upgraded to SQL 2000, found the optimization job ate 9G
> transaction (the db size is 21G). How can I limit the transaction log usage?
> Thanks & regards,
> henry
>
>sql

Optimization problem

Hi,
I got a problem when run optimization. In SQL 7, transaction log usage is
very limit but after upgraded to SQL 2000, found the optimization job ate 9G
transaction (the db size is 21G). How can I limit the transaction log usage?
Thanks & regards,
henry
Hi,
you can set maximum size of the log. But then set an alert to notify you
when log is nearly full.
Other good way to keep tran log small is to regulary backup it...
Danijel
"Monster Henry" <henry@.monster.com> wrote in message
news:eJnzn3cAFHA.3592@.TK2MSFTNGP09.phx.gbl...
> Hi,
> I got a problem when run optimization. In SQL 7, transaction log usage is
> very limit but after upgraded to SQL 2000, found the optimization job ate
> 9G transaction (the db size is 21G). How can I limit the transaction log
> usage?
> Thanks & regards,
> henry
>
|||Hi
The transaction log growing due to a Index Re-Builds are expected. Since the
operation is fully logged, you need as much space (1.2 times) as the biggest
table when a clustered index is re-built.
Regards
Mike
"Monster Henry" wrote:

> Hi,
> I got a problem when run optimization. In SQL 7, transaction log usage is
> very limit but after upgraded to SQL 2000, found the optimization job ate 9G
> transaction (the db size is 21G). How can I limit the transaction log usage?
> Thanks & regards,
> henry
>
>

Optimization problem

Hi,
I got a problem when run optimization. In SQL 7, transaction log usage is
very limit but after upgraded to SQL 2000, found the optimization job ate 9G
transaction (the db size is 21G). How can I limit the transaction log usage?
Thanks & regards,
henryHi,
you can set maximum size of the log. But then set an alert to notify you
when log is nearly full.
Other good way to keep tran log small is to regulary backup it...
Danijel
"Monster Henry" <henry@.monster.com> wrote in message
news:eJnzn3cAFHA.3592@.TK2MSFTNGP09.phx.gbl...
> Hi,
> I got a problem when run optimization. In SQL 7, transaction log usage is
> very limit but after upgraded to SQL 2000, found the optimization job ate
> 9G transaction (the db size is 21G). How can I limit the transaction log
> usage?
> Thanks & regards,
> henry
>|||Hi
The transaction log growing due to a Index Re-Builds are expected. Since the
operation is fully logged, you need as much space (1.2 times) as the biggest
table when a clustered index is re-built.
Regards
Mike
"Monster Henry" wrote:

> Hi,
> I got a problem when run optimization. In SQL 7, transaction log usage is
> very limit but after upgraded to SQL 2000, found the optimization job ate
9G
> transaction (the db size is 21G). How can I limit the transaction log usag
e?
> Thanks & regards,
> henry
>
>