Re: Slow performance after restarting

From: Tom Moreau (tom_at_dont.spam.me.cips.ca)
Date: 03/05/04 

Date: Fri, 5 Mar 2004 10:15:08 -0500

I checked with Microsoft and they were able to reproduce it with SP3a
without the hotfix (Build 818) but the problem disappeared when the hotfix
was applied. What build do you have? 

--
Tom
---------------------------------------------------------------
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"Eric Hirst" <s.p.a.r.k.y.d.o.o.d.l.e.d.o.o@y.a.h.o.o> wrote in message
news:#Wlw7do9DHA.2672@TK2MSFTNGP10.phx.gbl...
OK, I have a (partial) explanation. Our service opens about 9 connections to
SQL Server during its startup cycle. These connections open rapidly when
there's no "orphaned" connection around, but take about 5-8 seconds each
otherwise.  (Since our service stops and starts several hundred times during
our product's daily build/test cycle, this adds up to something we actually
care about.)
Try this one at home:
1) Disconnect all SQL Server clients and restart SQL Server
2) Launch Query Analyzer and connect to SQL Server - notice the time it
takes to connect (fast)
3) Repeat step 2 to verify the amount of time it takes to connect (fast)
4) Close all but one instance of Query Analyzer.
5) Restart SQL Server
6) Repeat step 2. Notice the time it takes to connect (5+ seconds, slow)
7) Launch additional instances of Query Analyzer and verify the connection
time is slow
8) Close the instance of Q.A. that was running prior to the restart in step
5
9) Repeat step 2 - notice that connection time is faster again.
Basically, if there is an orphaned QA window floating around, it takes 5+
seconds to connect and log in. If not, login is almost instantaneous.
Any ideas why SQL Server behaves this way? Is this by design, maybe a
security feature? And -- any way to disable this behavior?
-Eric
P.S. I realize that we may just need to add diagnostics to find out who's
connected when we're shutting down SQL server during our process. That's on
the todo list...
"Eric Hirst" <s.p.a.r.k.y.d.o.o.d.l.e.d.o.o@y.a.h.o.o> wrote in message
news:%23ffO8mn9DHA.328@tk2msftngp13.phx.gbl...
> Yes, the .ldf. Detach/Rename/Re-attach gets me a much smaller transaction
> log (these are all dev boxes, yes), but doesn't seem to affect the
behavior.
>
> Not a lot of disk I/O showing up either. It's more like the server is
> pretending to be MSDE for a while, and pressing it's own "slow down"
button.
>
> Next step is to take all of our products out of the mix and make this
happen
> on a clean system -- divide & conquer...
>
> -Eric
>
> "Tim" <Tim@NoSpam> wrote in message
> news:OFK6WCm9DHA.1804@TK2MSFTNGP12.phx.gbl...
> > Log File? Do you mean transaction log? If this is a dev machine, I would
> > shoot the log files contents.... The fast way is Detach / rename the log
> > file / attach - if the attach fails, rename the log file back and bung
it
> > back on. This is your choice & I appreciate I can't have any idea why
your
> > log file is this size or what you do for backups. However... if this
issue
> > exists with the log file then it may indicate that there is some larger
> > problem under the hood. Perhaps you are starting a v large transaction
and
> > not finishing it? Perhaps exclusive locks are required... (doesn't make
> > sense given the sequence).
> >
> > Have you run perfmon to see what is happening during a slow startup IE
is
> it
> > perhaps doing a lot of Disc IO's on the log file?
> > Also you could try the wininternals utility to see which file (if it is
> IO)
> > is getting hit so hard. Perhaps you could see by disc volume...
> >
> > This doesn't make sense :)
> >
> > - Tim
> >
> >
> >
> > "Eric Hirst" <s.p.a.r.k.y.d.o.o.d.l.e.d.o.o@y.a.h.o.o> wrote in message
> > news:eAS6tXl9DHA.1632@TK2MSFTNGP12.phx.gbl...
> > > I don't think it's a network issue, this happens all on a single box.
> > > (Although I think I get the same effect when the QA client is on a
> > different
> > > box.)
> > >
> > > Shutting off Norton AV doesn't affect it.
> > >
> > > Changing connection pooling settings (60 seconds, 10 seconds, no
> pooling)
> > > has no effect (Thanks Tom, we had not thought about pooling.)
> > >
> > > Not machine dependent, can repro on XP & Win2K, latest SR's, single
> proc,
> > > dual proc, and dual + hyperthreading.
> > >
> > > Next step: look at the log files. They are very large for the db in
> > > question, on the order of .5 to 1.5 GB. But
> > > DBCC SHRINKDATABASE and DBCC SHRINKFILE don't seem to affect the
> behavior
> > > either.
> > >
> > > -Eric
> > >
> > >
> > > "Tim" <Tim@NoSpam> wrote in message
> > > news:%23J6u4Re9DHA.2368@TK2MSFTNGP11.phx.gbl...
> > > > Eric,
> > > >
> > > > If the problem was the other way around, I would be thinking DNS or
> > > perhaps
> > > > WINS issues (NT  / etc).
> > > > May be that'll kick brain cells in the right direction....
> > > >
> > > > Is the ODBC connection right for the network? What does server
network
> > > > config and client network config say?
> > > > Any firewall software in sight? IE Nortons...
> > > >
> > > > - Tim
> > > >
> > > > "Eric Hirst" <s.p.a.r.k.y.d.o.o.d.l.e.d.o.o@y.a.h.o.o> wrote in
> message
> > > > news:#y0cpAb9DHA.1636@TK2MSFTNGP12.phx.gbl...
> > > > > We have a service application which typically starts up in 1-3
> > seconds.
> > > > Most
> > > > > of this time is spent running SQL and DML against SQL Server 2000,
> > SP3,
> > > > via
> > > > > ODBC. The service is registered to depend on SQL Server.
> > > > >
> > > > > When I restart SQL Server, our app sometimes takes 1-3 seconds,
but
> > > > > occasionally takes several minutes. The difference? It's only slow
> > when
> > > > > there is a secondary client, such as Query Analyzer, running
during
> > this
> > > > > process.  That is, I can:
> > > > >
> > > > > (1) Start SQL Server
> > > > > (2) Start Query Analyzer and connect to SQL Server
> > > > > (3) Stop SQL Server
> > > > > (4) Start our service, which runs DML and SQL against SQL Server.
> > > > > (4.1) YAAAAWN Wait several minutes, with the CPU almost idle,
while
> > our
> > > > > service runs its initial DDL and SQL.
> > > > >
> > > > > I can then stop and restart our service several times. In each
case,
> > > it's
> > > > > very slow.  But then I can:
> > > > >
> > > > > (5a) Close Query Analyzer
> > > > > -or-
> > > > > (5b) Execute some SQL in Query Analyzer
> > > > >
> > > > > and suddenly our service jumps back to life, stopping and starting
> > > > snappily
> > > > > in response to the SCM, pushing the CPU hard for a second or two
> each
> > > > time.
> > > > >
> > > > > Any idea what's going on here? Ideally, we'd like to find a
> > programatic
> > > > way
> > > > > to make SQL Server "forget" about Query Analyzer and any other
> client
> > > > > connections whenever it is cycled. Note that this is a test
> > environment
> > > > and
> > > > > we don't actually care if these clients crash or otherwise
> misbehave.
> > > > >
> > > > > Thanks, Eric
> > > > >
> > > > > --
> > > > > Eric Hirst, SSE
> > > > >
> > > > > Sorry, I don't want to test my company's spam filters.
> > > > > Please follow up to newsgroup, or send mail to the
> > > > > following address, which I check occasionally:
> > > > >
> > > > > s-p-a-r-k-y-d-o-o-d-l-e-d-o-o
> > > > > y-a-h-o-o
> > > > > c-o-m
> > > > >
> > > > >
> > > >
> > > >
> > >
> > >
> >
> >
>
>