WangKhar (Wa******@yahoo.com) writes:
For some reason our web client box times out occasionally. Maybe a
few times a day.
Nothing appears in the logs.
What I do know is that the disk queue ramps up to <high>(think top of
the graph), processor queue jumps up and the tps drops to 0 (naturally
enough!) along with reduced pagefile usage. Usually at this time the
tps is between 20 and 300. Running a pair of mirrored 18gb scsi disks
for the whole server (yeah yeah) and a whopping 640mb memory. Oh and
a single piii 1 gig. Sql2k standard vanilla, server2k.
What server trace events is it worth my catching to try and get a
better Idea of what may be causing this? No major jobs (backups etc
seem to be occuring at these times).
Times out? You mean that requests from the web client to SQL Server
dies with "Timeout expired"?
There are a couple of possible explanations to what is going. The two
most likely that comes to mind is:
o Someone somewhere submits a query with a bad execution plan that
takes up a lot of resources and blocks other users.
o Database auto-grow.
The best way to track this is probably to use the Profiler. Set up a
filter to capture Auto-grow events. Also set up a trace for
SQL:StmtCompleted, filtered for a Duration > 1000 ms. It's probably
good to include RPC:Completed and TSQL:BatchCompleted as well.
Or take a shortcut. Do an sp_spaceused and sp_helpdb on the database and on
tempdb, and see how much unallocated space you have in relation to the
size of the data files. If the margin is narrow, increase the size of
the databases with ALTER DATABASE (or use Enterprise Manager) by the
double. If the problem goes away for some time, you know it was auto-grow.
--
Erland Sommarskog, SQL Server MVP,
so****@algonet.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp