This post has been republished via RSS; it originally appeared at: New blog articles in Microsoft Community Hub.
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 NOT (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
Lesson Learned #45: CPU at 100% using nvarchar parameter data type in the filter against varchar column data type. - Microsoft Community Hub
Lesson Learned #354: Why is Python using a lot of CPU of Azure SQ Database? - Microsoft Community Hub
