[dba-SQLServer] SQL Server speed issues - was server locking

How big is your log file?
Have you truncated it?
How big is your space for teh tempdb file?



At 12:00 PM 9/29/2009, you wrote:
>Date: Tue, 29 Sep 2009 12:29:35 -0400
>From: jwcolby <jwcolby at colbyconsulting.com>
>Subject: [dba-SQLServer] SQL Server speed issues - was server locking
>         up.
>To: Dba-Sqlserver <dba-sqlserver at databaseadvisors.com>, Access
>         Developers discussion and problem solving
>         <accessd at databaseadvisors.com>
>Message-ID: <4AC235EF.2040002 at colbyconsulting.com>
>Content-Type: text/plain; charset=ISO-8859-1; format=flowed
>
>Well, it appears that I have issues with SQL Server, above and 
>beyond the "locking up" issue
>
>When I rebooted the server this morning (it never did finish this 
>update process last night), my
>database being updated was NOT corrupted, indicating no writes were 
>happening to it when I rebooted.
>   Furthermore it did not take off and try and do "gotta finish log 
> file stuff" so it appears that it
>wasn't even doing that stuff any more.  Strange in itself.
>
>I have spent a confusing morning trying to discover what exactly is 
>going on.  The first thing that
>is going on is that just updating a single field To NULL in 21 
>million records is taking 24 minutes.
>   That is a million records / minute which is not stellar 
> IMHO.  The field is not indexed so it is
>not an "updating the index" kind of problem.
>
>I can tell you that I fed the "update" stored procedure a "Top() one 
>million" kind of query and it
>took only 17 seconds to update one million records, that same 
>field.  If you do the math, 17 seconds
>/ million times 21 million records is only about 6 minutes to update 
>the field for every record.  So
>why does it take 24 minutes to just do a simple "set that field in 
>every record to null"?
>
>This just makes no sense to me, but I am not a SQL Server kind of guy.