Post reply

sql server 2008 queries with intermittent poor performance

  • July 12, 2011 at 9:54 am

    #1351708

    I'll let Grant or Gail take over from here.

  • July 12, 2011 at 10:07 am

    #1351727

    jmaia (7/12/2011)

    Ninja's_RGR'us (7/12/2011)

    It's not basic at all actually.

    I'm no expert in waits but I'll hang around if I can help more.

    What's the normal use of the system? OLTP, OLAP, both?

    What's the cost threshold for parallelism setting atm (you can find out with sp_configure using advanced options)?

    Normal use for the system is OLTP.

    The Max Degree of Parallelism setting is 0.

    The Cost Threshold for Parallelism setting is 5.

    Locks setting is 0, and Query Wait setting is -1.

    Regards,

    jm

    With an OLTP system, it's very easy to get an estimated cost on queries that exceeds a value of 5. That means that query is a candidate for parallel execution. If you're seeing lots of CXPACKET waits, then you're seeing parallelism. I'd suggest changing the cost threshold to a more viable value for OLTP systems, say 35. This will reduce the number of queries that are experiencing parallelism.

    "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

  • July 12, 2011 at 10:15 am

    #1351740

    Grant Fritchey (7/12/2011)

    With an OLTP system, it's very easy to get an estimated cost on queries that exceeds a value of 5. That means that query is a candidate for parallel execution. If you're seeing lots of CXPACKET waits, then you're seeing parallelism. I'd suggest changing the cost threshold to a more viable value for OLTP systems, say 35. This will reduce the number of queries that are experiencing parallelism.

    Thanks, I'll try that one out and see if the CXPACKET wait number is reduced.

    The thing that is really puzzling me is the OLEDB waits though... Any ideas regarding where these may come from ?

    Regards,

    jm

  • July 12, 2011 at 2:07 pm

    #1351869

    I've heard, but not 100% sure...

    DBCC statements.

    Need to test and confirm that. Will do so, sometime.

    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

Viewing 4 posts - 31 through 33 (of 33 total)

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