SQL Server 定期清除计划缓存和执行统计信息
sql-server将 SQL Server 2014 升级到 2016 后，服务器每隔几个小时dm*就会不断重置缓存的执行计划和视图（如dm_exec_query_stats）等
好像有人手动执行（除了没有人执行，它是自动发生的）DBCC FREEPROCCACHE。DBCC DROPCLEANBUFFERS
同一个数据库在 SQL Server 2014 和 Windows Server 2012 上运行良好，但在迁移到 SQL Server 2016（和 Windows Server 2016）之后，情况就变糟了
我检查的事情：数据库没有“自动关闭”标志。SQL 服务器ad hoc optimized设置为true（我认为它会有所帮助，但没有）。“查询存储”是“关闭”的。服务器有 16 GB 内存。
“SQL Server 日志”中也没有任何帮助。只是每周的备份消息…
我还检查了这篇文章https://learn.microsoft.com/en-us/sql/t-sql/statements/alter-database-transact-sql-set-options（向下滚动到“示例”部分，就在上面它）有一个自动清除计划的情况列表。这些都不适用。
更新：
不幸的是，这些建议都没有帮助。授予 LPIM 权限，检测和修复为同一查询生成大量计划的非参数化查询，降低“最大服务器内存”......计划不断随机重置，从每几个小时到每 5-10 分钟。如果服务器“承受内存压力”，2014 版如何在同一台机器上正常工作。
这是请求的 sp_Blitz 输出
**Priority 10: Performance**:
- Query Store Disabled - The new SQL Server 2016 Query Store feature has not been enabled on this database.
* xxx
**Priority 50: Server Info**:
- Instant File Initialization Not Enabled - Consider enabling IFI for faster restores and data file growths.
**Priority 100: Performance**:
- Resource Governor Enabled - Resource Governor is enabled. Queries may be throttled. Make sure you understand how the Classifier Function is configured.
**Priority 120: Query Plans**:
- Implicit Conversion Affecting Cardinality - One of the top resource-intensive queries has an implicit conversion that is affecting cardinality estimation.
*
- Missing Index - One of the top resource-intensive queries may be dramatically improved by adding an index.
*
- RID or Key Lookups - One of the top resource-intensive queries contains RID or Key Lookups. Try to avoid them by creating covering indexes.
*
**Priority 170: File Configuration**:
- System Database on C Drive
* master - The master database has a file on the C drive. Putting system databases on the C drive runs the risk of crashing the server when it runs out of space.
* model - The model database has a file on the C drive. Putting system databases on the C drive runs the risk of crashing the server when it runs out of space.
* msdb - The msdb database has a file on the C drive. Putting system databases on the C drive runs the risk of crashing the server when it runs out of space.
**Priority 200: Backup**:
- MSDB Backup History Not Purged msdb - Database backup history retained back to Jun 10 2017 9:47PM
**Priority 200: Informational**:
- Backup Compression Default Off - Uncompressed full backups have happened recently, and backup compression is not turned on at the server level. Backup compression is included with SQL Server 2008R2 & newer, even in Standard Edition. We recommend turning backup compression on by default so that ad-hoc backups will get compressed.
**Priority 200: Non-Default Server Config**:
- Agent XPs - This sp_configure option has been changed. Its default value is 0 and it has been set to 1.
- max server memory (MB) - This sp_configure option has been changed. Its default value is 2147483647 and it has been set to 15000.
- optimize for ad hoc workloads - This sp_configure option has been changed. Its default value is 0 and it has been set to 1.
- show advanced options - This sp_configure option has been changed. Its default value is 0 and it has been set to 1.
- xp_cmdshell - This sp_configure option has been changed. Its default value is 0 and it has been set to 1.
**Priority 200: Performance**:
- Buffer Pool Extensions Enabled - You have Buffer Pool Extensions enabled, and one lives here: Z:\sql_buffer_pool.BPE. It's currently 60.00000000000 GB. Did you know that BPEs only provide single threaded access 8KB (one page) at a time?
- cost threshold for parallelism - Set to 5, its default value. Changing this sp_configure setting may reduce CXPACKET waits.
**Priority 240: Wait Stats**:
- No Significant Waits Detected - This server might be just sitting around idle, or someone may have cleared wait stats recently.
**Priority 250: Informational**:
- SQL Server Agent is running under an NT Service account - I'm running as NT Service\SQLSERVERAGENT. I wish I had an Active Directory service account instead.
- SQL Server is running under an NT Service account - I'm running as NT Service\MSSQLSERVER. I wish I had an Active Directory service account instead.
**Priority 250: Server Info**:
- Default Trace Contents - The default trace holds 125 hours of data between Aug 19 2017 11:55AM and Aug 24 2017 4:59PM. The default trace files are located in: C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\Log
- Hardware - Logical processors: 2. Physical memory: 15GB.
- Hardware - NUMA Config - Node: 0 State: ONLINE Online schedulers: 2 Offline schedulers: 0 Processor Group: 0 Memory node: 0 Memory VAS Reserved GB: 29
- Locked Pages In Memory Enabled - You currently have 12.02534484863 GB of pages locked in memory.
- Memory Model Unconventional - Memory Model: LOCK_PAGES
- Server Last Restart - Aug 20 2017 12:32PM
- Server Name - xx
- Services
- Service: SQL Full-text Filter Daemon Launcher (MSSQLSERVER) runs under service account NT Service\MSSQLFDLauncher. Last startup time: not shown.. Startup type: Manual, currently Running.
- Service: SQL Server (MSSQLSERVER) runs under service account NT Service\MSSQLSERVER. Last startup time: Aug 20 2017 12:32PM. Startup type: Automatic, currently Running.
- Service: SQL Server Agent (MSSQLSERVER) runs under service account NT Service\SQLSERVERAGENT. Last startup time: not shown.. Startup type: Automatic, currently Running.
- SQL Server Last Restart - Aug 20 2017 12:33PM
- SQL Server Service - Version: 13.0.4446.0. Patch Level: SP1. Edition: Enterprise Edition (64-bit). AlwaysOn Enabled: 0. AlwaysOn Mgr Status: 2
- Virtual Server - Type: (HYPERVISOR)
- Windows Version - You're running a pretty modern version of Windows: Server 2012R2 era, version 6.3
**Priority 254: Rundate**:
- Captain's log: stardate something and something...
阅读 210
2022-11-11
共1个答案
首先，获取清除计划缓存的确切时间。这是最简单的方法 - 它应该几乎立即运行，并且不会阻止任何人：
SELECT TOP 1 creation_time
FROM sys.dm_exec_query_stats WITH (NOLOCK)
ORDER BY creation_time;
如果您认为该日期/时间比您预期的要旧，那么只有部分计划缓存被清除。例如，也许有人正在执行索引重建或更新统计工作，这将刷新受影响的特定对象的计划缓存 - 但其他对象仍将保留。当系统查询（如 DMV 查询）持续存在时，我经常看到这种情况，但用户数据库计划已被清除。
如果该日期/时间以特定的时间间隔更新，例如它似乎每 2 小时更新一次，比如 6:00、8:00、10:00 等，那么可能有人正在运行导致计划缓存的作业或查询清除。一旦您知道确切的频率，您就可以：
- 查看您的工作计划以查看在该时间间隔运行的内容
- 在该时间跨度内运行 Profiler 跟踪或扩展事件跟踪以找出谜团（我通常不喜欢在生产中进行跟踪，但如果您确切知道杀手何时会发动攻击，那么很容易触发低- 正在运行的开销示例）
- 在此期间记录
sp_WhoIsActive到表（最简单的方法，但最不可能将其缩小到导致它的确切查询）
如果每次运行查询时该日期/时间都在变化，那么您的服务器可能处于内存压力之下。运行此程序以生成基本的健康检查信息，然后您可以将其复制/粘贴到您的 Stack 问题中，以便我们对其进行诊断：
sp_Blitz @OutputType = 'markdown', @CheckServerInfo = 1, @CheckUserDatabaseObjects = 1
（披露：我是 的作者之一sp_Blitz。）
使用您的 sp_Blitz 数据更新 2017/08/25 - 感谢您运行 sp_Blitz 并将其添加到您的问题中，它确实有助于显示一些内容。您在具有 2 个内核和 16GB RAM 的 VM 上运行 SQL Server 2016 Enterprise Edition。首先，关于许可的简要说明：如果您是由来宾许可，则最低购买要求是 4 个核心，而不是 2 个。（有关更多详细信息，请参阅SQL Server 许可指南。）企业版的 4 个核心约为 28,000 美元，而且仅在 16GB RAM 上花费如此多的许可费用是相当不寻常的。如果您在主机级别授权 SQL Server Enterprise Edition，则可以忽略它并运行较小的 VM。
看起来您的 SQL Server 正在承受外部内存压力。您有 16GB 的 RAM，并且您已将最大服务器内存设置为 15GB。不幸的是，剩下的 1GB 不足以供操作系统使用（加上您将在其中运行的任何其他东西，例如备份软件和 SSMS。）在我们的 SQL Server 设置指南中，我们建议保留 4GB 或 10% 的空闲空间，以两者为准更大 - 在您的情况下，这将是 4GB，因此您的最大服务器内存设置应该是 12GB 而不是 15GB。
更多证据显示在您当前的内存分配中：您已打开内存中的锁定页面 (LPIM)，但您只有 12.02GB 的页面锁定在内存中。这可能（但不能保证）意味着其他一些应用程序需要内存，因此 Windows 发出了内存压力通知，SQL Server 放弃了另外 3GB 的内存让其他应用程序来做它的事情。这更加证明了您不能真正使用最大 15GB - 您需要内存来存储其他东西。
当您的 SQL Server 承受外部内存压力并需要为其他应用程序释放内存时，您的计划缓存将受到影响。
所以你有几个选择：
- *适当地设置最大内存*- 比如说，12GB（如果你要在服务器上运行其他应用程序，甚至更低。）这样，SQL Server 就不必因为其他一些东西而对内存进行大甩卖并清除内存。应用程序需要 2-3GB 的 RAM - 它已经可用
- *停止在服务器上运行其他应用程序*- 但是，如果它是其他系统管理员远程桌面和运行 SSMS 之类的东西，这可能会很困难。我已经为打开的 RDP 会话数设置了 Perfmon 计数器警报，并在它不是 0 时发出警报——这有助于抓住罪魁祸首。
- *向 VM 添加更多内存*- 但我认为您并不需要它。sp_Blitz 报告“未检测到明显的等待”显示了一些证据。我不认为你经常承受记忆压力，特别是因为你报告说它只是偶尔发生。这是成本效益最低的选择。