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

Opinions on SQL Server hardware

Hi all,
I was wondering if I could get some experienced opinions on SQL hardware to
run an ERP app on SQL 2000. The app does not yet support SQL 2005. The ERP
app has 25 users and likely won't exceed 30 users for several years. All
traffic is on the LAN. The ERP clients basically submit SQL requests for
reads and writes. The app makes heavy use of temp tables, temp views but
not many stored procedures. The current size of the db is 6GB and will
likely double in 4 years.

Planned server:
Windows Server 2003
4 GB RAM
SQL 2000 Standard (ERP app does not yet support SQL 2005)
RAID1 for OS
RAID 10 for SQL data
RAID1 for SQL logs
RAID1 for temp db
Dual, teamed NICs

I would try to get 15K SCSI drives. Any thoughts on SATA instead of SCSI?

Could I expect much of an impovement by using SQL 2000 Enterprise since it
can use more RAM? I would rather wait for SQL 2005 to be supported.

Does anyone have a Dell or HP server configured in an email-able cart that
they would care to share?

Thank you.Artie (artie2269@.yahoo.com) writes:

Quote:

Originally Posted by

I was wondering if I could get some experienced opinions on SQL hardware
to run an ERP app on SQL 2000. The app does not yet support SQL 2005.
The ERP app has 25 users and likely won't exceed 30 users for several
years. All traffic is on the LAN. The ERP clients basically submit SQL
requests for reads and writes. The app makes heavy use of temp tables,
temp views but not many stored procedures. The current size of the db
is 6GB and will likely double in 4 years.
>
Planned server:
Windows Server 2003
4 GB RAM
SQL 2000 Standard (ERP app does not yet support SQL 2005)
RAID1 for OS
RAID 10 for SQL data
RAID1 for SQL logs
RAID1 for temp db
Dual, teamed NICs
>
I would try to get 15K SCSI drives. Any thoughts on SATA instead of SCSI?

Hardware recommendations is not my best game, and it's always a gamble,
because there are so many "It depends". But from what you say, this
appears to be a very able server for your database.

Quote:

Originally Posted by

Could I expect much of an impovement by using SQL 2000 Enterprise since it
can use more RAM? I would rather wait for SQL 2005 to be supported.

Given the price tag of Enterprise, I find it difficult to recommend
Enterprise when your database is that modest.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||i've dealt with extreme traffic with dual xeon dual core with 6GB ram,
win 2003 ent, sql ent. we were using standard edition with sql 2003
standard. the db size then was about 4GB. traffic was high (for me) at
few hundred per sec.
we had to change the db design to utilize select-inserts rather than
traditional updates. and change all non critical selects with (NOLOCK)
locking. with (NOLOCK) saved my job.
but if u ask me about the improvement using enterprise version over
standard version, personally from my limited experience, i'd say
unnoticable.
now, using RAMDISK boost performance almost two-fold.

On Oct 9, 12:23 am, "Artie" <artie2...@.yahoo.comwrote:

Quote:

Originally Posted by

Hi all,
I was wondering if I could get some experienced opinions on SQL hardware to
run an ERP app on SQL 2000. The app does not yet support SQL 2005. The ERP
app has 25 users and likely won't exceed 30 users for several years. All
traffic is on the LAN. The ERP clients basically submit SQL requests for
reads and writes. The app makes heavy use of temp tables, temp views but
not many stored procedures. The current size of the db is 6GB and will
likely double in 4 years.
>
Planned server:
Windows Server 2003
4 GB RAM
SQL 2000 Standard (ERP app does not yet support SQL 2005)
RAID1 for OS
RAID 10 for SQL data
RAID1 for SQL logs
RAID1 for temp db
Dual, teamed NICs
>
I would try to get 15K SCSI drives. Any thoughts on SATA instead of SCSI?
>
Could I expect much of an impovement by using SQL 2000 Enterprise since it
can use more RAM? I would rather wait for SQL 2005 to be supported.
>
Does anyone have a Dell or HP server configured in an email-able cart that
they would care to share?
>
Thank you.

|||On Mon, 8 Oct 2007 09:23:58 -0700, "Artie" <artie2269@.yahoo.com>
wrote:

I don't consider SATA suitable for servers. Too many crashes of the
RAID array. Pay the extra money and get a proven technology.

-Tom.

<clip>

Quote:

Originally Posted by

>
>I would try to get 15K SCSI drives. Any thoughts on SATA instead of SCSI?
>
>Could I expect much of an impovement by using SQL 2000 Enterprise since it
>can use more RAM? I would rather wait for SQL 2005 to be supported.
>
>Does anyone have a Dell or HP server configured in an email-able cart that
>they would care to share?
>
>Thank you.
>
>