Post reply

pages/sec question

  • July 15, 2012 at 8:26 am

    #272565

    Hi Guys,

    Let's say my server has 16 GB physical ram. I have set the sql server max memory setting to 4 GB to cap it.

    If my sql server doesn't have enough memory (since i cap it), will i see an increase in pages/sec?

    thanks

  • July 15, 2012 at 8:36 am

    #1512868

    Yes, you'll see an increase not the physical disk io, provided that your working set is larger than the amount of memory you have allocated to SQL Server.

  • July 15, 2012 at 8:43 pm

    #1512910

    Hi Denny,

    thanks.

    can elaborate more on this point? --> increase not the physical disk io

  • July 15, 2012 at 9:57 pm

    #1512912

    That should have said increase in physical io. Apparently autocorrect made a mess of my post.

  • July 16, 2012 at 8:40 am

    #1513112

    chewychewy (7/15/2012)

    Hi Guys,

    Let's say my server has 16 GB physical ram. I have set the sql server max memory setting to 4 GB to cap it.

    If my sql server doesn't have enough memory (since i cap it), will i see an increase in pages/sec?

    thanks

    Did you set it that low for testing or for some other reason?

    Best,
    Kevin G. Boles
    SQL Server Consultant
    SQL MVP 2007-2012
    TheSQLGuru on googles mail service

  • July 18, 2012 at 7:31 am

    #1514205

    Most memory used by SQL Server is used for the Buffer Cache. The Buffer Cache is the working space in where data is kept for frequent access. This works similar to the Windows disk cache which is kept in memory as well. In general if you allocate a lot of memory to SQL Server this will reduce reading from the data files. A smaller Buffer Cache will increase reading the same information multiple times from disk, which may become a bottle neck depending on your workload.

    The Buffer Cache competes for memory with the Windows disk cache and other memory intensive processes. Both the Buffer Cache and the Windows disk cache are very gently in returning memory to the operating system when needed. Both the Buffer Cache and the Windows disk cache do not swap to the page file since the data is already stored somewhere else on disk.

    If you decrease the memory allocated to SQL Server from 16 GB to 4 GB. You will probably not see a increase in the Pages/sec performance counter. If you have other memory of disk intensive processes running on the same server you might even see an decrease in Pages/sec.

    You might see a drop in Buffer Cache Hit Ratio. If this falls below 90 or 80% you will probable increase performance by adding the more memory to Buffer Cache. If this stays above 98% you have more than enough memory for you current workload at the moment.

  • July 21, 2012 at 1:40 am

    #1515830

    Hi SQLGURU,

    Just for testing purpose.

    thanks

  • July 21, 2012 at 1:41 am

    #1515831

    Hi Arjen,

    In this case under what scenario can i see an increase of pages/sec?

    thanks

  • July 21, 2012 at 4:16 am

    #1515833

    Hi Chewychewy,

    You may want to look at these articles:

    PerfMon: High Number of Pages/Sec Not Necessarily Low Memory

    Enable the Lock Pages in Memory Option (Windows)

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply