Post reply

Waits issues

  • May 2, 2011 at 6:13 am

    #1318349

    Thanks for your valuable time,

    Attached image file explain Memory settings of our server.

    Thanks

    Azhar

  • May 2, 2011 at 7:03 am

    #1318375

    azhar.iqbal499 (5/2/2011)

    Please suggest values for MaxDOP and Cost Threshold for Parallaism,lock and query wait time.

    Leave MAXDop. Consider increasing the value of Cost threshold slightly, see if it alleviates the problems with CXPacket

    Any further Suggestion would be definitely helpful for us.

    Did you get the latch wait stats that I asked for?

    p.s. Please save the images as jpegs or png. On a poor connection downloading 1MB or so images is not fun.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • May 2, 2011 at 11:32 am

    #1318592

    azhar.iqbal499 (5/2/2011)

    Thanks for your valuable time,

    Attached image file explain Memory settings of our server.

    Thanks

    Azhar

    6400 MB of RAM used by SQL Server on an 8 gig system. Can you confirm nothing else is running on this box? I wanted to double check you're not running IIS or something similar that's competing for memory.

    Also, you may want to cycle up PerfMon and check Page Faults/sec under memory counters. I think you're ending up memory starved for the OS and going to swapfile, which isn't helping matters.

    - Craig Farrell

    Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

    For better assistance in answering your questions[/url] | Forum Netiquette
    For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
    Twitter: @AnyWayDBA

  • May 2, 2011 at 11:35 am

    #1318598

    Did anybody look at the pictures before responding?

    Looks like you're rebuilding an index, or performing other index-modification work. You'll get lots of page latching on metadata pages (mostly the GAM) as the process allocates new pages.

    Index mods need some amount of exclusive access to the underlying table (a schema-modify, or LCK_SCH_M, lock). If the action is performed using WITH (ONLINE = ON), then only a few locks are briefly taken. Otherwise (the default), the underlying table is locked for the duration of the process.

    Session_id 52, the process that is blocking everything else, is the session doing the index work. Kill the index work and either run it when less activity is on the server or use the WITH (ONLINE = ON) switch.

    -Eddie

    Eddie Wuerch
    MCM: SQL

  • May 3, 2011 at 6:56 am

    #1319023

    Nice catch Eddie.

    Azhar, since you missed this, I will say that you REALLY need to get a professional on your system for a review and to help you out. This is something that should have been known about already.

    Best,
    Kevin G. Boles
    SQL Server Consultant
    SQL MVP 2007-2012
    TheSQLGuru on googles mail service

  • May 3, 2011 at 6:58 am

    #1319026

    Ah, another reason to NOT post 1-2 mb bmp attachements... Gail would have spotted those if she had opened the files!

  • May 3, 2011 at 7:04 am

    #1319032

    Eddie Wuerch (5/2/2011)

    Looks like you're rebuilding an index, or performing other index-modification work. You'll get lots of page latching on metadata pages (mostly the GAM) as the process allocates new pages.

    True

    What I find curious is that the waits are Latch waits, not Page Latch waits, so not IO or page related.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • May 3, 2011 at 7:28 am

    #1319059

    GilaMonster (5/3/2011)

    Eddie Wuerch (5/2/2011)

    Looks like you're rebuilding an index, or performing other index-modification work. You'll get lots of page latching on metadata pages (mostly the GAM) as the process allocates new pages./quote]

    True

    What I find curious is that the waits are Latch waits, not Page Latch waits, so not IO or page related.

    Possibly heavy memory contention due to ongoing indexing operation (and lord knows what else)?

    Best,
    Kevin G. Boles
    SQL Server Consultant
    SQL MVP 2007-2012
    TheSQLGuru on googles mail service

  • May 4, 2011 at 11:50 pm

    #1320214

    Hi ,

    It is better if you tune the queries,may be stored procedure whci are running in your environment.We also had the same problem our development team tuned the sp's and the queries,they could bring down the problem.thanks

Viewing 9 posts - 16 through 23 (of 23 total)

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