Connection Pool in SQLserver 2005

What is the performance cost of not using connection pool when connecting to
a sqlserver 2005 using windows identity.
We are planning an application where the users windows identity is forwarded
through all layers all the way into the sqlserver.
This gives security and auditing advantages.
But a pros/cons evaluation is always proper to do.
The cons include: the sqlserver connection pool cannot be used...(?) If
there is more problems, pls let me know.
This rises the question: what is the price for not using connection pools.
Who is paying: the sqlserver or the business layer on the IIS server or both?
regards
Jens Chr
If the users are constantly in/out of the server, you can still benefit from
using connection pooling. It's just that each user will have their own
connection pool. (Set the max connections in the pool to 2, and set the pool
timeout up to 10-20 minutes -or longer.)
Of course, this will require a bit more memory on the IIS box, and a bit
more on the SQL Server, but will greatly improve responsiveness.
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
You can't help someone get up a hill without getting a little closer to the
top yourself.
- H. Norman Schwarzkopf
"jcjj" <jcjj@.discussions.microsoft.com> wrote in message
news:CD85E9F3-21BE-42B6-AC23-EDBF0234C713@.microsoft.com...
> What is the performance cost of not using connection pool when connecting
> to
> a sqlserver 2005 using windows identity.
> We are planning an application where the users windows identity is
> forwarded
> through all layers all the way into the sqlserver.
> This gives security and auditing advantages.
> But a pros/cons evaluation is always proper to do.
> The cons include: the sqlserver connection pool cannot be used...(?) If
> there is more problems, pls let me know.
> This rises the question: what is the price for not using connection pools.
> Who is paying: the sqlserver or the business layer on the IIS server or
> both?
> regards
> Jens Chr

Connection Pool in SQLserver 2005

What is the performance cost of not using connection pool when connecting to
a sqlserver 2005 using windows identity.
We are planning an application where the users windows identity is forwarded
through all layers all the way into the sqlserver.
This gives security and auditing advantages.
But a pros/cons evaluation is always proper to do.
The cons include: the sqlserver connection pool cannot be used...(?) If
there is more problems, pls let me know.
This rises the question: what is the price for not using connection pools.
Who is paying: the sqlserver or the business layer on the IIS server or both
?
regards
Jens ChrIf the users are constantly in/out of the server, you can still benefit from
using connection pooling. It's just that each user will have their own
connection pool. (Set the max connections in the pool to 2, and set the pool
timeout up to 10-20 minutes -or longer.)
Of course, this will require a bit more memory on the IIS box, and a bit
more on the SQL Server, but will greatly improve responsiveness.
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
You can't help someone get up a hill without getting a little closer to the
top yourself.
- H. Norman Schwarzkopf
"jcjj" <jcjj@.discussions.microsoft.com> wrote in message
news:CD85E9F3-21BE-42B6-AC23-EDBF0234C713@.microsoft.com...
> What is the performance cost of not using connection pool when connecting
> to
> a sqlserver 2005 using windows identity.
> We are planning an application where the users windows identity is
> forwarded
> through all layers all the way into the sqlserver.
> This gives security and auditing advantages.
> But a pros/cons evaluation is always proper to do.
> The cons include: the sqlserver connection pool cannot be used...(?) If
> there is more problems, pls let me know.
> This rises the question: what is the price for not using connection pools.
> Who is paying: the sqlserver or the business layer on the IIS server or
> both?
> regards
> Jens Chrsqlsql

Connection from PHP slower when server is remote

I'm trying to get some clues on why I get (much) slower responses from
my PHP applications when dealing with a remote sql server as opposed to
a local sql server.
Here's my situation:
Server #1
MSSQL 2000 sp4
Windows 2000 Server
PHP4 on IIS5 (CGI)
Server #2
MSSQL 2000 sp4
Windows 2003 Server
PHP4 on IIS6 (CGI)
Server #1 and #2 are on the same local network and the same switch. We
use TCP/IP as the connection protocol and our connections use IPs
instead of domain names.
Server#1PHP --> Server#2 MSSQL = slow
Server#2PHP --> Server#1 MSSQL = slow
Server#1PHP --> Server#1 MSSQL = fast
Server#2PHP --> Server#2 MSSQL = fast
This slower behavior is most noticable when many stored procedures
(10-15) are being executed on one php page. When the database is local
on a page such as this, the response time is around 2 seconds. When the
sql server is remote, the response time is between 8 and 15 seconds.
Does this make sense at all? Does anyone have any ideas of what the
bottleneck could be?
Hi
You may want to look at how your network is performing using an network
monitoring tool and perfmon. A local SQL Server could be using shared memory
which may be more significant on a poorly performing network.
You can also use SQL profiler to compare the times taken to execute the
stored procedures on each of the servers, this will allow you to check if
they are similar or acceptable if they are on different hardware.
Check that indexes are not fragmented and statistics are up to date on each
machine. Make sure that you have either a maintenance plan or some job that
will run periodically to maintain your indexes and statistics.
You may also what to check fragmentation of the physical disc. If you HDD is
very fragmented you may want to stop SQL Server and defrag it.
John
"gharmel" wrote:

