Hi,
We have a 64bit SQL Server 16GB Memory running on Windows 2003 x64. This is
one of our main business database servers. At this time we have an issue
where the server will stop allowing new connections in, the client will
receive a timeout (instantly) and from .NET the stack is
at System.Data.ProviderBase.DbConnectionPool.GetConnection(DbConnection
owningObject)
at
System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnection
owningConnection)
at
System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection
outerConnection, DbConnectionFactory connectionFactory)
at System.Data.SqlClient.SqlConnection.Open()
If the client keeps trying eventually they might get in but other connection
attempts continue to fail. This lasts for about 10 minutes usually then goes
away.
Occassionally when this happens the .NET AppDomain is unloaded and a new one
is created. However this does not happen all the time and appears to be a
side-effect not the cause.
What I really would like some help on is what performance counters might be
worth looking at.
This is an upgrade of a server with 4GB 32bit SQL Server on Win2003 32 bit
which never had these issues. No code has changed, there are 5 CLR Stored
procedures which have not changed.
Any pointers would be much appreciated.
Thanks
James WrenThis sounds suspiciously like a full connection pool. Check out my
whitepaper on managing the connection pool on my site.
http://www.betav.com/sql_server_magazine.htm
I would write an application to monitor the pool or simply setup a
performance counter view on the counters exposed by ADO.NET. I expect that
you've missed a Connection Close in an exception handler.
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
INETA Speaker
www.betav.com/blog/billva
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________
Visit www.hitchhikerguides.net to get more information on my latest book:
Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition)
and Hitchhiker's Guide to SQL Server 2005 Compact Edition (EBook)
----
---
"James Wren" <JamesWren@.discussions.microsoft.com> wrote in message
news:50D6E4FA-173A-4751-A2EC-A9F8549C7356@.microsoft.com...
> Hi,
> We have a 64bit SQL Server 16GB Memory running on Windows 2003 x64. This
> is
> one of our main business database servers. At this time we have an issue
> where the server will stop allowing new connections in, the client will
> receive a timeout (instantly) and from .NET the stack is
> at System.Data.ProviderBase.DbConnectionPool.GetConnection(DbConnection
> owningObject)
> at
> System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnection
> owningConnection)
> at
> System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection
> outerConnection, DbConnectionFactory connectionFactory)
> at System.Data.SqlClient.SqlConnection.Open()
> If the client keeps trying eventually they might get in but other
> connection
> attempts continue to fail. This lasts for about 10 minutes usually then
> goes
> away.
> Occassionally when this happens the .NET AppDomain is unloaded and a new
> one
> is created. However this does not happen all the time and appears to be a
> side-effect not the cause.
> What I really would like some help on is what performance counters might
> be
> worth looking at.
> This is an upgrade of a server with 4GB 32bit SQL Server on Win2003 32 bit
> which never had these issues. No code has changed, there are 5 CLR Stored
> procedures which have not changed.
>
> Any pointers would be much appreciated.
> Thanks
> James Wren|||I agree that sounds like a probable cause, however there are two issues I am
not sure about. However in my experience filling the connection pool affects
the client not the SQL Server.
1. No code has changed at all just the build of SQL Server and the fact it
is SP2 - so this would mean the behaviour of SQL Server w.r.t the code has
changed to cause this issue now.
2. Also there is a large amount of code called from the main website,
internal software and application servers, so I'm not sure it will be very
easy at all to track down a wrongly closed connection.
3. If, say, a rogue web server component did not close connections surely
this would not effect a .NET application connecting to the SQL Server from
another machine.
Thank you for your response.
James Wren
"William (Bill) Vaughn" wrote:
> This sounds suspiciously like a full connection pool. Check out my
> whitepaper on managing the connection pool on my site.
> http://www.betav.com/sql_server_magazine.htm
> I would write an application to monitor the pool or simply setup a
> performance counter view on the counters exposed by ADO.NET. I expect that
> you've missed a Connection Close in an exception handler.
> --
> ____________________________________
> William (Bill) Vaughn
> Author, Mentor, Consultant
> Microsoft MVP
> INETA Speaker
> www.betav.com/blog/billva
> www.betav.com
> Please reply only to the newsgroup so that others can benefit.
> This posting is provided "AS IS" with no warranties, and confers no rights
.
> __________________________________
> Visit www.hitchhikerguides.net to get more information on my latest book:
> Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition)
> and Hitchhiker's Guide to SQL Server 2005 Compact Edition (EBook)
> ----
---
> "James Wren" <JamesWren@.discussions.microsoft.com> wrote in message
> news:50D6E4FA-173A-4751-A2EC-A9F8549C7356@.microsoft.com...
>
>|||The fact that the server is calling out to other code could certainly lead
to client-side connection pool issues as the application threads queue up
waiting for the server to respond. I'm assuming this is an ASP application
where this sort of behavior is most common. Remember if the client (the ASP
app) request is not handled, when another app instance is started it can't
reuse the connection but has to create another. If this continues the pool
can fill quite quickly. I would monitor the pooled connections and see if
this is not the case.
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
INETA Speaker
www.betav.com/blog/billva
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________
Visit www.hitchhikerguides.net to get more information on my latest book:
Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition)
and Hitchhiker's Guide to SQL Server 2005 Compact Edition (EBook)
----
---
"James Wren" <JamesWren@.discussions.microsoft.com> wrote in message
news:7E6E797A-9481-4871-AF86-DFCAA043A9E9@.microsoft.com...[vbcol=seagreen]
>I agree that sounds like a probable cause, however there are two issues I
>am
> not sure about. However in my experience filling the connection pool
> affects
> the client not the SQL Server.
> 1. No code has changed at all just the build of SQL Server and the fact it
> is SP2 - so this would mean the behaviour of SQL Server w.r.t the code has
> changed to cause this issue now.
> 2. Also there is a large amount of code called from the main website,
> internal software and application servers, so I'm not sure it will be very
> easy at all to track down a wrongly closed connection.
> 3. If, say, a rogue web server component did not close connections surely
> this would not effect a .NET application connecting to the SQL Server from
> another machine.
> Thank you for your response.
> James Wren
>
> "William (Bill) Vaughn" wrote:
>|||Thank you for the follow up. As I had indicated the client does start to fai
l
to contact SQL Server but alll clients will stop at the same time indicating
a server side issue not a client side issue. SQL Server prevents new
connecitons being created. SQL Server allows unlimited connections. I can
understand a client filling its connection pool but why do all clients,
anywhere, including SQL Management Studio all fail to connect to SQL Server.
The SQL Server perf counters themselves show only 300 logical connections.
Regards
James
"William (Bill) Vaughn" wrote:
> The fact that the server is calling out to other code could certainly lead
> to client-side connection pool issues as the application threads queue up
> waiting for the server to respond. I'm assuming this is an ASP application
> where this sort of behavior is most common. Remember if the client (the AS
P
> app) request is not handled, when another app instance is started it can't
> reuse the connection but has to create another. If this continues the pool
> can fill quite quickly. I would monitor the pooled connections and see if
> this is not the case.
> --
> ____________________________________
> William (Bill) Vaughn
> Author, Mentor, Consultant
> Microsoft MVP
> INETA Speaker
> www.betav.com/blog/billva
> www.betav.com
> Please reply only to the newsgroup so that others can benefit.
> This posting is provided "AS IS" with no warranties, and confers no rights
.
> __________________________________
> Visit www.hitchhikerguides.net to get more information on my latest book:
> Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition)
> and Hitchhiker's Guide to SQL Server 2005 Compact Edition (EBook)
> ----
---
> "James Wren" <JamesWren@.discussions.microsoft.com> wrote in message
> news:7E6E797A-9481-4871-AF86-DFCAA043A9E9@.microsoft.com...
>
>|||Ok, so if it's a server issue, we need to look at what's bogging down the
server. Is it hosing Reporting Services or acting as a print server? If the
server bogs down, the ASP clients have to wait and their pool(s) grow
accordingly as new requests come in. Eventually, the server crowbars and
won't accept any more connections due to the fact it has no CPU cycles to
launch another thread--even for SSMS. I would at least open up PerfMon to
monitor what's going on AFA the CPU use and other critical counters such as
logical connections etc. The connection pools are not on the server unless
IIS (which is hosting the ASP applications) is there too. In that case, the
application code itself might be bogging down the host system.
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
INETA Speaker
www.betav.com/blog/billva
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________
Visit www.hitchhikerguides.net to get more information on my latest book:
Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition)
and Hitchhiker's Guide to SQL Server 2005 Compact Edition (EBook)
----
---
"James Wren" <JamesWren@.discussions.microsoft.com> wrote in message
news:8E8970C6-84BB-4B05-AB71-A7E4381147EB@.microsoft.com...[vbcol=seagreen]
> Thank you for the follow up. As I had indicated the client does start to
> fail
> to contact SQL Server but alll clients will stop at the same time
> indicating
> a server side issue not a client side issue. SQL Server prevents new
> connecitons being created. SQL Server allows unlimited connections. I can
> understand a client filling its connection pool but why do all clients,
> anywhere, including SQL Management Studio all fail to connect to SQL
> Server.
> The SQL Server perf counters themselves show only 300 logical connections.
> Regards
> James
> "William (Bill) Vaughn" wrote:
>|||Thanks again for the response. The SQL Server is extremely powerful for what
we need, it is brand new and based on analysis of running the old version
(4GB 32 bit). The CPU remains very low all the time even though there are a
very large number of queries primarily from the website, however there are
numerous background services and client applications running on many other
machines.
The Logical connections never goes above 380 and when this problem happens
existing connections in Management studio work but are VERY slow in returnin
g
any results - yet the CPU is very low (typically under 105)
The problem did not happen at all today but happened 5 times in 6 hours
yesterday. Nothing else runs on the SQL Server, we do not use Reporting
Services, Analysis Services or Notification Services in SQL Server from this
machine.
Yes if the server is getting slow then the clients would back up, fill their
pools and then throw the "No connection available from pool" error but it is
not obvious in anyway that the SQL Server is getting bogged down. It has 16G
B
of RAM, the main database is 20GB but this worked fine on a 32bit 4GB machin
e.
The issue stills revolves around if code is constant then what has changed
the behaviour when using 64bit SQL Server with 16GB RAM on Service Pack 2?
Regards
James
"William (Bill) Vaughn" wrote:
> Ok, so if it's a server issue, we need to look at what's bogging down the
> server. Is it hosing Reporting Services or acting as a print server? If th
e
> server bogs down, the ASP clients have to wait and their pool(s) grow
> accordingly as new requests come in. Eventually, the server crowbars and
> won't accept any more connections due to the fact it has no CPU cycles to
> launch another thread--even for SSMS. I would at least open up PerfMon to
> monitor what's going on AFA the CPU use and other critical counters such a
s
> logical connections etc. The connection pools are not on the server unless
> IIS (which is hosting the ASP applications) is there too. In that case, th
e
> application code itself might be bogging down the host system.
> --
> ____________________________________
> William (Bill) Vaughn
> Author, Mentor, Consultant
> Microsoft MVP
> INETA Speaker
> www.betav.com/blog/billva
> www.betav.com
> Please reply only to the newsgroup so that others can benefit.
> This posting is provided "AS IS" with no warranties, and confers no rights
.
> __________________________________
> Visit www.hitchhikerguides.net to get more information on my latest book:
> Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition)
> and Hitchhiker's Guide to SQL Server 2005 Compact Edition (EBook)
> ----
---
> "James Wren" <JamesWren@.discussions.microsoft.com> wrote in message
> news:8E8970C6-84BB-4B05-AB71-A7E4381147EB@.microsoft.com...
>
>
