SQL Server 2019实例查询计划缓存随机清空原因排查求助

以下是除定时执行DBCC FREEPROCCACHE外，可能导致该现象的原因：

• 内存压力触发自动缓存清理
  SQL Server在系统出现内存压力时会自动清理计划缓存，比如外部进程占用大量内存导致SQL Server可用内存不足。可通过查询sys.dm_os_ring_buffers查看是否存在RESOURCE_MEMPHYSICAL_LOW事件，或检查sys.dm_os_memory_clerks中各类内存分配情况，确认是否存在内存挤压。

• 查询计划的自动失效与回收

  • Schema变更：若应用频繁修改表结构（如增删列、索引），会导致依赖该表的所有计划失效并被清理。可通过sys.dm_exec_query_stats的last_execution_time和plan_generation_num字段，结合系统日志排查是否有频繁的schema变更操作。
  • 统计信息更新：自动或手动更新统计信息后，SQL Server会标记相关计划为无效，触发重新编译并清理旧计划。可查询sys.dm_db_stats_properties查看统计信息的更新频率。
  • 参数敏感或非参数化查询：大量非参数化查询、参数敏感导致的计划频繁重编译，会使计划无法长期缓存。可检查sys.dm_exec_cached_plans中objtype为Adhoc的占比，以及sys.dm_exec_query_stats的recompile_count字段查看重编译次数。

• 实例配置或服务器环境问题

  • 内存配置冲突：虽设置了6GB最大服务器内存，但可能min server memory设置过高，或服务器总内存不足导致SQL Server无法分配足够内存。可通过sys.dm_os_sys_info的physical_memory_in_use_kb和total_physical_memory_kb确认实际分配情况。
  • 服务账户权限不足：若SQL Server服务账户没有锁定内存页权限，会限制内存使用，进而影响缓存。
  • 内存优化功能抢占资源：若实例启用内存优化表，可能抢占计划缓存的内存空间，可通过sys.dm_db_xtp_memory_consumers确认内存优化对象的占用情况。

• 应用层或连接池问题

  • 连接池配置不合理：应用连接池频繁重置，或每次请求新建连接并执行DBCC FREESESSIONCACHE（会话级清理会影响该会话的计划），会导致计划无法复用。可检查应用连接字符串是否禁用连接池或设置过短超时。
  • 一次性查询占比过高：应用执行大量无复用价值的动态生成SQL，SQL Server会将这类计划标记为“单使用”，在内存压力下优先清理，甚至不缓存。可查看sys.dm_exec_cached_plans中usecounts为1的计划占比。

• 第三方工具或扩展存储过程影响
  部分监控、备份工具或自定义扩展存储过程可能在后台执行缓存清理操作，这类操作可能未被SQL Profiler完全捕获。可检查服务器上运行的第三方服务，或通过sys.dm_exec_requests、sys.dm_exec_query_history排查异常后台操作。

内容的提问来源于stack exchange，提问作者Paul Trotter