ADO.NET：安全指定-1 SqlParameter.Size为将所有varchar参数？ [英] ADO.NET: Safe to specify -1 for SqlParameter.Size for all VarChar parameters?
问题描述
我们有C＃code调用参数即席SQL Server的查询，在许多地方的现有机构。我们从未指定SqlParameter.Size，和它的记录，在这种情况下，类的SqlParameter推断从参数值的大小。我们最近刚刚开始意识到，在SQL Server计划缓存污染问题这创造，在一个单独的计划高速缓存每个不同的参数大小的组合。
We have an existing body of C# code that calls parameterized ad-hoc SQL Server queries in many places. We never specify SqlParameter.Size, and it's documented that in this case, the SqlParameter class infers the size from the parameter value. We just recently became aware of the SQL Server plan cache pollution issues this creates, where a separate plan is cached for each distinct combination of parameter sizes.
幸运的是，当我们创建一个SqlParameter的，我们通过一个单一的工具方法做到这一点，所以我们有机会几行添加到该方法，使这个问题消失。我们正在考虑增加以下内容：
Luckily, whenever we create a SqlParameter, we do so via a single utility method, so we have the opportunity to add a few lines to that method and make this problem go away. We are thinking about adding the following:
if((sqlDbType == SqlDbType.VarChar) || (sqlDbType == SqlDbType.NVarChar))
m_sqlParam.Size = -1;
在换句话说，每次我们传递一个VARCHAR参数的时候，把它作为一个varchar（最大值）。根据一些快速的测试，能正常工作，我们可以看到（通过SQL事件探查器和sys.dm_exec_cached_plans），现在有在高速缓存中的每个即席查询单计划，以及字符串参数的类型（S）现在VARCHAR（最大值）。
In other words, every time we pass a varchar parameter, pass it as a varchar(max). Based on some quick testing, this works fine, and we can see (via SQL Profiler and sys.dm_exec_cached_plans) that there is now a single plan in the cache for each ad-hoc query, and the type of the string parameter(s) is now varchar(max).
这似乎是这样一个简单的解决方案，它必须有一些隐藏的，性能破坏的缺点。任何人是否知道一个？
This seems like such an easy solution that there must be some hidden, performance-destroying downside. Is anyone aware of one?
（请注意，我们只需要支持SQL Server 2008及更高版本。）
(Please note that we only need to support SQL Server 2008 and later.)
是的，有一个隐藏的，性能破坏的缺点！
非常感谢马丁·史密斯，他的回答（见下文）向我指出了正确的方式来分析这一点。我与我们的应用程序的用户表，其中有定义为nvarchar（100），电子邮件列，并且有一个非聚集索引（IX_Users_Email）在电子邮件列测试。我修改了马丁的例子查询，如下所示：
Many thanks to Martin Smith, whose answer (see below) pointed me to the right way to analyze this. I tested with our application's Users table, which has an Email column defined as nvarchar(100), and has a non-clustered index (IX_Users_Email) on the Email column. I modified Martin's example query as follows:
declare @a nvarchar(max) = cast('a' as nvarchar(max))
--declare @a nvarchar(100) = cast('a' as nvarchar(100))
--declare @a nvarchar(4000) = cast('a' as nvarchar(4000))
select Email from Users where Email = @a
根据该声明的语句我未评论，我得到一个非常不同的查询计划。在为nvarchar（100）和nvarchar（4000）两种版本，给我一个指数征求在IX_Users_Email - 事实上，任何长度我指定给了我同样的计划。在为nvarchar（max）版本，而另一方面，给了我一个指数扫描在IX_Users_Email，随后过滤运营商应用predicate。
Depending which of the "declare" statements I un-comment, I get a VERY different query plan. The nvarchar(100) and nvarchar(4000) versions both give me an index seek on IX_Users_Email -- in fact, any length I specify gives me the same plan. The nvarchar(max) version, on the other hand, gives me an index scan on IX_Users_Email, followed by a Filter operator to apply the predicate.
这足以让我 - 如果得到一个扫描，而不是寻求任何可能性，那么这种治疗比疾病更糟糕
That's enough for me -- if there is any possibility of getting a scan rather than a seek, then this "cure" is worse than the disease.
新提案
我注意到，每次SQL Server的参数化与VARCHAR参数的查询，高速缓存的计划只是使用VARCHAR（8000）（或nvarchar（4000））为参数。我想，如果它的SQL Server的不够好，这对我来说不够好！替换C＃code。在我原来的问题（上图）与：
I noticed that every time SQL Server parameterizes a query with a varchar parameter, the cached plan simply uses varchar(8000) (or nvarchar(4000)) for the parameter. I figure if it's good enough for SQL Server, it's good enough for me! Replacing the C# code in my original question (above) with:
if(sqlDbType == SqlDbType.VarChar)
m_sqlParam.Size = 8000;
else if(sqlDbType == SqlDbType.NVarChar)
m_sqlParam.Size = 4000;
这似乎解决了计划缓存污染问题，而不会对查询计划为使用-1大小相同的影响。但是，我没有做大量的测试与此的，我很感兴趣地听到这个修订后的办法任何人的意见。
This seems to solve the plan cache pollution problem without the same impact on the query plans as using a Size of -1. However, I have not done a lot of testing with this, and I am very interested to hear anyone's input on this revised approach.
我们不得不修改现有版本（新提案，上文）来处理的情况下的参数值大于最大值。在这一点上，你没有选择，只能使一个varchar（最大值）：
We had to modify the prior version (New Proposal, above) to handle the case where the parameter value is longer than the maximum. At that point, you have no choice but to make it a varchar(max):
if((sqlDbType == SqlDbType.VarChar) || (sqlDbType == SqlDbType.NVarChar))
{
m_sqlParam.Size = (sqlDbType == SqlDbType.VarChar) ? 8000 : 4000;
if((value != null) && !(value is DBNull) && (value.ToString().Length > m_sqlParam.Size))
m_sqlParam.Size = -1;
}
我们一直在使用这个版本大约半年没有问题。
We've been using this version for about six months with no problems.
推荐答案
这是不理想的，因为它是最好指定所涉及的列（S）的数据类型相匹配的参数。
It is not ideal in that it is best to specify a parameter that matches the datatype of the column(s) involved.
您需要检查您的查询计划，看看他们是否仍然看起来合理的。
You would need to check your query plans to see if they still look reasonable.
尝试以下的试验
CREATE TABLE #T
(
X VARCHAR(10) PRIMARY KEY
)
DECLARE @A VARCHAR(MAX) = CAST('A' AS VARCHAR(MAX))
SELECT *
FROM #T
WHERE X = @A
给出了一个计划，像
Gives a plan like
SQL Server会将计算标到调用内部函数 GetRangeWithMismatchedTypes 的计划，仍然能执行一个索引查找（<一href="http://sqlblog.com/blogs/paul_white/archive/2012/01/18/dynamic-seeks-and-hidden-implicit-conversions.aspx"相对=nofollow>更多关于隐式转换的细节这里）。
SQL Server adds a compute scalar to the plan which calls the internal function GetRangeWithMismatchedTypes and still manages to perform an index seek (more details on implicit conversions here).
其中它物质示于文章<一个甲反例href="http://sqlblog.com/blogs/paul_white/archive/2012/09/12/why-doesn-t-partition-elimination-work.aspx"相对=nofollow>为什么不分区排除工作？。在那篇文章中所描述的行为也适用于一个 VARCHAR（最大）参数对分区上的 VARCHAR（n）的列。
A counter example where it does matter is shown in the article Why Doesn’t Partition Elimination Work?. The behaviour described in that article also applies for a varchar(max) parameter against a table partitioned on a varchar(n) column.
这篇关于ADO.NET：安全指定-1 SqlParameter.Size为将所有varchar参数？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
