主页 / user-121246

Professional Sounding Name's questions

Martin Hope
Professional Sounding Name
Asked: 2017-10-19 15:52:40 +0800 CST
  • 15

几天后，CACHESTORE_SQLCP Sql Plans 占用 > 38 GB。

我们已经在“针对临时工作负载进行优化”选项的情况下运行。（实体框架和自定义报告会产生很多临时性问题！）

具有多可用区镜像的 AWS RDS 上的 SQL Server 2016 SE 3.00.2164.0.v1

当我运行时：

DBCC FREESYSTEMCACHE('SQL Plans');

或者

DBCC FREEPROCCACHE

或者

DBCC FREESYSTEMCACHE ('SQL Plans') WITH MARK_IN_USE_FOR_REMOVAL

或者

DBCC FREESYSTEMCACHE ('ALL') WITH MARK_IN_USE_FOR_REMOVAL;

它似乎没有清除它：

SELECT TOP 1 type, name, pages_kb FROM sys.dm_os_memory_clerks ORDER BY pages_kb  desc

type                name        pages_kb
CACHESTORE_SQLCP    SQL Plans   38321048

我在启用查询存储的情况下运行，但我禁用了它以查看是否会干扰任何东西，它似乎没有帮助，但我将其关闭。

真正奇怪的是

SELECT COUNT(*) FROM sys.dm_exec_cached_plans

是 1-3 左右（它似乎只显示过显示当前正在运行的查询），即使所有内存都被保留，甚至在我试图清除任何东西之前。我错过了什么？

CACHESTORE_SQLCP 占用了所有可用内存的 60% 以上，这是一个令人担忧的问题，因为偶尔会发生内存等待。此外，我们不得不在周末终止一个持续 4 小时的例程 DBCC CHECKDB，因为内存不足正在堆积等待（它立即完成，并且 PHYSICAL_ONLY 开启时没有错误）。

有没有办法回收这个内存（除了每晚重新启动！？）？

内存消耗图 内存消耗图表

从评论/答案更新

当我跑

SELECT * FROM sys.fn_my_permissions(NULL,NULL)

我明白了

entity_name subentity_name  permission_name
server      CONNECT SQL
server      CREATE ANY DATABASE
server      ALTER ANY LOGIN
server      ALTER ANY LINKED SERVER
server      ALTER ANY CONNECTION
server      ALTER TRACE
server      VIEW ANY DATABASE
server      VIEW ANY DEFINITION
server      VIEW SERVER STATE
server      ALTER SERVER STATE
server      CREATE SERVER ROLE
server      ALTER ANY SERVER ROLE

其中包括所需的ALTER SERVER STATE权限。

的输出DBCC FREEPROCCACHE是

DBCC 执行完成。如果 DBCC 打印错误消息，请联系您的系统管理员。

这是标准消息。RDS 不支持的其他 DBCC 函数会提供有关权限的错误消息。

（一天后再次运行，SQL 计划仍然是 38,321,280 kb）

从评论/答案更新

SELECT pool_id, name, cache_memory_kb, compile_memory_kb FROM sys.dm_resource_governor_resource_pools

输出：

pool_id name    cache_memory_kb  compile_memory_kb
1   internal    38368408         1168080

DBCC FREEPROCCACHE ('internal')没有做任何不同的事情

更新

SQL 错误日志每次记录以下内容：

2017-10-19 14:26:47.22 spid85 SQL Server has encountered 1 occurrence(s) of cachestore flush for the 'Object Plans' cachestore (part of plan cache) due to 'DBCC FREEPROCCACHE' or 'DBCC FREESYSTEMCACHE' operations.

2017-10-19 14:26:47.22 spid85 SQL Server has encountered 1 occurrence(s) of cachestore flush for the 'SQL Plans' cachestore (part of plan cache) due to 'DBCC FREEPROCCACHE' or 'DBCC FREESYSTEMCACHE' operations.

2017-10-19 14:26:47.22 spid85 SQL Server has encountered 1 occurrence(s) of cachestore flush for the 'Bound Trees' cachestore (part of plan cache) due to 'DBCC FREEPROCCACHE' or 'DBCC FREESYSTEMCACHE' operations.

更新

RDS 上的引擎版本从 13.00.2164.0.v1 升级到 13.00.4422.0.v1 可用。虽然它设置为自动次要版本升级，但它似乎并没有让我们更新到最新版本。将在本周末重新启动并安装它，看看它是否有帮助。

sql-server optimization
Martin Hope
Professional Sounding Name
Asked: 2017-04-12 07:12:00 +0800 CST
  • 17

每周一次，在过去的 5 周内，大约在一天中的同一时间（清晨，可能基于人们开始使用它时的用户活动），SQL Server 2016（AWS RDS，镜像）开始大量超时查询。

所有表上的 UPDATE STATISTICS 总是立即修复它。

第一次之后，我让它每晚（而不是每周）更新所有表上的所有统计信息，但它仍然发生（在更新统计信息运行后大约 8 小时，但不是每天运行）。

上次，我启用了查询存储，看看我是否可以找到它是哪个特定的查询/查询计划。我想我可以把它缩小到一个：

错误的查询计划

找到该查询后，我添加了一个推荐的索引，该索引从这个不常用的查询中丢失（但它确实触及了很多常用的表）。

错误的查询计划正在执行索引扫描（在只有 10k 行的表上）。其他以毫秒为单位返回的查询计划过去常常执行相同的扫描。最新的查询计划，在创建新索引后只会寻找。但即使没有该索引，在 99% 的情况下，它也会在几毫秒内返回，但每周，它需要超过 40 秒。

从 2012 年迁移到 SQL Server 2016 后，这种情况开始发生。

DBCC CHECKDB 不返回错误。

  1. 新索引会解决问题，使其不再选择糟糕的计划吗？
  2. 我应该“强制”现在行之有效的计划吗？
  3. 我如何确保这不会发生在另一个查询/计划中？
  4. 这是更大问题的征兆吗？

我刚刚添加的索引：

CREATE NONCLUSTERED INDEX idx_AppointmetnAttendee_AttendeeType
ON [dbo].[AppointmentAttendee] ([UserID],[AttendeeType])

CREATE NONCLUSTERED INDEX [idx_appointment_start] ON [dbo].[Appointment]
(
    [ProjectID] ASC,
    [Start] ASC
)
INCLUDE (   [ID],
    [AllDay],
    [End],
    [Location],
    [Notes],
    [Title],
    [CreatedByID]) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

完整的查询文本：

https://pastebin.com/Z5szPBfu（LINQ 生成，我可以/应该能够优化选择的列，但应该与这个问题无关）

sql-server execution-plan