May not be a SQL question but you may have the answer..

Every once in a while our SQL Servers hiccup or take a blip in the form of
blocking,etc.. and when this happens our Web and Application servers begin
to misbehave and our system engineers begin to reboot these servers to
resolve the issue. They do not have a solid answer, but I think these
servers try to open up more connections and probably consume all threads and
memory( not too sure, just speculating)..
Have you seen this kind of behavior and if so, why is it per your
environment and what have you done to avoid those reboots by making the Web
and app servers smart to know what to do when there are SQL failures? Its
one thing to have SQL conk off which is costly, but then we spend another
good portion of our time rebooting every other non SQL Server too. Sounds
like some bad way of programming but I want to take your inputs to my
programmers/architects.
Thank you
>From your question, i assume that you will never restart SQL Server
services, and only webserver and app's, and if thats the case i have a
strong feeling that SQL Server is not causing the issue. I had seen in
very rare scenerios where SQL will use all the 255 (Default) worker
threads, and if its with memory, by restarting other services, SQL
will not release its memory. Since you are speculating, this info is
just for your understanding.
Also you might want to collect more symptoms as to what is happening
on SQL when you are seeing the issue, like CPU spike, from
sysprocesses check and see if any extensive blocking (though these
might not be related, but getting all symptoms will always give you
answers )
|||Dinu. thanks for the response.
I do know whats happening to SQL, but the problem exacerbates to where the
web and app servers suffer and need to be restarted and want to know how can
we make the app servers smart
<dinu_babu@.hotmail.com> wrote in message
news:1185077813.592904.45890@.e9g2000prf.googlegrou ps.com...
> services, and only webserver and app's, and if thats the case i have a
> strong feeling that SQL Server is not causing the issue. I had seen in
> very rare scenerios where SQL will use all the 255 (Default) worker
> threads, and if its with memory, by restarting other services, SQL
> will not release its memory. Since you are speculating, this info is
> just for your understanding.
> Also you might want to collect more symptoms as to what is happening
> on SQL when you are seeing the issue, like CPU spike, from
> sysprocesses check and see if any extensive blocking (though these
> might not be related, but getting all symptoms will always give you
> answers )
>
|||You are probably on the right track with your speculation. Connections and
commands take longer when there's a network or SQL problem. Without a
governor mechanism in place, client requests can accumulate to the point of
resource starvation and an application restart or server reboot is often the
easiest and fastest way to get things running again.
The effect of network/SQL issues can be mitigated by setting a limit on
concurrent application requests. This assumes applications are coded in
such a way as to be resilient following a problem. For example, a
middle-tier app with a persistent database connections will need to be smart
enough to gracefully retry database connections instead of requiring a
restart. Exception handling needs to be especially thorough and tested
accordingly.
Hope this helps.
Dan Guzman
SQL Server MVP
"Hassan" <hassan@.hotmail.com> wrote in message
news:e77sQu$yHHA.4476@.TK2MSFTNGP06.phx.gbl...
> Every once in a while our SQL Servers hiccup or take a blip in the form of
> blocking,etc.. and when this happens our Web and Application servers begin
> to misbehave and our system engineers begin to reboot these servers to
> resolve the issue. They do not have a solid answer, but I think these
> servers try to open up more connections and probably consume all threads
> and memory( not too sure, just speculating)..
> Have you seen this kind of behavior and if so, why is it per your
> environment and what have you done to avoid those reboots by making the
> Web and app servers smart to know what to do when there are SQL failures?
> Its one thing to have SQL conk off which is costly, but then we spend
> another good portion of our time rebooting every other non SQL Server too.
> Sounds like some bad way of programming but I want to take your inputs to
> my programmers/architects.
> Thank you
>
|||This is what I was looking for to hear and the more I can obtain knowledge
on this area, the better.
Is there any whitepaper or anything on this subject that I can forward to
the devs. Basically they need to know how to make the apps more resilient
and have the right exception handling.
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:C973F7EA-C997-4169-98F5-5ABA025D7772@.microsoft.com...
> You are probably on the right track with your speculation. Connections
> and commands take longer when there's a network or SQL problem. Without a
> governor mechanism in place, client requests can accumulate to the point
> of resource starvation and an application restart or server reboot is
> often the easiest and fastest way to get things running again.
> The effect of network/SQL issues can be mitigated by setting a limit on
> concurrent application requests. This assumes applications are coded in
> such a way as to be resilient following a problem. For example, a
> middle-tier app with a persistent database connections will need to be
> smart enough to gracefully retry database connections instead of requiring
> a restart. Exception handling needs to be especially thorough and tested
> accordingly.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Hassan" <hassan@.hotmail.com> wrote in message
> news:e77sQu$yHHA.4476@.TK2MSFTNGP06.phx.gbl...
>
|||> Is there any whitepaper or anything on this subject that I can forward to
> the devs. Basically they need to know how to make the apps more resilient
> and have the right exception handling.
Here's an article that discusses database mirroring failover retry but the
same retry pattern can be used in general:
http://www.microsoft.com/technet/prodtechnol/sql/bestpractice/implappfailover.mspx
There are many articles on exception handling best practices. Here are a
couple recent ones for .Net.
http://www.ftponline.com/vsm/2007_06/magazine/columns/csharp/default.aspx
http://www.codeproject.com/dotnet/exceptionbestpractices.asp
Hope this helps.
Dan Guzman
SQL Server MVP
"Hassan" <hassan@.hotmail.com> wrote in message
news:uUA94ZJzHHA.748@.TK2MSFTNGP04.phx.gbl...
> This is what I was looking for to hear and the more I can obtain knowledge
> on this area, the better.
> Is there any whitepaper or anything on this subject that I can forward to
> the devs. Basically they need to know how to make the apps more resilient
> and have the right exception handling.
> "Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
> news:C973F7EA-C997-4169-98F5-5ABA025D7772@.microsoft.com...
>
|||On Sat, 21 Jul 2007 17:59:31 -0700, "Hassan" <hassan@.hotmail.com>
wrote:

