Login or Sign Up to become a member!
LessThanDot Sit Logo

LessThanDot

Data Management

Less Than Dot is a community of passionate IT professionals and enthusiasts dedicated to sharing technical knowledge, experience, and assistance. Inside you will find reference materials, interesting technical discussions, and expert tips and commentary. Once you register for an account you will have immediate access to the forums and all past articles and commentaries.

LTD Social Sitings

Lessthandot twitter Lessthandot Linkedin Lessthandot friendfeed Lessthandot facebook Lessthandot rss

Note: Watch for social icons on posts by your favorite authors to follow their postings on these and other social sites.

Your profile

Tools

Tag cloud

book business intelligence database dates dmv excel functions gemini gotcha hotfix how to howto indexing interview linq madison performance performance tuning programming sql sql friday sql server sql server 2000 sql server 2005 sql server 2008 sql server 2008 r2 t-sql tip trick xml

Authors

Main Categories

Search

XML Feeds

What is RSS?

Google Ads

« How to Rebuild System Databases in SQL Server 2008SQL Server 2008 Express with Advanced Services And SQL Server 2008 Express With Tools Now Available For Download »
6
comments

LINQ to SQL queries involving strings cause SQL Server procedure cache bloat

by SQLDenis on Aug 28, 2008 in categories Data Modelling & Design
Rate Post:
submit to reddit Digg!FacebookDotnetkicks

Adam Machanic created an item on connect explaining how LINQ to SQL queries involving strings cause SQL Server procedure cache bloat

If an application is using LINQ to SQL and the queries involve the use of strings that can be highly variable in length, the SQL Server procedure cache will become bloated with one version of the query for every possible string length. For example, consider the following very simple queries created against the Person.AddressTypes table in the AdventureWorks2008 database:

c#
Line number Off | Hide | Select all
  1. var p =
  2.                 from n in x.AddressTypes
  3.                 where n.Name == "Billing"
  4.                 select n;
  5.  
  6.             var p =
  7.                 from n in x.AddressTypes
  8.                 where n.Name == "Main Office"
  9.                 select n;

If both of these queries are run, we will see two entries in the SQL Server procedure cache: One bound with an NVARCHAR(7), and the other with an NVARCHAR(11). Now imagine if there were hundreds or thousands of different input strings, all with different lengths. The procedure cache would become unnecessarily filled with all sorts of different plans for the exact same query. Even worse, imagine if a query used two or three different string parameters. The procedure cache could end up with hundreds of thousands or even millions of entries, the only differences being the variable lengths

Wow that is bad indeed, please go to the connect site and vote for this so that Microsoft ‘fixes’ this. Here is the URL: https://connect.microsoft.com/VisualStudio/feedback/ViewFeedback.aspx?FeedbackID=363290

About the Author

User bio imageDenis has been working with SQL Server since version 6.5. Although he worked as an ASP/JSP/ColdFusion developer before the dot com bust, he has been working exclusively as a database developer/architect since 2002. In addition to English, Denis is also fluent in Croatian and Dutch, but he can curse in many other languages and dialects (just ask the SQL optimizer) He lives in Princeton, NJ with his wife and three kids.
Social SitingsTwitterFacebookLinkedInHomePageLTD RSS Feed
3445 views
More Posts by SQLDenis
bug, linq, performance
submit to reddit Digg!FacebookDotnetkicks

Comments and Feedback

6 comments

Comment from: chrissie1 [Member] Email
I have a feelig they released Linq a bit to early. On the other hand it is a complicated system. So it was bounf to have some bugs.
08/28/08 @ 14:30
Comment from: SQLDenis [Member] Email
Yep, RAM - proc cache = free ram for other stuff. If your proc cache is too big you won't have that much RAM left unless you want to run DBCC FREEPROCACHE every 10 minutes :-0
08/28/08 @ 14:33
Comment from: coolcoder [Visitor] Email · http://sqldev.wordpress.com
****-
Is this still the case in SP1?, I know they made changes to LINQ to SQL specifically in the service pack.
08/29/08 @ 01:57
Comment from: SQLDenis [Member] Email
Coolcoder, the connect item shows Visual Studio 2008 Service Pack 1
08/29/08 @ 03:53
Comment from: José Flores [Visitor]
*****
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]
08/17/09 @ 12:36
Comment from: SQLDenis [Member] Email
This is very interesting, I haven't tried the Entity Framework myself and feel reluctant doing so when I see stuff like that
08/17/09 @ 12:43

Leave a comment


Your email address will not be revealed on this site.

Your URL will be displayed.
(Line breaks become <br />)
(Name, email & website)
(Allow users to contact you through a message form (your email will not be revealed.)

Powered by B2Evolution
Valid XHTML | Valid CSS