执行使用表值参数的存储过程时的执行超时

Question

我有一个使用表值参数(tvp)的存储过程。在我的应用程序中，我使用一个数据表作为SqlParameter来匹配tvp结构。问题是，一旦我已经执行了存储过程，有时仅仅将数据(30k行左右)从应用程序插入tvp就需要25秒，这意味着存储过程中的代码本身只有5秒(命令超时为30秒)来完成，而对于大量数据来说，这并不总是发生的。

我完全知道我可以增加命令超时，但我想了解为什么需要25秒才能将数据插入tvp，以及可以做些什么来加快速度。

需要明确的是，这不是SSMS中存储过程中的代码，它占用了25秒，这是应用程序本身在我从应用程序中执行存储过程后将行插入tvp。

这个令人不快的语句如下(我们的tvp大约有20列):

declare @p3 dbo.table_valued_parameter insert into @p3 (col1, col2, col3) values (v1, v2, v3)

我的问题是，为什么需要25秒才能将30k行插入到tvp中，我可以使用什么方法来加快速度?也许问题是使用SqlParameter的数据表?我还认为CommandTimeout只会在存储过程本身开始在SSMS中执行时才开始计数，而不是在准备参数时开始计数。

c#代码如下所示(GetDataTable方法通过向与tvp定义匹配的新数据表添加列来创建数据表，然后通过迭代代码中其他地方使用的列表来向数据表添加行)。

List<SqlParameter> parameters = new List<SqlParameter>()
{
new SqlParameter("@textParam1", "Value1"), 
new SqlParameter("@testParam2", "Value2"),
new SqlParameter("@tvp", GetDataTable())
};
DataSet dataSet = new DataSet();
SqlCommand command = new SqlCommand(StoredProcName); 
command.CommandType = CommandType.StoredProcedure;
command.Parameters.AddRange(parameters.ToArray());
using (SqlConnection connection = new SqlConnection(ConnectionString))
{
connection.Open();
command.Connection = connection;
using (SqlDataAdapter dataAdapter = new SqlDataAdapter(command))
{
dataAdapter.Fill(dataSet);
}
connection.Close();
}

Answer 1

我设法从分析器中获取应用程序发出的RPC调用，并使用应用程序正在使用的相同SQL代码(更重要的是，参数)，并在SSMS中运行它。在SSMS中，进程运行大约需要2秒，而在应用程序中则需要30秒。

这些步骤为我解决了这个问题。

1. 阅读这篇伟大的文章确实帮助澄清了我所遇到的问题:https://www.sommarskog.se/query-plan-mysteries.html

2. 从文章中，我发现应用程序调用存储进程时的执行计划实际上与在SSMS中调用时的执行计划不同。我通过清除缓存(DBCC FREEPROCCACHE)，在应用程序和SSMS中使用相同的输入参数运行进程，然后查询sys来验证这一点。Dm_exec_cached_plan显示了我2个不同的缓存计划。为了解决这个问题，我将所有应用程序的Arithabort打开(以匹配SSMS) - https://blog.sqlauthority.com/2018/08/07/sql-server-setting-arithabort-on-for-all-connecting-net-applications/

3. 由于所讨论的进程插入数据(或者删除并重新插入以获得最新的数据，如果数据过期)，我将此作为帮助改进数据加载过程的机会。这包括删除重复的非聚集索引，将我们使用的staging表转换为堆(之前有一个聚集索引)，在进程体中删除TVP的使用，并替换为临时表(因为这可以防止使用TVP的查询并行):https://www.brentozar.com/archive/2018/06/how-table-variables-mess-with-parallelism/)，使用局部变量来防止参数嗅探(即在进程体中声明一个新变量并将其设置为输入参数的值)。这确实有助于加快进程，然而，我仍然得到偶尔超时…

4. 从这个存储过程中插入/删除的目标表是一个非常大的表(1亿+行)，而且是高度事务性的——我们几乎每小时从这个表中插入/删除数据。我注意到这在单个加载过程中不止一次达到自动更新统计值阈值。我还设法将超时时间与统计数据自动更新的时间相匹配(https://blog.sqlauthority.com/2020/06/01/sql-server-statistics-modification-counter-sys-dm_db_stats_properties/)。我关闭了该表上的自动统计信息，而是设置了一个夜间作业来手动更新统计信息。从那以后，我们再也没有看到任何超时。