Entity Framework, Parameterised Queries and Plan Caches.

Firstly, apologies if you were expecting regular (or frequent) updates here. I've been pretty for the last few months actually building software rather than writing about it. I've been using a lot of tech that I've only really dabbled with in the past, so most of the things I've been learning have been the same noob things you can read about anywhere.

One piece of tech I'm using commercially for the first time is Entity Framework (via Ideablade Devforce on a silverlight client). We started the first week of user training en-masse this week, and immediately noticed unusually high memory usage on the SQL server. Our database is approx 1.7Gb (Data + Indexes), yet the machine shot to 3.8Gb of the 4Gb Memory available (4Gb 64bit SQL 2008 R2 on an ESX VM).... and stayed there.

Investigating the issues showed a significant amount of memory consumed by SQL's plan cache, with very low use counts:

SELECT objtype AS [CacheType]
        , count_big(*)AS [Total Plans]
        , sum(cast(size_in_bytes as decimal(18,2)))/1024/1024 AS [Total MBs]
        , avg(usecounts) AS [Avg Use Count]
        , sum(cast((CASE WHEN usecounts = 1 THEN size_in_bytes ELSE 0 END) as decimal(18,2)))/1024/1024 AS [Total MBs - USE Count 1]
        , sum(CASE WHEN usecounts = 1 THEN 1 ELSE 0 END) AS [Total Plans - USE Count 1]

Running the above on or server returned the following for the AdHoc cache:

Adhoc 6919  1471.867187 4  930.242187  5565

Wow, almost 1/4 of our memory is being consumed storing cached execution plans that were only used the one time they were generated!

The Cause:

Profiling the SQL being run against the server showed that the queries being genereated by EF were not parameterised. There's a great writeup from Julie Lerman about when EF uses parameters and when it doesn't, but I haven't had a chance to experiment with our codebase to try and get the parameters queries happening. I also don't know whether to blame EF, DevForce or myself. There's always Phase 2 of the project to improve things, but what to do for Phase 1?

The Solution:

It turns out SQL 2008 has an advanced option perfectly suited to this scenario. Kim Tripp (as always) has a great writeup of the 'Optimise for AdHoc Workflows option', and is the source for the SQL shown above. You can read the nitty gritty details of how the setting works elsewhere, but here's the results for the query above after enabling the setting, clearing the ProcCache and letting the system warm back up to the same number of plans in cache as the day before.

Nb Yes, I know these numbers are smaller than those above. We didn't get enough usage from the system in one day to pull the cached plan count up to the values above. Luckily I'd been taking measurements at random points through the day, so I can do some pretty resonable comparisons.

Before:

Adhoc 5022 1154.125 4 721.398437 3984

After:

Adhoc 5153 425.205192 2 183.306755 4580

And for the visually oriented: