Intermittent Connection Timeout

We have a production SQL Server Enterprise x64 on bare metal running 4
instances. The instances are not limited in resource usage in any shape or
form. They can grab as much memory, processor, etc. as they please. We
have consolidated a lot of our applications requiring Database on this server
(database wise). I have a web application that is using a database on one of
the instances. Most of the time when the SQL Server is behaving, the most
complex query for this particular application will run under 2 seconds
(including rendering back to the browser), but when the SQL Server is
misbehaving, even the simplest query like login stored proc that is only
checking if a user exists in the database will time out or take a very long
time to finish executing. We've run index tuning on the database and it
doesn't seem to mitigate the problem.

Looking at the behavior of the problem sometimes when I'm spamming a query
from the web UI (get me all the records blah blah blah). It might take 15
seconds to finish the first time, but subsequent spam of the same query will
take less than 2 seconds until we wait a while (i.e. 5 minutes) and try
again, and it started slow again. I am thinking it's some sort of a caching
problem on the SQL Server or perhaps memory / disk paging issue. To
complicate the matter further, this is very intermittent and we also suspect
that some of the backup tasks are what might be causing this to happen as
well as we're trying to track a time-based pattern on when the problems are
happening.

Performance monitoring sort of confirm that they are a lot of disk paging
activities taken place.

Before we consolidated our applications into this one giant sql cluster,
this kind of problem never happened.

I am at my wit's end trying to figure out this problem. Any idea?

--
Jimmy Chandra, MCSD.NET, Certified ScrumMaster

My guess - The 4 instances combined are grabbing more memory than is
physically on the computer. This causes the other instances to start paging.
The solution might be to use the lock pages in memory option.
Our advice on that has changed a few times over the last couple of years.
Currently the Books Online topic :Enabling Memory Support for Over 4 GB of
Physical Memory" says: "Although it is not required, we recommend locking
pages in memory when using 64-bit operating systems."
The topic "How to: Enable the Lock Pages in Memory Option (Windows)"
describes how to configure that.
Alternatively, consider setting max memory levels for each instance so
memory is not over obligated. In fact some reasonable limits for max and min
memory for each instance is probably a good idea when running 4 instances.
If the memory configuration doesn't resolve your performance problem you
might have processor contention as well and may need to use processor
affinity masks to separate the instances.
--
Rick Byham (MSFT), SQL Server Books Online
This posting is provided "AS IS" with no warranties, and confers no rights.

"Jimmy Chandra" wrote in message
news:B58CCF95-3376-4101-8551-5C9E7C10E6ED-at-microsoft.com...
> We have a production SQL Server Enterprise x64 on bare metal running 4
> instances. The instances are not limited in resource usage in any shape
> or
> form. They can grab as much memory, processor, etc. as they please. We
> have consolidated a lot of our applications requiring Database on this server
> (database wise). I have a web application that is using a database on one
> of
> the instances. Most of the time when the SQL Server is behaving, the most
> complex query for this particular application will run under 2 seconds
> (including rendering back to the browser), but when the SQL Server is
> misbehaving, even the simplest query like login stored proc that is only
> checking if a user exists in the database will time out or take a very
> long
> time to finish executing. We've run index tuning on the database and it
> doesn't seem to mitigate the problem.
>
> Looking at the behavior of the problem sometimes when I'm spamming a query
> from the web UI (get me all the records blah blah blah). It might take 15
> seconds to finish the first time, but subsequent spam of the same query
> will
> take less than 2 seconds until we wait a while (i.e. 5 minutes) and try
> again, and it started slow again. I am thinking it's some sort of a
> caching
> problem on the SQL Server or perhaps memory / disk paging issue. To
> complicate the matter further, this is very intermittent and we also
> suspect
> that some of the backup tasks are what might be causing this to happen as
> well as we're trying to track a time-based pattern on when the problems
> are
> happening.
>
> Performance monitoring sort of confirm that they are a lot of disk paging
> activities taken place.
>
> Before we consolidated our applications into this one giant sql cluster,
> this kind of problem never happened.
>
> I am at my wit's end trying to figure out this problem. Any idea?
>
> --
> Jimmy Chandra, MCSD.NET, Certified ScrumMaster

