none
High memory page faults/sec on sql server

    Domanda

  • I am suspecting something is need to check on our SQL server  because last more than 3 months I am getting many times high  OS disk queue length  during production hours specially on Disk on S: which contains our database on RAID 10 SAN system .Here is SQL server detail

    Microsoft SQL Server 2005 - 9.00.4035.00 (X64)

                    Nov 24 2008 16:17:31

                    Copyright (c) 1988-2005 Microsoft Corporation

                    Enterprise Edition (64-bit) on Windows NT 5.2 (Build 3790: Service Pack 2)

     

    Here are CPU and RAM details

     

    Logical CPU Count Hyperthread Ratio Physical CPU Count Physical Memory (MB)

    ----------------- ----------------- ------------------ --------------------

    8                 4                 2                  32762

     

    I am continue seeing value of this Memory page faults /sec counter so high (For example, yesterday this value was near 12574 and today it is 26374).What should be the value of this counter?

     Please someone help me and explain why I am seeing extremely high page faults? How can I make this measurement lower? What are the others memory counters do I need to check to figure out why SQL frequently accessing data from Disk?

    Thanks in advance.

    • Spostato Darren GosbellMVP mercoledì 16 febbraio 2011 22:32 This is a DB Engine question, not an OLAP one (From:SQL Server Analysis Services)
    mercoledì 16 febbraio 2011 18:31

