I recently realized we had a gap in our database deployment processes. The .NET developers were generating queries using LINQ and having our operations folks deploy this new code. As a result the DBA’s were being bypassed. I had mentioned to our developers that I would like to see execution plans of all LINQ queries. However, the only way I knew how to capture this information was through a SQL profiler trace. Setting up a trace for each LINQ query is somewhat time-consuming. There is an easier way which involves searching the SQL Server plan cache. Below I have outlined a quick way to search the plan cache for LINQ queries that were just executed. Please do not run this in production; this is a task which should be performed in the development phases of the project.

1. Execute .NET LINQ query

2. Configure the @searchstring parameter to search for a unique characteristic of the LINQ query. For example the name of a table you are executing a LINQ query against.

3. Review the SQL text and execution plan which is returned in the column titled “query_plan” also review the column titled “query_text”

/** Configure search string parameter **/

declare @searchstring varchar(255);
set @searchstring = '%TableOrDatabaseName%';

WITH Plan_Cache_CTE (capture_date,
query_text,
execution_count,
total_logical_reads,
last_logical_reads,
total_logical_writes,
last_logical_writes,
total_physical_reads,
last_physical_reads,
total_worker_time,
last_worker_time,
total_elapsed_time_in_S,
last_elapsed_time_in_S,
last_execution_time,
query_plan,
plan_handle)
AS
(
SELECT
GETDATE() as capture_date,
SUBSTRING(qt.TEXT, (qs.statement_start_offset/2)+1 ,
((CASE qs.statement_end_offset
WHEN -1 THEN DATALENGTH(qt.TEXT)
ELSE qs.statement_end_offset
END - qs.statement_start_offset)/2)+1) as query_text,
qs.execution_count,
qs.total_logical_reads, qs.last_logical_reads,
qs.total_logical_writes, qs.last_logical_writes,
qs.total_physical_reads, qs.last_physical_reads,
qs.total_worker_time,
qs.last_worker_time,
qs.total_elapsed_time/1000000 total_elapsed_time_in_S,
qs.last_elapsed_time/1000000 last_elapsed_time_in_S,
qs.last_execution_time,
qp.query_plan,
qs.plan_handle
FROM sys.dm_exec_query_stats qs with (nolock)
CROSS APPLY sys.dm_exec_sql_text (qs.sql_handle) qt
CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) qp
)
select *
from Plan_Cache_CTE
where query_text like @searchstring and query_text not like '%Plan_Cache_CTE%'
order by last_execution_time desc;