How can you find implicit_conversions without having to run every statement on your running system?
First, to make it clear, not all implicit conversions hurt performance. But, when does it actually hurt performance? Well, as always – it depends. If you want to find the queries within conversions, and assess them to see if the conversion hurts performance, you have a couple of choices. You can query the plan cache, looking for xml plans where Implicit conversion appears. This will find all the places where the plans are in cache, but the key here is the plan has to be in the plan cache. Your most curial plans are often in the plan cache, but you could make a job and run the query every day and store the result in a table.
SQL Query:
A query could look like this
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
DECLARE @dbname SYSNAME
SET @dbname = QUOTENAME(DB_NAME());
WITH XMLNAMESPACES
(DEFAULT 'http://schemas.microsoft.com/sqlserver/2004/07/showplan')
SELECT
stmt.value('(@StatementText)[1]', 'varchar(max)'),
t.value('(ScalarOperator/Identifier/ColumnReference/@Schema)[1]', 'varchar(128)'),
t.value('(ScalarOperator/Identifier/ColumnReference/@Table)[1]', 'varchar(128)'),
t.value('(ScalarOperator/Identifier/ColumnReference/@Column)[1]', 'varchar(128)'),
ic.DATA_TYPE AS ConvertFrom,
ic.CHARACTER_MAXIMUM_LENGTH AS ConvertFromLength,
t.value('(@DataType)[1]', 'varchar(128)') AS ConvertTo,
t.value('(@Length)[1]', 'int') AS ConvertToLength,
query_plan
FROM sys.dm_exec_cached_plans AS cp
CROSS APPLY sys.dm_exec_query_plan(plan_handle) AS qp
CROSS APPLY query_plan.nodes('/ShowPlanXML/BatchSequence/Batch/Statements/StmtSimple') AS batch(stmt)
CROSS APPLY stmt.nodes('.//Convert[@Implicit="1"]') AS n(t)
JOIN INFORMATION_SCHEMA.COLUMNS AS ic
ON QUOTENAME(ic.TABLE_SCHEMA) = t.value('(ScalarOperator/Identifier/ColumnReference/@Schema)[1]', 'varchar(128)')
AND QUOTENAME(ic.TABLE_NAME) = t.value('(ScalarOperator/Identifier/ColumnReference/@Table)[1]', 'varchar(128)')
AND ic.COLUMN_NAME = t.value('(ScalarOperator/Identifier/ColumnReference/@Column)[1]', 'varchar(128)')
WHERE t.exist('ScalarOperator/Identifier/ColumnReference[@Database=sql:variable("@dbname")][@Schema!="[sys]"]') = 1(Query credits to Jonathan Kehayias)
This will find plans containing conversions, now it's up to you to test if the conversion is hurting the performance. Another option is using Extended events. If you create an Extended Event session, look for plan_affecting_convert.
Extended events:
Go into SQL Server Management Studio, and in the Object Explorer, expand the Database you want to monitor and there you will find Extended events.
On a Azure Database, you will find extended events onprem server under Management -> Extended Events.
But, back to my Azure Database.
Expand Extended events and right click on Sessions -> New Session.
Give it a name and go to Events.
Add SQL_Batch Starting, and SQL Batch Complete.
And finally, add plan_affecting_convert
Now, press configure and you can choose what information you would like to get.
Add how you would like to store your data.
I’m using Azure Blob storage. (if you don’t have a blob storage setup, you can follow the guide from the link in the guide)
Click create, and right click and choose start session.
If you’re using Azure Database, you can add Azure Storage Connection:
Want to read more about implicit conversion the read more here implicit conversion why it hurts performance
Thanks for reading. If you have any questions, troubles with your SQL server, queries, or environment, please don’t hesitate to call me or one of my colleagues at Unit IT phone: 88 333 333.
