CSS SQL Server Engineers

This is the official team Web Log for Microsoft Customer Service and Support (CSS) SQL Support. Posts are provided by the CSS SQL Escalation Services

Query performance and plan cache issues when parameter length not specified correctly

Query performance and plan cache issues when parameter length not specified correctly

  • Comments 5

We recently worked with a customer who reported his update to a linked server table runs very slow.   This only happens when he doesn’t specify the character parameter length in the .NET code.  This actually brings up plan cache issue as well.   So this is worth a blog.

 

Let’s use this simplified .NET example.  Here is the .NET code:

SqlConnection conn = new SqlConnection (@"Data Source=Server1;Integrated Security=SSPI");
conn.Open();
SqlCommand cmd = conn.CreateCommand();
cmd.CommandText = @"update Server2.master.dbo.t set c2 = @c2 where c1 = 1";
string str = "abc";
cmd.Parameters.Add("@c2", SqlDbType.VarChar).Value = str;
cmd.ExecuteNonQuery();

Furthermore, the table t on the remote server has a definition of “create table t (c1 int, c2 varchar(500))”

In the above code, the query runs on Server1 but updates a table on Server2.   The query is parameterized to take @c2 as a parameter.  But when adding the parameter, the code didn’t specify the length of parameter for @c2.

When the latest SQL .NET provider sees this, it will determine the length of the string str and use that as the length of the parameter for @c2.  This translate into the following query:

exec sp_executesql N'update Server2.master.dbo.t set c2 = @c2 where c1 = 1',N'@c2 varchar(3)',@c2='abc'

 

Why the above query will perform slowly?

The table t on remote server has a column c2 as varchar(500).  But the parameterized update (translated by the Provider) specify that the @c2 parameter is of varchar(3).   This will result in mismatching parameter.   In linked server situation, we are very cautious involving update and insert if the character data length do not match for fear of truncation of characters.   So SQL Server generates a plan that would bring all data locally to the server and then do update.  Finally it sends the update back to remote server.

Here is the plan.  As you can see a “remote scan” is used to bring the entire table locally.

update [Server2].master.dbo.t set c2 = @c2 where c1 = 1
  |--Remote Update(SOURCE:(Server2), OBJECT:("master"."dbo"."t"), SET:([Server2].[master].[dbo].[t].[c2] = [Expr1003]))
       |--Compute Scalar(DEFINE:([Expr1003]=CONVERT_IMPLICIT(varchar(500),[@c2],0)))
            |--Table Spool
                 |--Filter(WHERE:([Server2].[master].[dbo].[t].[c1]=(1)))
                      |--Remote Scan(SOURCE:(Server2), OBJECT:("master"."dbo"."t"))

One can argue that SQL Server should know that this particular case there is no risk of truncation because the parameter length is less than column length.  But currently SQL Server is being conservative and is being looked into for future products.   However, even this is addressed, the approach application is taking has other disadvantages that I will talk about.

Problems of not specifying parameter length

  1. Linked server performance:  As illustrated above, linked server performance will suffer because the parameter length doesn’t match table definition.   This will happen for update and insert because both are at risk of character truncation.
  2. Multiple compiles and plan cache pollution:  In the above .NET code in the very beginning, you can supply different strings of different length.  for example, if you supply string str=”abcd”.  Now, the provider generates a parameterized query like this: “exec sp_executesql N'update [Server2].master.dbo.t set c2 = @c2 where c1 = 1',N'@c2 varchar(4)',@c2='abcd'”.  Note that length of parameter @c2 is now 4.   This  requires a different plan for the parameterized query.  You will have multiple compiles and multiple plans being cached.    Note that this problem is not specific to linked server queries.  Any query done this way will pollute the procedure cache.   This impacts all parameterized queries such as insert, update, delete and select.

Solutions

The solution is to change your app so that the parameter length matches the column length.   In this example, you just do this “cmd.Parameters.Add("@c2", SqlDbType.VarChar, 500).Value = str;”.  Note that last parameter is the length of the parameter.   After doing this, you solve two problems mentioned above.  You will have a linked server query that runs fast and you will have just one copy of plan in cache.

After the change, you will get the following plan.  Note that “Remote Query” means that the entire update was sent to remote server without pulling data locally for processing.

update [Server2].master.dbo.t set c2 = @c2 where c1 = 1
  |--Remote Query(SOURCE:(Server2), QUERY:(UPDATE "master"."dbo"."t" set "c2" = ?  WHERE "c1"=(1)))

 

Jack Li | Senior Escalation Engineer |Microsoft SQL Server Support

Leave a Comment
  • Please add 8 and 3 and type the answer here:
  • Post
  • Does this affect stored procedure execution where CommandType is StoredProcedure?

  • I noticed that in addition to specifying the size you changed the type from NVARCHAR to VARCHAR:

    Orig: cmd.Parameters.Add("@c2", SqlDbType.NVarChar).Value = str;

    New:  cmd.Parameters.Add("@c2", SqlDbType.VarChar, 500).Value = str;

    Would that have anything to do with the linked query performance issue?

  • Regarding Michael's comment about nvarchar and varchar, it's a typo.  It's corrected now. thanks for pointing this out.   The example intended to use varchar data type though the problem also occurs for nvarchar data type.  If your column is varchar, you will need to use SqlDbType.VarChar.   If your column is nvarchar, you will need to use SqlDbType.NVarChar.  

    Regarding JR's question:   None of the two problems described here apply to stored procedure.  Stored procedure will perform just fine and there will be not cache bloating either.

  • Hi,

    Just want to confirm from you that, if table column length has varchar(50) and query variable length has varchar(25) does this difference make any difference in the query execution?

    Example:

    --==============

    Create table TableName (Col1 int, Col2 varchar(50))

    go

    declare @str varchar(25)

    select @str = 'my text'

    select * from TableName where Col2 = @str

    --==============

    As per execution plan there is no difference. Can you please confirm? Is that SQL internally adjusting the length or it will ignore because of length is less than actual length?

    Thanks..

  • Is there any performance penalty in setting all ADO.NET string parameter lengths to -1 (max) or 4000? (i.e. set all params to the same large length)?

Page 1 of 1 (5 items)