Styjun

We're using .NET Entity Framework to talk to an Azure SQL database. We used QueryOriginInterceptor to add some comments to the top of each SQL command being sent to SQL Server, with the goal of helping identify the location where a particular query came from in the code.

The problem is, when looking at long running queries in the Azure UI (and looking in sys.dm_exec_query_stats), the comments are not there.

For example, if we run this query:

-- Stack:
-- Utils.Orders.GetOrders
select * 
from [Order] o 
join OrderItem oi on oi.OrderId = o.ID

And looking in Azure, the long running query looks like:

Is there a way to preserve these comments?

sys.dm_exec_query_stats does not include the comments, but dm_exec_sql_text does.

This artice explains how to use the two to diagnose issues.

The relevant SQL query from the article is:

SELECT TOP 25
databases.name,
dm_exec_sql_text.text AS TSQL_Text,
CAST(CAST(dm_exec_query_stats.total_worker_time AS DECIMAL)/CAST(dm_exec_query_stats.execution_count AS DECIMAL) AS INT) as cpu_per_execution,
CAST(CAST(dm_exec_query_stats.total_logical_reads AS DECIMAL)/CAST(dm_exec_query_stats.execution_count AS DECIMAL) AS INT) as logical_reads_per_execution,
CAST(CAST(dm_exec_query_stats.total_elapsed_time AS DECIMAL)/CAST(dm_exec_query_stats.execution_count AS DECIMAL) AS INT) as elapsed_time_per_execution,
dm_exec_query_stats.creation_time, 
dm_exec_query_stats.execution_count,
dm_exec_query_stats.total_worker_time AS total_cpu_time,
dm_exec_query_stats.max_worker_time AS max_cpu_time, 
dm_exec_query_stats.total_elapsed_time, 
dm_exec_query_stats.max_elapsed_time, 
dm_exec_query_stats.total_logical_reads, 
dm_exec_query_stats.max_logical_reads,
dm_exec_query_stats.total_physical_reads, 
dm_exec_query_stats.max_physical_reads,
dm_exec_query_plan.query_plan,
dm_exec_cached_plans.cacheobjtype,
dm_exec_cached_plans.objtype,
dm_exec_cached_plans.size_in_bytes
FROM sys.dm_exec_query_stats 
CROSS APPLY sys.dm_exec_sql_text(dm_exec_query_stats.plan_handle)
CROSS APPLY sys.dm_exec_query_plan(dm_exec_query_stats.plan_handle)
INNER JOIN sys.databases
ON dm_exec_sql_text.dbid = databases.database_id
INNER JOIN sys.dm_exec_cached_plans 
ON dm_exec_cached_plans.plan_handle = dm_exec_query_stats.plan_handle
WHERE databases.name = 'AdventureWorks2014'
ORDER BY dm_exec_query_stats.max_logical_reads DESC;