String concatenation with max types stops plan caching

Erland Sommarskog (http://www.sommarskog.se/) was recently investigating delimited strings and was finding odd behaviour, which he wanted verifying. The behaviour was that he was getting recompiles with certain functions he was using for string splitting.

I had a look and found exactly the same behaviour.

I started dissecting the code and found the issue was with string concatenation and use of tables. These two sps identify the problem

create   procedure usp_CollationBug

  @param nvarchar(max)

 as

 SELECT top 1 @param +N','  AS output

   FROM sys.objects 

go

create procedure usp_CollationBugTest

as

  dbcc freeproccache

  exec usp_CollationBug N'1,2,3'

  SELECT o.name,*

  FROM sys.dm_exec_cached_plans cp

OUTER APPLY (sys.dm_exec_query_plan(cp.plan_handle) qp

             LEFT JOIN sys.objects o ON qp.objectid = o.object_id)

  WHERE qp.dbid = db_id()

    and o.name = 'usp_CollationBug'

  ORDER BY o.name

If you execute usp_CollationBugTest then you will find that there is no record returned in the last query, i.e. there isn't a cached query plan.

So what is causing it. Well if you take off the string concatenation (+ N',') or the FROM sysobjects you will get a query plan cached

alter  procedure usp_CollationBug

  @param nvarchar(max)

 as

 SELECT @param +N','  AS output

 go

exec usp_CollationBugTest

go

alter  procedure usp_CollationBug

  @param nvarchar(max)

 as

 SELECT top 1 @param AS output

 from sys.objects 

go

exec usp_CollationBugTest

go

Both of these should return a cached plan.

So I thought about using a CTE, using CHAR to append the string, or using varchar and not Nvarchar

alter  procedure usp_CollationBug

  @param nvarchar(max)

 as

 ;with param as(select @param + ',' value)

 SELECT top 1 param.value  AS output

 from sys.objects 

 cross join param

go

exec usp_CollationBugTest

go

alter  procedure usp_CollationBug

  @param nvarchar(max) 

 as

 SELECT top 1 @param + char(44) AS output

 from sys.objects 

 go

exec usp_CollationBugTest

go

alter  procedure usp_CollationBug

  @param varchar(max)

 as

 SELECT top 1 @param +',' AS output

 from sys.objects 

 go

exec usp_CollationBugTest

go

But unfortunately none of these work.

So far I've found two solutions, one is to force the collation of the expression and the second is to not use MAX datatypes

alter  procedure usp_CollationBug

  @param nvarchar(max)

 as

 SELECT top 1 @param + N',' COLLATE SQL_Latin1_General_CP1_CI_AS  AS output

 from sys.objects 

 go

exec usp_CollationBugTest

go

alter  procedure usp_CollationBug

  @param nvarchar(4000)

 as

 SELECT top 1 @param + N',' COLLATE SQL_Latin1_General_CP1_CI_AS  AS output

 from sys.objects 

 go

exec usp_CollationBugTest

go

I thought about this more and thought about using STUFF, I came up with the following solution that does result in a cached plan

alter  procedure usp_CollationBug

  @param nvarchar(max)

 as

 SELECT top 1 stuff(@param,LEN(@param),1, right(@param,1) + N',') AS output

 from sys.objects 

 go

exec usp_CollationBugTest

go

I then thought about my post when is varchar(max) not a varchar(max) This highlights that you have to make sure your input variables are of a max type to get a max type out. So I tried the following, and found it worked.

alter  procedure usp_CollationBug
  @param nvarchar(max)
 as
 SELECT top 1 @param + cast(N',' as nvarchar(max))  AS output
 from sys.objects  
 go
exec usp_CollationBugTest
go

Many people use MAX data types as an easy opt out, DONT as it will lead to this problem if you are concatenating

The forcing of the collation should not be a problem as you have to specify it for your server, database and columns

So now you know the solution, why is it important. Query plans are essential for the performance of queries that are regularly executed, to avoid the compile time, this is especially true in OLTP systems.

If you do have a query that is executed 100s of times per second , avoiding compilation will more than likely improve performance

 


-
Published Saturday, July 11, 2009 3:57 PM by simonsabin

Comments

Sunday, July 12, 2009 7:18 PM by Martin Bell UK SQL Server MVP

# How parameter datatypes can effect caching

Plan caching is something I think most DBAs would probably never look at until it becomes a problem on a live system. This post by Simon Sabin shows that something which may seem so insignificant during developement could potentially have a huge adverse

# Something for the Weekend 17/07/09 | John Sansom - SQL Server DBA in the UK

Pingback from  Something for the Weekend 17/07/09 | John Sansom - SQL Server DBA in the UK

Monday, July 20, 2009 4:51 AM by Paul White NZ

# re: String concatenation with max types stops plan caching

Using a local variable set to a comma instead of the literal also results in a cached plan:

go

alter procedure dbo.usp_CollationBug

@param nvarchar(max)

as

declare @comma nchar(1) = ',';

select top (1) @param + @comma AS [output]

from sys.objects

go

Monday, July 20, 2009 7:30 AM by simonsabin

# re: String concatenation with max types stops plan caching

Well spotted.

That works because the optimiser can't snif the value at compile time and so the query is defined with 2 parameters even though one is fixed.

As an update we've been told this is fixed for the next release (not sure if thats R2 or 2010 and they are considering one for SQL 2008

Sunday, July 26, 2009 6:12 PM by SqlServerKudos

# String concatenation with max types stops plan caching

Kudos for a great Sql Server article - Trackback from SqlServerKudos