A significant part of sql server process memory has been paged out. This may result in performance d

A good article about

"A significant part of sql server process memory has been paged out. This may result in performance degradation. Duration 0 seconds. Working set (KB) 25432, committed (KB) 11296912, memory utilization 0%"

[@more@]

If you are using SQL Sever Server standard edition 64 bit on a Windows 2003 64bit, you will frequently encounter this problem where SQL Server says:

A significant part of sql server process memory has been paged out. This may result in performance degradation. Duration 0 seconds. Working set (KB) 25432, committed (KB) 11296912, memory utilization 0%

The number in working set and duration will vary. What happens here is SQL Server is forced to release memory to operating system because some other application or OS itself needs to allocate RAM.

We went through many support articles like:

  • 918483: How to reduce paging of buffer pool memory in the 64-bit version of SQL Server 2005
  • 905865: The sizes of the working sets of all the processes in a console session may be trimmed when you use Terminal Services to log on to or log off from a computer that is running Windows Server 2003
  • 920739: You may experience a decrease in overall system performance when you are copying files that are larger than approximately 500 MB in Windows Server 2003 Service Pack 1

But nothing solved the problem. We still have the page out problem happening every day.

The server has 16 GB RAM where 12 GB is maximum limit allocated to SQL Server. 4 GB is left to OS and and other application. We have also turned off antivirus and any large backup job. 12 GB RAM should be plenty because there’s no other app running on the dedicated SQL Server box. But the page out still happens. When this happens, SQL Server becomes very slow. Queries timeout, website throws error, transactions abort. Sometimes this problems goes on for 30 to 40 minutes and website becomes slow/unresponsive during that time.

I have found what causes SQL Server to page out. File System cache somehow gets really high and forces SQL Server to trim down.

clip_image002

You see the System cache resident bytes are very high. During this time SQL Server gets much less RAM than it needs. Queries timeout at very high rate like 15 per sec. Moreover, there’s high SQL Lock Timeout/sec (around 15/sec not captured in screen shot).

clip_image004

SQL Server max memory is configured 12 GB. But here it shows it’s getting less than 8 GB.

While the file system cache is really high, there’s no process that’s taking significant RAM.

clip_image006

After I used SysInternal’s CacheSet to reset file system cache and set around 500 MB as max limit, memory started to free up.

clip_image008

SQL Server started to see more RAM free:

clip_image010

Then I hit the “Clear” button to clear file system cache and it came down dramatically.

clip_image012

Paging stopped. System cache was around 175 MB only. SQL Server lock timeout came back to zero. Everything went back to normal.

So, I believe there’s either some faulty driver or the OS itself is leaking file system cache in 64bit environment.

What we have done is, we have a dedicated person who goes to production database servers every hour, runs the CacheSet program and clicks “Clear” button. This clears the file system cache and prevents it from growing too high.

There are lots of articles written about this problem. However, the most informative one I have found is from the SQL Server PSS team:

http://blogs.msdn.com/psssql/archive/2007/05/31/the-sql-server-working-set-message.aspx

UPDATE – THE FINAL SOLUTION!

The final solution is to run this program on Windows Startup:

SetSystemFileCacheSize 128 256

This sets the lower and higher limit for the System Cache. You need to run this on every windows startup because a restart will undo the cache setting to unlimited.

You can run the program without any parameter to see what is the current setting.

Download the program from this page:

http://www.uwe-sieber.de/ntcacheset_e.html

Go to the end and you will get the link to the SetSystemFileCacheSize.zip

From: http://omaralzabir.com/a_significant_part_of_sql_server_process_memory_has_been_paged_out__this_may_result_in_performance_degradation/

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/66009/viewspace-1058490/,如需转载,请注明出处,否则将追究法律责任。

转载于:http://blog.itpub.net/66009/viewspace-1058490/

  • 0
    点赞
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值