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

Robert Stewart robert at webedb.com
Wed Sep 30 13:18:27 CDT 2009


You never answered the questions below...

You said you were shrinking the log files. What you need to do is 
TRUNCATE them.
Do a Books-on-line look up of TRUNCATE LOG.



At 12:00 PM 9/30/2009, you wrote:
>Date: Tue, 29 Sep 2009 12:44:16 -0500
>From: Robert Stewart <robert at webedb.com>
>Subject: Re: [dba-SQLServer] SQL Server speed issues - was server
>         locking
>To: dba-sqlserver at databaseadvisors.com
>Message-ID: <200909291744.n8THiauN005272 at databaseadvisors.com>
>Content-Type: text/plain; charset="us-ascii"; format=flowed
>
>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.



More information about the dba-SQLServer mailing list