资源池“默认"中没有足够的系统内存来运行此查询.在SQL上 [英] There is insufficient system memory in resource pool 'default' to run this query. on sql
问题描述
我有一项正在运行的服务，该服务每分钟会收到50-100个查询.这些都不是高成本的查询.该服务已运行3-4个月左右，没有任何错误.
I have a running service that gets 50-100 queries per minute. And these are not high cost queries. This service has been running for around 3-4 months without any errors.
突然几天前，它开始给出资源池'default'中没有足够的系统内存来运行此查询." 有时会出错.当我调查问题时，我发现 sqlservr.exe 使用的是〜1.5 GB RAM和％25的CPU(全部为1/4CPU).当我重新启动
Suddenly few days ago it started giving "There is insufficient system memory in resource pool 'default' to run this query." error occasionally. When I investigate the problem I saw that sqlservr.exe was using ~1.5 gb ram and %25 of CPU(all of 1/4CPU). And when I restarted the sqlservr.exe the ram starts from ~50mb and slowly increase till it becomes ~1.5gb then leads to crashes in the apps using it.
经过一些研究后，我发现这是由我使用的sql server版本引起的.这是速成版，数量仅限于此.因此，我已将sql服务器从'2008r2 express'升级到'2012 enterprise'.当我启动该服务时，我认为我的问题终于解决了，因为该服务仅使用了约60mb的内存，但是不幸的是，在一小时内开始出现相同的问题，但是这次我在Windows任务管理器上看到的已用内存仍然是〜60mb，不超过任何限制.
After I have made little bit of research I figured that it is caused by the edition of sql server I use. It was express edition limiting the numbers to those. So I have upgraded my sql server from '2008r2 express' to '2012 enterprise'. When I started the service I thought my problems are finally over, since the service uses only ~60mb of memory, but in an hour unfortunately same problem started occurring, but this time the used memory I see on windows task manager is still ~60mb, not excessing any limits.
我在wcf服务中将 EntityFramework 用作ORM.连同它，我还有 SqlQueryNotification (代理和填充)系统来进行某些缓存操作.
I use EntityFramework as ORM in a wcf service. And along with it I have SqlQueryNotification(broker and stuff) system for some caching operations.
我缺少一些关键的配置点吗?还是6GB的内存和我的4个CPU真的不够用?但这不可能是因为相同的负载持续了3个月，并且那时没有任何错误，并且代码也没有任何变化.
Am I missing some crucial configuration points? Or 6gbs of memory and my 4 CPU is really not enough for this? But it can't be that because same load was like that for 3 months and there wasn't any error back then, and there is not any change of codes either.
推荐答案
SQL Server将根据需要启动尽可能多的内存，然后缓慢启动直到使用服务器属性中分配给它的所有内存为止
SQL Server will start with as much memory as needed, then slowly ramp up until it uses all allocated to it in the Server Properties:
在重新启动服务之前，它不会释放任何该内存；这是设计使然.
It will not release any of this memory until the service is restarted; this is by design.
通常建议为操作系统保留2ish GB，并且您需要注意与SQL在同一服务器上运行的任何其他处理.通常建议在自己的服务器上安装SQL Server，而不在该服务器上运行其他任何软件.
It is generally recommended to leave 2ish GB for the OS, and you need to be mindful of any other processing running on the same server as SQL. It is usually recommended to have SQL Server on it's own server without anything else running there.
也就是说，用于SQL Server的1.5 GB RAM并不多.如果没有更多可用资源，则可能是时候添加一些资源或升级服务器了.
That said, 1.5 GB of RAM for SQL Server isn't that much. If you don't have more available, it may be time to add some or upgrade the server.
