利用大型数据库进行有效的值更新

Question

我有一张8GB的桌子，有2000万张唱片。有一个名为mth的int字段。mth字段以YYYYMM的形式存储日期信息，我希望将mth字段转换为连续整数。因此，我使用一个公式从字段mth获取年份和月份，并计算月份顺序，具体来说，我使用以下代码：

create function mth_to_num(@month int)
returns int
as 
begin
   return(round(@month/100,0)*12+@month-100*round(@month/100,0))
end

然后，我使用下面的代码更新巨型表中的值

update full_orig_month_Q1_1999 
set mth_order = dbo.mth_to_num(period)
Go 

然而，代码花费了相当长的时间执行，大约2-3分钟。我的系统是使用SQL server 2016的windows 10 64位。有什么办法让我加快速度吗？

另一个问题是，在执行上面的查询后，我发现SQL服务器在数据库中占据了多达8GB的空间。它需要这么多记忆吗？我怎么才能释放他们？

谢谢你提前帮忙！

杰森

Answer 1

如果可能，不要在查询中使用标量用户定义函数(UDF)。它们强制整个计划是串行的，它们可能导致大量额外的CPU资源被使用，它们是优化器的一个黑匣子，可能导致计划的其他部分的基数估计问题，并且它们可能导致不必要的大内存授权。

尝试不使用UDF运行UPDATE查询。只需将您拥有的代码直接放入UPDATE查询。就像这样：

update full_orig_month_Q1_1999 
set mth_order = round(period/100,0)*12+period-100*round(period/100,0);

这会改善运行时和内存的使用吗？

Answer 2

您可以尝试使用Paul的脚本来实现在执行脚本期间发生的捕获等待统计信息。

根据返回的结果，您可以搜索顶级等待类型并解释性能问题。

示例结果可能如下所示：

WaitType    Wait_S  Resource_S  Signal_S    WaitCount   Percentage  AvgWait_S   AvgRes_S    AvgSig_S
ASYNC_NETWORK_IO    0.00    0.00    0.00    2   100.00  0.0005  0.0005  0.0000

然后搜索ASYNC_NETWORK_IO就会发现，例如：

这种等待类型是Server通过TDS向客户端发送一些数据的地方，并等待客户端确认已消耗数据，如果日志读取器代理作业由于某种原因运行缓慢，也可以显示事务复制。(联机书籍描述：“发生在网络写入时，任务被阻塞在网络后面。验证客户端正在处理来自服务器的数据“)

在回应你的评论时：

保罗兰德尔's和布伦特·奥扎尔's网站是性能优化和优化的良好起点。我仍然发现，Server 2008 R2联机丛书(仍然可以是下载)包含大量关于数据库内部的信息，尽管其中一些信息已经过时。

亚伦·伯特兰有一些关于性能调优的好信息，其他很多人也是如此。