Lesson Learned #364: Getting all conversion implicit warnings using Query Data Store Repository

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

Continue reading...