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... >