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

jwcolby jwcolby at colbyconsulting.com
Thu Jun 10 07:37:29 CDT 2010


 >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 


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 DEFINITELY NOT be 
actually running my application on the server, but that is what I am stuck with until I figure out 
the VisualSVN thing.


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

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

More information about the dba-VB mailing list