SQL Server Performance

MemoryPages/sec is high - Could Service Broker be responsible?

Discussion in 'Getting Started' started by lcerni, Aug 28, 2009.

  1. lcerni New Member

    We have SQL Server 2005 SP3 CU4 installed on a 64 bit server. I am trying to determine why the MemoryPages/sec is pinning about every 5 minutes. I don't believe it is a job. I know it isn't backups. Could Service Broker be responsible?
    Wednesday - Thursday
    Acceptable Desired 20090819 10:45am - 20090820 10:45am
    ObjectCounterAverage RangeValueAvgMinMax
    MemoryPages/secavg value < 50low993.177026253.04
  2. satya Moderator

    Do you see BROKER_TASK_STOP event wait times?
  3. lcerni New Member

    select * from Sys.dm_os_wait_stats
    This is how it looks
    wait_type waiting_tasks_count wait_time_ms max_wait_time_ms signal_wait_time_ms
    BROKER_TASK_STOP 6244857 919642203 10000 51640
    What does this mean?
    Is this good or is this bad?

    Could this be an issue for the MemoryPages/sec?
    wait_type waiting_tasks_count wait_time_ms max_wait_time_ms signal_wait_time_ms
    CLR_MANUAL_EVENT 34329 918277546 30609 93
    CLR_AUTO_EVENT 10871 1838550781 496875 125
    When I run this: select * from Sys.dm_os_wait_stats where waiting_tasks_count > 1000
    wait_type waiting_tasks_count wait_time_ms max_wait_time_ms signal_wait_time_ms
    LATCH_SH 4981603 208625 46 47937
    LATCH_EX 5328758 460062 296 96015
    PAGELATCH_SH 339022 5671 15 1765
    PAGELATCH_UP 135076 31890 1203 5312
    PAGELATCH_EX 1724852 9437 62 6890
    PAGEIOLATCH_SH 238323 636468 953 2109
    PAGEIOLATCH_UP 3392 15687 1843 93
    PAGEIOLATCH_EX 76049 399265 1234 500
    LAZYWRITER_SLEEP 926799 919769062 1656 8187
    IO_COMPLETION 1157529 801468 437 1781
    ASYNC_NETWORK_IO 16811820 5282203 2218 205984
    SLEEP_BPOOL_FLUSH 153650 150281 15 2546
    SLEEP_TASK 9858080 922450015 1156 918984265
    OLEDB 81597761 650161343 274537437 0
    SOS_SCHEDULER_YIELD 11718075 518156 187 512031
    BACKUPBUFFER 547010 4304562 1156 3125
    BACKUPIO 844886 3207515 687 5921
    BACKUPTHREAD 8911 228781 1250 187
    SQLTRACE_BUFFER_FLUSH 229943 919770031 4375 656
    WRITELOG 473313 838921 218 7921
    CMEMTHREAD 624366 24421 15 21437
    CXPACKET 5852703 18284265 10750 3660093
    EXECSYNC 37661 8984 156 2031
    MSQL_XP 47592 60906 921 0
    MSQL_DQ 44708 80765 5453 0
    LOGBUFFER 5936 4171 31 328
    CLR_CRST 156273 7265 187 1203
    CLR_MANUAL_EVENT 34344 918697546 30609 93
    CLR_AUTO_EVENT 10874 1839004093 496875 125
    IO_AUDIT_MUTEX 3629 281 15 78
    BROKER_TASK_STOP 6245086 920787203 10000 51640
  4. lcerni New Member

    I was reading http://sql-server-performance.com/Community/forums/t/15407.aspx
    Can the network affect the Memorypages/sec? When I run performance monitor and look at the Network Utilization on Windows Task Manger/Networking Tab, I do see a correlation. However, I am not sure I understand. Is there a web page that explains this?

Share This Page