none
How to reduce resource waits on sql server without upgrading CPU??

    Question

  • Hi Experts

    How to reduce resource waits on sql server without upgrading CPU??

    Thanks

    Wednesday, March 20, 2013 5:09 PM

Answers

All replies

  • Hello,

    Please share with us the result of the following query:

    SELECT TOP 40 *

    FROM sys.dm_os_wait_stats

    ORDER BY wait_time_ms DESC

    Thank you in advance.

    Regards,

    Alberto Morillo
    SQLCoffee.com

    Wednesday, March 20, 2013 5:40 PM
    Moderator
  • wait_type waiting_tasks_count wait_time_ms max_wait_time_ms signal_wait_time_ms
    LAZYWRITER_SLEEP 5255286 2355971687 8244406 294453
    SQLTRACE_BUFFER_FLUSH 294543 1178169921 4171 0
    CXPACKET 960112 144910468 173843 324687
    PAGEIOLATCH_SH 11201079 119171859 19703 228656
    PAGEIOLATCH_EX 7481430 52116484 9843 109390
    BACKUPIO 8167206 8935375 1453 12781
    ASYNC_IO_COMPLETION 99 8665046 615953 0
    BACKUPBUFFER 1385353 8413203 1671 49328
    SLEEP_TASK 24932142 5758031 1000 25125
    SLEEP_BPOOL_FLUSH 4491653 5622156 31 83968
    WRITELOG 5937107 4286296 2187 302953
    BACKUPTHREAD 1407 2767218 200734 31
    BROKER_TASK_STOP 376 1879734 10000 0
    ASYNC_NETWORK_IO 754104 1245421 2000 65015
    BROKER_RECEIVE_WAITFOR 2 605906 600000 0
    SOS_SCHEDULER_YIELD 42357648 535750 3406 486750
    LCK_M_IX 147 340375 68343 0
    PAGEIOLATCH_UP 85572 184734 921 390
    LCK_M_U 13033 134453 2703 2140
    LATCH_EX 53353 101953 375 3828
    IO_COMPLETION 555147 101578 687 703
    MSQL_XP 22837 59265 7328 0
    LCK_M_X 672 31078 4843 0
    PAGELATCH_EX 277786 28921 406 6984
    CMEMTHREAD 1322 21921 2234 4031
    LATCH_SH 17078 16109 1953 890
    LCK_M_SCH_M 201 12890 2250 15
    SQLTRACE_LOCK 55 11187 1000 0
    LCK_M_S 345 8875 4031 46
    PAGELATCH_SH 77374 8109 875 1453
    PAGELATCH_UP 12094 2296 15 515
    OLEDB 131407 1062 31 0
    CHKPT 1 375 375 0
    SLEEP_SYSTEMTASK 1 375 375 0
    EXECSYNC 3214 265 15 93
    LOGBUFFER 267 203 109 0
    THREADPOOL 32 62 62 0
    SOS_RESERVEDMEMBLOCKLIST 1 15 15 0
    MSSEARCH 12 15 15 0
    TRAN_MARKLATCH_NL 0 0 0 0
    Wednesday, March 20, 2013 6:11 PM
  • Hello,

    That computer certainly has parallelism as the top cause of poor performance, but you need to need to do some work with your storage configuration also.

    Please configure max degree of parallelism based on the results of the following query:

    SELECT CASE

    WHEN cpu_count / hyperthread_ratio > 8 then 8

    ELSE cpu_count / hyperthread_ratio

    END AS 'Optimal Maxdop Setting'

    FROM sys.dm_os_sys_info

    The following post may help:

    http://blogs.msdn.com/b/arali/archive/2009/11/26/sql-server-max-degree-of-parallelism-maxdop.aspx


    Configuring parallelism may reduce the storage subsystem contention, but still need to be configured better.

    Hope this helps.

    Regards,

    Alberto Morillo
    SQLCoffee.com

    Wednesday, March 20, 2013 6:30 PM
    Moderator
  • Maxdop setting is 2
    Wednesday, March 20, 2013 7:09 PM
  • Hello,

    Is that the result of the query I posted above? Then,perfect. Please configure “max degree of parallelism” with that value.

    Before making improvements on the storage, make sure there are no missing indexes. Missing indexes may create parallelism, high I/O and can consume great amount of RAM.

    http://blogs.msdn.com/b/bartd/archive/2007/07/19/are-you-using-sql-s-missing-index-dmvs.aspx

    Please configure “max server memory” option also. Reserve 3 or 4 GB of RAM for the operating system.

    Hope this helps.

    Regards,

    Alberto Morillo
    SQLCoffee.com

    • Marked as answer by SQLism Wednesday, March 20, 2013 8:10 PM
    Wednesday, March 20, 2013 7:25 PM
    Moderator