"Tryfon Gavriel" <tryfon@gtryfon.demon.co.uk> wrote in message
news:bviv5o$hnf$1$8302bc10@news.demon.co.uk...[color=blue]
> Hi there
>
> Thank you for that feedback.
>
> I should have also mentioned the following: There was an unexpected[/color]
database[color=blue]
> server shutdown last night recorded in the w2k event viewer. I had to
> request the database server to be restarted. Once it did it was working[/color]
fine[color=blue]
> for a while, but then I noticed on my ASP error log, the "cannot allocate
> space" errors. It was then that I increased the size, but also put[/color]
auto-grow[color=blue]
> on. It just died within 2 hours. What I then did was take off autogrow,[/color]
and[color=blue]
> restart the database server again.
>
> So basically:-
>
> * I have doubled the size of the tempdb database to 2 gig for data file[/color]
and[color=blue]
> 1 gig for transaction log[/color]
That's a fairly large tempdb. I'd question your design if you really need
that much. But I don't know how many users you're supporting.
But it does indicate that perhaps your transactions are lasting for two
long.
Consider this, let's say (and this is a wild guess here) you're supporting 1
million users. If each one needs 1KB of temp data stored, that would be
about 1 gig of data. However, in theory it should be unusual for all 1
million users to need that data stored at once.
But like I say, I don't know much about the design, but I'd be suspicious
about how long your transactions are being held open.
[color=blue]
> * I have also doubled the size of the Chessworld database (One concern[/color]
here[color=blue]
> is the time it takes the backup the database, but it still seems to be[/color]
able[color=blue]
> to back it up within a few minutes.. a relief :) )[/color]
Note that a backup will not affect database performance. (or at least the
affect is extremely small because of an increased disk I/O. Note that a
backup does NOT stop processing.)
[color=blue]
> * Auto-grow taken off both databases
>
> If the server shuts down in 2 months time, then fine. I will request a
> database server reboot and increase their sizes again.[/color]
Even if the SQL server locks up, simply stopping or worse killing it should
be enough. Rebooting the box should be an absolutel last resort.
[color=blue]
> I cannot have a
> background process re-allocating space, when I have tonnes of players[/color]
online[color=blue]
> playing chess moves (or trying to!), resulting in me having to reboot the
> server. The "cannot allocate" space errors that occured last night have[/color]
now[color=blue]
> stopped.
>[/color]
Understandable.
[color=blue]
> The following may be useful for other ASP/SQL Server developers for[/color]
general[color=blue]
> problem diagnosis: About two weeks ago, I knocked up an ASP admin page to
> monitor the sysprocesses table. This is useful to me in trying to[/color]
understand[color=blue]
> the processes with greatest CPU usage. I ordered it by CPU, but also made[/color]
it[color=blue]
> highlight in red processes which had a last batch time of more than 10
> minutes ago. The idea was to highlight potential processes that could be
> killed. I found the following three particularly useful web references :-
>[/color]
Hmm, can you post thse someplace? Might be interesting to use.
Just as a reference we have databases that had uptimes over over a year.
(only reason we needed to change that was due to finally being able to apply
SP3a.)
[color=blue]
> Kill documentation:
>[/color]
http://msdn.microsoft.com/library/de...kf-kz_1zos.asp[color=blue]
> Tips for handling blocking:
>
http://www.sql-server-performance.com/blocking.asp
> Understanding and resolving blocking problems:
>[/color]
http://support.microsoft.com/default...NoWebContent=1[color=blue]
>
> Which I have put links at the top of my admin page for viewing processes[/color]
:-)[color=blue]
> It also made me paranoid about the background processes going on- hence my
> intuition to turn off the auto-grow tick boxes.
>
> Some insights include - simplification of pages, do seem to lead to
> processes consuming less CPU, and generally a faster site. But also the
> Background processes are highlighted. I think viewing the sysprocesses[/color]
table[color=blue]
> is very useful point of reference, and the reason I started investigating
> it, is because it is mentioned in SQL Serrver 2000 programming book page
> 1081, where it also highlights using the following tools for analysing
> problems:-
>
> a) SHOWPLAN TEXT | ALL
> b) STATISTICS IO
> c) DBCC
> d) Query governer
> e) sp_lock
> f) sysprocesses table
> g) SQL Server Profiler
> (should be listed because detailed): h) Perfmon
>
> Before posting to this excellent group, I had not actually used the SQL
> profiler much at all. I did have admin pages already for sp_lock and[/color]
sp_who.[color=blue]
> But I usually use sp_lock for analysing locks, and ignored the sp_who most
> of the time. The view on sysprocesses is more useful to me because you can
> order by cpu, etc. I now regularly look at the sp_lock page and the
> "processes" page.
>
> I also make use of the ASP error object to generate errors in a log file,
> and my most frequently logged error is now SQL Server related. This means[/color]
I[color=blue]
> can immediately see any bottleneck ASP pages where there is potentially[/color]
bad[color=blue]
> SQL or other issues.[/color]
That's some good stuff.
[color=blue]
>
> Best wishes
> Tryfon
>
>
> "Greg D. Moore (Strider)" <mooregr_deleteth1s@greenms.com> wrote in[/color]
message[color=blue]
> news:Ug0Tb.13391$n62.4463@twister.nyroc.rr.com...[color=green]
> >
> > "Tryfon Gavriel" <tryfon@gtryfon.demon.co.uk> wrote in message
> > news:bvhpk1$7qm$1$8302bc10@news.demon.co.uk...[color=darkred]
> > > Hi Erland and all
> > >
> > > The server has been standing for 2 weeks without a reboot. This has[/color][/color][/color]
been[color=blue]
> a[color=green][color=darkred]
> > > great relief to me. If my solution may help others, the two things I[/color][/color][/color]
did[color=blue][color=green][color=darkred]
> > > were:-
> > >
> > > a) Simplify some of the SQL - taking out some luxury sub-queries off[/color][/color]
> many[color=green][color=darkred]
> > > pages
> > > b) Taking off auto-grow from three of the databases - tempdb, the main
> > > Chessworld db, and master.
> > >[/color]
> > That'll do it right there.
> >
> > Here's a typical scenario:
> >
> > DB growth is set to 10%
> >
> > DB is 100MB...
> >
> > An insert is performed.... limit gets reached. So, now the DB wants to
> > expand.
> >
> > It starts to allocate 10MB.
> >
> > During this time, deletes and updates can generally be performed, but
> > basically any additional inserts will be blocked while the space is
> > allocated. (and any updates or deletes that need to occur on those[/color][/color]
blocked[color=blue][color=green]
> > inserts obviously get blocked.)
> >
> > Now, SQL Server can generally allocate 10MB pretty quick.
> >
> > But now you've got 110MB. Next expansion will be 11MB. Putting you at
> > 121MB. Next one will be 12.1 MB. And this continues.
> >
> > Before you know it, you've got a 10 gig DB trying to allocate 1GB. (and[/color]
> the[color=green]
> > kicker is, it probably only needs 10MB at that point. :-)
> >
> > And of course during this allocation, the DB appears hung.
> >
> > So, I generally try NOT to allow auto-growth, or set it to a fixed[/color][/color]
amount[color=blue][color=green]
> > (like 10MB or 100MB, etc. depending on the size and type of DB).
> >
> > Also, this can occur a lot with transaction logs. Which generally[/color][/color]
means[color=blue][color=green]
> > that no transcation backups are being done. Which on a production DB is
> > almost always a "bad thing".
> >
> > Hmm, given what yu say, I'm guessing that your tempb may be growing a[/color][/color]
lot.[color=blue][color=green]
> > (Since upon restart I believe it'll get resized back to it's original[/color]
> size.)[color=green]
> >
> > This could be a result of a bad design, or simply the result of a[/color]
> necessary[color=green]
> > design.
> >
> > What I'd do is check which DB is growing the most and resize it.
> >
> > The master DB normally should not grow much at all.
> >
> > So it's most likely the tempdb or the chessworld one. (as he states the
> > obvious.)
> >
> >[color=darkred]
> > > I was not exactly sure if it was a) or b) but I have more evidence now[/color][/color][/color]
i[color=blue]
> t[color=green][color=darkred]
> > > was in fact b) that was causing massive slow-downs requiring a reboot
> > > because CPU seems to go unrecoverably to 100%.
> > >
> > > The reason for more evidence, is that today, I finally had a "cannot
> > > allocate space error" being logged. I increased the size of the[/color][/color]
> chessworld[color=green][color=darkred]
> > > db, and the tempdb, and put back the auto-grow on the chessworld db.[/color]
> > Within[color=darkred]
> > > about an hour or two, the symptoms of a big slow-down came back with[/color][/color][/color]
CPU[color=blue][color=green][color=darkred]
> > > 100%.
> > >
> > > I rebooted the database server but have again taken off auto-grow[/color][/color]
> options.[color=green]
> > I[color=darkred]
> > > believe for my site with many concurrent users, the auto-grow is[/color][/color][/color]
causing[color=blue][color=green][color=darkred]
> > > issues. I will keep you posted.
> > >[/color]
> >
> > Please do.
> >
> >[color=darkred]
> > > Best wishes
> > > Tryfon
> > >
> > >
> > >
> > > "Erland Sommarskog" <sommar@algonet.se> wrote in message
> > > news:Xns946EED4BB70EFYazorman@127.0.0.1...
> > > > Tryfon Gavriel (tryfon@gtryfon.demon.co.uk) writes:
> > > > > (The first six are all event type 15 - which is "Disconnect" i[/color]
> > believe.[color=darkred]
> > > > > They have massive duration times, and massive values for Reads.)
> > > > >...
> > > > >
> > > > > I am not sure how to interpret these events. What does a massive
> > > > > duration on Event Type 15 mean?! Also there are a massive amount[/color][/color][/color]
of[color=blue][color=green][color=darkred]
> > > > > "Reads" associated with these.
> > > >
> > > > As you said, event 15 is disconnection. Duration is just how long[/color][/color][/color]
the[color=blue][color=green][color=darkred]
> > > > connection was open. And Reads are just the accumulated number of
> > > > reads during that session.
> > > >
> > > > In itself, not that exciting. Then again, maybe it is a clue that[/color][/color][/color]
four[color=blue][color=green][color=darkred]
> > > > long-running processes owned by sa quits just before the machines
> > > > reaches nirvana. No, please don't ask me what that clue would mean!
> > > >
> > > > It is possible that the SQL statements you see when you sort on[/color][/color]
> Duration[color=green][color=darkred]
> > > > has anything to do with the CPU hog. However, I wouid not really[/color][/color]
> expect[color=green][color=darkred]
> > > > that process to show up. I would include the Starting events in the[/color]
> > trace,[color=darkred]
> > > > and then investigate the uncompleted events at the end of the trace
> > > > when the CPU goes 100%.
> > > >
> > > >
> > > > --
> > > > Erland Sommarskog, SQL Server MVP,
sommar@algonet.se
> > > >
> > > > Books Online for SQL Server SP3 at
> > > >
http://www.microsoft.com/sql/techinf...2000/books.asp
> > >
> > >[/color]
> >
> >[/color]
>
>[/color]
