从 C 调用的 Azure SQL 存储过程速度慢得离谱#

问：

摘要：

我们有两个相同的数据库，一个在本地服务器上，一个在 Azure 上。

我们有一个 C# 系统，它通过调用存储过程来访问这些数据库。

从 C# 系统调用到 Azure 数据库时，存储过程的运行速度非常非常慢。从 C# 到本地服务器，以及从 SSMS 到 Azure 和本地数据库，它们运行良好。

例如，调用存储过程“usp_DevelopmentSearch_Select”

本地数据库，SSMS：1 秒

本地数据库，C#：1 秒

Azure 数据库，SSMS：1 秒

Azure 数据库，C#：17 分钟

这发生在多个存储过程中，我只是以usp_DevelopmentSearch_Select为例，测试解决方案并跟踪执行计划。

我已经排除了 ARITHABORT（通常的嫌疑人），似乎在 SSMS 和 C# 系统中运行 usp_DevelopmentSearch_Select 会生成功能相同的执行计划。

细节：

我们编写了一个非常大的 C# 系统，用于访问 SQL Server 数据库。

目前，我们所有的客户都在自己的服务器上本地托管自己的数据库，但是我们正在研究在 Azure 上托管数据库的选项。因此，我设置了一些小型 Azure 测试数据库，解决了这些问题，并启动了一个 Azure 托管的系统。

然后，我复制了我们客户的一个数据库，以比较本地托管和 Azure 上托管的性能。

实际客户端数据库在 Azure 上表现不佳！

第一个屏幕调用存储过程“usp_DevelopmentSearch_Select”

连接到他们服务器上的数据库：-

在 SSMS 中，调用存储过程（如下所示）会在大约 1 秒内返回值

EXEC usp_DevelopmentSearch_Select @MaxRecord = 100, @SearchType = 'CUR'

在我们的 C# 程序中，调用存储过程会在大约 1 秒内返回值

连接到 Azure 上的数据库：-

在 SSMS 中，调用存储过程会在大约 1 秒内返回值

在我们的 C# 程序中，调用存储过程会在大约 17 分钟内返回值！

SSMS中的“快”和C#中的“慢”通常意味着ARITHABORT，因此我在存储过程开始时将其打开：

SET ARITHABORT ON; 

这没有任何区别，所以我更新了它以将传递的参数转换为局部变量。

ALTER PROCEDURE [dbo].[usp_DevelopmentSearch_Select]
     (@MAXRECORD INT,
      @SEARCHTYPE VARCHAR(3))
AS
BEGIN
    SET ARITHABORT ON; 

    DECLARE @MAXRECORD_Var INT = @MAXRECORD
    DECLARE @SEARCHTYPE_Var VARCHAR(3) = @SEARCHTYPE

    ... (Updated all references to @MAXRECORD and @SEARCHTYPE to @MAXRECORD_Var and @SEARCHTYPE_Var)

END

仍然没有喜悦，所以我得到了两者的执行计划详细信息：-

select o.object_id, s.plan_handle, h.query_plan 
from sys.objects o 
inner join sys.dm_exec_procedure_stats s on o.object_id = s.object_id
cross apply sys.dm_exec_query_plan(s.plan_handle) h
where o.object_id = object_id('usp_DevelopmentSearch_Select')

为了检查，我在 C# 程序中重新加载了屏幕，并检查了正在运行的查询：-

SELECT sqltext.TEXT,
req.session_id,
req.status,
req.command,
req.cpu_time,
req.total_elapsed_time,
req.plan_handle
FROM sys.dm_exec_requests req
CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS sqltext

它肯定使用了上面返回的两个执行计划之一。

因此，请检查执行计划的设置

SELECT * FROM sys.dm_exec_plan_attributes (0x05002D00D1A1EA5510E66E783602000001);
SELECT * FROM sys.dm_exec_plan_attributes (0x05002D00D1A1EA55E0FC6E783602000001);

Set_Options两者都是 4345，所以他们肯定都在使用 ARITHABORT。

唯一的区别是本地化位：语言和日期格式。Azure 数据库卡在美式中，似乎无法改变这一点，而 C# 程序则强制它使用英式。

我尝试了 C# 程序，但没有强迫它使用英国语，但仍然遇到同样的问题。它还使用了完全相同的执行计划，因此显然本地化不会影响这一点。

所以，我调出了执行计划的信息：-

