Results 1 to 5 of 5

Thread: Memory Issue

  1. #1
    Join Date
    Jul 2008
    Posts
    13

    Unanswered: Memory Issue

    Hello,

    Based on the following stats (every 3 minutes) what would the contributing factors for extreme slowness on the server?

    Buffer Distribution Buffers
    ---------------------- -----------
    Free 18GB

    Memory Grants Pending Memory Grants Outstanding
    22 26
    19 194
    18 239
    18 238
    37 67
    28 233
    33 227


    User Connections
    317
    350
    418
    466
    357
    392
    361
    423

    Buffer Manager\Page life Expectancy
    6389
    6565
    6748
    6764
    6933
    7101
    7268
    7446
    7625
    7804
    7960


    Same time frame have .......
    2904 processes with lastwaittype 'RESOURCE_SEMAPHORE'
    11 processes with lastwaittype 'I/O COMPLETION'

    Thank you.

  2. #2
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    did you take some baselines when the server was not slow?

    1. whats the disk que length at?
    2. is it slow all of the time or some of the time?
    3. what runs on the server?
    4. I assume you have looked at the task manager during this slowness. was there anything going on in the processes tab?
    5. do you have any long running queries?
    6. What are your reading for Pages/Sec and % Processor Time?
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

  3. #3
    Join Date
    Jul 2008
    Posts
    13
    Thanks for your reply.

    1. whats the disk que length at?
    At the time we had the issue, it wasn't running. Else it is at an average of 10.

    Physical I/O (Sysprocesses) at the time we had the issue was:
    47018
    44659
    43939
    43301
    43301
    35022
    35022
    35022
    35022
    35022
    29917
    29917

    2. is it slow all of the time or some of the time?
    Every morning

    3. what runs on the server?
    Only SQL Server 2000 EE SP4
    Windows 2003 - SP2 - 2 nodes Active/Passive Cluster

    4. I assume you have looked at the task manager during this slowness. was there anything going on in the processes tab?
    Nothing more other than the sqlserver.exe and other system processes.

    5. do you have any long running queries?
    We do have some 'select' queries that reference tables that contain close to 2 millions rows. They extract 12-15% of the data. Take around 11-13 secs to complete.

    6. What are your reading for Pages/Sec and % Processor Time?
    Are you referring to 'Memory- Pages/Sec'? Don't have that setup.
    '% Processor Time' was at an average of 33% for the same time frame as the earlier mentioned stats. Have 8 CPUs.


    Thank you.

  4. #4
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    Quote Originally Posted by UnSure
    Thanks for your reply.
    1. whats the disk que length at?
    At the time we had the issue, it wasn't running. Else it is at an average of 10.
    In the performance monitor.

    Quote Originally Posted by UnSure
    2. is it slow all of the time or some of the time?
    Every morning
    What happens in the morning?

    Quote Originally Posted by UnSure
    4. I assume you have looked at the task manager during this slowness. was there anything going on in the processes tab?
    Nothing more other than the sqlserver.exe and other system processes.
    any of them taking up a lot cpu or memory? inetinfo.exe maybe?

    Quote Originally Posted by UnSure
    5. do you have any long running queries?
    We do have some 'select' queries that reference tables that contain close to 2 millions rows. They extract 12-15% of the data. Take around 11-13 secs to complete.
    Are these run a lot in the morning? Have we checked for blocking using sp_who2 during these slowdowns?

    Quote Originally Posted by UnSure
    6. What are your reading for Pages/Sec and %Processor Time?
    Are you referring to 'Memory- Pages/Sec'? Don't have that setup.
    '% Processor Time' was at an average of 33% for the same time frame as the earlier mentioned stats. Have 8 CPUs.
    add these counters to your performance monitor. take reading before and during the slowdowns.

    Quote Originally Posted by UnSure

    Thank you.
    u r welcome.
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

  5. #5
    Join Date
    Jul 2008
    Posts
    13
    1. Two drives. The Performance Monitor - Avg. Disk Queue Length is around
    10 and 7.

    2. Two or three times in the morning (peak hours) users cannot access the
    databases. On the server end, every command typed (eg sp_who) takes
    more than 10 minutes to return output. A failover is required each time.

    3. Don't see any difference in memory usage or CPU.

    4. The queries are executed all day during business hours. There is no
    blocking.

    5. Will add 'Memory- Pages/Sec'.

    Thank you.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •