[dba-VB] SQL Server / 2003 server locks up

Hans-Christian Andersen ha at phulse.com
Thu Jun 10 16:31:08 CDT 2010


Without looking at code, it would be hard to determine if the issue you are
suffering from is related to deadlocks or race conditions or misbehaving
code in your thread model or whatnot... nor is it out of the realm of
possibility that SQL Server itself is causing the operating system to hang
at a time of high load from your application, due to its own internal

But by performance graphs, I'm referring to something that collects data of
your system (cpu, networking, memory, paging, database queries/loads, etc)
and provides graphs or raw data for you to get a general idea of where the
problem may lie (take one of my servers, for example:
On my side of the fence in *nix land, I use something called Munin which
does such a thing. I believe there's even a Munin node for Windows, although
I'm sure there are solutions that suit the Windows environment far better (
ie. http://www.monitortools.com/ ).

Long story short, while it may not necessarily spell out what your exact
problem is, at least you have some information about the mental state of
your server in the last moments before it shuffled off this mortal coil... and
this can be quite useful in narrowing down where the problem is.

But, I have to say, it doesn't seem like you are doing anything too
'exotic', and considering that SQL Server is used by governments and big
enterprise, I'd be surprised if there were serious stability problems merely
from the process of exporting and importing.

With regards to VisualSVN, well, personally, I do all my subversioning from
the command line, because I felt relying too much on a GUI would one day put
me at a disadvantage, but I recommend http://tortoisesvn.tigris.org/ . It
integrates with Windows Explorer, so it's a level deeper than a plug-in for
Visual Studio and has always worked rather reliably.

However, it would be interesting to see if moving your app off to
a separate workstation solves the problem... I think that would raise more
questions than it would answer though! :p

Software Developer, UK

tel: +44 (0)782 894 5456
e-mail: hans.andersen at phulse.com
www: nokenode.com
Unique Gifts, Collectables, Artwork
Come one
Come all to

On 10 June 2010 12:37, jwcolby <jwcolby at colbyconsulting.com> wrote:

> Hans-Christian
>  >Have you tried monitoring the performance graphs to see what happens just
> before the lock-up?
> I am not very hip on SQL Server, so the answer is NO, I don't even know
> where to go to find the
> "performance graphs" or even what you mean by "performance graphs".
> My application is not that complex in terms of number of threads created.
>  In fact to this point I
> only have a single thread.  Basically I have a form which I use to set
> parameters to an import or
> export process, things like specific database, specific table in that
> database, disk directory that
> holds the files etc.  Once all of the parameters are set I just click a
> button and a single worker
> thread is created which starts a "process".  This process is exporting 60
> million records into 2
> million record files, or importing back in 2 million record files and
> assembling them into a table.
> So... one thread.
> My hope in this mess was that I could (eventually) be able to do other
> things within the program.
> For example start exporting the 60 million record table and then go start
> processing an order.  The
> "export" runs in its worker thread and the application still responds and
> allows me to open another
> form and do other things.
> There are some things that I have discovered through the school of hard
> knocks.
> 1) Threads have priority levels which you can explicitly set.
> 2) Priority levels can range from "background only" to "lock the OS and
> don't do ANYTHING other than
> this thread until I tell you otherwise".
> 3) SQL Server does issue a "lock the OS" level threads under some
> (undefined) circumstances, in
> order to perform processing that it deems critical.
> 4) Once a "lock the OS" level thread is issued, NO display level operations
> are performed.  Only
> things like disk processing, network etc. (very specific hardware level
> things which must happen at
> all times) are allowed to continue at the OS level.
> 5) On top of all that there may be "deadlock" type of issues which can lock
> things up.
> Given that my application (to this point) is only ever issuing a single
> worker thread, it seems
> unlikely that my thread is directly the cause of the lockup.
> My client has agreed in principal to fund a server upgrade.  AMD has
> released a new series of server
> chips
> http://www.anandtech.com/show/2978/amd-s-12-core-magny-cours-opteron-6174-vs-intel-s-6-core-xeon
> Which will allow me to build a server (I build my own) with anywhere from 8
> to 24 real cores and up
> to 64 gigs of ram at a "reasonable" price.  Pair that with Windows 2008 and
> SQL Server 2008 and I
> could have a system to efficiently handle the volumes of data which I am
> struggling to handle with a
> much less powerful server.
> OTOH, if the server is going to lock up on me, having 4 or 24 cores won't
> make much difference.
> Windows can lock up 24 cores as easily as one!  ;)
> All of that said, I have been doing my development and running my
> application on my server, through
> remote desktop.  I am doing so because of technical issues - I can't figure
> out how to get VisualSVN
> to play nice and accept the path to the source code database from my
> workstations.  This is the one
> sucky thing about VisualSVN, it is rather stone-aged in that regard.
> Obviously I should NOT be doing my development on the server, and I should
> actually running my application on the server, but that is what I am stuck
> with until I figure out
> the VisualSVN thing.
> Sigh.
> So it may in fact work out that the whole "lock-up" issue would just go
> away if I move the
> application off to a workstation.
> John W. Colby
> www.ColbyConsulting.com
> Hans-Christian Andersen wrote:
> > While I could not give you any technical advice with regards to your
> > application or SQL Server, it seems rather antithetical to the whole
> premise
> > of multi-threading (a rather robust technology at this point) to have the
> > server lock up when you are doing things like dragging a window. Have you
> > tried monitoring the performance graphs to see what happens just before
> the
> > lock-up? You might have a run-away process spawning too many threads or a
> > thread which is consuming too many resources and memory, causing your
> server
> > to start paging like mad. Perhaps your threads are doing too many things
> > (typically you have to strike a balancing between keeping your threads
> > light-weight vs the overhead of spawning threads). Seems a bit quaint to
> > have an application so specific that it requires you to tweak the
> scheduler
> > of the operating system. Just a thought?
> >
> >
> > Hans-Christian
> > Software Developer, UK
> _______________________________________________
> dba-VB mailing list
> dba-VB at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/dba-vb
> http://www.databaseadvisors.com

More information about the dba-VB mailing list