One more point. I think 64-bit SQL Server can only lock pages in memory when
using the Enterprise or Developer editions. Not the Standard Edition.
--
Rick Byham (MSFT), SQL Server Books Online
This posting is provided "AS IS" with no warranties, and confers no rights.

"Rick Byham, (MSFT)" wrote in message
news:8B9015B4-685A-402B-8588-970DDF17F58F-at-microsoft.com...
> My guess - The 4 instances combined are grabbing more memory than is
> physically on the computer. This causes the other instances to start
> paging.
> The solution might be to use the lock pages in memory option.
> Our advice on that has changed a few times over the last couple of years.
> Currently the Books Online topic :Enabling Memory Support for Over 4 GB of
> Physical Memory" says: "Although it is not required, we recommend locking
> pages in memory when using 64-bit operating systems."
> The topic "How to: Enable the Lock Pages in Memory Option (Windows)"
> describes how to configure that.
> Alternatively, consider setting max memory levels for each instance so
> memory is not over obligated. In fact some reasonable limits for max and
> min memory for each instance is probably a good idea when running 4
> instances.
> If the memory configuration doesn't resolve your performance problem you
> might have processor contention as well and may need to use processor
> affinity masks to separate the instances.
> --
> Rick Byham (MSFT), SQL Server Books Online
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
>
> "Jimmy Chandra" wrote in message
> news:B58CCF95-3376-4101-8551-5C9E7C10E6ED-at-microsoft.com...
>> We have a production SQL Server Enterprise x64 on bare metal running 4
>> instances. The instances are not limited in resource usage in any shape
>> or
>> form. They can grab as much memory, processor, etc. as they please. We
>> have consolidated a lot of our applications requiring Database on this server
>> (database wise). I have a web application that is using a database on
>> one of
>> the instances. Most of the time when the SQL Server is behaving, the
>> most
>> complex query for this particular application will run under 2 seconds
>> (including rendering back to the browser), but when the SQL Server is
>> misbehaving, even the simplest query like login stored proc that is only
>> checking if a user exists in the database will time out or take a very
>> long
>> time to finish executing. We've run index tuning on the database and it
>> doesn't seem to mitigate the problem.
>>
>> Looking at the behavior of the problem sometimes when I'm spamming a
>> query
>> from the web UI (get me all the records blah blah blah). It might take
>> 15
>> seconds to finish the first time, but subsequent spam of the same query
>> will
>> take less than 2 seconds until we wait a while (i.e. 5 minutes) and try
>> again, and it started slow again. I am thinking it's some sort of a
>> caching
>> problem on the SQL Server or perhaps memory / disk paging issue. To
>> complicate the matter further, this is very intermittent and we also
>> suspect
>> that some of the backup tasks are what might be causing this to happen as
>> well as we're trying to track a time-based pattern on when the problems
>> are
>> happening.
>>
>> Performance monitoring sort of confirm that they are a lot of disk paging
>> activities taken place.
>>
>> Before we consolidated our applications into this one giant sql cluster,
>> this kind of problem never happened.
>>
>> I am at my wit's end trying to figure out this problem. Any idea?
>>
>> --
>> Jimmy Chandra, MCSD.NET, Certified ScrumMaster
>

Thanks Rick, we'll give this a try.
--
Jimmy Chandra, MCSD.NET, Certified ScrumMaster


"Rick Byham, (MSFT)" wrote:

