Server 2016并发限制？对db并发性的优化

Question

我有一个表，有10亿个时间戳记录，每个记录都对一个会话表持有一个FK (每天一个会话&每天3500,000条记录)，所以查找给定一天的记录只是一个整数连接。

我正在尝试分析这个表中的数据(使用按会话分组的数据)，当使用客户端计算机上的C#控制台应用程序时，我可以在70分钟内运行完整的分析(每条记录)。当我试图在TSQL中直接运行类似的分析时，需要超过12个小时。由于TSQL查询使用了一个标量函数和一个自定义聚合(Clr)，所以我预计会有一些损失。

My问题：在C#中，我了解如何最大化和优化并发性，因此70分钟是一个调优的数字。是否可以在SQL中直接调优查询的最大并发性，还是将其留给C# apis比较好？(我也可以在R、db或外部完成这项工作，但是.Net并发API给我的印象更好。)

查询：

SELECT TypeNumber, SessionId, dbo.udf_SessionName([timestamp]) SessionName, 
CAST(max(price)-min(price) AS REAL) as Variance, sum(EventNumber) as Volume, 
dbo.Direction(price,[timestamp]) as MoveDirection
INTO temp.AnalysisResults
FROM MyTable
WHERE ISNULL(price,0)<>0
GROUP BY TypeNumber, SessionId, dbo.udf_SessionName([timestamp])

Misc

• 由于插入，因此启用了此查询的大容量日志记录
• 在这个查询中不使用主键(它是跨三个字段的复合键，这里不需要这个键)。但是，查询计划显示正在扫描该索引，而不是下面提到的索引(最初建议的索引)。
• 启用行级压缩。
• 数据跨度为五年，每个月都有一个只读文件组(按月份划分)；所有文件组都驻留在同一个SSD上(我知道不是很好)。
• 索引:非聚集在SessionId asc上，包括TypeNumber、时间戳、价格。
• 提供4个CPU核心
• 标量函数接受每个时间戳，将其转换为带有AT时区的本地时间(两个调用)，并在一个5记录表中查找它。
• 自定义聚合使用自定义序列化程序，它接受十进制和datetime2并返回字符串。序列化程序传递需要解析的字符串(这不是很好)。
• 查看查询计划(删除了insert)，到目前为止最昂贵的操作是排序(成本的98%；我显式启动的唯一排序是clr聚合器函数)：

注意事项：我知道使用CLR聚合将花费我的查询时间和压缩时间。如果我使用控制台应用程序，我可以将所有分析工作卸载到一台功能更强大的机器上，让db服务器只执行IO操作。这是“显而易见的答案”，还是我可以将大部分工作保存在数据库中(通常，我可以在数据库中做的越多，越好)。

我意识到，通过设置数据库的方式和压缩设置，可以更多地为IO而不是CPU进行调优。我不希望获得与纯C解决方案相同的性能，而在纯C解决方案中，db只执行IO；但是，通过最大化数据库可以完成的cpu工作，可以获得很多好处。

udf_SessionName:

create function dbo.[udf_SessionName](@timestamp datetime2)
returns nvarchar(100)
begin
declare @localTime time = CAST(@timestamp at time zone 'UTC' at time zone 'Pacific Standard Time' as time)
declare @result nvarchar(100) = (select top 1 sessionname from MarketSessions where @localTime>=StartTime and @localTime < EndTime)
if (@result is null) set @result = 'European'
return @result
end

SQL Fiddle中的表结构

After Action：我实现了@SolomonRutzky的建议，该查询现在在3小时对12+的时间内完成。

变动摘要

1. 将时区操作从标量udf更改为clr函数(没有TimeZoneInfo的D27实现)。
2. 将该clr函数滚动到非持久化计算列中。
3. 添加了一个新索引:在.MyTable上创建非NONCLUSTERED索引(TypeNumber ASC，SessionId ASC)包含(SessionName、价格、时间戳、卷)

SessionName作为索引中的键确实会更好，但是即使它是精确的和确定性的，因为它是一个CLR函数，除非它是持久化的，否则它不可能是键，而且该列虽然大部分是静态的，但不足以保持静态。

1. 移除ISNULL

修改后的查询

