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

最新推荐文章于 2021-08-03 15:41:40 发布
最新推荐文章于 2021-08-03 15:41:40 发布
阅读量878 收藏
点赞数
文章标签： 数据库

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/

ciwan8376
关注
  • 0
    点赞
  • 踩
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
A significant part of sql server process memory has been paged out
cuihengju8933的博客
12-25 1658
问题现象： Sqlserver 2014 alwayson架构主节点日志依次报错： 日期 2017/12/25 10:02:35 日志 SQL Server (当前 - 2017/12/25 10:33:00) 源 ...
Expert SQL Server in-Memory OLTP(Apress,2015)
09-22
Expert SQL Server In-Memory OLTP is a deep dive into one of the most significant features of SQL Server 2014 – support for In-Memory Online Transaction Processing. The book describes the architecture...
SQL Error log/Event ID(17890): A significant part of sql server process memory has been paged out. T...
weixin_30270889的博客
08-13 834
SQL Error log/Event ID(17890): A significant part of sql server process memory has been paged out. This may result.... When you are using SQL2005, maybe you'll meet the SQL ...
数据库服务器禁用虚拟内存,SQLServer因为OS虚拟内存不足而hang住异常解决-锁定内存页 (LPIM)...
weixin_35698805的博客
08-03 681
SQLServer因为OS虚拟内存不足而hang住异常解决-锁定内存页 (LPIM)发布时间：2020-08-13 12:30:03来源：ITPUB博客阅读：73作者：guocun09异常：DB: SQL Server2012SP3OS: Windows2012一线工程师反应系统访问DB突然卡住了，DBA登录DB检查SQLServer发现DB无法登录，之前SMSS已经连接的session中无法执行...
理解内存----优化SQL Server内存配置
热门推荐
Burgess_Liu的专栏
01-03 2万+
最小和最大Server内存 Min Server Memory (MB) 和 Max Server Memory (MB)控制所有SQL Server内存使用的许可大小。比起之前的版本，SQL Server 2012的Memory Manager可以更简单地设置SQL Server内存需求的大小。SQL Server服务是以所需的最小量启动的，并根据需要增长。一旦内存使用增长超过Min Serv
3 .6 .4 优化SQL Server内存酉己置
weixin_33860553的博客
06-29 85
3 .6 .4 优化SQL Server内存酉己置1 .最小和最大服务器内存这两个配置用于控制SQL Server可用内存的大小。对于最小内存，在 SQL Server服务 启动时，不会马上达到这个设置值，而是仅使用最小的需求内存，然后按需增长，一旦增 长到最小内存设置值时，SQL Server将不会再释放内存。最大内存用于设置内存使用的上 限，可以使用SSMS或者sp_COnfigU re来配置...
Murach's SQL Server 2016 for Developers
09-11
of the In-Memory OLTP technology in SQLServer 2016, which extend the number of potential use cases and allow implementation with less development effort and risk. Chapter 13, Supporting R in SQL ...
SQL Server 2016 Developer's Guide
03-26
It prepares you for the more advanced topics by starting with a quick introduction to SQL Server 2016's new features and a recapitulation of the possibilities you may have already explored with ...
SetSystemFileCache
03-27
windows2008专用的刷新缓存工具
SQL Server内存故障排除
weixin_33970449的博客
06-16 415
SQLServer内存故障排除 翻译自：https://blogs.msdn.microsoft.com/karthick_pk/2012/06/15/troubleshooting-sql-server-memory/ SQL Server内存故障排除的第一步，是识别是否低内存条件出现在了MemToLeave或BPool或者因为外部内存压力。 注意：如果你不知道什么是BPOOL或者MemToLe...
Write a paper about Deep-learning based analysis of metal-transfer images in GMAW process , requiring 10000 words
最新发布
02-16
Introduction Gas metal arc welding (GMAW), also known as metal inert gas (MIG) welding, is a widely used industrial process that involves the transfer of metal droplets from a consumable electrode wire to a workpiece through a welding arc. In this process, the welding operator controls various welding parameters, such as welding current, voltage, wire feed speed, and electrode polarity, to achieve the desired weld bead geometry and properties. The metal transfer mechanism plays a critical role in determining the weld quality and productivity in GMAW. Therefore, there has been significant interest in developing automated methods for analyzing the metal transfer images and extracting useful information about the process. In recent years, deep learning has emerged as a powerful technique for analyzing and processing images. Convolutional neural networks (CNNs) are a type of deep learning model that can learn features from images in an end-to-end manner, without requiring explicit feature engineering. In this paper, we present a deep-learning based approach for analyzing metal transfer images in GMAW. We first discuss the dataset used in this study, followed by a detailed description of the proposed method. We then present the experimental results and discuss the implications of our findings. Dataset The metal transfer images were captured using a high-speed camera at a frame rate of 20,000 frames per second. The camera was positioned perpendicular to the welding direction and had a resolution of 1280 × 1024 pixels. The images were captured during the welding of mild steel plates using a GMAW process with a 1.2 mm diameter wire. The welding current, voltage, and wire feed speed were varied to obtain a range of metal transfer modes, including short-circuiting, globular, and spray transfer modes. The dataset consists of 10,000 metal transfer images, with each image labeled with the corresponding metal transfer mode. Proposed method The proposed method for analyzing metal transfer images in GMAW consists of the following steps: 1. Image preprocessing: The metal transfer images are preprocessed to remove any noise and artifacts. A Gaussian filter is applied to smooth the images, followed by a contrast enhancement step using histogram equalization. 2. Feature extraction: A CNN is used to extract features from the preprocessed images. The CNN architecture used in this study is based on the VGG-16 model, which has shown excellent performance in image classification tasks. The VGG-16 model consists of 13 convolutional layers and 3 fully connected layers. The output of the last convolutional layer is used as the feature vector for each image. 3. Classification: The feature vectors extracted from the metal transfer images are used to train a multiclass classification model. In this study, we used a support vector machine (SVM) classifier with a radial basis function (RBF) kernel. The SVM classifier was trained on 80% of the dataset and tested on the remaining 20%. Experimental results The proposed method was evaluated on the dataset of 10,000 metal transfer images. The classification accuracy achieved by the SVM classifier was 96.7%, indicating that the method can accurately classify the metal transfer modes in GMAW. To further validate the performance of the method, we compared it with two other classification models: a decision tree classifier and a random forest classifier. The decision tree classifier achieved an accuracy of 85.2%, while the random forest classifier achieved an accuracy of 94.5%. These results demonstrate that the proposed method outperforms these traditional machine learning models. To further analyze the performance of the method, we conducted a sensitivity analysis by varying the number of convolutional layers in the CNN. We found that the performance of the method improved with increasing number of convolutional layers, up to a certain point, after which there was no significant improvement

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

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

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

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

抵扣说明：

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

余额充值