C
Chris Smith
I have an ASP.NET 2 web page (running on Windows Server 2003 - IIS6) that,
each time it runs, runs 6 stored procedure queries against 2 SQL 7
databases; 3 against database #1 and then 3 against database #2. Each stored
procedure takes less than 1 second to complete. However, connection pooling
is not working at all and I would appreciate some help.
A SQL trace shows the Audit Login & Audit Logout events but no
sp_reset_connection. (I've not changed the SQL Server settings to disable
sp_reset_connection.) The SPID & ClientProcessID values are the same for
each trace item but SPID may change for subsequent calls of the web page.
Looking at the .NET statistics in Profile Manager shows hard connects &
disconnects but no soft connects & disconnects. However, the statistics show
that connection pools are being created.
The web application is using impersonation and the connection string has
been confirmed to be the same each time.
The web page does the following for each database:
1) Create a SqlConnection instance and open it with the connection string:
"Initial Catalog=MyDatabase; Data Source=MyServer; Connect
Timeout=30;integrated security=SSPI;persist
securityinfo=False;Trusted_Connection=Yes; Application Name=MyApplication"
(We rely on pooling being enabled by default.)
2) Create a SqlAdapter instance and set SqlAdapter.SelectCommand =
MySqlConnection.CreateCommand()
3) Create a DataSet instance and call SqlAdapter.Fill(MySqlAdapter)
4) Call MySqlConnection.Dispose()
each time it runs, runs 6 stored procedure queries against 2 SQL 7
databases; 3 against database #1 and then 3 against database #2. Each stored
procedure takes less than 1 second to complete. However, connection pooling
is not working at all and I would appreciate some help.
A SQL trace shows the Audit Login & Audit Logout events but no
sp_reset_connection. (I've not changed the SQL Server settings to disable
sp_reset_connection.) The SPID & ClientProcessID values are the same for
each trace item but SPID may change for subsequent calls of the web page.
Looking at the .NET statistics in Profile Manager shows hard connects &
disconnects but no soft connects & disconnects. However, the statistics show
that connection pools are being created.
The web application is using impersonation and the connection string has
been confirmed to be the same each time.
The web page does the following for each database:
1) Create a SqlConnection instance and open it with the connection string:
"Initial Catalog=MyDatabase; Data Source=MyServer; Connect
Timeout=30;integrated security=SSPI;persist
securityinfo=False;Trusted_Connection=Yes; Application Name=MyApplication"
(We rely on pooling being enabled by default.)
2) Create a SqlAdapter instance and set SqlAdapter.SelectCommand =
MySqlConnection.CreateCommand()
3) Create a DataSet instance and call SqlAdapter.Fill(MySqlAdapter)
4) Call MySqlConnection.Dispose()