SELECT * FROM sys.dm_exec_query_plan (0x05002D00D1A1EA5510E66E783602000001);
SELECT * FROM sys.dm_exec_query_plan (0x05002D00D1A1EA55E0FC6E783602000001);

保存了它们，并比较了结果：-

最左边的两列显示了整体比较：黄色表示不同，白色表示相同。 正如你所看到的，这两个执行计划几乎完全相同，只是在顶部有一些不同之处。

在上面的屏幕截图中可以看到第一个差异：SSMS（左）窗格中的“StatementCompId”比 C#（右）窗格中高 1。Google 不想告诉我 StatementCompId 是什么，但鉴于它们是按顺序排列的，我猜这是执行它们的顺序，而 SSMS 更高，因为调用 SP 的 EXEC 命令算作一个。

为了方便起见，我已将所有剩余的差异编译成一个屏幕截图：-

编译时间和 CPU 使用率、可用内存以及更多“StatementCompId”

因此，这两个执行计划在功能上是相同的，具有相同的设置（除了本地化，它似乎没有效果）。

那么，为什么从 C# 调用 Azure SP 大约需要 17 分钟，而从 SSMS 调用 Azure SP 或从本地托管的数据库调用本地 SP 大约需要 1 秒呢？

存储过程本身只是一个 SELECT FROM，与其他表有一些 LEFT JOIN，没什么花哨的，它从来没有给我们在本地托管的数据库上带来任何麻烦。

