Kaz on Commerce!

If, like me, you are having reliability issue with some of your Entity Framework queries performing consistently this post is going to help you immensely.

To start with, make sure you understand the LINQ queries and how they translate to SQL and make sure you’ve done what you can to get the end SQL query to be as reliably performant as possible. You will most likely work with a SQL DBA as it’s a mix of art and science.

This post is going to talk you through how to get queries to perform more reliably specifically in SQL Server 2008.

Start by identifying the queries by running the SQL Profiler and capturing the necessary trace. For us we were having infrequent issues where queries were timing out from our application so I could look through the trace for a value of ‘2’ in the ‘Error’ column.

Once you identify the problematic query, look to remove their cached query plans from SQL Server. The below query can be used to view the cached plans based on part of the identified query e.g. by extracting a table name of part of the query.

SELECT plan_handle, st.text, *
FROM sys.dm_exec_cached_plans
CROSS APPLY sys.dm_exec_sql_text(plan_handle) AS st
where text like '%TableName%'

 

Identify the plans that exist for the query in question and proceed to removing them. This can be done by copying and pasting the value from the plan_handle column (query above) into the below query:

declare @plan_handle varbinary(1000) = ????
dbcc FREEPROCCACHE(@plan_handle)

 

You should be able to run the original query (the one from the trace) and get better query performance. You might need to try this a few times or when not many people or on the system so when you run your query its the one that triggers the SQL plan cache to be created.

Once you have a reliably running query you will be able to extract the generated query plan and set about creating a SQL Server ‘Plan Guide’. Here is a good link to the MSDN article: link. Or follow the steps below:

 

Obtain the plan handle (same as you did above) and feed it into the query below:

declare @plan_handle varbinary(1000) = ????
exec sp_create_plan_guide_from_handle 'MyPlan_Guide_1', @plan_handle=@plan_handle

 

To test this simply run the query again with statistics XML on and analyse the generated show plan XML for an attribute names ‘QueryPlanName’ and it should correlate to your plan name above.

set statistics xml on
exec sp_executesql N'SELECT COUNT(*) FROM [TableName]'
set statistics xml off

 

Here are some other helpful queries:

-- Show Plan Guides
select * from sys.plan_guides

-- Drop Plan
EXEC sp_control_plan_guide N'DROP',N'MyPlan_Guide_1';