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

  • Article

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