> One more point. I think 64-bit SQL Server can only lock pages in memory when
> using the Enterprise or Developer editions. Not the Standard Edition.
> --
> Rick Byham (MSFT), SQL Server Books Online
> This posting is provided "AS IS" with no warranties, and confers no rights.
>
> "Rick Byham, (MSFT)" wrote in message
> news:8B9015B4-685A-402B-8588-970DDF17F58F-at-microsoft.com...
> > My guess - The 4 instances combined are grabbing more memory than is
> > physically on the computer. This causes the other instances to start
> > paging.
> > The solution might be to use the lock pages in memory option.
> > Our advice on that has changed a few times over the last couple of years.
> > Currently the Books Online topic :Enabling Memory Support for Over 4 GB of
> > Physical Memory" says: "Although it is not required, we recommend locking
> > pages in memory when using 64-bit operating systems."
> > The topic "How to: Enable the Lock Pages in Memory Option (Windows)"
> > describes how to configure that.
> > Alternatively, consider setting max memory levels for each instance so
> > memory is not over obligated. In fact some reasonable limits for max and
> > min memory for each instance is probably a good idea when running 4
> > instances.
> > If the memory configuration doesn't resolve your performance problem you
> > might have processor contention as well and may need to use processor
> > affinity masks to separate the instances.
> > --
> > Rick Byham (MSFT), SQL Server Books Online
> > This posting is provided "AS IS" with no warranties, and confers no
> > rights.
> >
> > "Jimmy Chandra" wrote in message
> > news:B58CCF95-3376-4101-8551-5C9E7C10E6ED-at-microsoft.com...
> >> We have a production SQL Server Enterprise x64 on bare metal running 4
> >> instances. The instances are not limited in resource usage in any shape
> >> or
> >> form. They can grab as much memory, processor, etc. as they please. We
> >> have consolidated a lot of our applications requiring Database on this server
> >> (database wise). I have a web application that is using a database on
> >> one of
> >> the instances. Most of the time when the SQL Server is behaving, the
> >> most
> >> complex query for this particular application will run under 2 seconds
> >> (including rendering back to the browser), but when the SQL Server is
> >> misbehaving, even the simplest query like login stored proc that is only
> >> checking if a user exists in the database will time out or take a very
> >> long
> >> time to finish executing. We've run index tuning on the database and it
> >> doesn't seem to mitigate the problem.
> >>
> >> Looking at the behavior of the problem sometimes when I'm spamming a
> >> query
> >> from the web UI (get me all the records blah blah blah). It might take
> >> 15
> >> seconds to finish the first time, but subsequent spam of the same query
> >> will
> >> take less than 2 seconds until we wait a while (i.e. 5 minutes) and try
> >> again, and it started slow again. I am thinking it's some sort of a
> >> caching
> >> problem on the SQL Server or perhaps memory / disk paging issue. To
> >> complicate the matter further, this is very intermittent and we also
> >> suspect
> >> that some of the backup tasks are what might be causing this to happen as
> >> well as we're trying to track a time-based pattern on when the problems
> >> are
> >> happening.
> >>
> >> Performance monitoring sort of confirm that they are a lot of disk paging
> >> activities taken place.
> >>
> >> Before we consolidated our applications into this one giant sql cluster,
> >> this kind of problem never happened.
> >>
> >> I am at my wit's end trying to figure out this problem. Any idea?
> >>
> >> --
> >> Jimmy Chandra, MCSD.NET, Certified ScrumMaster
> >
>

I asked for a clarification from someone here at MS and received comments:
If enabled and max server memory is not set on instances it may cause an
instance to grab/lock the memory and starve the OS from its normal operation
which will cause the system to be unresponsive. Now question becomes what to
set the “max server memory” to. As a minimum they should probably have 1 or
2 GB available to the OS (thus sum of all max’es should not exceed amount of
physical memory – 1 or 2 Gb).

Another interesting question about this case is to see if the instances are
getting paged out or if there is paging activity due to something else.
Since they have 4 of them and most likely 4 of them do not have max server
memory set, setting this alone may help them.

They can see if there are any messages in SQL error log that indicate that
instances are paged out. Or, they can take a look at sys.dm_os_ring_buffers
for system health events of RING_BUFFER_SCHEDULER_MONITOR ring buffer. This
will help determining if the instance is paging at the time the problem
happens.

You can also recommend perf dashboard available for SQL 2005 that would also
look at this info when the problem happens (alternatively they can manually
pull the queries from dashboard if they would prefer to do so).
--
Rick Byham (MSFT), SQL Server Books Online
This posting is provided "AS IS" with no warranties, and confers no rights.

