[dba-SQLServer] SQL Server 2005 unresponsive

Francisco Tapia fhtapia at gmail.com
Fri Sep 4 16:26:26 CDT 2009


>On Fri, Sep 4, 2009 at 1:26 PM, jwcolby <jwcolby at colbyconsulting.com>wrote:
>I thought that SQL Server would magically prevent anyone from bringing it
to it's knees.  Naive I know.

Any badly written query can bring down even the most robust Sql Server to
it's knees.  Someday on your development server create a series of GOTO
statements with cursors... you can program it so that at nth points the
cursor doesn't close correctly, this will cause a run-away cursor, if you
write your procedure to insert data into a table before the commit, you can
lock up the table tight to create a deadlock.  under normal conditions the
system will choose a victor and you will be on your way, but you can spin
this loop many times so that instead of the server simply timing something
out, there is too much memory in use and nothing ever comes back.

so I learned the hard way when I had a DBA whom no longer works here craft
himself a nifty little procedure he found on the web,  only he forgot to
close out his cursors correctly and set this up as a job and had it running
every 15 minutes.... by the afternoon the system was down to a crawl even
though it had plenty of I/O throughput and plenty of CPU and RAM.  we
identified the culprit, but it was difficult as Enterprise Manager would
continue to time out due to the amount of resources that the Agent kept
spinning off.  In the end it was easier to shutdown the server and identify
the culprit on reboot than to try to connect to the server as everything had
become unresponsive.

in your case with ton's of data you need I/O, as you have plenty of memory
and from the examples you've written about you tend to stick to either
actual tables.  Speeding up your transaction log disk and your tempdb disk
will produce the biggest performance gain possible.

(now I mention the transaction log disk, even when you have your recovery
mode to simple, the transaction can get some data written to it temporarily,
but it tends to write back to the main db files as soon as it hits a
checkpoint.  so speeding this disk 2nd to your tempdb will yield noticeable
improvements)

>
>
> -Francisco
> http://sqlthis.blogspot.com | Tsql and More...
>



More information about the dba-SQLServer mailing list