THE SQL Server Blog Spot on the Web

Welcome to - The SQL Server blog spot on the web Sign in | |
in Search

SQLBI - Marco Russo

Please visit the same blog at - comments are now disabled here but you can comment on the new blog (all blog posts available here are available there, too).
You can follow me on Twitter: @marcorus

LINQ to SQL and the procedure cache of SQL Server

I just received a mail from Adam Machanic that pointed me to this bug (I would call it a performance issue) about the construction of SQL statements generated by the LINQ to SQL engine.

The issue: every string passed as a constant in the query will be auto-parameterized using the length of the passed string, even when you used a string variable into the LINQ query. If you write something like

string s = "Wine";
var query =
        from x in db.Products
        where x.ProductName == s
select x;

you will see that a parameter of type NVARCHAR(4) will be passed to the generated SQL query. The next execution of query might contain a different value in the s parameter, and for this reason a different parameter type might be used: if the length of the string in the s variable changes, then the same query will be sent to SQL Server, but using a different type in the sp_executesql parameters .For example, a NVARCHAR(5) would be used whether s contains"Bread".

The consequence of this behavior is that you could have a non-optimal performance from SQL Server and, more important, the procedure cache could be filled up with several copies of the same query, differing each other only in the length of the parameter type.

I agree with Adam: this is something to be fixed. But my suspect is that we will get a "by design" answer another time...

Published Thursday, August 28, 2008 3:14 AM by Marco Russo (SQLBI)



Anonymous said:

Thanks for posting, Marco.  Readers, please vote 5 so we don't have to deal with still more proc cache issues!

August 27, 2008 8:53 PM

José Flores said:

i'm sorry if i don't write well.

I have the same problem with Linq to Entity Framework.

Then the problem might be the provider? ".Net SqlClient Data Provider"

This is a GREAT PROBLEM because it does not only generates different plans for different string lengths.

i have different plans on my procedure cache for the same length.

i've restarted SQL Server today and after six hours i have 130 results for the same query

select top 500 objtype,p.usecounts,p.size_in_bytes,[sql].[text]

from sys.dm_exec_cached_plans p

outer apply sys.dm_exec_sql_text (p.plan_handle) sql

order by 4

Look at these three "plans" for the same length

(@p__linq__1 nvarchar(30))SELECT   [Limit1].[dll_nombre] AS [dll_nombre],   [Limit1].[dll_version] AS [dll_version],   [Limit1].[dll_archivo] AS [dll_archivo]  FROM ( SELECT TOP (1)    [Extent1].[dll_nombre] AS [dll_nombre],    [Extent1].[dll_version] AS [dll_version],    [Extent1].[dll_archivo] AS [dll_archivo]   FROM [dbo].[ADdlls] AS [Extent1]   WHERE (UPPER([Extent1].[dll_nombre])) = (UPPER(@p__linq__1))  )  AS [Limit1]

(@p__linq__10 nvarchar(30))SELECT   [Limit1].[dll_nombre] AS [dll_nombre],   [Limit1].[dll_version] AS [dll_version],   [Limit1].[dll_archivo] AS [dll_archivo]  FROM ( SELECT TOP (1)    [Extent1].[dll_nombre] AS [dll_nombre],    [Extent1].[dll_version] AS [dll_version],    [Extent1].[dll_archivo] AS [dll_archivo]   FROM [dbo].[ADdlls] AS [Extent1]   WHERE (UPPER([Extent1].[dll_nombre])) = (UPPER(@p__linq__10))  )  AS [Limit1]

(@p__linq__11 nvarchar(30))SELECT   [Limit1].[dll_nombre] AS [dll_nombre],   [Limit1].[dll_version] AS [dll_version],   [Limit1].[dll_archivo] AS [dll_archivo]  FROM ( SELECT TOP (1)    [Extent1].[dll_nombre] AS [dll_nombre],    [Extent1].[dll_version] AS [dll_version],    [Extent1].[dll_archivo] AS [dll_archivo]   FROM [dbo].[ADdlls] AS [Extent1]   WHERE (UPPER([Extent1].[dll_nombre])) = (UPPER(@p__linq__11))  )  AS [Limit1]

August 17, 2009 2:46 PM

Marco Russo said:

Good news: Visual Studio 2010 (thus, .NET 4.0) solves the issue and uses NVARCHAR(4000) for each parameter in a LINQ query.

August 17, 2009 6:18 PM
New Comments to this post are disabled

About Marco Russo (SQLBI)

Marco Russo is a consultant, writer and trainer specialized in Business Intelligence with Microsoft technologies. He runs the SQLBI.COM website, which is dedicated to distribute resources useful for BI developers, like Integration Services components, Analysis Services models, tools, technical information and so on. Marco is certified as MCT, MCDBA, MCSD.NET, MCSA, MCSE+I.

This Blog



Privacy Statement