> I'm trying to get some clues on why I get (much) slower responses from
> my PHP applications when dealing with a remote sql server as opposed to
> a local sql server.
> Here's my situation:
> Server #1
> MSSQL 2000 sp4
> Windows 2000 Server
> PHP4 on IIS5 (CGI)
> Server #2
> MSSQL 2000 sp4
> Windows 2003 Server
> PHP4 on IIS6 (CGI)
> Server #1 and #2 are on the same local network and the same switch. We
> use TCP/IP as the connection protocol and our connections use IPs
> instead of domain names.
> Server#1PHP --> Server#2 MSSQL = slow
> Server#2PHP --> Server#1 MSSQL = slow
> Server#1PHP --> Server#1 MSSQL = fast
> Server#2PHP --> Server#2 MSSQL = fast
> This slower behavior is most noticable when many stored procedures
> (10-15) are being executed on one php page. When the database is local
> on a page such as this, the response time is around 2 seconds. When the
> sql server is remote, the response time is between 8 and 15 seconds.
> Does this make sense at all? Does anyone have any ideas of what the
> bottleneck could be?
>
|||After more investigation it seems that every query had a connect and
disconnect. The act of connecting/disconnecting seems to be much more
expensive when the SQL server is not local.
|||Hi
Usually connection pooling is used to overcome this.
John
"gharmel" wrote:

> After more investigation it seems that every query had a connect and
> disconnect. The act of connecting/disconnecting seems to be much more
> expensive when the SQL server is not local.
>

Connection from PHP slower when server is remote

I'm trying to get some clues on why I get (much) slower responses from
my php applications when dealing with a remote sql server as opposed to
a local sql server.
Here's my situation:
Server #1
MSSQL 2000 sp4
Windows 2000 Server
PHP4 on IIS5 (CGI)
Server #2
MSSQL 2000 sp4
Windows 2003 Server
PHP4 on IIS6 (CGI)
Server #1 and #2 are on the same local network and the same switch. We
use TCP/IP as the connection protocol and our connections use IPs
instead of domain names.
Server#1PHP --> Server#2 MSSQL = slow
Server#2PHP --> Server#1 MSSQL = slow
Server#1PHP --> Server#1 MSSQL = fast
Server#2PHP --> Server#2 MSSQL = fast
This slower behavior is most noticable when many stored procedures
(10-15) are being executed on one php page. When the database is local
on a page such as this, the response time is around 2 seconds. When the
sql server is remote, the response time is between 8 and 15 seconds.
Does this make sense at all? Does anyone have any ideas of what the
bottleneck could be?Hi
You may want to look at how your network is performing using an network
monitoring tool and perfmon. A local SQL Server could be using shared memory
which may be more significant on a poorly performing network.
You can also use SQL profiler to compare the times taken to execute the
stored procedures on each of the servers, this will allow you to check if
they are similar or acceptable if they are on different hardware.
Check that indexes are not fragmented and statistics are up to date on each
machine. Make sure that you have either a maintenance plan or some job that
will run periodically to maintain your indexes and statistics.
You may also what to check fragmentation of the physical disc. If you HDD is
very fragmented you may want to stop SQL Server and defrag it.
John
"gharmel" wrote:

> I'm trying to get some clues on why I get (much) slower responses from
> my php applications when dealing with a remote sql server as opposed to
> a local sql server.
> Here's my situation:
> Server #1
> MSSQL 2000 sp4
> Windows 2000 Server
> PHP4 on IIS5 (CGI)
> Server #2
> MSSQL 2000 sp4
> Windows 2003 Server
> PHP4 on IIS6 (CGI)
> Server #1 and #2 are on the same local network and the same switch. We
> use TCP/IP as the connection protocol and our connections use IPs
> instead of domain names.
> Server#1PHP --> Server#2 MSSQL = slow
> Server#2PHP --> Server#1 MSSQL = slow
> Server#1PHP --> Server#1 MSSQL = fast
> Server#2PHP --> Server#2 MSSQL = fast
> This slower behavior is most noticable when many stored procedures
> (10-15) are being executed on one php page. When the database is local
> on a page such as this, the response time is around 2 seconds. When the
> sql server is remote, the response time is between 8 and 15 seconds.
> Does this make sense at all? Does anyone have any ideas of what the
> bottleneck could be?
>|||After more investigation it seems that every query had a connect and
disconnect. The act of connecting/disconnecting seems to be much more
expensive when the SQL server is not local.|||Hi
Usually connection pooling is used to overcome this.
John
"gharmel" wrote:

> After more investigation it seems that every query had a connect and
> disconnect. The act of connecting/disconnecting seems to be much more
> expensive when the SQL server is not local.
>