INSERT INTO temp.AnalysisResults
SELECT TypeNumber, SessionId, SessionName, 
CAST(max(price)-min(price) AS REAL) as Variance, sum(EventNumber) as Volume, 
dbo.Direction(price,[timestamp]) as MoveDirection
FROM MyTable
WHERE price <> 0 AND price IS NOT NULL
GROUP BY TypeNumber, SessionId, SessionName

新的执行计划

Answer 1

在此之前，我认为您需要尝试一些方法来减少12+小时查询：

1. 我首先要检查的是索引。在TypeNumber, SessionId, dbo.udf_SessionName([timestamp])上有一个组，但是索引在SessionId asc, INCLUDE TypeNumber,Timestamp,Price上。也就是说，顺序是不一样的(因此很可能是索引被忽略和表被扫描的原因)。您需要索引，至少从TypeNumber, SessionId开始，以匹配这些列的GROUP BY顺序。然后INCLUDE ([price], [timestamp], [EventNumber])使它成为一个覆盖指数.关于这个指数还有更多的话要说，但接下来的部分.
2. 接下来是标量UDF。这些都是众所周知的坏的原因有几个。而且使用AT TIME ZONE的速度也不会太快。所以，考虑一下：
   1. 将其转换为内联TVF通常会产生奇迹，但不确定这是否与索引的最佳效果相冲突。
   2. 使标量UDF变慢的部分原因是它们阻止了并行计划。标记为DataAccess和SystemDataAccess的Scalar = none和IsDeterministic=true不会阻止并行计划:-)。如果要从UTC转换为本地时间(反之亦然)，则可以使用可以在SAFE程序集中调用的类。如果您需要从不同的时区进行转换，那么您需要使用TimezoneInfo类(我认为)，这要求将程序集标记为UNSAFE。UNSAFE不会剥夺允许并行计划的好处，但如果可能的话，可以使用SAFE方法，那么就这样做。
   3. SQLCLR方法的复杂之处在于，您正在查找表：MarketSessions。你说过这只有5排。这5行相当静态吗？如果是这样的话，您可能仍然可以通过在程序集中创建一个静态集合来获得不执行任何数据访问的SQLCLR，然后在静态类构造函数中从表中填充它。静态类构造函数将在加载程序集的任何时候执行，并且可以用需要在udf_SessionName UDF中检查的值预填充集合。唯一的问题是静态类构造函数中没有可用的内部context connection，因此需要将程序集标记为EXTERNAL_ACCESS。但是UDF不会调用SqlConnection，它只会从静态集合读取:-)。如果MarketSessions中的值比较易失性，则始终可以创建一个SQLCLR或存储过程，该存储过程调用相同的方法来填充类构造函数调用的静态集合。然后，您可以在运行此查询之前执行该操作，以便内部静态集合具有来自该表的“当前”记录。但是，在这种情况下，您可能无法执行以下两个步骤，因为索引的值可能会过时/不正确。但是你仍然可以得到一个并行计划的好处。
   4. 无论您是将WITH SCHEMABINDING添加到table (如果没有这样做)，还是将其转换为具有上述属性的SQLCLR，您都应该将一个列添加到表中，作为一个NONPERSISTED计算列，它只是对UDF的调用。
   5. 一旦NONpersisted计算列存在，就可以在：TypeNumber, SessionId, computedColumn INCLUDE ([price], [timestamp], [EventNumber])上创建实际的索引。您可能还需要为SQLCLR设置SqlFunction属性IsPrecise=true，以便使其可索引。

3. 您可能需要重新考虑ISNULL。price可能只是一个INCLUDE列，因此ISNULL函数在这里可能不会出现使用索引的问题，但是您可能需要将它分解为price <> 0 AND price IS NOT NULL。
4. 我不确定对整体性能的影响，但我从来都不是SELECT...INTO结构的粉丝。最好先创建表，然后执行INSERT INTO...SELECT。
5. 正如@LowlyDBA在关于这个问题的评论中所指出的:小心使用REAL (或FLOAT)来获取财务价值。是的，他们更紧凑，转移更快，但他们有时也可以得到额外的价值，在非常低的一端。如果你在计算的话，我肯定不会使用那种数据类型。但是，仅仅将其传递回应用程序可能是可以的。但是如果你在做计算，你真的应该使用DECIMAL()，甚至MONEY。