SQL Server Query Execution Plan Rebuild

Question

I have this query that gets executed though Linq to Entities. First time the query runs it generates the execution plan which takes just under 2 minutes. After the plan is cached the query takes 1 or 2 seconds. The problem I have is that the plan keeps getting rebuild every few hours and I am not sure why that would be?

This is the linq query we are using, I know it looks crazy but for what we need this was our only option.

var data = from row in mgr.ServiceDesk_RequestEvent
    .Include("ServiceDesk_Event")
    .Include("ServiceDesk_Event.ServiceDesk_SLAEventRule")
    .Include("ServiceDesk_Event.ServiceDesk_SLAEventRule.ServiceDesk_RuleSet")
    .Include("ServiceDesk_Event.ServiceDesk_SLAEventRule.ServiceDesk_RuleSet.ServiceDesk_Rule")
    .Include("ServiceDesk_Event.ServiceDesk_SLAEventRule.ServiceDesk_RuleSet.ServiceDesk_Rule.ServiceDesk_RuleOperator")
    .Include("ServiceDesk_Event.ServiceDesk_SLAEventRule.ServiceDesk_RuleSet.ServiceDesk_Rule.ServiceDesk_RuleConstraintField")
    .Include("ServiceDesk_Event.ServiceDesk_SLAEventRule.ServiceDesk_RuleSet.ServiceDesk_Rule.ServiceDesk_RuleConstraintValue")
    .Include("ServiceDesk_Event.ServiceDesk_SLAEventRule.ServiceDesk_RuleSet.ServiceDesk_Action")
    .Include("ServiceDesk_Request")
    .Include("ServiceDesk_Request.People_User")
    .Include("ServiceDesk_Request.ServiceDesk_RequestCategory")
    .Include("ServiceDesk_Request.ServiceDesk_RequestCategory.ServiceDesk_SLA")
    .Include("ServiceDesk_Request.ServiceDesk_RequestRole_Groups")
    .Include("ServiceDesk_Request.ServiceDesk_RequestRole_Groups.Security_Role.Security_UserRoles")
    .Include("ServiceDesk_Request.ServiceDesk_RequestRole_Groups.Security_Role.Security_UserRoles.Security_User")
    .Include("ServiceDesk_Request.ServiceDesk_RequestPriority")
    .Include("ServiceDesk_Request.Offices_User")
    .Include("ServiceDesk_Request.ServiceDesk_RequestTechnicians")
    .Include("ServiceDesk_Request.ServiceDesk_RequestTechnicians.People")
    where row.Completed == false && row.Deleted == false
    select row;

I don't want to paste the generated t-sql here since it quite large. If anyone has ideas please feel free to contribute.

Thank You.

Answer 1

A possible cause is that your server is under memory pressure, which causes the query plan cache to recycle more quickly.

How much RAM does the server have?

Re-reading your question, this line is a bit of a worry: "First time the query runs it generates the execution plan which takes just under 2 minutes". 2 minutes the first time for a simple query is a long time. Do you run any other applications on the SQL Server box (hopefully not)?

I suggest you monitor with the built-in performance counters: SQL Server, Plan Cache Counters.

Removing Execution Plans from the Procedure Cache

Execution plans remain in the procedure cache as long as there is enough memory to store them. When memory pressure exists, the Database Engine uses a cost-based approach to determine which execution plans to remove from the procedure cache. To make a cost-based decision, the Database Engine increases and decreases a current cost variable for each execution plan according to the following factors.

When a user process inserts an execution plan into the cache, the user process sets the current cost equal to the original query compile cost; for ad-hoc execution plans, the user process sets the current cost to zero. Thereafter, each time a user process references an execution plan, it resets the current cost to the original compile cost; for ad-hoc execution plans the user process increases the current cost. For all plans, the maximum value for the current cost is the original compile cost.

When memory pressure exists, the Database Engine responds by removing execution plans from the procedure cache. To determine which plans to remove, the Database Engine repeatedly examines the state of each execution plan and removes plans when their current cost is zero. An execution plan with zero current cost is not removed automatically when memory pressure exists; it is removed only when the Database Engine examines the plan and the current cost is zero. When examining an execution plan, the Database Engine pushes the current cost towards zero by decreasing the current cost if a query is not currently using the plan.

The Database Engine repeatedly examines the execution plans until enough have been removed to satisfy memory requirements. While memory pressure exists, an execution plan may have its cost increased and decreased more than once. When memory pressure no longer exists, the Database Engine stops decreasing the current cost of unused execution plans and all execution plans remain in the procedure cache, even if their cost is zero.

The Database Engine uses the resource monitor and user threads to free memory from the procedure cache in response to memory pressure. The resource monitor and user threads can examine plans run concurrently to decrease the current cost for each unused execution plan. The resource monitor removes execution plans from the procedure cache when global memory pressure exists. It frees memory to enforce policies for system memory, process memory, resource pool memory, and maximum size for all caches.

The maximum size for all caches is a function of the buffer pool size and cannot exceed the maximum server memory. For more information on configuring the maximum server memory, see the max server memory setting in sp_configure (Transact-SQL).

If you haven't already seen it: Plan Caching in SQL Server 2008

Answer 2

This MSDN article is a good reference on the execution plan cache. One way to optimise execution plan usage is to use parameterised SQL instead of hardcoded/dynamic SQL.

e.g.

SELECT * FROM MyTable WHERE ID=@Id

is better than

SELECT * FROM MyTable WHERE ID=1

as the same plan can be cached and reused, no matter what the value of @Id is. The more an execution plan is reused, the more likely it is to stay around in the cache as it's deemed useful.

I don't know the type of statement LINQ creates, but it's worth bearing in mind. Also as Mitch said, the more memory you have, the more you can store in the cache.

Also, note it's not just the execution plan caching that's at work here. You also have the data cache which has a dramatic difference on performance - once you've executed the query once, the data will be held in the data cache so for subsequent calls the data is already in memory - this is the source of most of the performance difference.