pages/sec question

Question

pages/sec question

chewychewy

Hall of Fame

Points: 3126
More actions
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

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

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

mrdenny SSCrazy Points: 2898 More actions · Answer 1

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.

chewychewy Hall of Fame Points: 3126 More actions · Answer 2

Hi Denny,

thanks.

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

mrdenny SSCrazy Points: 2898 More actions · Answer 3

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

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 4

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

Arjen Krap SSC-Addicted Points: 473 More actions · Answer 5

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.