sql server 2008 sp2- slowness

  • Hi

    we applied sql server 2008 sp2 in our production.

    But after we applied it ,we see slowness. we see writelog and log buffer wait types. we checked the disk subsystem, and it looks good. but the ms/write goes up over 300 ms when lot of writes come in and they get clogged. but when there aren't lot of writes, then the ms/write is below 10 ms

    we didn't see these writes getting clogged up when we were running the 2008 RTM version.there were some writelog waits, but not as many as now. the end users are facing slowness and freezing

    have any one seen behavior like this?

    any advice will be of great help

    thanks for the help.

  • The question is, what is causing it? What are your queries doing and what is your structure such that you're getting excessive writes? Could something have changed between RTM & SP2? Yes. But that's not the place to focus. The place to focus is your code and structure.

    "The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
    - Theodore Roosevelt

    Author of:
    SQL Server Execution Plans
    SQL Server Query Performance Tuning

  • Microsoft has updated only following stuffs in SP2

    SQL Server Utility,

    Data-Tier Application (DAC),

    as well as integration capability for Microsoft Reporting Services with the Microsoft SharePoint 2010 Technologies

    Try applying CUs gfor SP2 in ur DEV environment and measure the performance. Available CUs for SP2 are

    CU1

    CU2

    CU3

    CU4

    CU5

    SQLforU
    info@sqlforu.com
    For online training on SQL Server and Sybase, please contact
    contact@sqlforu.com
    www.sqlforu.com

  • what version were these databases originally, SQL 2008 or a previous version?

    -----------------------------------------------------------------------------------------------------------

    "Ya can't make an omelette without breaking just a few eggs" 😉

  • so is your storage san, das or internal?

    I can't think of anything offhand which might cause this from a SQL service pack, other than there may ( probably will) be changes to the optimiser which would affect queries but that shouldn't affect io issues on the storage.

    [font="Comic Sans MS"]The GrumpyOldDBA[/font]
    www.grumpyolddba.co.uk
    http://sqlblogcasts.com/blogs/grumpyolddba/

  • colin.Leversuch-Roberts (9/5/2011)


    so is your storage san, das or internal?

    I can't think of anything offhand which might cause this from a SQL service pack, other than there may ( probably will) be changes to the optimiser which would affect queries but that shouldn't affect io issues on the storage.

    it is SAN..

    we suspect the SAN and have opened a call with EMC.

    we also removed the SP just to confirm that it is not an issue with the SP.

    thank you all for the help.

  • What does your disk couters say

    Avg Disk secs \Read

    Avg Disk secs \Write

    Current disk queue length ...

    Also did you run a perfmon trace to find the possible resource bottleneck ?

    Thank You,

    Best Regards,

    SQLBuddy

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

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