My MS SQL 2005 very slowly response for every first query after few minutes
of servers inactivity. It doesn’t matter if query is very simple or not, if
return many rows or not, etc. Even simple SELECT on table that contains only
few records takes about 15 seconds.

After this first operation every other queries (even very complicated) runs
very fast (results are always returned in milliseconds).

SQL is running on dedicated newly installed server (W2K3 Svr, 2 CPUs, 4GB
RAM, etc.) which is not used by any other users, so when this first
“long-time-running-query� is processing the system is basically doing nothing
(utilization of CPU or HDD is almost 0). There is no difference client
application is connected to server from other workstation or query is run in
SQL Management Studio directly on server.

Do you have any ideas what is the reason of this behavior or how to better
trace source of the problem?

On Jun 18, 12:05pm, Marant <Mar...@discussions.microsoft.com> wrote:
> My MS SQL 2005 very slowly response for every first query after few minutes
> of servers inactivity. It doesn’t matter if query is very simple or not, if
> return many rows or not, etc. Even simple SELECT on table that contains only
> few records takes about 15 seconds.
>
> After this first operation every other queries (even very complicated) runs
> very fast (results are always returned in milliseconds).
>
> SQL is running on dedicated newly installed server (W2K3 Svr, 2 CPUs, 4GB
> RAM, etc.) which is not used by any other users, so when this first
> “long-time-running-query” is processing the system is basically doingnothing
> (utilization of CPU or HDD is almost 0). There is no difference client
> application is connected to server from other workstation or query is runin
> SQL Management Studio directly on server.
>
> Do you have any ideas what is the reason of this behavior or how to better
> trace source of the problem?

Do you have the auto-close option enabled for the database in question?

No. All setting are default (except Recovery Model is changed to Simple) , so
Auto Close option is set to False.


"Tracy McKibben (http://www.realsqlguy.co" wrote:

> On Jun 18, 12:05 pm, Marant <Mar...@discussions.microsoft.com> wrote:
> > My MS SQL 2005 very slowly response for every first query after few minutes
> > of servers inactivity. It doesn’t matter if query is very simple or not, if
> > return many rows or not, etc. Even simple SELECT on table that contains only
> > few records takes about 15 seconds.
> >
> > After this first operation every other queries (even very complicated) runs
> > very fast (results are always returned in milliseconds).
> >
> > SQL is running on dedicated newly installed server (W2K3 Svr, 2 CPUs, 4GB
> > RAM, etc.) which is not used by any other users, so when this first
> > “long-time-running-query� is processing the system is basically doing nothing
> > (utilization of CPU or HDD is almost 0). There is no difference client
> > application is connected to server from other workstation or query is run in
> > SQL Management Studio directly on server.
> >
> > Do you have any ideas what is the reason of this behavior or how to better
> > trace source of the problem?
>
> Do you have the auto-close option enabled for the database in question?
>

Have you tried querying your databases locally where this SQL Server
instance is installed? I mean if you are querying your database remotely
from another machine in your network, you could suspect from a network issue
as well.

--
Ekrem Önsoy



"Marant" <Marant@discussions.microsoft.com> wrote in message
news:700F15CB-5AC2-4797-B0F4-2ED6D07D7643@microsoft.com...
> No. All setting are default (except Recovery Model is changed to Simple) ,
> so
> Auto Close option is set to False.
>
>
> "Tracy McKibben (http://www.realsqlguy.co" wrote:
>
>> On Jun 18, 12:05 pm, Marant <Mar...@discussions.microsoft.com> wrote:
>> > My MS SQL 2005 very slowly response for every first query after few
>> > minutes
>> > of servers inactivity. It doesn’t matter if query is very simple or
>> > not, if
>> > return many rows or not, etc. Even simple SELECT on table that contains
>> > only
>> > few records takes about 15 seconds.
>> >
>> > After this first operation every other queries (even very complicated)
>> > runs
>> > very fast (results are always returned in milliseconds).
>> >
>> > SQL is running on dedicated newly installed server (W2K3 Svr, 2 CPUs,
>> > 4GB
>> > RAM, etc.) which is not used by any other users, so when this first
>> > “long-time-running-query� is processing the system is basically doing
>> > nothing
>> > (utilization of CPU or HDD is almost 0). There is no difference client
>> > application is connected to server from other workstation or query is
>> > run in
>> > SQL Management Studio directly on server.
>> >
>> > Do you have any ideas what is the reason of this behavior or how to
>> > better
>> > trace source of the problem?
>>
>> Do you have the auto-close option enabled for the database in question?
>>

Yes, as I said: "There is no difference if client application is connected to
server from other workstation or query is run in SQL Management Studio
directly on server."

I have also find out that delay for first query is always about 45 seconds
long, and that at the same time Event Viewer shows error: Event ID 560
(object access problem for WinHttpAutoProxySvc). I'm trying right now to
google solution for this problem, but changes that already tested did not
...



"Ekrem Önsoy" wrote:

