Linq To SQL Query plans not being reused
When you use Linq to SQL to apply filter, orderby or any other operator, Linq provider translates the query to SQL and sends it to the database. When the query is send to the database, SQL server determines if it can use an existing query plan for this query or create a new query plan. One of interesting point I discovered is, Linq to SQL query plans do not get reused. For instance if I write a Linq query to find customers with Contact Title of Sales Agent, SQL server would generate an query plan which you can see in syscacheobjects table. When I run the query again with a different Contact Title say, Accounting Manager, SQL server, instead of reusing the same query plan generates a new query plan. The problem with this is, dynamic queries have limited caching buffer and once it reaches it max size allocated for the dynamic query buffer, it would start to flush out the old query plans. Since this process would be happening so fast, that it would hard to reuse the same query plan again resulting in a new query plan being created every time. Secondly if you look at syscacheobjects table, there is another column called usecounts which tells how many times this query plan has been used. Most of the time with Linq to SQL query the usecounts never goes past one. Below is an example that shows different queries being send to the database using Linq to SQL.
In the above screen shot, I have the same query being executed that searches for contacts based on Contact Title. Only thing different between all 3 queries is the value for contact title we are searching on. On executing the following queries, SQL server generates 3 different unique plans.
The above example shows 3 different query plan generated by SQL server for different Linq query and all of the them has a usecounts set to 1. It would be nice that consecutive query send to the database that searches on same column, be created in a such way that SQL server can leverage the same query plan instead of having to create a new one. The reason why SQL server generates a new different query plan is because the parameter type it uses to filter the contact title is declared sometimes to be size 20, 18 and 15. Linq to query engine determines the size of the variable p0 to be based on the value you are searching on. Since Linq to SQL mapping file contains the size of column we are searching on, it should always use that size. This way it would generate a consistent query, resulting in the same query plan being executed. Below shows the SQL generated by Linq to SQL provider for the queries shown above.
As you can see from the above query generated by Linq to SQL, in all 3 cases the size of p0 parameter is set to the size of the value for contact title you are searching on. If the size of p0 was set to the size of ContactTitle column, than SQL server would not generate a new query plan and reuse the same query plan.