提示信息
资源池“default”没有足够的系统内存来运行此查询
测试过程(SQL Server 2022),采用SQL SERVER的系统视图
测试语句:
SELECT distinct text
FROM sys.messages
![](/download/01006cf74b04ea0445b6aaf44d56d405750b.png)
由于排序操作,所以产生了worktable
![](/download/01006429a80710c74cb89c5180eb7d3b7073.png)
Sort操作属性
![](/download/01002326c8b3f7e14008a4fc957bcf184fda.png)
![](/download/01002c43e04333b54fb28fc68bb30e37fc6d.png)
SELECT操作属性
![](/download/0100a9234a421db24069a0d0b2b994290df4.png)
从执行计划中,可以看到,SQL语句需要超过80MB内存来完成。
(Sql server 2022 自动调整内存功能,第二次执行时,发现上次(LastRequestedMemory=45688)需要内存为45MB,所以调整到到45MB)
![](/download/0100c8f9b680242b40219afe751e2d6be048.png)
通过语句查询内存分配情况,分配的内存为45MB
SELECT
pool_id
,total_memory_kb
,available_memory_kb
,granted_memory_kb
,used_memory_kb
,grantee_count, waiter_count
,resource_semaphore_id
FROM sys.dm_exec_query_resource_semaphores rs
![](/download/01004baa5da62f88408187e9f3f720b8007c.png)
将SQL SERVER的最大物理内存设置为100MB,进行测试,并且设置执行计划按100%分配内存,发现一直处于正在执行…状态
![](/download/010095bcb5465ace4e6c8efbf35c4877bd3f.png)
使用查看计划内存耗用语句,提示:资源池“default”没有足够的系统内存来运行此查询。
![](/download/01006a60ba20352143afbfcff968203f6296.png)
查看sql server的错误日志,可以看到大量的如下信息
![](/download/0100a194d8e7cc7040af944e6e7c6334ed38.png)
基于错误17300的解释如下,说明当前的内存不足
![](/download/0100df9011a6a0f043e0ad4aeff362619019.png)
说明采用100%分配的内存模式,无法完成,因为物理内存总共才100MB。
去掉提示,可以看到如下信息
![](/download/01005ef3e44e691a4fd8a736a82a04f70abd.png)
所以当出现没有足够的系统内存来运行此查询的时候,可以明确当时的确无法分配需要的内存来执行SQL,这种情况下,需要考虑增加物理内存来解决。
如果执行任何语句都报这个错误,那么只能重启SQL SERVER来解决。
如何消除此问题
上面示例在于机器内存只有1GB,无法满足需求,更换一台高内存的机器,调大内存,看是否可以解决溢出问题。版本:SQL Server 2017
![](/download/01009be5a84c5a88444d8069ccc4c2191799.png)
可以看到,设置后,可以解决溢出问题。
但是否没有溢出问题,性能就变好呢?
![](/download/01004d860efe2fc64d51bf395de9c3e56074.png)
从执行情况来看,溢出的情况下,执行效率实际好于非溢出的情况,所以除非分配更大的授权内存可以解决问题,否则不建议采用查询提示。
基于分配内存过高或者不准确问题的问题,可以升级到sql server 2022采用自动调整分配内存的参数来解决。
另外验证过程中,发现相同的distinct操作,SQL Server 2022采用sort,SQL Server 2017采用hash操作。说明不同的SQL Server版本,执行计划是会存在变化的。
![](/download/010085855a9069ca4ff8a1d8b7e05e8c4ece.png)
附
如果执行哈希或排序操作需要的内存多于初始授予,这些内存将溢出到磁盘。 溢出的哈希操作为workfile 支持,而溢出的排序操作为worktable。
https://learn.microsoft.com/zh-cn/troubleshoot/sql/database-engine/performance/troubleshoot-memory-grant-issues#what-can-a-developer-do-about-sort-and-hash-operations
![](/download/0100a131ff3ae8234781a678cd11b46a08e3.png)