CLR With Thread Pooling Using Context Connection=true
Mar 15, 2008
Hi
I have a DLL deployed in SQL2005 which uses Thread pool. Since DLL exists in DB we can directly access it using context connection = true.
In my case the following works fine, if i execute the same out side Threadpool
SqlCommand cmd = new SqlCommand("select 1...", connection);
SqlContext.Pipe.ExecuteAndSend(cmd);
When i execute inside threadpool, either by passing opened connection object, or tried to open connection to "context connection = true" i am getting following error.
System.InvalidOperationException: The requested operation requires a Sql Server execution thread. The current thread was started by user code or other non-Sql Server engine code. at Microsoft.SqlServer.Server.SmiContextFactory.GetCurrentContext() at Microsoft.SqlServer.Server.SqlContext.get_CurrentContext() at Microsoft.SqlServer.Server.SqlContext.get_Pipe() at hiqidty4.hiqidty4.Identify_Search(Object identifyin)
Anyone have any experience with running MS SQL Server web applications using Java servlets with connection pooling? Any settings/tricks/traps that we should be aware of?
Our basic problem is that under CERTAIN senarios (which we haven`t been able to strictly identify yet), it appears that each browser client has to wait for the last client to finish - only shared locks are being applied to the database.
I see the connection showing up and in the next second it is gone.
The connectionstring i have is
Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;pooling=true;Min Pool Size=2;Max Pool Size=50;Connection Lifetime=120;Initial Catalog=test;Data Source=TESTMACHINESQLEXPRESS;
And then in the log, the log grows almost every second 3 entiries !! "Login succeeded for user 'TESTMACHINEUser'. Connection: trusted...." "Login succeeded for user 'TESTMACHINEUser'. Connection: trusted...." "Login succeeded for user 'TESTMACHINEUser'. Connection: trusted...."
So the problem is that pooling does not seem to work. I dont see any connections being held for more than 1 second
I am looking for some guidelines for SQL Server connection pooling. I would like to know the number of recomended connections for a SQL server based upon number of CPU's and or memory and I want to monitor or check the connection pooling? I would also like to know any guidelines around SQL server connection pooling.
Hi,I'm using ASP 3.0 and ADO with a web based MS SQLServer 2000 database.Webpage forms collect criteria for SQL queries (dynamic SQL?). These are mostlySelect queries to return inventory lists. Inventory details are posted bymeans of Insert Update and Delete queries. All connections use the sameconnection string. A new connection is opened each time the data is accessedand closed and set to null/nothing afterwards. If site traffic becomes highwill connection pooling be handled automaticaly (by MTS)?Many thanksRay Allison
I have created an instance of sqlconnection and using transaction i am executing 5 insert statements (Database - SQL Server 2005). I have the finally block where i call the dispose method of the transaction and close method of the connection object.I often get the below mentioned error:
Exception Type: System.InvalidOperationException
Message: Timeout expired. The timeout period elapsed prior to obtaining a connection from the pool. This may have occurred because all pooled connections were in use and max pool size was reached.
Hi I have a Community Server Forum in my site with a 2500 hit per day and I confused with Connection Pooling. Is it necessary for my site to use Connection Pooling? If it€™s necessary, what is the best value for following items in order to maximize performance?
Pooling Connection Reset Enlist Max Pool Size Min Pool Size Connection Lifetime Thank you very much.
If I have two ASP.NET applications installed on the same machine, and both are using the same connection string to access SQL Server 2005. Are they share the same pool or use separated pools? I need to be sure that how many connections would be connected to my database server. For example, the max. connection pool size is 100, if both Web app share the same pool, then there're may be maximum 100 connections to database. It they use separated pools, then connections may grow to 200.
I've got a function that returns a connection to a DataReader:static public SqlDataReader GetDR(string sql, string connString) { SqlConnection myConn = new SqlConnection(connString); SqlCommand cmd = new SqlCommand(sql, myConn); SqlDataReader dr;
myConn.Open(); dr = cmd.ExecuteReader();
return dr; }I call it like so:SqlDataReader dr = PAS.GetSqlDataReader.GetDR(sql, connString);And I'm always careful to close the connection when I'm done using it via dr.Close(). Except I just started noticing that when I run my app andstart doing a fair amount of work around it - I very quickly get a timeout that I've run out of connections. D'oh! I've triple checked my code andverified that I always remember to close my connections. It's there - every single time!So I'm obviously missing something. Should I not be using a function like GetDR since it doesn't close the connection within the function?
Hello, I have a question about Connection Pooling. I use the following Code on three defferent machines with the same result. No machine has any special configuration. OS is Win XP Pro Sql Server 2005. One Server has Windows 2003 Web Edition with Sql Express 2005. 1 for(int i=0; i<500;i++) { 3 SqlConnection con = new SqlConnection("Data Source=127.0.0.1\DB;Initial Catalog=Test;User Id=sa;Password=secret;Connection Lifetime=30;Pooling=true;"); 4 SqlCommand cmd = new SqlCommand("SELECT TOP 1 * FROM test", con); 5 con.Open(); 6 IDataReader reader = cmd.ExecuteReader(); 7 reader.Close(); 8 con.Close(); 9 con.Dispose(); 10 } 11 When monitoring The Counter logins/sec in Perfmon logins/sec getsup to 500 per second when i call this method (everytime!). MSDN says when logins/sec does not stay zero after initial call, pooling does not work. Can you tryy it on your machine an tell me what experience you make? Does Pooling really does not work?
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
There's always been a lot of confusion about what prevents connection pooling -- when I was learning .Net 1.0, I was told that any differences in connection strings -- even cosmetic ones -- would prevent pooling (or, more accuratlely, that they could only share connections with identically formatted connections). For example, conn string1 =
Data Source=myServerAddress;Initial Catalog=myDataBase;User Id=myUsername;Password=myPassword;
I want to know what is min size ,max size,Connection Timeout and Connection Leak in connection pooling i have searched it on Google i found this below answer but still i have a confusion Please explain me in simple language with example"Setting an initial size other than zero can reduce the start time of the client applications because it ensures that a certain number of connections are always available. That could be an issue at around 09:00 in the morning when all users start their applications and connect to the server at the same time.
Setting the maximum size of the connection pool is usefull if you suspect that you have blocking problems due to the large number of concurrent queries. In this case you may be able to reduce blocking by limiting the maximum size of the connection pool. Of course, the pool already limits the number of concurrent connections, so you may never encounter this problem"
I have a web application. In this app i have one class genral for database transections. I create new object of this class on every page in page load event & dispose it on UnLoad event. After sometime this app gives connection pooling exception.
Means connection are not closed, but i disposed the object like
I was asking in one Technical Test, the question is not exactly like, but i want post the outline of that(because i don't remember the exact one)
Suppose u have connection with Sql Server database, and let say some 30 users are already connected (through some aspx page), if one more new request is coming, is it created new path or it will reallocate or destroy existing connection poll and assign??
Can anybody tell me?
if possible please post some of Basic and Adavanced questions regarding Sql Server (with aspx)
I am using VB5 SP3 and ADO 2.5 to connect to SQL Server 7 SP2 and I'm trying to test connection pooling.
I open three ADO Connection objects as follows:
Set con = New Connection con.Open "Provider=SQLOLEDB;Data Source=(local);Initial Catalog=pubs;User ID=sa;Password=;OLE DB Services=-1"
The second and third connections open virtually instantly, which seems to indicate that pooling is working, however, when I list processes in SQL Server it shows one process for each separate ADO connection. I thought the whole point of pooling was to make all these ADO connections map to the one physical connection and therefore one process. What is going on?
Just to make sure, I also tried it with RDO 2.0 and ODBC connection pooling and I get exactly the same results.
I'm very worried about this because it could result in our using up twenty or so SQL Server connection licences per workstation instead of one.
Hello:How does one pool a connection object? I have the same application runningon 4 different machines, all connecting to the same server/SQL Server 2000instance for DB activity.Some posts have mentioned pooling the connection objects to reduce overhead,but how do I do that for the 4 separate computers.Appreciate any response.Regards,Ryan Kennedy
Hi,Is there a way to control the connection pools using SQL Server 2000? Iknow how to alter the connection string to dothis(http://msdn.microsoft.com/library/d...ry/en-us/cpguide/html/cpconconnectionpoolingforsqlservernetdataprovider. asp), but I amlooking for a setting in SQL Server that allows me ultimate control over allconnections to the DB. eg: I'd like all connection pools to timeout after5min of inactivity, set the maximum pool size, etc. I am assuming that thereis some part of the GUI that allows this, or maybe even some (ugh) registryediting! ;-)Thanks,Lance
We have an large SQL database being accessed by a C# application. Each hit on the database opens a connection then closes it when done. We are currently experiencing errors when more than 20 users access the database at one time. One of the things I am wondering is if we are using connection pooling. From BOL I am lead to believe connection pooling is off by default. Can I turn on connection pooling as a default in SQL Server or do I pass 'pooling=true' as a parameter on each connection string?
I use IIS 6.0 with php and the authentication method is the €śintegration windows authentication€?
I have a problem i d€™ like to use odbc connections pooling to access my database, however the authenticated user must be member of the administrator group to use this feature.
I tried without success to activate ODBC connection pooling for an ASP (classic) application running on Windows Server 2003. I used the ODBC administration applet and created the registry value described in "INFO: How to Enable Connection Pooling in an ODBC Application".
What are the steps to correctly activate ODBC connection pooling with IIS 6?
I would like to find out, what is the actual formula to set the right amount of connection pool size in a conn string. In my scenario, when 400 concurrent users are logged into the website, SQL Server starts timing out. I am thinking the following connection string should fix it, but I would appreciate if I can get some tips to optimize connection pooling. I really need to find out the criteria or the formula to set connection pool size in a connection string. I got the following conn string over the web, by just searching for optimized connection pools, but I am not sure if this will work. Also please explain max and min, and how what criteria do i use to set that size, I mean what does in the following example min pool size of 5 and max pool size of 60 means. Thanks.
conn.ConnectionString = "integrated security=SSPI;SERVER=YOUR_SERVER;DATABASE=YOUR_DB_NAME;Min Pool Size=5;Max Pool Size=60;Connect Timeout=2;"; // Notice Connection Timeout set to only two seconds!
I would like to find out, what is the actual formula to set the right amount of connection pool size in a conn string. In my scenario, when 400 concurrent users are logged into the website, SQL Server starts timing out. I am thinking the following connection string should fix it, but I would appreciate if I can get some tips to optimize connection pooling. I really need to find out the criteria or the formula to set connection pool size in a connection string. I got the following conn string over the web, by just searching for optimized connection pools, but I am not sure if this will work. Also please explain max and min, and how what criteria do i use to set that size, I mean what does in the following example min pool size of 5 and max pool size of 60 means. Thanks.
conn.ConnectionString = "integrated security=SSPI;SERVER=YOUR_SERVER;DATABASE=YOUR_DB_NAME;Min Pool Size=5;Max Pool Size=60;Connect Timeout=2;"; // Notice Connection Timeout set to only two seconds!
I posted the long version of this over on the ADO.NET framework group: http://groups.google.com/group/microsoft.public.dotnet.framework.adonet/browse_thread/thread/51597180ec77f543/70e804505c2df829#70e804505c2df829
The short version is:
In .NET 2.0 SP1, Microsoft have deprecated the "Reset Connection" connection string API which, when set to false, stops connection pooling from calling "sp_reset_connection" on connections drawn from the pool. We need this for our DB security: we "EXECUTE AS" on connection open and since we cannot tell when it will close (passed to 3rd parties and there is no useful "closing" event), we must "REVERT" the connection next time it is drawn from pool e.g. OPENS. We must do the revert before sp_reset_connection is called or SQL Server kills the connection (security violation) - we accomplish this by keeping track of connections' SPIDs. We implemented this using EL DAAB + custom SQL provider which handled connection open event. Now it is entirely broken!
This all worked fine in .NET 2.0 RTM when we could control when sp_reset_connection was called (could delay until after revert). Now I've run out of options and will have to rewrite the entire security infrastructure. What I'd really like is a decent "closing" event on SqlConnection (statechanged will fire after close - too late!). As I asked in the full post above.. how are we expected to build scalable apps with the features that Microsoft tell us to use when they don't work together?
How can I REVERT my connection before it is sent back to the pool?
If any of this isn't clear, please see above linked post.
My total test page is shown below. I monitor the connections by SP_WHO2. Without the second call, connection pooling seems to be working, ie I refresh my browser repeately but the number of connections as seen from SP_WHO2 does not increase. However, if I have the second call, every time I refresh the page at the browser, the number of connections increases by one. This is obviously not acceptable in a real world application. I tried both Integrated Authentication (with no impersonation) and using a hardcoded service account. Both have the exact same results. In fact this test is not about multi-user yet, it is the same single user just refreshing the same page. May I know what have I done wrong? All the documentation from Microsoft says close the connection after using it. In the case of SqlDataSource how do I close the connection? Thanks <%@ Page Language="C#" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<script runat="server"> protected void Page_Load(object sender, EventArgs e) { SqlDataSource1.SelectCommand = "Select ID from Master"; System.Data.SqlClient.SqlDataReader reader = (System.Data.SqlClient.SqlDataReader)SqlDataSource1.Select(DataSourceSelectArguments.Empty); if (reader.HasRows && reader.Read()) Label1.Text = reader["ID"].ToString(); SqlDataSource1.Dispose();
//second call: read from another table SqlDataSource1.SelectCommand = "Select Name from Students"; reader = (System.Data.SqlClient.SqlDataReader)SqlDataSource1.Select(DataSourceSelectArguments.Empty); if (reader.HasRows && reader.Read()) Label1.Text += reader["Name"].ToString();
We have 2 main database servers, with our clients accessing either one DB or another via a website click. Our application, in the background, chooses randomly, which server to connect to. This approach achieves simulated load balancing.
For efficiency purposes, we also have the application manage connection pooling.
Recently, we realized that our current "simulated load balancing" is not good enough, as it blindly chooses which server to connect to, without considering the current workload of that server. We are now considering to acquire a load balancer [i.e. hardware piece] so that the load is balanced more wisely, by it. Our only concern is connection pooling. Will connection pooling still work with the new, proposed topology [i.e. applications sit to the left of the load balancer and the db servers to the right of it, with the load balancer in the middle].
If anyone has had any experience with this and could share or give advice, it would be appreciated.
I have an ASP (IIS 4.0) based website fronting a SQL Server 2000database. I am trying to avoid using dynamic SQL for queries, and I amconsidering prepared statements as an alternative...All the advice I have seen strongly suggests that connection objectsshould not be explicitly cached, either at the application or sessionlevel. Therefore, I am creating a connection for each page (sameconnection string), and closing it immediately after use. By myunderstanding, this is utilising the MTS connection pool and is themost resource efficient method to use.However, I have seen a number of comments to the effect that connectionpooling and prepared statements do not mix.For example..."Avoid creating prepared statements or temporary stored procedures.These create server resources attached to a specific connection.Because you might not get the same connection from the pool the nexttime you ask for one, you lose the benefit of creating them in thefirst place. Worse, because the connection might reside in the pool fora long time, it can get clogged up with the useless but expensiveserver resources created by each "user" that creates them." - RobMacdonald(http://msdn.microsoft.com/library/d...00/html/Rob.asp)Is this still the case? This article was written in 2000, and I washoping there was now some workaround for this. Are there any SQLServer/driver level settings that can fix or alleviate this problem.Are there any other options besides prepared statements or storedprocedures? Thanks.
Hi All, I've written a script component that essentially uses a connection manager to connect to a database and executes a stored proc to return a retail price for a product sold in a store. This works fine with small volumes of data. (50 or so executes) When I use larger volumes of data (4500 executes, eventually will be 2.5 million) I get connection pooling errors as follows:-
Timeout expired. The timeout period elapsed prior to obtaining a connection from the pool. This may have occurred because all pooled connections were in use and max pool size was reached.
This I can fix on the 4500 data volumes by setting the connection pool to 10000.
My question is... is this the correct way to fix the error, or is there an underlying problem that needs to be sorted? My script code is as follows.
Public Overrides Sub SalesData_ProcessInputRow(ByVal Row As SalesDataBuffer)
Dim InSightConnectionManager As IDTSConnectionManager90 = Me.Connections.InSight
Dim InSightConnection As SqlConnection = CType(InSightConnectionManager.AcquireConnection(Nothing), SqlConnection)
How does database connection pooling work for multiple "copies" of the same web application on the same server? My IIS 5 win2K server has 32 sites, all pointed to the same code. Each site responds to a different IP/URL(www.a.com, www.b.com, etc.) for SSL reasons. DB is SQL 2000. Based on perf stats, as a new site spins up, a new pool is created. So when I hit site 1, I see 10 pooled connections. Site 2 bumps it up to 20, etc. However, I don't see it go up to 320, so it isn't linear. BTW, I'm trying to build a case for having 1 site/application that all URL's point to in order to conserve memory, connection pooling, caching, etc. Any help is greatly appreciated. -Allen
I'm getting the following error in my v1.1 page: Timeout expired. The timeout period elapsed prior to obtaining a connection from the pool. This may have occurred because all pooled connections were in use and max pool size was reached. Not sure why, since I'm trying to close the connection right when I'm done, and I've got the timeout set to "30" Select Case DBselected Case "DB8" Try Dim sCon1 As New SqlConnection sCon1.ConnectionString = ConfigurationSettings.AppSettings("db8") Session("DBDDL") = sCon1.ConnectionString.ToString() sCon1.Open() Dim cmd As New SqlCommand("[usp_Security]", sCon1) cmd.CommandType = System.Data.CommandType.StoredProcedure cmd.Parameters.Add(New System.Data.SqlClient.SqlParameter("@IDNumber", System.Data.SqlDbType.NVarChar, 50)) cmd.Parameters("@IDNumber").Value = Session("User") Dim DR As SqlDataReader = cmd.ExecuteReader If DR.HasRows = False Then btnSwitchboard1.Enabled = False lblnotice.Visible = True lblnotice.Text = "The IDNumber, " & strIDNumber & ", doesn't exist in the database for access permission!" Else btnSwitchboard1.Enabled = True lblnotice.Visible = False End If DR.Close() DR = Nothing Catch ex As Exception lblStatus.Text = "Error building IDNumber: " & ex.Message Finally sCon1.Close() End Try Case "DB1" Try Dim sCon1 As New SqlConnection sCon1.ConnectionString = ConfigurationSettings.AppSettings("db1") Session("DBDDL") = sCon1.ConnectionString.ToString() sCon1.Open() Dim cmd As New SqlCommand("[usp_Security]", sCon1) cmd.CommandType = System.Data.CommandType.StoredProcedure cmd.Parameters.Add(New System.Data.SqlClient.SqlParameter("@IDNumber", System.Data.SqlDbType.NVarChar, 50)) cmd.Parameters("@IDNumber").Value = Session("User") Dim DR As SqlDataReader = cmd.ExecuteReader If DR.HasRows = False Then btnSwitchboard1.Enabled = False lblnotice.Visible = True lblnotice.Text = "The IDNumber, " & strIDNumber & ", doesn't exist in the database for access permission!" Else btnSwitchboard1.Enabled = True lblnotice.Visible = False End If DR.Close() DR = Nothing Catch ex As Exception lblStatus.Text = "Error building IDNumber: " & ex.Message Finally sCon1.Close() End Try Case "DB2" Try Dim sCon1 As New SqlConnection sCon1.ConnectionString = ConfigurationSettings.AppSettings("db2") Session("DBDDL") = sCon1.ConnectionString.ToString() sCon1.Open() Dim cmd As New SqlCommand("[usp_Security]", sCon1) cmd.CommandType = System.Data.CommandType.StoredProcedure cmd.Parameters.Add(New System.Data.SqlClient.SqlParameter("@IDNumber", System.Data.SqlDbType.NVarChar, 50)) cmd.Parameters("@IDNumber").Value = Session("User") Dim DR As SqlDataReader = cmd.ExecuteReader If DR.HasRows = False Then btnSwitchboard1.Enabled = False lblnotice.Visible = True lblnotice.Text = "The IDNumber, " & strIDNumber & ", doesn't exist in the database for access permission!" Else btnSwitchboard1.Enabled = True lblnotice.Visible = False End If DR.Close() DR = Nothing Catch ex As Exception lblStatus.Text = "Error building IDNumber: " & ex.Message Finally sCon1.Close() End Try Case "DB3" Try Dim sCon1 As New SqlConnection sCon1.ConnectionString = ConfigurationSettings.AppSettings("db3") Session("DBDDL") = sCon1.ConnectionString.ToString() sCon1.Open() Dim cmd As New SqlCommand("[usp_Security]", sCon1) cmd.CommandType = System.Data.CommandType.StoredProcedure cmd.Parameters.Add(New System.Data.SqlClient.SqlParameter("@IDNumber", System.Data.SqlDbType.NVarChar, 50)) cmd.Parameters("@IDNumber").Value = Session("User") Dim DR As SqlDataReader = cmd.ExecuteReader If DR.HasRows = False Then btnSwitchboard1.Enabled = False lblnotice.Visible = True lblnotice.Text = "The IDNumber, " & strIDNumber & ", doesn't exist in the database for access permission!" Else btnSwitchboard1.Enabled = True lblnotice.Visible = False End If DR.Close() DR = Nothing Catch ex As Exception lblStatus.Text = "Error building IDNumber: " & ex.Message Finally sCon1.Close() End Try Case "DB4" Try Dim sCon1 As New SqlConnection sCon1.ConnectionString = ConfigurationSettings.AppSettings("db4") Session("DBDDL") = sCon1.ConnectionString.ToString() sCon1.Open() Dim cmd As New SqlCommand("[usp_Security]", sCon1) cmd.CommandType = System.Data.CommandType.StoredProcedure cmd.Parameters.Add(New System.Data.SqlClient.SqlParameter("@IDNumber", System.Data.SqlDbType.NVarChar, 50)) cmd.Parameters("@IDNumber").Value = Session("User") Dim DR As SqlDataReader = cmd.ExecuteReader If DR.HasRows = False Then btnSwitchboard1.Enabled = False lblnotice.Visible = True lblnotice.Text = "The IDNumber, " & strIDNumber & ", doesn't exist in the database for access permission!" Else btnSwitchboard1.Enabled = True lblnotice.Visible = False End If DR.Close() DR = Nothing Catch ex As Exception lblStatus.Text = "Error building IDNumber: " & ex.Message Finally sCon1.Close() End Try
I have an application that is using ADO connection strings from multiple clients. One site with about 20 users was recently upgraded to a newer version of my application, and the number of connections to the SQL Server I'm told went from about 20 to spiking over 100 from time to time. There were myriad changes to my app over the course of the last year since their prior version of my app. I need to nail down which version introduced a change in connection pooling (?) and I was wondering what tools I should use to monitor that.
My application works with the Connection pooling ODBC. Everything works well, when it data base this down the SQLConnect function finishes steeply my application.
Does anyone know how to make the ODBC Connection Pooling performance counters work? I read http://support.microsoft.com/default.aspx?scid=kb;en-us;216950 to enable the performance counters, but they still all report 0s.
