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

if your transaction log file is not big enough to house all the data, then
you will end up paging...that's normal, but this brings other issues as well
your log file will want to grow (if you've set it to autogrow) and also if
you're not in simple recovery model. and even in simple recovery model
you'll see the log file grow a good amount based on how much data you push
through your system.  page and working with only 1 million rows is different
than working on all 100 million rows, it's all about resources and how
you've allocated them.  a small tlog fle will require the server to stop
what it was doing in order to grow the tlog first then it will continue on
it's merry way...

I remember you were going to re-organize the location of the tempdb, and the
tlog files... how did that pan out and how much space did you allocate for
it?

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


On Tue, Sep 29, 2009 at 10:44 AM, Robert Stewart <robert at webedb.com> wrote:

> 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.
> _______________________________________________
> dba-SQLServer mailing list
> dba-SQLServer at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
> http://www.databaseadvisors.com
>
>