SQL Server Performance

Poor performance with very high page faults and low page life expectancy.

Discussion in 'SQL Server 2005 Performance Tuning for DBAs' started by greenant, Sep 3, 2008.

  1. greenant New Member

    I'm attempting to verify that the performance of a new server that we are planning to roll out is as good as or better than the existing server. I used profiler to capture a trace and replayed it against the new server. For some of the key stored procedures, the average cpu per execution was significantly higher by up to 20x. Also, the number of reads was about 5x greater on the new server. I used SQL Compare to verify that all of the procs, tables, indexes and stats were the same and that the record counts were also the same. When I reviewed the perfmon data I saw that as soon as I start the test the number of page faults/s goes up to around 20,000 and the page life expectancy drops to around 5. When I stop the test the page faults falls to almost 0 and the page life expectancy immediately begins to increase.
    Can anyone explain what could cause this behaviour or what other performance counters I should look at to try to identify the cause of the problem.
  2. MohammedU New Member

    How many threads you are using?
    Is memory on both servers the same? if yes check max server memory setting and total server memory and target server memory counters.
  3. Saurabh Srivastava New Member

    You haven't mentioned about new server configuration. Anyway, try these things
    1)Make sure you have "lock Pages in memory" enabled
    2) I hope before running Trace on prod u backed up prod database and then restore it for replay
    3) Caplure all OS level Memory counters. SQL Server buffer manager, SQL server memory manager.
    4) Capture disk counters as well.
  4. Elisabeth Redei New Member

    Hi Tim,
    Can you also provide us with information about what edition of SQL Server you are running (32 or 64 bit) and if it's 32 bit, what your settings in boot.ini are.
    Have you done an upgrade of your databases or did you move them between the same version? The reason I am asking is that if your database(s) have been upgraded you need to update your statistics (you said they were the same but I don't know what exactly that means in SQLCompare terms :) ).
    If you expected the same, or better, performance on the new server and it just suddenly went pear shaped, it sounds like a configuration issue as suggested already in the thread.
    One thing... don't grant the "Lock Pages in Memory Recommendation" if you are on 64-bit (although it is a workaround for the problem described in http://support.microsoft.com/default.aspx?scid=kb;EN-US;918483) and you can read about the reason for this here: http://blogs.technet.com/askperf/ar...do-you-really-need-it.aspx?CommentPosted=true. It is granted to Local System by default.

Share This Page