"Jimmy Chandra" wrote in message
news46B88B6-063C-4E0B-B9DF-1F1B73E346BE-at-microsoft.com...
> Thanks Rick, we'll give this a try.
> --
> Jimmy Chandra, MCSD.NET, Certified ScrumMaster
>
>
> "Rick Byham, (MSFT)" wrote:
>
>> One more point. I think 64-bit SQL Server can only lock pages in memory
>> when
>> using the Enterprise or Developer editions. Not the Standard Edition.
>> --
>> Rick Byham (MSFT), SQL Server Books Online
>> This posting is provided "AS IS" with no warranties, and confers no
>> rights.
>>
>> "Rick Byham, (MSFT)" wrote in
>> message
>> news:8B9015B4-685A-402B-8588-970DDF17F58F-at-microsoft.com...
>> > My guess - The 4 instances combined are grabbing more memory than is
>> > physically on the computer. This causes the other instances to start
>> > paging.
>> > The solution might be to use the lock pages in memory option.
>> > Our advice on that has changed a few times over the last couple of
>> > years.
>> > Currently the Books Online topic :Enabling Memory Support for Over 4 GB
>> > of
>> > Physical Memory" says: "Although it is not required, we recommend
>> > locking
>> > pages in memory when using 64-bit operating systems."
>> > The topic "How to: Enable the Lock Pages in Memory Option (Windows)"
>> > describes how to configure that.
>> > Alternatively, consider setting max memory levels for each instance so
>> > memory is not over obligated. In fact some reasonable limits for max
>> > and
>> > min memory for each instance is probably a good idea when running 4
>> > instances.
>> > If the memory configuration doesn't resolve your performance problem
>> > you
>> > might have processor contention as well and may need to use processor
>> > affinity masks to separate the instances.
>> > --
>> > Rick Byham (MSFT), SQL Server Books Online
>> > This posting is provided "AS IS" with no warranties, and confers no
>> > rights.
>> >
>> > "Jimmy Chandra" wrote in
>> > message
>> > news:B58CCF95-3376-4101-8551-5C9E7C10E6ED-at-microsoft.com...
>> >> We have a production SQL Server Enterprise x64 on bare metal running 4
>> >> instances. The instances are not limited in resource usage in any
>> >> shape
>> >> or
>> >> form. They can grab as much memory, processor, etc. as they please.
>> >> We
>> >> have consolidated a lot of our applications requiring Database on this
>> >> server
>> >> (database wise). I have a web application that is using a database on
>> >> one of
>> >> the instances. Most of the time when the SQL Server is behaving, the
>> >> most
>> >> complex query for this particular application will run under 2 seconds
>> >> (including rendering back to the browser), but when the SQL Server is
>> >> misbehaving, even the simplest query like login stored proc that is
>> >> only
>> >> checking if a user exists in the database will time out or take a very
>> >> long
>> >> time to finish executing. We've run index tuning on the database and
>> >> it
>> >> doesn't seem to mitigate the problem.
>> >>
>> >> Looking at the behavior of the problem sometimes when I'm spamming a
>> >> query
>> >> from the web UI (get me all the records blah blah blah). It might
>> >> take
>> >> 15
>> >> seconds to finish the first time, but subsequent spam of the same
>> >> query
>> >> will
>> >> take less than 2 seconds until we wait a while (i.e. 5 minutes) and
>> >> try
>> >> again, and it started slow again. I am thinking it's some sort of a
>> >> caching
>> >> problem on the SQL Server or perhaps memory / disk paging issue. To
>> >> complicate the matter further, this is very intermittent and we also
>> >> suspect
>> >> that some of the backup tasks are what might be causing this to happen
>> >> as
>> >> well as we're trying to track a time-based pattern on when the
>> >> problems
>> >> are
>> >> happening.
>> >>
>> >> Performance monitoring sort of confirm that they are a lot of disk
>> >> paging
>> >> activities taken place.
>> >>
>> >> Before we consolidated our applications into this one giant sql
>> >> cluster,
>> >> this kind of problem never happened.
>> >>
>> >> I am at my wit's end trying to figure out this problem. Any idea?
>> >>
>> >> --
>> >> Jimmy Chandra, MCSD.NET, Certified ScrumMaster
>> >
>>