SQL Server Performance

Relating Memory/Pages/sec to process.

Discussion in 'Performance Tuning for DBAs' started by HarryArchibald, Feb 20, 2003.

  1. HarryArchibald New Member

    The server hosting a database periodically runs slowly. This seems to coincide with a high (approx 2000) value for Memory/Pages/sec which last for five minutes or so.
    I have combed through sql traces but have been unable to find a correlation with a sql statement and the high paging.
    I am now trying to trace all processes running on the server but I am unsure how to link paging to the processes.
    Does anyone know what counter to monitor to link Memory/Pages/sec usage to a process.
  2. bradmcgehee New Member

    SQL Server does minimal paging, except when performing backups or restores, so I imagine that the problem is with another process. Use the following counter to track pages/sec for each process.

    Process: Page Faults/sec: (Then select all process instances you want to track).

    Brad M. McGehee
  3. Argyle New Member

    Do you have any third party surveillance or backup programs (like BMC Patrol / Tivoli) running maybe?

  4. satya Moderator

    How about memory settings on the server?
    Also from PERFMON trace look for PROCESS counter which shows about process taking too much of resources.


    Satya SKJ
  5. Dave Wells New Member

    I've encountered this before with anti virus software being the cause if that helps.
  6. satya Moderator

    Well well well, if still that AV is running as a service disable it striaghtaway and this is the culprit to hack the memory.

    Satya SKJ
  7. HarryArchibald New Member

    Thanks for the ideas guys.

    I already had a trace on for Process: Page Faults/sec but that should no link to Memory/Pages/Sec.

    We have no surveillance tool but my hunch was the anti-virus software which is now disabled and the problem reoccurred.

    The memory is dynamically configured. I've not looked at the Process counter so I'll try that.

    This one may run and run...
  8. Chappy New Member

    I find task manager is the best way to find the source of excessive page faults. <br />View-&gt;Select Columns-&gt<img src='/community/emoticons/emotion-4.gif' alt=';P' />age Faults

Share This Page