Tom V's questions

在 Dynamics AX 中有一种缓存机制，可以将表配置为加载到内存中并进行缓存。此缓存限制为一定数量的 KB 以防止内存问题。我正在谈论的设置被调用entiretablecache，并在请求单个记录后立即将整个表加载到内存中。

直到最近，我们依靠一些脚本来验证具有此设置的表的大小，以查看表大小是否超过此限制。

然而现在，压缩开始发挥作用，像sp_spaceused或sys.allocation_units这样的东西似乎报告了压缩数据实际使用的空间。

显然，应用程序服务器正在处理未压​​缩的数据，因此 SQL Server 中磁盘上的数据大小无关紧要。我需要未压缩数据的实际大小。

我知道sp_estimate_data_compression_savings但顾名思义，这只是一个估计值。
我希望尺寸尽可能正确。

我能想到的唯一方法是一些复杂的动态 SQL 创建与压缩表具有相同结构的未压缩表，将压缩数据插入该影子表中，然后检查该影子表的大小。
不用说，这有点乏味，并且需要一段时间才能在数百 GB 的数据库上运行。

Powershell 可能是一个选项，但我不想遍历所有表以select *对它们执行 a 以检查脚本中的大小，因为这只会淹没缓存并且可能也需要很长时间。

简而言之，如果可能的话，我需要一种方法来获取每个表的大小，因为它一旦被解压缩并且在呈现给应用程序的等式中会出现碎片。我对不同的方法持开放态度，首选 T-SQL，但我不反对 Powershell 或其他创造性方法。

假设应用程序中的缓冲区是数据的大小。bigint 始终是 bigint 的大小，字符数据类型是每个字符 2 个字节（unicode）。BLOB 数据也采用数据的大小，枚举基本上是一个 int，数字数据是 numeric(38,12)，datetime 是 datetime 的大小。此外，没有NULL值，它们要么存储为空字符串，要么存储1900-01-01为零。

没有关于如何实现的文档，但假设是基于一些测试以及 PFE 和支持团队使用的脚本（显然也忽略了压缩，因为检查是内置在应用程序中的，应用程序无法分辨如果基础数据被压缩），它还会检查表大小。例如，此链接指出：

避免对大型表使用 EntireTable 缓存（在 AX 2009 中超过 128 KB 或 16 页，在 AX 2012 中超过“整个表缓存大小”应用程序设置 [默认值：32KB 或 4 页]）——改为使用记录缓存。

我遇到了一个我似乎无法理解的查询的性能问题。

我将查询从游标定义中提取出来。

此查询需要几秒钟才能执行

SELECT A.JOBTYPE
FROM PRODROUTEJOB A
WHERE ((A.DATAAREAID=N'IW')
AND ((A.CALCTIMEHOURS<>0)
AND (A.JOBTYPE<>3)))
AND EXISTS (SELECT 'X'
FROM PRODROUTE B
WHERE ((B.DATAAREAID=N'IW')
AND (((((B.PRODID=A.PRODID)
AND ((B.PROPERTYID=N'PR1526157') OR (B.PRODID=N'PR1526157')))
AND (B.OPRNUM=A.OPRNUM))
AND (B.OPRPRIORITY=A.OPRPRIORITY))
AND (B.OPRID=N'GRIJZEN')))
AND NOT EXISTS (SELECT 'X'
FROM ADUSHOPFLOORROUTE C
WHERE ((C.DATAAREAID=N'IW')
AND ((((((C.WRKCTRID=A.WRKCTRID)
AND (C.PRODID=B.PRODID))
AND (C.OPRID=B.OPRID))
AND (C.JOBTYPE=A.JOBTYPE))
AND (C.FROMDATE>{TS '1900-01-01 00:00:00.000'}))
AND ((C.TODATE={TS '1900-01-01 00:00:00.000'}))))))
GROUP BY A.JOBTYPE
ORDER BY A.JOBTYPE

实际的执行计划是这样的。

注意到服务器范围的设置被设置为 MaxDOP 1 我尝试使用 maxdop 设置。

添加OPTION (MAXDOP 0)到查询或更改服务器设置会导致更好的性能和此查询计划。

