SQL Server Performance Forum – Threads Archive

Connection pooling (IIS 6 + SQL 2000)

On heavy load to our webserver we got around 80-100 connections from the webbserver to the SQL-server. We use ODBC (System DSN) and connection pooling is activated. Is it normal to have that many connections or do we have a problem with the ASP code?

If you are not closing your connections correctly the numbers can creep up. But if it’s a heavily used app then it could be normal behaviour as well. Which cursor mode you use in ADO can also affect this. Avoid dynamic server side cursors in a web environment. Try to use client side disconnected record sets as much as possible.

What version of the ODBC drivers do you have? How is your app set up? Do you have the app with a common user that connects to the database, or is each user connecting as themselves? MeanOldDBA
[email protected] When life gives you a lemon, fire the DBA.

Drivers: MDAC 2.6 SQL Server ODBC-driver, Version 2000.85.1025.00 — A typical ASP-page on our server looks like this: <% MM_SQLDB_STRING = "dsn=SqlDB;uid=XXXXX<img src=’/community/emoticons/emotion-4.gif’ alt=’;p’ />wd=XXXXX;" %> <% set Give_First_Page = Server.CreateObject("ADODB.Command") Give_First_Page.ActiveConnection = MM_SQLDB_STRING Give_First_Page.CommandText = "dbo.Give_First_Page" Give_First_Page.CommandType = 4 Give_First_Page.CommandTimeout = 0 Give_First_Page.Prepared = true Give_First_Page.Parameters.Append Give_First_Page.CreateParameter("@RETURN_VALUE", 3, 4) set first_page = Give_First_Page.Execute %> <% set Give_Latest_Intern_Updates = Server.CreateObject("ADODB.Command") Give_Latest_Intern_Updates.ActiveConnection = MM_SQLDB_STRING Give_Latest_Intern_Updates.CommandText = "dbo.Give_Latest_Intern_Updates" Give_Latest_Intern_Updates.CommandType = 4 Give_Latest_Intern_Updates.CommandTimeout = 0 Give_Latest_Intern_Updates.Prepared = true Give_Latest_Intern_Updates.Parameters.Append Give_Latest_Intern_Updates.CreateParameter("@RETURN_VALUE", 3, 4) set update_data = Give_Latest_Intern_Updates.Execute %> <% set Give_Chat_Nr_Online_Data = Server.CreateObject("ADODB.Command") Give_Chat_Nr_Online_Data.ActiveConnection = MM_SQLDB_STRING Give_Chat_Nr_Online_Data.CommandText = "dbo.Give_Chat_Nr_Online_Data" Give_Chat_Nr_Online_Data.CommandType = 4 Give_Chat_Nr_Online_Data.CommandTimeout = 0 Give_Chat_Nr_Online_Data.Prepared = true Give_Chat_Nr_Online_Data.Parameters.Append Give_Chat_Nr_Online_Data.CreateParameter("@RETURN_VALUE", 3, 4) set chat_data = Give_Chat_Nr_Online_Data.Execute %> <% = first_page.Fields.Item("data").Value %> <% = update_data.Fields.Item("time").Value %> <% = chat_data.Fields.Item("nr").Value %> — We do not close the recordsets but is this really a problem?

If you’re using ADO, then why do you use a DSN? All parameters from the DSN can be included in the connect string you use for ADO, so I would use set up a function that returns the connect string, and call that from your ASP pages.

Agree with Adriaan. Also, you need to upgrade to at least MDAC 2.7 SP1 Refresh. This needs to be done on all sides of the connection. A lot of issues have been fixed with connectivity, pooling, etc since the version you are running. MeanOldDBA
[email protected] When life gives you a lemon, fire the DBA.

We have been trying to use a DSN-less connection:
MM_SQLDB_STRING = "Provider=MSDASQL; Driver={SQL Server}; Server=xxx.xxx.xxx.xxx,yyyy; Database=SqlDB; Uid=XXXXX; Pwd=XXXXX;"
But this gave us the same number of connections as with DSN!
We have also been trying to connect directly through the OLEDB layer but this gave us some strange errors when running some queries. Is it better to update to MDAC 2.8 directly?

MDAC 2.8 won’t hurt. I haven’t taken that plunge yet, and I’m comfortable with MDAC 2.7 SP1 Refresh. I don’t really think this is a good way to setup connections for an application. I’ll let some of the experts on this area reply though. Definitely upgrade the MDAC though. MeanOldDBA
[email protected] When life gives you a lemon, fire the DBA.

quote:Originally posted by Kalle We have been trying to use a DSN-less connection:
MM_SQLDB_STRING = "Provider=MSDASQL; Driver={SQL Server}; Server=xxx.xxx.xxx.xxx,yyyy; Database=SqlDB; Uid=XXXXX; Pwd=XXXXX;"
But this gave us the same number of connections as with DSN!
We have also been trying to connect directly through the OLEDB layer but this gave us some strange errors when running some queries. Is it better to update to MDAC 2.8 directly?

Using the DSN means you’re invoking overhead. The DSN is a flat text file with exactly the same parameters as you would put in a connection string: if you set up the connection for each call to your ASP pages, the DSN file would have to be read each time, which will be slower than retrieving a fixed string through a function. I don’t know ASP in any detail, but there must be a way to set up an ADO connection and keep it alive for a session, and just re-use that connection. That way you should be able to reduce the number of connections.

Yes, I know that DSN invokes overhead, but the main problem is the connections to the SQL-server.
The ASP-code is generated by Macromedia Dreamweaver and everytime we add a query to a webpage, Dreamweaver creates a new ADODB.Command object. Maybe this is the problem. For example if we have a webpage with three ADODB.Command objects and 20 users is askin for the webpage at the same time, this will create 3×20 connections from the webserver to the SQL-server. Is this right? I’m not sure, but i don’t think it’s possible to execute parallell queries in one connection. And connections pooling is only used for reusing already made connections.
Or am I wrong?

