[AccessD] SQL Server speed issues - was server locking up.

jwcolby jwcolby at colbyconsulting.com
Tue Sep 29 11:29:35 CDT 2009


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.

-- 
John W. Colby
www.ColbyConsulting.com



More information about the AccessD mailing list