>Every once in a while our SQL Servers hiccup or take a blip in the form of
>blocking,etc.. and when this happens our Web and Application servers begin
>to misbehave and our system engineers begin to reboot these servers to
>resolve the issue.
If SQL Server connections are being blocked, as shown by EXEC sp_who,
rebooting is an extreme response. The first thing to check for is a
long running task that is holding locks. If there is one connection
doing the blocking then KILL is quicker and more selective than a
reboot. Of course it is best to figure out what that connection is
doing before KILLing it, DBCC INPUTBUFFER can be a start for that.
Roy Harvey
Beacon Falls, CT
|||Roy,
I meant we reboot our web and application servers and not out SQL Servers.
"Roy Harvey" <roy_harvey@.snet.net> wrote in message
news:hr89a3drmd47bfsvqhn0kkhf13ar92dvto@.4ax.com...
> On Sat, 21 Jul 2007 17:59:31 -0700, "Hassan" <hassan@.hotmail.com>
> wrote:
>
> If SQL Server connections are being blocked, as shown by EXEC sp_who,
> rebooting is an extreme response. The first thing to check for is a
> long running task that is holding locks. If there is one connection
> doing the blocking then KILL is quicker and more selective than a
> reboot. Of course it is best to figure out what that connection is
> doing before KILLing it, DBCC INPUTBUFFER can be a start for that.
> Roy Harvey
> Beacon Falls, CT
|||On Mon, 23 Jul 2007 09:37:11 -0700, "Hassan" <hassan@.hotmail.com>
wrote:

>Roy,
>I meant we reboot our web and application servers and not out SQL Servers.
So that drops all the connections from the client side. It still
seems like it would be worth looking at KILLing a specific connection
rather than taking the application off line long enough for a reboot.
Of course it requires much more knowledge.
Roy Harvey
Beacon Falls, CT
|||Our web and app servers go bonkers and thats what I am trying to figure..
why they do so when there is a small blip on the SQL server ?
"Roy Harvey" <roy_harvey@.snet.net> wrote in message
news:iop9a3hb9ca2ql3a6hgh3dasp20p33duqf@.4ax.com...
> On Mon, 23 Jul 2007 09:37:11 -0700, "Hassan" <hassan@.hotmail.com>
> wrote:
>
> So that drops all the connections from the client side. It still
> seems like it would be worth looking at KILLing a specific connection
> rather than taking the application off line long enough for a reboot.
> Of course it requires much more knowledge.
> Roy Harvey
> Beacon Falls, CT