如何防止在SELECT语句中对.NET转换为EXEC的参数进行嗅探？

Question

我有一条SQL SELECT语句，它在SQL Server Management Studio中运行得非常快(5秒)，但从我的ASP.NET代码运行起来却非常慢。

我读到这可能与参数嗅探有关。当我从SSMS运行SELECT语句时，我使用的是SELECT语句。但是当我观察SQL Profiler时，我的SELECT语句是以存储过程的模式执行的: exec sp_executesql N'SELECT foo FROM bar WHERE userid = @userid'，N'@userid int'，@userid=2

此ASP.NET代码在四分钟后超时(连接字符串设置)：

SqlCommand objCommand = new SqlCommand("SELECT foo FROM bar WHERE userid = @userid", objCS);
SqlDataReader reader;
objCommand = new SqlCommand("", objCS);
objCommand.CommandType = CommandType.Text;
objCommand.Parameters.Add("@userid", SqlDbType.Int).Value = 1;
SqlDataReader reader = objCommand.ExecuteReader();

//objCommand.ExecuteReader()超时；

此代码在SQL Server Management Studio中5秒内执行：

DECLARE @userid AS Int = 2
SELECT foo FROM bar WHERE userid = @userid

在5秒内返回60000+行。

如何使ASP.NET代码作为SELECT语句而不是存储过程执行，同时保留参数化存储过程的安全值？

编辑:我认为这可能与使用SQL语句运行代码的不同用户和在SSMS中运行SQL语句的用户有关。从代码运行的用户具有不同的服务器权限，包括对表的有限访问权限。SQL语句包含一个视图，而用户对该视图中的所有表没有底层的SELECT访问权限。这是否也意味着他们无法获得统计数据？

Answer 1

如果确实是参数嗅探(您必须查看实际计划才能确定这一点)，并且您确信您有正确的索引，那么您可以更新您的查询以使用索引提示，但这不是最佳方式。如果您可以将查询放入存储过程中，则可以使用RECOMPILE标记存储过程，这样它就不会重用第一个计划。这是一种比强制索引提示更好的做法。

Answer 2

您的问题与参数嗅探无关。但是设置SSMS和后端的选项。无论何时从SSMS执行语句，它都会使用SSMS定义的属性。您可以在

工具>>选项>>查询执行>> SQL Server >>高级

您将在SSMS中启用SET ARITH_ABORT选项。但你在你的手术中没有。因此，在您的过程中添加以下行，并再次测试它。

SET ARITHABORT ON

我对你的问题非常有信心，这个问题会得到解决的。