Tutte le risposte

  • one counter you can check is page life expectancy (SQLServer:Buffer Manager) and Buffer cache hit ratio, they give you an idea how much sql server is able to cache and how often sql server has to read from disk. it sounds a bit like you are memory bottlenecked. thus many io's on the disks which would explain the queue lengths. how big are your db's/tables, and do you have regulary table scans against them?

    mercoledì 16 febbraio 2011 23:03
  • As FZB says, page life expectancy and buffer cache hit ratio are the gold standards for perfmon counters.

    Page faults can just be logical, they don't all turn into physical faults (or do I have this confused with some other system?)  So that high number could be nothing.

    You also said disk queue numbers are high - how high, and for how much of the time?  It seems that with later versions of SQL Server you can get spikes in the disk queues, but they don't mean anything, just that SQL Server is very clever these days and will queue up a bunch of stuff instead of waiting for the first to get done before posting the next.

    You have 32gb of RAM, do you have the max ram configured to a smaller number, probably less than 28gb?  Are there any other busy processes running on the server along with SQL Server?

    Most of all - are users complaining of slower response?

    Josh

     

    mercoledì 16 febbraio 2011 23:54
  • Thanks both of you .

    I was measuring the value of page life expectancy and buffer cache hit ratio counters last 2 days .

    Here are details during this time and date the page life expectancy value is below 300

    on 2/15/2011

    on 2/16/2011

    12:32 pm to 1:00 pm

    12:20 pm to 12:28pm

    1:300pm to 1:36 pm

    2:57pm to 3:22 pm

    1:43pm to 1:48 pm

    1:00 am to 1:28 am

    3:21 pm to 3:26 pm

    7:11am to 7:16 am

    3:45 pm to 4:32 pm

    page life expectancy value -32

    page life expectancy value -34

    Buffer cache hit ration is above 99 % most of the time but some time it sometime went down near 87 %  on 2/16/2011 .

    last two days , I was seeing the total no of time high disk queue length was 19 times on 2/15/2011 with maximum value 67 and 15 time Disk queue length on 2/16/2011 with maximum value 50 .(time between 8 am to 5 pm ).

    The process of sqlservr  is taking high CPU usage most of the time greater than 150 %..

    Please advise me .

    Thanks . 

    Daizy

     

     

     

    giovedì 17 febbraio 2011 15:45
  • Can you please

     

    • Share what your Max Server Memory setting is currently set to?
    • Provide some general memory numbers:

    SELECT total_physical_memory_kb, available_physical_memory_kb, 
           total_page_file_kb, available_page_file_kb, 
           system_memory_state_desc
    FROM sys.dm_os_sys_memory OPTION (RECOMPILE);


    • Provide your memory clerk usage using the query below.

    SELECT TOP(10) [type] AS [Memory Clerk Type], SUM(single_pages_kb) AS [SPA Mem, Kb] 
    FROM sys.dm_os_memory_clerks 
    GROUP BY [type]  
    ORDER BY SUM(single_pages_kb) DESC OPTION (RECOMPILE);
    

     


    John Sansom | SQL Server DBA Blog | Twitter
    giovedì 17 febbraio 2011 16:20
  • Monitoring Memory Usage

    http://msdn.microsoft.com/en-us/library/ms176018.aspx

     

    The Available Bytes counter indicates how many bytes of memory are currently available for use by processes. The Pages/sec counter indicates the number of pages that either were retrieved from disk due to hard page faults or written to disk to free space in the working set due to page faults.

    Low values for the Available Bytes counter can indicate that there is an overall shortage of memory on the computer or that an application is not releasing memory. A high rate for the Pages/sec counter could indicate excessive paging. Monitor the Memory: Page Faults/sec counter to make sure that the disk activity is not caused by paging.

    A low rate of paging (and hence page faults) is typical, even if the computer has plenty of available memory. The Microsoft Windows Virtual Memory Manager (VMM) takes pages from SQL Server and other processes as it trims the working-set sizes of those processes. This VMM activity tends to cause page faults. To determine whether SQL Server or another process is the cause of excessive paging, monitor the Process: Page Faults/sec counter for the SQL Server process instance.

     

    also I'd suggest you to look at your disk performance, I am not sure about your IO subsystem architecture, perfmon counters of sec/read, sec/write, sec/transfer  are good ones.


    If you think my suggestion is useful, please rate it as helpful.
    If it has helped you to resolve the problem, please Mark it as Answer.

    Sevengiants.com
    giovedì 17 febbraio 2011 16:28
  • as long as the buffer cache ratio stays above 99%, that means SQL Server is running efficiently.

    if page life expectancy drops from time to time, that says SQL Server is *busy*, but as long as the buffer cache ratio stays high, it is probably running about as fast as it can. 

    remember, the server has to work for a living, too!

    it does suggest you're scanning some big tables that aren't staying in memory, if you can avoid those scans with better indexing or better coding, that might help.

    if you have 8 cores, then when they are all busy you would see 800% CPU, so if you're seeing only 150% CPU, that's fine, wouldn't worry about that until it spent a lot of time over about 300%.

    so, overall, things sound pretty much OK to me!

    Josh

     

    giovedì 17 febbraio 2011 17:06
  • Do you experience any performance degradation on the SQL instance w.r.t. users complaining about slower than normal response times from the application when your page life expectancy and buffer cache hit ratio numbers drop. Ideally, the buffer cache hit ratio should be in the high 90s range but if you are running query which brings a lot of pages in the database buffer cache, then number can drop when such queries are executed.


    This posting is provided "AS IS" with no warranties, and confers no rights.
    My Blog: http://troubleshootingsql.com
    Twitter: @banerjeeamit
    SQL Server FAQ Blog on MSDN: http://blogs.msdn.com/sqlserverfaq
    giovedì 17 febbraio 2011 19:22
  • Here is memory seeting on sql server 

    Minimum server memory set     -2000MB

    Maximum server memory set    -24000MB

    Page file size      -48000MB

    Available memory near 5 GB .

    AWE disable

     

    I could not able to run query to get general memory number .I granted my domain account with view server stats permission ,but still getting invalid object .

    Here is memory clerk usage information.

    Memory Clerk Type                                            SPA Mem, Kb

    ------------------------------------------------------------ --------------------

    CACHESTORE_SQLCP                                             3910480

    OBJECTSTORE_SNI_PACKET                                  232208

    CACHESTORE_OBJCP                                             220512

    MEMORYCLERK_SQLCONNECTIONPOOL           148064

    USERSTORE_TOKENPERM                                     146952

    USERSTORE_SXC                                                     57376

    MEMORYCLERK_SQLGENERAL                              43576

    MEMORYCLERK_SQLSTORENG                             38072

    CACHESTORE_PHDR                                              32792

    USERSTORE_SCHEMAMGR                                    24992

    I am seeing avg.disk sec /read many times  taking time more than 0.020ms look like SQL server accessing data from disk  more than RAM .

    Thanks everyone for helping me .

    giovedì 17 febbraio 2011 20:22
  • 0.020 ms is not a bad number. SQLCP has the highest value which means that there are large number of cached plans in your procedure cache. You might want to look into the procedure cache and see if you have a lot of ad-hoc plans in the cache. Based on the analysis, you can opt for either parameterizing queries or converting the frequently used ad-hoc SQL queries into SPs or even look at Forced Parameterization as an option. Not necessarily a 3GB proc cache is a problem but there might be scope for tuning here.

    Reference: http://blogs.msdn.com/b/sqlprogrammability/archive/2007/01/21/2-0-diagnosing-plan-cache-related-performance-problems-and-suggested-solutions.aspx

    A sidenote: AWE on 64-bit systems has no meaning. As long as you grant the SQL service account Lock Pages in Memory security privilege, SQL Server can use locked pages for the buffer pool.

    HTH


    This posting is provided "AS IS" with no warranties, and confers no rights.
    My Blog: http://troubleshootingsql.com
    Twitter: @banerjeeamit
    SQL Server FAQ Blog on MSDN: http://blogs.msdn.com/sqlserverfaq
    giovedì 17 febbraio 2011 20:32
  • Amit ,

    What is the threshold value for avg disk sec/read counter ? Because I have this counter value sometime near 0.624 ms during production hours .Is this normal?

    Thanks '

    Daizy

    giovedì 17 febbraio 2011 20:54
  • Do you really need a 48GB page file?
    John Sansom | SQL Server DBA Blog | Twitter
    giovedì 17 febbraio 2011 20:57
  • Hi ,

    not sure .But while I am measuring page file % usage ,I have this counter value vary near  0.037229 .I am not sure how can I find out is this page file is in use or not ? Looking this counter value do we can say this file is in use .

    Thanks .

    giovedì 17 febbraio 2011 21:05
  • Unless you are expecting to catch memory dumps, I see no reason for your page file to be that size. I would suggest cutting it right down to around 2-5GB. 

    Your low page life expectancy would seem to suggest that you have some inefficient queries. Rather than looking at hardware performance metrics in isolation, I would suggest looking to identify which of your queries are generation the most IO. You may for example have some large index scan operations that are causing the existing pages in the buffer to be paged out, in order to make room for the data being read in to the buffer from disk.

    A quick and easy way for you to get a lot of performance tuning information for your environment is to use the freely available SQL Server Performance dashboard reports. These will show you your poorest performing queries in terms of IO, CPU and duration for example.

    The following query may prove useful too:

     

    SELECT TOP 20
      qs.sql_handle,
      qs.execution_count,
      qs.total_physical_reads,
    	qs.total_logical_reads,
      qs.total_worker_time AS Total_CPU,
      total_CPU_inSeconds = --Converted from microseconds
        qs.total_worker_time/1000000,
      average_CPU_inSeconds = --Converted from microseconds
        (qs.total_worker_time/1000000) / qs.execution_count,
      qs.total_elapsed_time,
      total_elapsed_time_inSeconds = --Converted from microseconds
        qs.total_elapsed_time/1000000,
      st.text,
      qp.query_plan
    FROM
      sys.dm_exec_query_stats AS qs
      CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS st
      CROSS apply sys.dm_exec_query_plan (qs.plan_handle) AS qp
    ORDER BY qs.total_physical_reads DESC

     

     


    John Sansom | SQL Server DBA Blog | Twitter
    giovedì 17 febbraio 2011 21:09
  • Daizy

    Have you managed to get following info? I think we need find out whether it is SQLserver doing lots of paging or other process .

    To determine whether SQL Server or another process is the cause of excessive paging, monitor the Process: Page Faults/sec counter for the SQL Server process instance.


    If you think my suggestion is useful, please rate it as helpful.
    If it has helped you to resolve the problem, please Mark it as Answer.

    Sevengiants.com
    giovedì 17 febbraio 2011 21:11
  • Hi ,

    Yesterday ,I mesured page faults/sec counter value between 11 am to 4:30 pm .The minimum value of this counter was 46 and maximum value was 26374.This value indicate SQL server process is the cause of excessive paging .

    Daizy

    giovedì 17 febbraio 2011 21:23
  • Avg disk seconds/read for disk drives hosting SQL Server database files should have average values below 30ms and you shouldn't see prolonged periods with values above 30ms.

    The numbers may vary based on your disk configuration. If the response time is very high, then you might want check if SQL Server is the process that is driving the IO on the disk. You can check the IO performed by SQL Server using IO Data Bytes counter under process (instance name: sqlservr) performance object.


    This posting is provided "AS IS" with no warranties, and confers no rights.
    My Blog: http://troubleshootingsql.com
    Twitter: @banerjeeamit
    SQL Server FAQ Blog on MSDN: http://blogs.msdn.com/sqlserverfaq
    giovedì 17 febbraio 2011 21:26
  • Do you really need a 48GB page file?

    That's an interesting question - what is "best practice" these days, especially for a SQL Server server that is not intended to page much if at all?

    48gb is just 1.5 his RAM size.  Back in the day, that ratio was conservative, but that's before people had 32gb of RAM!

    OTOH, 48gb of disk isn't much anymore, either!

    Josh

     

    giovedì 17 febbraio 2011 22:15
  • I believe the "official" answer (even now) is that if it is required then aim at 1.5x Total Memory, however where things differ from days of yore is that the suggestion is that if your system is over provisioned with memory then the page file can be sized appropriately. For instance it might never be used and therefore it's sizing is irrelevant.

    This url is quite a good reference on the subject http://support.microsoft.com/kb/2267427


    Regards, Mark Broadbent. www.twitter.com/retracement http://tenbulls.co.uk
    giovedì 17 febbraio 2011 23:12
  •  

    I saw some postiong related page file but still Question about Page file :

    The page file is 48 GB and not in use.What would happen if I keet page file size as it is ? Does the big size of page size degrade the SQL server perfomance .

    we are using two nodes cluster .what would be the location of page file ?

    what is good practice to keep the size of page file small or bigger ?

    help me to understand how the page file used by SQL server ,why we need and when will the page file be in use?

    Thanks .

    venerdì 18 febbraio 2011 14:59
  • The size of the page file is recommended 1.5x physical RAM size, because when the server has a page dump, it uses the page file as temp space to do the dump.

    If you turn off the dump and set the memory correctly, so there is very little paging, which is the goal on any server, then you can make the page file smaller.  However, Windows uses the page file internally for temp space, so you do not want to remove it entirely.

    The page file has no effect on SQL Server directly.  It is uses by the OS for extra RAM space.  However, on a dedicated SQL Server, you want to eliminate any paging.

    venerdì 18 febbraio 2011 15:08
    Moderatore
  • Well, in olden days when RAM was super-expensive, the page file was used when there were more programs running, and needing RAM, than you had RAM.  If even a few data pages of the program were not being accessed very often, they'd be copied to the page file.  I believe Windows from NT and XP days on, has also used the .exe and .dll files as their own "pagefiles" for code that gest pages out, as necessary.

    So back then, your 32mb of RAM might need a 48mb (yes megabyte!) page file, just so you could run Excel and Word at the same time!

    Nowadays - not so much!  Some netbooks run Windows and Office and have no pagefiles at all!  Certainly SQL Server wants to be locked in memory and handle its own buffers and caches.  But, even if SQL Server behaves, it's possible that the Windows OS, or some other program you decide to run on the server (like Visual Studio!), may at some point exceed whatever RAM SQL Server has not eaten up, and it will look for a pagefile, and be unhappy if it can't find one!

    So, you probably want to keep a modest pagefile in any case, say 4gb (yes gigabyte, how things have changed!).  But, disk space being cheap, if you're going to do 4gb, why not 48gb?  Well, maybe disk space isn't quite that cheap yet, after all!  Especially if you have a cluster, and need two of them?  Not to mention your dev, QA, and DR servers that you would then want to configure the same way.  So, if it makes sense to cut the pagefiles down towards 4gb, I'm thinking that might be OK.  Of course, if you have a 48gb pagefile now, outside of reserving a bunch of disk space it will never use, it has no other impact. 

    Memory dump in case of BSOD?  Well, if you start getting BSODs, then maybe expand your pagefile up to full size.  FORTUNATELY we don't seem to get a lot of those anymore ... do we? :)

    Josh

     

    venerdì 18 febbraio 2011 18:46
  • Yep I think we are all in agreement that we should always have a page file and the size of it will depend upon potential OS paging. For instance if AV is running on the SQL Server that would cause difficulties governing memory usage/ paging whereas if sql alone was running there would be more control and confidence that a large windows page would be needed.

    I would argue that unless you will be doing crash dump analysis, having memory dumps enabled is potentially rather pointless and so dumps might as well be turned off.

    If space is plentiful then 1.5x is obviously what you might as well go for, however lets not forget a Virtual environment hosted on SAN, 48gb page file per VM can soon add up to a lot of space - 100Vms = 4.8TB!


    Regards, Mark Broadbent. www.twitter.com/retracement http://tenbulls.co.uk
    venerdì 18 febbraio 2011 20:51
  • John ,

    I agree with you .There are some inefficient quries whenever they run I am seeing Disk queue length and low page life expectancy and high cpu usage problems  .What should I do this these type of stored procedures? How can I make those stored procedures better ?

    I used your give query and found their perfomance infromation and query plan .How do I analyze those query plan ?

    for example the stored procedure ran with

    execution _count  -5944

    total_pysical_reads -44329

    total_logical_reads - 17461708

    total_cpu -23168940

    total_cpu_in second - 23

    avg cpu in sec - 0

    total_elapsed _time - 72221675

    total_elapsed _time  in sec - 72

    Any documnetation which will help me in this case.

    Thanks .

    Daizy

     

    lunedì 21 febbraio 2011 22:04
  • Hi Daizy,

    So the thing to consider here is that the stats produced by the query I provided are aggregated. So when looking at CPU for example, a total of 23 seconds from 6 thousand executions does not seem bad at all.

    You may however wish to look at reducing the volume of reads required in order to satisfy your query.

    So the next step is to look at the execution plan for your poorly performing queries to see what operations are being performed. For example, are there any Scan operations being performed (as these are expensive) when a seek may be more preferable. You will also want to look at what tables are being referenced, what predicates are being used and whether or not additional indexes could be benifical.

    Take a look at the following post which contains details toward the end as to how you can extrapolate/view the XML query plan from the T-SQL query in the earlier post, in a graphical format.

    How to identify the most costly SQL Server queries using DMV’s

     


    John Sansom | SQL Server DBA Blog | Twitter
    lunedì 21 febbraio 2011 22:29
  • John ,

    I agree with you .There are some inefficient quries whenever they run I am seeing Disk queue length and low page life expectancy and high cpu usage problems  .What should I do this these type of stored procedures? How can I make those stored procedures better ?

    I used your give query and found their perfomance infromation and query plan .How do I analyze those query plan ?

    for example the stored procedure ran with

    execution _count  -5944

    total_pysical_reads -44329

    total_logical_reads - 17461708

    total_cpu -23168940

    total_cpu_in second - 23

    avg cpu in sec - 0

    total_elapsed _time - 72221675

    total_elapsed _time  in sec - 72

    Any documnetation which will help me in this case.

    Thanks .

    That's about 0.004 seconds, 3000 reads per execution.

    That's already pretty fast!

    Say, 1m pages/second max *logical* reads, about 1500 physical pages/sec - 12mbytes/second.

    Remember, you *will* see spikes in usage stats on even the very best systems when there is a spike in activity!

    If I had those system numbers, I'd go play golf.

    Josh

     

    martedì 22 febbraio 2011 00:03
  • Any documnetation which will help me in this case.
    Thanks .
    Daizy
    http://technet.microsoft.com/en-us/library/cc966540.aspx (Troubleshooting Performance Problems in SQL Server 2005)

    BTW, can you confirm that you have enabled lock pages in memory for SQL Startup (Service) account? If you can post first 20 lines from ERRORLOG, we can get information about your system.
    Balmukund Lakhani | Please mark solved if I've answered your question, vote for it as helpful to help other user's find a solution quicker
    --------------------------------------------------------------------------------
    This posting is provided "AS IS" with no warranties, and confers no rights.
    --------------------------------------------------------------------------------
    My Blog: http://blogs.msdn.com/blakhani
    Team Blog: http://blogs.msdn.com/sqlserverfaq
    martedì 22 febbraio 2011 01:50
    Moderatore
  • Balmukund ,

    Here is some line from errorlog file ,

    2011-02-20 19:11:15.04 Server      Microsoft SQL Server 2005 - 9.00.4035.00 (X64)
     Nov 24 2008 16:17:31
     Copyright (c) 1988-2005 Microsoft Corporation
     Enterprise Edition (64-bit) on Windows NT 5.2 (Build 3790: Service Pack 2)

    2011-02-20 19:11:15.06 Server      (c) 2005 Microsoft Corporation.
    2011-02-20 19:11:15.06 Server      All rights reserved.
    2011-02-20 19:11:15.06 Server      Server process ID is 1224.
    2011-02-20 19:11:15.06 Server      Authentication mode is MIXED.
    2011-02-20 19:11:15.06 Server      Logging SQL Server messages in file 'S:\Microsoft SQL Server\MSSQL.1\MSSQL\LOG\ERRORLOG'.
    2011-02-20 19:11:15.06 Server      This instance of SQL Server last reported using a process ID of 2416 at 2/20/2011 7:10:31 PM (local) 2/21/2011 1:10:31 AM (UTC). This is an informational message only; no user action is required.
    2011-02-20 19:11:15.06 Server      Registry startup parameters:
    2011-02-20 19:11:15.07 Server        -d S:\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\master.mdf
    2011-02-20 19:11:15.07 Server        -e S:\Microsoft SQL Server\MSSQL.1\MSSQL\LOG\ERRORLOG
    2011-02-20 19:11:15.07 Server        -l S:\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\mastlog.ldf
    2011-02-20 19:11:15.10 Server      SQL Server is starting at normal priority base (=7). This is an informational message only. No user action is required.
    2011-02-20 19:11:15.10 Server      Detected 8 CPUs. This is an informational message; no user action is required.
    2011-02-20 19:11:15.12 Server      Large Page Extensions enabled.
    2011-02-20 19:11:15.12 Server      Large Page Granularity: 2097152
    2011-02-20 19:11:15.14 Server      Large Page Allocated: 32MB
    2011-02-20 19:11:15.23 Server      Using locked pages for buffer pool.
    2011-02-20 19:11:15.41 Server      Using dynamic lock allocation.  Initial allocation of 2500 Lock blocks and 5000 Lock Owner blocks per node.  This is an informational message only.  No user action is required.
    2011-02-20 19:11:15.64 Server      Attempting to initialize Microsoft Distributed Transaction Coordinator (MS DTC). This is an informational message only. No user action is required.
    2011-02-20 19:11:16.85 Server      Attempting to recover in-doubt distributed transactions involving Microsoft Distributed Transaction Coordinator (MS DTC). This is an informational message only. No user action is required.
    2011-02-20 19:11:16.88 Server      Database mirroring has been enabled on this instance of SQL Server.
    2011-02-20 19:11:16.95 spid5s      Starting up database 'master'.
    2011-02-20 19:11:17.83 spid5s      Recovery is writing a checkpoint in database 'master' (1). This is an informational message only. No user action is required.

    Thanks

    Daizy

    martedì 22 febbraio 2011 14:40
  • 2011-02-20 19:11:15.23 Server      Using locked pages for buffer pool.

    If you think my suggestion is useful, please rate it as helpful.
    If it has helped you to resolve the problem, please Mark it as Answer.

    Sevengiants.com
    martedì 22 febbraio 2011 16:37