Microsoft SQL Server 2005 and 2008 Standard edition will be getting the Lock Pages in Memory feature that SQL Server Enterprise Edition has had starting in SQL Server 2005. This was announced by Bob Ward via the CSS Blog in his post “SQL Server, Locked Pages, and Standard SKU…“. Per Bob’s post a CU will be released for SQL 2005 SP3 and SQL 2008 SP1 which will allow for a trace flag to be used to turn this feature on.
On behalf of the users I’d like to thank Bob and the rest of the SQL Server team for being able to get this into the product.
On behalf of the developer team, I emplore you to not turn this on for no reason. Only use this feature once you understand what this feature does and in the correct places.
The Locked Pages flag bascially tells the SQL Server that if it is told to flush RAM to disk to ignore it. If the setting is enabled then SQL doesn’t flush to disk. If you find that your SQL Server is flushing to disk, don’t just enable the flag and move on. I emplore you to contact CSS and figure out why SQL is being told to flush to disk. This is the only way the bug will be fixed. Once the issue has been reported to Microsoft and they have the information they need enable the flag until the bug is fixed. Then install the patch to fix the bug, disable the flag and you’ll be fine.
Because of the fact that this is how bugs are found and fixed I hope that this is a CSS only CU which will require that users contact CSS before they can get the fix.
I have a server with SQL Server Standard Edition 64 bit. I granted the service account Lock Pages In Memory priviledges. Whenever the server or service is restarted, I see this message in the Error Log.
A significant part of sql server process memory has been paged out. This may result in a performance degradation. Duration: 0 seconds. Working set (KB): 52536, committed (KB): 203428, memory utilization: 25%.
I don’t ever see it after that. I’ve read some opinions that this is due to a limitation with Standard edition. Do you have any thoughts on this? Thanks.