但是，有问题的应用程序（Dynamics AX）不执行这样的查询，它使用游标。

捕获的实际代码是这样的。

declare @p1 int
set @p1=189527589
declare @p3 int
set @p3=16
declare @p4 int
set @p4=1
declare @p5 int
set @p5=2
exec sp_cursoropen @p1 output,N'SELECT A.JOBTYPE FROM PRODROUTEJOB A WHERE ((A.DATAAREAID=N''IW'') AND ((A.CALCTIMEHOURS<>0) AND (A.JOBTYPE<>3))) AND EXISTS (SELECT ''X'' FROM PRODROUTE B WHERE ((B.DATAAREAID=N''IW'') AND (((((B.PRODID=A.PRODID) AND ((B.PROPERTYID=N''PR1526157'') OR (B.PRODID=N''PR1526157''))) AND (B.OPRNUM=A.OPRNUM)) AND (B.OPRPRIORITY=A.OPRPRIORITY)) AND (B.OPRID=N''GRIJZEN''))) AND NOT EXISTS (SELECT ''X'' FROM ADUSHOPFLOORROUTE C WHERE ((C.DATAAREAID=N''IW'') AND ((((((C.WRKCTRID=A.WRKCTRID) AND (C.PRODID=B.PRODID)) AND (C.OPRID=B.OPRID)) AND (C.JOBTYPE=A.JOBTYPE)) AND (C.FROMDATE>{TS ''1900-01-01 00:00:00.000''})) AND ((C.TODATE={TS ''1900-01-01 00:00:00.000''})))))) GROUP BY A.JOBTYPE ORDER BY A.JOBTYPE ',@p3 output,@p4 output,@p5 output
select @p1, @p3, @p4, @p5

导致这个执行计划（不幸的是相同的多秒执行时间）。

我已经尝试了几件事，例如删除缓存计划，在游标定义内的查询中添加选项，......但它们似乎都没有让我得到一个并行计划。

我还在谷歌上搜索了相当多的游标并行限制，但似乎找不到任何限制。

我在这里遗漏了一些明显的东西吗？

我意识到实际的 SQL 构建SQL Server 2008 (SP1) - 10.0.2573.0 (X64)不受支持，但我无法升级此实例，因为我认为合适。我需要将数据库转移到另一台服务器，这意味着通过慢速 WAN 拉取相当大的未压缩备份。

跟踪标志 4199 没有任何区别，OPTION (RECOMPILE) 也没有。

游标属性为：

API | Fast_Forward | Read Only | Global (0)

我正在使用 3 层应用程序 Microsoft Dynamics AX，其中中间层维护与 SQL Server 的连接。几个客户端连接到这个中间层服务器。

中间层服务器通常有几个连接到 SQL Server 的连接，所以我很确定它们正在被池化，但是没有关于如何实现的文档。

通常我们无法将 SPID 与用户或客户端应用程序相关联，但我们可以通过一个选项设置注册表项（特定于 Microsoft Dynamics AX），从而使此信息context_info在sys.dm_exec_sessions.

同样，没有关于如何实现的文档。我们掌握的唯一信息是 MSDN 上的一个模糊的博客条目。

帖子提到

添加此信息的性能开销很小。

因此，我们不知道任何实现细节，例如：

1. 信息是否以某种方式包含在连接字符串中，还是由 SET CONTEXT_INFO 完成？
2. 什么时候重用连接？
3. 可以预期的确切影响

有什么方法可以确定服务器端连接池的工作方式以及 context_info 的影响是什么？

更新：从这里
使用这个查询

SELECT des.program_name,
       des.login_name,
       des.host_name,
--       der.database_id,
       COUNT(des.session_id) AS [Connections]
FROM sys.dm_exec_sessions des
INNER JOIN sys.dm_exec_connections DEC
        ON des.session_id = DEC.session_id
WHERE des.is_user_process = 1
--AND des.status <> 'running'
GROUP BY des.program_name,
         des.login_name,
         des.host_name
--         ,der.database_id
HAVING COUNT(des.session_id) > 2
ORDER BY COUNT(des.session_id) DESC

我可以看到使用了连接池。