Post reply

SP2 causes major slowdown?

  • April 11, 2007 at 10:58 am

    #153521

    I have a SS2005 box that is home to a data warehouse. It loads every weekend. An admin accidentally installed SS2005sp2 on 4/4. The next weekend the load was much slower when looking up the dimension keys for one of the fact tables.

    This is the statement:

    UPDATE STAGE.dbo.FactStage

    SET DimKey = dim.DimKey

    FROM DW.dbo.Dim dim WITH (NOLOCK)

    JOIN STAGE.dbo.FactStage stage WITH (NOLOCK) ON

    stage.NaturalKey1 = dim.NaturalKey1

    AND stage.NaturalKey2 = dim.NaturalKey2

    (I have substituted our actual dim and key names with generic substitutes.)

    The FactStage table has 22 million rows in it. The Dim table has 100 rows in it.

    This update statement jumped from taking about 30 minutes to taking about 4 hours.

    One other odd thing I noticed. If I limit the statement with SET ROWCOUNT 50000 the update happens in 10 seconds. If I change it to SET ROWCOUNT 100000 the update takes about 2 minutes. If I put it back to SET ROWCOUNT 50000 and then loop through it 10 times it takes 40 seconds.

    Any suggestions?

    Thanks,

    Chris

  • April 11, 2007 at 11:03 am

    #699858

    First suggestion: UPDATE your STATISTICS. when you installed the SP you may have flushed the cache

    Suggestion #2 NOLOCK statements on the FROM of an update statement are supposed to be deprecated (plan for that)

    Cheers,


    * Noel

  • April 11, 2007 at 11:14 am

    #699867

    Noel,

    Thanks for the response. Pardon my ignorance (I'm not a trained DBA, but I'm acting as one currently) - any tricks to updating my statistics? Or is it straightforward once I read Books Online, or similar?

    Good to know about the NOLOCKs. They are not needed here... only the ETL process is running at the time of ETL... the only reason they are there is a belief that as a best practice this would have required less resources of the server (ie, it wouldn't have to lock them).

    Thank you so much,

    Chris

  • April 11, 2007 at 12:01 pm

    #699878

    Regarding the Updating of Statistics... I have read up on it, and thing I have it under control. I'm currently updating with 1% of rows as a test. Based on how long that takes I'll increase the percent.

    Thanks,

    Chris

  • April 11, 2007 at 3:12 pm

    #699914

    This is odd. I updated 100% of statistics on my table, and an update statement for 50,000 rows went very quickly, less then 1 second. Then I tried updating 100,000 rows and it took 1 minute 20 seconds. Back to 50,000 and less then 1 second.

    So then I implemented logic in my stored procedure to batch up 10 sets of 50,000 rows. This took several minutes and now single updates of 50,000 rows is taking a very long time (more than 8 minutes).

    I'm so confused.

    Thanks,

    Chris

  • April 12, 2007 at 7:38 am

    #700058

    One thing to factor into your tests...  An update of 50,000 rows may take place entirely in the buffer pool.  An update of 100,000 rows may require some updates to be written to disk before the remainder can be done.

    This may not be the actual or only cause of the performance difference, but eventually updates will slow down as you flood the buffer cache.

    Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.

    When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara

  • April 14, 2007 at 8:13 pm

    #700576

    Well, I don't have a full solution, but, I have an acceptable work around. I batched up the updates for one key lookup and noticed that the batches got slower as we progressed through the table. The first few batches of 50,000 rows could be updated in 1-2 seconds. The last few would take something like 90 seconds. So, I decided to try splitting our Stage table into 3 separate tables, Stage1, Stage2 and Stage3. I then changed the code that loads the Stage table to load each of these tables with precisely one third of the total rows. I then changed all the following code to use these new smaller tables. I ran this end to end and it took about as long as all this took before I started having problems last weekend.

    How's this sound?

    I don't have any experience with partitions. Could I have accomplished the same thing more easily with them?

    Thanks,

    Chris

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply