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;