主页 / dba / 问题 / 182227
Accepted
jitbit
Asked: 2017-07-31 23:22:25 +0800 CST

SQL Server 定期清除计划缓存和执行统计信息

  • 772

将 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...
sql-server sql-server-2016

3 个回答

  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 报告“未检测到明显的等待”显示了一些证据。我不认为你经常承受记忆压力，特别是因为你报告说它只是偶尔发生。这是成本效益最低的选择。
    • 34
  2. Best Answer

    好的，在这里，我终于通过将 SQL Server 2016 更新到最新版本来解决这个问题。我有SP1，昨天我安装了Cumulative Update 6.

    我还像布伦特的回答建议的那样适当地设置了“最大内存”。顺便说一句，答案很好，我敦促大家投票。

    已经 36 个小时了，而且还在继续，计划没有被重置。

    Brent Ozar 在这里也有一个非常好的网站：https ://sqlserverupdates.com/以帮助确定您需要哪些更新。

    另一件有帮助的事情是检测和解决“不受信任的外键”问题。布伦特有一篇非常好的文章（哈哈，是的，又是布伦特，我知道是对的）关于如何解决它，只是谷歌，他是排名第一的结果

    • 6

  3. 我在我的家庭测试框中遇到了这个问题，我发现通过向 SQL Server 服务帐户添加“锁定内存中的页面”权限可以解决问题，但我不确定这是最好的建议。

    请参阅启用内存中的锁定页面选项 (Windows)

    • 0

相关问题