During our last session in SQL Data Saturday, we received a question about if it is possible to know all the conversion implicit captured by Query Data Store. In the following example, I would like to share with you an example how to capture this considering among of SQL Antipatterns.
Basically, in sys.query_store_plan we found the column called query_plan that contains the text of the execution plan. With this information plus other Query Data Store DMVs we could see the information required.
Opening a XML Plan we could see a section called Warnings with all possible issues that we might have including the PlanAffectingConvert issue.
Right now, we have everything and we could include other columns and filters if needed.
WITH XMLNAMESPACES
(DEFAULT 'http://schemas.microsoft.com/sqlserver/2004/07/showplan')
SELECT QueryID,
query_plan,
plan_id,
qrs.count_executions,
qrs.avg_duration,
qrs.min_duration,
qrs.max_duration,
qrs.first_execution_time,
qrs.last_execution_time,
stmtTQL.value('(@StatementText)[1]', 'varchar(max)') AS SQL_Text,
obj.value('(@ConvertIssue)[1]', 'varchar(max)') AS ConvertIssue,
obj.value('(@Expression)[1]', 'varchar(max)') AS Expression
FROM
(
SELECT query_plan,QueryID,PlanID
FROM
(
SELECT TRY_CONVERT(XML, [qsp].[query_plan]) AS [query_plan], [qsp].plan_id as PlanID, [qsp].query_id as QueryID
FROM sys.query_store_plan [qsp]) tp
) AS tab(query_plan, queryID,PlanID)
JOIN sys.query_store_runtime_stats qrs on tab.planid = qrs.plan_id
JOIN sys.query_store_runtime_stats_interval itvl ON itvl.runtime_stats_interval_id = qrs.runtime_stats_interval_id
CROSS APPLY query_plan.nodes('/ShowPlanXML/BatchSequence/Batch/Statements/StmtSimple/QueryPlan/Warnings') AS batch(stmt)
CROSS APPLY query_plan.nodes('/ShowPlanXML/BatchSequence/Batch/Statements/StmtSimple') AS TSQL(stmtTQL)
CROSS APPLY stmt.nodes('.//PlanAffectingConvert') AS idx(obj)
where obj.value('(@ConvertIssue)[1]', 'varchar(max)')='Seek Plan'
and count_executions>1
and (itvl.start_time > '2023-06-05 00:00:00' OR itvl.end_time < '2023-06-01 00:00:00')
OPTION(MAXDOP 1, RECOMPILE);
Additional references
Microsoft