C
Chris Smith
I have an ASP.NET 2 web application page that connects to the same remote
SQL7 database 10 times when it loads. It opens the connection, executes a
stored procedure, closes the connection and then repeats the procedure for
the next connection. (There is a genuine reason why the connection object
isn't re-used each time.) However if I look at a Performance Monitor trace
(.NET database counters) and time how long it takes to connect to the
database then connection pooling only seems to work if 'Connection
Reset=false' in the connection string.
The connection string is identical each time and is as follows:
Initial Catalog=MyDatabase; Data Source=MyServer; Connect Timeout=30;
integrated security=SSPI;persist security info=False;Trusted_Connection=Yes;
Application Name=MyApplication; Pooling=true; Connection Reset=true; Min
Pool Size=5
If I just change the value of Connection Reset then the behaviour is as
follows:
TRUE - Performance Monitor shows HardConnectsPerSecond increasing for each
database connection and connecting to the database takes > 0.2 seconds. It
increases every time the page loads and not just the first time, even if
there is only a few seconds delay between page loads.
HardDisconnectsPerSecond also indicates that the connections are dropped as
soon as the database connection is closed.
FALSE - HardConnectsPerSecond remains at zero each time (after first time)
and connecting to the database takes < 5 ms.
Can someone help me understand why Connection Reset causes this behaviour as
if the connection is being disconnected? (I understand the purpose of
resetting the connection and what it does.) I want to set the value to true
but I also need good performance.
SQL7 database 10 times when it loads. It opens the connection, executes a
stored procedure, closes the connection and then repeats the procedure for
the next connection. (There is a genuine reason why the connection object
isn't re-used each time.) However if I look at a Performance Monitor trace
(.NET database counters) and time how long it takes to connect to the
database then connection pooling only seems to work if 'Connection
Reset=false' in the connection string.
The connection string is identical each time and is as follows:
Initial Catalog=MyDatabase; Data Source=MyServer; Connect Timeout=30;
integrated security=SSPI;persist security info=False;Trusted_Connection=Yes;
Application Name=MyApplication; Pooling=true; Connection Reset=true; Min
Pool Size=5
If I just change the value of Connection Reset then the behaviour is as
follows:
TRUE - Performance Monitor shows HardConnectsPerSecond increasing for each
database connection and connecting to the database takes > 0.2 seconds. It
increases every time the page loads and not just the first time, even if
there is only a few seconds delay between page loads.
HardDisconnectsPerSecond also indicates that the connections are dropped as
soon as the database connection is closed.
FALSE - HardConnectsPerSecond remains at zero each time (after first time)
and connecting to the database takes < 5 ms.
Can someone help me understand why Connection Reset causes this behaviour as
if the connection is being disconnected? (I understand the purpose of
resetting the connection and what it does.) I want to set the value to true
but I also need good performance.