> Have you tried querying your databases locally where this SQL Server
> instance is installed? I mean if you are querying your database remotely
> from another machine in your network, you could suspect from a network issue
> as well.
>
> --
> Ekrem Önsoy
>
>
>
> "Marant" <Marant@discussions.microsoft.com> wrote in message
> news:700F15CB-5AC2-4797-B0F4-2ED6D07D7643@microsoft.com...
> > No. All setting are default (except Recovery Model is changed to Simple) ,
> > so
> > Auto Close option is set to False.
> >
> >
> > "Tracy McKibben (http://www.realsqlguy.co" wrote:
> >
> >> On Jun 18, 12:05 pm, Marant <Mar...@discussions.microsoft.com> wrote:
> >> > My MS SQL 2005 very slowly response for every first query after few
> >> > minutes
> >> > of servers inactivity. It doesn’t matter if query is very simple or
> >> > not, if
> >> > return many rows or not, etc. Even simple SELECT on table that contains
> >> > only
> >> > few records takes about 15 seconds.
> >> >
> >> > After this first operation every other queries (even very complicated)
> >> > runs
> >> > very fast (results are always returned in milliseconds).
> >> >
> >> > SQL is running on dedicated newly installed server (W2K3 Svr, 2 CPUs,
> >> > 4GB
> >> > RAM, etc.) which is not used by any other users, so when this first
> >> > “long-time-running-query� is processing the system is basically doing
> >> > nothing
> >> > (utilization of CPU or HDD is almost 0). There is no difference client
> >> > application is connected to server from other workstation or query is
> >> > run in
> >> > SQL Management Studio directly on server.
> >> >
> >> > Do you have any ideas what is the reason of this behavior or how to
> >> > better
> >> > trace source of the problem?
> >>
> >> Do you have the auto-close option enabled for the database in question?
> >>
>

> After this first operation every other queries (even very complicated)
> runs
> very fast (results are always returned in milliseconds).

A possible cause is that the buffer cache is cold upon the first execution
so all data must be retrieved from disk. Subsequent executions read data
from memory, which is of course much faster. This theory is consistent your
low CPU symptom but I would expect performance monitor to also report
significant disk/transfers sec. and/or disk bytes/sec.

You mention that you see this behavior even when few rows are returned.
This might indicate an inefficient queries so I suggest you examine the
execution plans for scans and other inefficiencies and perform index/query
tuning as needed. With an efficient path to the data, even queries against
cold cache out to be reasonably fast.

--
Hope this s.

Dan Guzman
SQL Server MVP
http://weblogs.sqlteam.com/dang/

"Marant" <Marant@discussions.microsoft.com> wrote in message
news:640C0F94-6931-4C2A-AB5F-4F77572A0153@microsoft.com...
> My MS SQL 2005 very slowly response for every first query after few
> minutes
> of servers inactivity. It doesn’t matter if query is very simple or not,
> if
> return many rows or not, etc. Even simple SELECT on table that contains
> only
> few records takes about 15 seconds.
>
> After this first operation every other queries (even very complicated)
> runs
> very fast (results are always returned in milliseconds).
>
> SQL is running on dedicated newly installed server (W2K3 Svr, 2 CPUs, 4GB
> RAM, etc.) which is not used by any other users, so when this first
> “long-time-running-query� is processing the system is basically doing
> nothing
> (utilization of CPU or HDD is almost 0). There is no difference client
> application is connected to server from other workstation or query is run
> in
> SQL Management Studio directly on server.
>
> Do you have any ideas what is the reason of this behavior or how to better
> trace source of the problem?
>

You could test the cold server theory by dumping the buffers and/or procedure
cache

dbcc dropcleanbuffers
dbcc freeproccache

Paul


"Dan Guzman" wrote:

> > After this first operation every other queries (even very complicated)
> > runs
> > very fast (results are always returned in milliseconds).
>
> A possible cause is that the buffer cache is cold upon the first execution
> so all data must be retrieved from disk. Subsequent executions read data
> from memory, which is of course much faster. This theory is consistent your
> low CPU symptom but I would expect performance monitor to also report
> significant disk/transfers sec. and/or disk bytes/sec.
>
> You mention that you see this behavior even when few rows are returned.
> This might indicate an inefficient queries so I suggest you examine the
> execution plans for scans and other inefficiencies and perform index/query
> tuning as needed. With an efficient path to the data, even queries against
> cold cache out to be reasonably fast.
>
> --
> Hope this s.
>
> Dan Guzman
> SQL Server MVP
> http://weblogs.sqlteam.com/dang/
>
> "Marant" <Marant@discussions.microsoft.com> wrote in message
> news:640C0F94-6931-4C2A-AB5F-4F77572A0153@microsoft.com...
> > My MS SQL 2005 very slowly response for every first query after few
> > minutes
> > of servers inactivity. It doesn’t matter if query is very simple or not,
> > if
> > return many rows or not, etc. Even simple SELECT on table that contains
> > only
> > few records takes about 15 seconds.
> >
> > After this first operation every other queries (even very complicated)
> > runs
> > very fast (results are always returned in milliseconds).
> >
> > SQL is running on dedicated newly installed server (W2K3 Svr, 2 CPUs, 4GB
> > RAM, etc.) which is not used by any other users, so when this first
> > “long-time-running-query� is processing the system is basically doing
> > nothing
> > (utilization of CPU or HDD is almost 0). There is no difference client
> > application is connected to server from other workstation or query is run
> > in
> > SQL Management Studio directly on server.
> >
> > Do you have any ideas what is the reason of this behavior or how to better
> > trace source of the problem?
> >
>