Well, the web server will create 3×20 ADODB.Command objects but I’m not sure if that implies ADO will try to create as many ADODB.Connection objects as well. Plus SQL Server will try to pool the connections anyway, so as long as all ADODB.Command objects are using the exact same connection string I’m pretty sure there will be some degree of pooling. You may want to consider developing web pages directly in ASP (preferably ASP.NET – old ASP uses Variant type variables only which results in one big mess if you ask me).

Try returning the data as disconnected recordsets using ADO client side cursors. Example below. Function GetAuthor(authorID) Dim rs, cmd ’Init objects Set rs = Server.CreateObject("adodb.Recordset") Set cmd = Server.CreateObject("adodb.Command") ’Open connection cmd.ActiveConnection = GetConnectionString() ’Specify stored procedure cmd.CommandText = "dbo.GetAuthorByAuthorID" cmd.CommandType = adCmdStoredProc ’Specify parameters cmd.Parameters.Append cmd.CreateParameter("@authorID", adInteger, adParamInput, , authorID) ’Execute the query with clientside cursor rs.CursorLocation = adUseClient rs.Open cmd, , adOpenStatic, adLockReadOnly ’Disconnect the recordset and clean up Set cmd.ActiveConnection = Nothing Set cmd = Nothing ’Return the recordset Set GetAuthor = rs End Function Function GetConnectionString() GetConnectionString = "Provider=sqloledb;Data Source=MyServer;Initial Catalog=MyDB;User Id=MyUser<img src=’/community/emoticons/emotion-4.gif’ alt=’;P’ />assword=MyPass;App=MyApp" End Function

This might be the problem!
But isn’t the server side cursor a forward-only and read-only cursor, sometimes called a firehose cursor, by default?
And isn’t these firehose cursors recommended to use instead of the client side cursor?

A bit about ADO cursors:
http://www.sql-server-performance.com/forum/topic.asp?TOPIC_ID=5997 Fire hose is standard but the connection will be open to the SQL server as long as you loop and do rs.MoveNext etc in your ASP page, using SQL Server side resources until you close the connection. A fire hose cursor can not be shared by other connections so there will be one for each connection until they are closed in your ASP page. With a client side cursor, ADO’s own cursor engine will be used. This is located on the client side (web server memory in this case) and it’s properties are very similar to a static server side cursor. You can do rs.RecordCount for example which you can not do on a standard server side fire hose cursor (since it doesn’t know in advance how many rows it will be fetching). The data will be fetched in fire hose mode here as well and populate the client side cursor all in one quick go. If you then disconnect it as well you no longer use the connection to the server and you can work with the client side record set and loop and do as many rs.MoveNext you want without using SQL Server resources. This will use resources on the web server instead. Client side disconnected record sets are often the way to go in a web environment. You put less stress on the SQL Server and load can be distributed if you use multiple load balanced web servers. The times you do not want to use client side is when you return very large amount of data. You do not want to fill a client side cursor with millions of rows. This will take a lot of time and consume memory. On the other hand this is not often the case in a web environment.

Is there any easy way to convert all queries to use client side instead of server side without rewriting too much code? Is it possible to use something like this or are there any other good solutions? <%
set connection = Server.CreateObject("ADODB.Connection")
connection.ConnectionString = "xxxxx"
connection.CursorLocation = 3 ‘adUseClient
connection.Open set Give_First_Page = Server.CreateObject("ADODB.Command")
Give_First_Page.ActiveConnection = connection
Give_First_Page.CommandText = "dbo.Give_First_Page"
Give_First_Page.CommandType = 4 ‘adCmdStoredProc
Give_First_Page.CommandTimeout = 0
Give_First_Page.Prepared = true
Give_First_Page.Parameters.Append Give_First_Page.CreateParameter("@RETURN_VALUE", 3, 4)
set first_page = Give_First_Page.Execute connection = Nothing
%>

Should work though in your case where you use the cmd.Execute and not rs.Open I’m not sure if you need to set the Give_First_Page.ActiveConnection = Nothing to disconnect or if connection = Nothing is enough.

The connections to the SQL-server dropped to about 40-50 on heavy load when using client side cursors. But from time to time we get these errors in the webserver logs: |8|80004005|[Microsoft][ODBC_SQL_Server_Driver][DBNETLIB]SQL_Server_does_not_exist_or_access_denied. I thought that this was because we had to many connections to the SQL-server but that may not be the case?

This sounds more like a network problem. Check that name resolution works between all servers 24/7 and that connectivity is ok.

Actually another cause of:
[DBNETLIB]SQL Server does not exist or access denied. can be a heavy loaded web server. We had an issue with this recently. The web server (Windows 2003) kept 5000 TCP sessions open to the SQL port 1433 (in time_wait state) even if only about 40 where active against the SQL Server. It didn’t close them fast enough and eventually you run out of ports. See: The TIME_WAIT State’s Effect on IIS Performance
http://www.windowsitpro.com/Web/Article/ArticleID/23276/Web_23276.html Unable to Connect from TCP Ports Above 5000
http://support.microsoft.com/support/kb/articles/q196/2/71.asp We had to inrease the max ports to 20000 and lower the TCP session termination to 30 seconds instead of the default 240 seconds to get around this. To check if you have this proplem run this in a command prompt:
netstat -na In our case we had 40 established session on the SQL server but 5000 in TIME_WAIT state on the web server.

]]>