Gå til indhold

Find Implicit conversions

How can you find implicit_conversions without having to run every statement on your running system?
Billede af Unit IT

Unit IT

SQL

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.

capture_implicit_conversions_01a

 

But, back to my Azure Database.

capture_implicit_conversions_01b

Expand Extended events and right click on Sessions -> New Session.

capture_implicit_conversions_02

 

Give it a name and go to Events.

capture_implicit_conversions_03

Add SQL_Batch Starting, and SQL Batch Complete.

capture_implicit_conversions_04

And finally, add plan_affecting_convert

Now, press configure and you can choose what information you would like to get.

capture_implicit_conversions_05

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)

capture_implicit_conversions_07

Click create, and right click and choose start session.

If you’re using Azure Database, you can add Azure Storage Connection:

capture_implicit_conversions_09

 

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.

Relaterede indlæg