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优化设置为true(我认为它会有所帮助,但它没有). “查询商店”是“关闭”.服务器有16 GB内存.

“SQL Server日志”中没有任何帮助.只是一周的备份消息……

我还检查了这篇文章https://docs.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 ServiceSQLSERVERAGENT. I wish I had an Active Directory service account instead.

- SQL Server is running under an NT Service account  - I'm running as NT ServiceMSSQLSERVER. 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 FilesMicrosoft SQL ServerMSSQL13.MSSQLSERVERMSSQLLog

- 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 ServiceMSSQLFDLauncher. Last startup time: not shown.. Startup type: Manual,currently Running.

 - Service: SQL Server (MSSQLSERVER) runs under service account NT ServiceMSSQLSERVER. Last startup time: Aug 20 2017 12:32PM. Startup type: Automatic,currently Running.

 - Service: SQL Server Agent (MSSQLSERVER) runs under service account NT ServiceSQLSERVERAGENT. 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...

解决方法

SELECT TOP 1 creation_time
FROM sys.dm_exec_query_stats WITH (NOLOCK)
ORDER BY creation_time;

（编辑：南京站长网）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!