好像有人手动执行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 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...
解决方法
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跟踪或扩展事件跟踪以找出谜团(我通常不会追踪生产中的追踪,但如果您确切知道凶手将要何时发生攻击,则很容易启动什么在运行的低开销样本)
> Log sp_WhoIsActive to a table在那段时间内(最简单的方法,但最不可能将其缩小到导致它的确切查询)
如果每次运行查询时该日期/时间都在不断变化,那么您的服务器可能会受到内存压力.运行此命令以生成基本运行状况检查信息,然后您可以将其复制/粘贴到堆栈问题中,以便我们对其进行诊断：
sp_Blitz @OutputType =’markdown’,@ CheckServerInfo = 1,@ CheckUserDatabaSEObjects = 1
(披露：我是sp_Blitz的作者之一.)
使用您的sp_Blitz数据更新2017/08/25 – 感谢您运行sp_Blitz并将其添加到您的问题中,这确实有助于展示一些内容.您在具有2个内核和16 GB RAM的VM上运行sql Server 2016 Enterprise Edition.首先,关于许可的快速说明：如果您是由客户许可,则最低购买要求是4个核心,而不是2个.(有关详细信息,请参阅SQL Server Licensing Guide.)企业版的4个核心大约是28,000美元,它是相当不寻常的是看到只有16GB内存的许可资金.如果您在主机级别许可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报告显示“未检测到重要等待时间”.我不认为你经常受到记忆压力,特别是因为你报告它只是时不时地发生.这是成本效益最低的选择.