SELECT TOP (@MAXRECORD_Var) <FieldList>
FROM (
    SELECT DISTINCT <FieldList>
    FROM <TableName> WITH (NOLOCK)
    LEFT JOIN <TableName> WITH (NOLOCK) ON <Link>
    LEFT JOIN <TableName> WITH (NOLOCK) ON <Link>
    LEFT JOIN <TableName> WITH (NOLOCK) ON <Link>
    LEFT JOIN <TableName> WITH (NOLOCK) ON <Link>
    LEFT JOIN <TableName> WITH (NOLOCK) ON <Link>
    WHERE (
        <Conditions>
    ) AS Base
ORDER BY <FieldName>

编辑：一些进展

我尝试了谷歌搜索中出现的几件事：-

1） 使用重新编译

我尝试将其添加到存储过程中，没有任何区别

2） 选项（优化（@MAXRECORD_Var未知，@SEARCHTYPE_Var未知））

我尝试将其添加到存储过程中，没有任何区别

3） 显式设置所有选项

这个产生了明显（但仍然太小）的差异！

我写了一个查询来告诉我当前的选项

DECLARE @options INT
SELECT @options = @@OPTIONS
PRINT @options
PRINT 'SET DISABLE_DEF_CNST_CHK ' + CASE WHEN ( (1 & @options) = 1 ) THEN 'ON' ELSE 'OFF' END + ';'
PRINT 'SET IMPLICIT_TRANSACTIONS ' + CASE WHEN ( (2 & @options) = 2 ) THEN 'ON' ELSE 'OFF' END + ';'
PRINT 'SET CURSOR_CLOSE_ON_COMMIT ' + CASE WHEN ( (4 & @options) = 4 ) THEN 'ON' ELSE 'OFF' END + ';'
PRINT 'SET ANSI_WARNINGS ' + CASE WHEN ( (8 & @options) = 8 ) THEN 'ON' ELSE 'OFF' END + ';'
PRINT 'SET ANSI_PADDING ' + CASE WHEN ( (16 & @options) = 16 ) THEN 'ON' ELSE 'OFF' END + ';'
PRINT 'SET ANSI_NULLS ' + CASE WHEN ( (32 & @options) = 32 ) THEN 'ON' ELSE 'OFF' END + ';'
PRINT 'SET ARITHABORT ' + CASE WHEN ( (64 & @options) = 64 ) THEN 'ON' ELSE 'OFF' END + ';'
PRINT 'SET ARITHIGNORE ' + CASE WHEN ( (128 & @options) = 128 ) THEN 'ON' ELSE 'OFF' END + ';'
PRINT 'SET QUOTED_IDENTIFIER ' + CASE WHEN ( (256 & @options) = 256 ) THEN 'ON' ELSE 'OFF' END + ';'
PRINT 'SET NOCOUNT ' + CASE WHEN ( (512 & @options) = 512 ) THEN 'ON' ELSE 'OFF' END + ';'
PRINT 'SET ANSI_NULL_DFLT_ON ' + CASE WHEN ( (1024 & @options) = 1024 ) THEN 'ON' ELSE 'OFF' END + ';'
PRINT 'SET ANSI_NULL_DFLT_OFF ' + CASE WHEN ( (2048 & @options) = 2048 ) THEN 'ON' ELSE 'OFF' END + ';'
PRINT 'SET CONCAT_NULL_YIELDS_NULL ' + CASE WHEN ( (4096 & @options) = 4096 ) THEN 'ON' ELSE 'OFF' END + ';'
PRINT 'SET NUMERIC_ROUNDABORT ' + CASE WHEN ( (8192 & @options) = 8192 ) THEN 'ON' ELSE 'OFF' END + ';'
PRINT 'SET XACT_ABORT ' + CASE WHEN ( (16384 & @options) = 16384 ) THEN 'ON' ELSE 'OFF' END + ';'

这将生成一组 SET 语句和当前 Options 值

5496
SET DISABLE_DEF_CNST_CHK OFF;
SET IMPLICIT_TRANSACTIONS OFF;
SET CURSOR_CLOSE_ON_COMMIT OFF;
SET ANSI_WARNINGS ON;
SET ANSI_PADDING ON;
SET ANSI_NULLS ON;
SET ARITHABORT ON;
SET ARITHIGNORE OFF;
SET QUOTED_IDENTIFIER ON;
SET NOCOUNT OFF;
SET ANSI_NULL_DFLT_ON ON;
SET ANSI_NULL_DFLT_OFF OFF;
SET CONCAT_NULL_YIELDS_NULL ON;
SET NUMERIC_ROUNDABORT OFF;
SET XACT_ABORT OFF;

注意：运行 SET DISABLE_DEF_CNST_CHK OFF;抛出一个错误，所以我注释掉了那个。

'DISABLE_DEF_CNST_CHK' is not a recognized SET option.

将此添加到存储过程的开头，将时间从 17 分钟缩短到 40 秒。

在 SSMS 中运行仍然需要 1 秒以上的时间，仍然不足以使用，但进展不减。

但是，我注意到它返回的选项值 （5496） 与我从上面的执行计划详细信息 （4345） 中获得的值不同，并且某些设置与该数据库的设置不同。

因此，我重新运行了硬编码为 4345 的查询

DECLARE @options INT
SELECT @options = 4345 --@@OPTIONS
PRINT @options
PRINT 'SET DISABLE_DEF_CNST_CHK ' + CASE WHEN ( (1 & @options) = 1 ) THEN 'ON' ELSE 'OFF' END + ';'
PRINT 'SET IMPLICIT_TRANSACTIONS ' + CASE WHEN ( (2 & @options) = 2 ) THEN 'ON' ELSE 'OFF' END + ';'
PRINT 'SET CURSOR_CLOSE_ON_COMMIT ' + CASE WHEN ( (4 & @options) = 4 ) THEN 'ON' ELSE 'OFF' END + ';'
PRINT 'SET ANSI_WARNINGS ' + CASE WHEN ( (8 & @options) = 8 ) THEN 'ON' ELSE 'OFF' END + ';'
PRINT 'SET ANSI_PADDING ' + CASE WHEN ( (16 & @options) = 16 ) THEN 'ON' ELSE 'OFF' END + ';'
PRINT 'SET ANSI_NULLS ' + CASE WHEN ( (32 & @options) = 32 ) THEN 'ON' ELSE 'OFF' END + ';'
PRINT 'SET ARITHABORT ' + CASE WHEN ( (64 & @options) = 64 ) THEN 'ON' ELSE 'OFF' END + ';'
PRINT 'SET ARITHIGNORE ' + CASE WHEN ( (128 & @options) = 128 ) THEN 'ON' ELSE 'OFF' END + ';'
PRINT 'SET QUOTED_IDENTIFIER ' + CASE WHEN ( (256 & @options) = 256 ) THEN 'ON' ELSE 'OFF' END + ';'
PRINT 'SET NOCOUNT ' + CASE WHEN ( (512 & @options) = 512 ) THEN 'ON' ELSE 'OFF' END + ';'
PRINT 'SET ANSI_NULL_DFLT_ON ' + CASE WHEN ( (1024 & @options) = 1024 ) THEN 'ON' ELSE 'OFF' END + ';'
PRINT 'SET ANSI_NULL_DFLT_OFF ' + CASE WHEN ( (2048 & @options) = 2048 ) THEN 'ON' ELSE 'OFF' END + ';'
PRINT 'SET CONCAT_NULL_YIELDS_NULL ' + CASE WHEN ( (4096 & @options) = 4096 ) THEN 'ON' ELSE 'OFF' END + ';'
PRINT 'SET NUMERIC_ROUNDABORT ' + CASE WHEN ( (8192 & @options) = 8192 ) THEN 'ON' ELSE 'OFF' END + ';'
PRINT 'SET XACT_ABORT ' + CASE WHEN ( (16384 & @options) = 16384 ) THEN 'ON' ELSE 'OFF' END + ';'

这返回了

4345
SET DISABLE_DEF_CNST_CHK ON;
SET IMPLICIT_TRANSACTIONS OFF;
SET CURSOR_CLOSE_ON_COMMIT OFF;
SET ANSI_WARNINGS ON;
SET ANSI_PADDING ON;
SET ANSI_NULLS ON;
SET ARITHABORT ON;
SET ARITHIGNORE ON;
SET QUOTED_IDENTIFIER OFF;
SET NOCOUNT OFF;
SET ANSI_NULL_DFLT_ON OFF;
SET ANSI_NULL_DFLT_OFF OFF;
SET CONCAT_NULL_YIELDS_NULL ON;
SET NUMERIC_ROUNDABORT OFF;
SET XACT_ABORT OFF;

同样，SET DISABLE_DEF_CNST_CHK ON; 行表示这不是您可以设置的选项，所以我将其注释掉了。

使用这些 SET 值更新了存储过程，然后重试。

它仍然需要 40 秒，所以没有进一步的进展。

在 SSMS 中运行它仍然需要 1 秒钟，所以至少它没有破坏它，并不是说它有任何帮助，但很高兴知道！

编辑#2：或者不...

似乎昨天的明显进展只是昙花一现：它又回到了 17 分钟！（没有任何变化）

尝试将所有三个选项组合在一起：WITH RECOMPILE、OPTION OPTIMIZE 和显式设置 SET 选项。仍然需要 17 分钟。

编辑3：参数嗅探设置

在 SQL Azure 中，可以从数据库选项屏幕关闭“参数探查”。

并使用

SELECT * FROM sys.database_scoped_configurations

将其设置为 OFF 后，尝试了两次 SSMS 和 C#。

和以前一样，SSMS 需要 1 秒，C# 仍然需要 15+ 分钟。

当然，如果 C# 在连接时强制加载参数到特定状态，则它完全有可能覆盖它。

所以，只是为了说我试过了，我把它关在了存储过程中

ALTER DATABASE SCOPED CONFIGURATION SET PARAMETER_SNIFFING = OFF;

还是 15+ 分钟。

啊，好吧，值得一试！

此外，还有许多新参数可供查找和测试。

编辑 #4：Azure 暂存池配置和自动优化

我在暂存池上尝试了几种不同的配置，看看是否有所作为。我没有尝试最糟糕的查询，因为它花费了我们花钱来增加 eDTU，但我尝试了其他几个，每个两次（每次都向下列出，所以不是同一个查询直接两次）。

从 50 个 eDTU 到 100 个 eDTU 有点不同，所以我想在我们的测试弹性池中，我们使用了所有 50 个 eDTU，但之后没有任何区别。奇怪的是，Premium 在某些地方的性能比 Standard 差。

然后，我将其发布在 Azure MSDN 网站上（当他们最终开始验证我的帐户时），他们建议浏览 Azure 门户上的所有性能选项，看看是否有任何建议。

它建议了我启用的几个索引，但仅此而已。

然后，我将自动调整从“服务器”翻转到“Azure 默认值”

我重新运行了大多数相同的计时测试，只是为了看看它有什么不同。

原本需要 17 分钟的查询现在通常只需要 13 秒，这是一个巨大的改进！耶！

其余的都是喜忧参半。C 通常更快，大多数仍然需要大约相同的时间，而 E 现在需要的时间几乎是原来的两倍（从 14 秒增加到 26 秒）。

结果似乎也比以前多得多，尽管更改 eDTU 大小可能会重置调整。第二次运行通常比第一次更好，通常很明显。

仍然比在本地服务器上对数据库运行相同的系统要慢得多，但至少对于最慢的存储过程来说，这是一个巨大的改进。