Home > Articoli > Conversioni implicite: La plan cache ci svela quante sono e dove avvengono!

Conversioni implicite: La plan cache ci svela quante sono e dove avvengono!

Nell’articolo Conversioni implicite: cosa sono e che impatto hanno sulle nostre query, Luca Bruni (@mrg3d) ci ha parlato delle conversioni implicite che avvengono, a nostra insaputa, all’interno dell’Engine di SQL Server. Tali conversioni, possono costringere il Query Optimizer ad accedere alle tabelle con operazioni di scansione (Table Scan, Index Scan) in alternativa alle più efficienti operazioni di Seek (Index Seek), e quando una parte significativa del carico di lavoro è interessata da conversioni implicite, le performance degradano visibilmente!

Una conversione implicita tra due tipi di dato differenti avviene sempre dal tipo di dato meno preciso al tipo di dato più preciso. Per ovvie ragioni, SQL Server non può perdere precisione nei dati a causa di una conversione implicita, parlando quindi di tipi di dato testo, una conversione implicita potrà avvenire, ad esempio, dal tipo VARCHAR (meno preciso) al tipo NVARCHAR (più preciso), mai nella direzione opposta.

Probabilmente vi starete chiedendo se il vostro carico di lavoro è affetto da conversioni implicite e in che percentuale.

Le conversioni implicite, all’interno del piano di esecuzione, vengono segnalate con un warning. Il Query Optimizer può segnalare i seguenti tipi di warning:

  • No statistics (ColumnsWithNoStatistics)
  • No join predicate (NoJoinPredicate)
  • Implicit conversions (PlanAffectingConvert)
  • Sort on tempdb (SpillToTempDb)
  • No matched indexes (UnmatchedIndexes)
  • Wait (WaitWarningType)

..tra cui troviamo l’Implicit Conversions Warning (PlanAffectingConvert) di cui parleremo in questo articolo.

Il warning PlanAffectingConvert viene segnalato proprio in presenza di conversioni implicite, quando viene utilizzata la funzione CONVERT_IMPLICIT. È quindi possibile fare indagini sulla presenza di conversioni implicite nel carico di lavoro analizzando la plan cache, ovvero l’area di memoria utilizzata da SQL Server per memorizzare i piani di esecuzione delle query.

Utilizzando la metodologia “top-down” per analizzare il problema, il primo dato che vorremmo avere è la percentuale dei piani di esecuzione in cache affetti da conversioni implicite. Per ottenerlo dovremo contare i piani di esecuzione in cache, e di questi, quelli affetti da conversioni implicite… la seguente CTE estrae le informazioni che cerchiamo: il numero dei piani di esecuzione in cache, il numero dei piani di esecuzione in cui viene utilizzata la funzione CONVERT_IMPLICIT ed il calcolo della percentuale.

WITH XMLNAMESPACES
(
 DEFAULT 'http://schemas.microsoft.com/sqlserver/2004/07/showplan'
),
Cached_Plans AS
(
 SELECT
 COUNT(cp.plan_handle) AS Total_Plan_Cached
 FROM
 sys.dm_exec_cached_plans AS cp
),
Cached_Plans_with_Convert AS
(
 SELECT
 COUNT(cp.plan_handle) AS Total_Plan_Cached_with_Convert
 FROM
 sys.dm_exec_cached_plans AS cp
 CROSS APPLY
 sys.dm_exec_query_plan(cp.plan_handle) AS qp
 CROSS APPLY
 sys.dm_exec_sql_text(cp.plan_handle) AS qt
 WHERE
 (qp.query_plan.exist('//Warnings') = 1)
 AND CAST(qp.query_plan.query('//Warnings') AS VARCHAR(MAX)) LIKE '%CONVERT_IMPLICIT%'
)
SELECT
 Total_Plan_Cached
 ,Total_Plan_Cached_with_Convert = (SELECT Total_Plan_Cached_with_Convert FROM Cached_Plans_with_Convert)
 ,CAST(CAST((SELECT Total_Plan_Cached_with_Convert FROM Cached_Plans_with_Convert) AS DECIMAL(10,3)) / 
 CAST(Total_Plan_Cached AS DECIMAL(10,3)) AS DECIMAL(6,3)) * 100 [% Plan_Cached_with_Convert]
FROM
 Cached_Plans;

L’output di esempio è illustrato nelle figura seguente, dove circa il 4% del carico di lavoro è affetto da conversioni implicite.

Qualora venisse rilevato un numero considerevole di piani di esecuzione affetti da conversioni implicite, la seguente CTE ci potrà aiutare nell’identificazione delle query specifiche in cui avvengono le conversioni implicite, tra le colonne estratte vi è il testo della query, il piano di esecuzione completo e la porzione relativa ai warning di tipo PlanAffectingConvert in cui è stata utilizzata la funzione CONVERT_IMPLICIT.

WITH XMLNAMESPACES
(
 DEFAULT 'http://schemas.microsoft.com/sqlserver/2004/07/showplan'
),
PlanAffectingConvert AS
(
 SELECT
 query_text = qt.text
 ,cp.objtype
 ,cp.refcounts
 ,cp.usecounts
 ,Plan_Warnings = qp.query_plan.query('//Warnings')
 ,qp.query_plan
 FROM
 sys.dm_exec_cached_plans AS cp
 CROSS APPLY
 sys.dm_exec_query_plan(cp.plan_handle) AS qp
 CROSS APPLY
 sys.dm_exec_sql_text(cp.plan_handle) AS qt
 WHERE
 (qp.query_plan.exist('//Warnings') = 1)
)
SELECT
 *
FROM
 PlanAffectingConvert
WHERE
 CAST(Plan_Warnings AS VARCHAR(MAX)) LIKE '%CONVERT_IMPLICIT%';

Concludendo, se avete il dubbio che il vostro carico di lavoro sia affetto da conversioni implicite, un buon punto di partenza è l’utilizzo delle query descritte in questo articolo… se pensate di non avere conversioni implicite nelle vostre query, fate lo stesso un controllo… non costa nulla!

Buon divertimento!

Chi è Sergio Govoni

Sergio Govoni è laureato in Scienze e Tecnologie Informatiche. Da oltre 16 anni lavora presso una software house che produce un noto sistema ERP, distribuito a livello nazionale ed internazionale, multi azienda client/server su piattaforma Win32. Attualmente si occupa di progettazione e analisi funzionale, coordina un team di sviluppo ed è responsabile tecnico di prodotto.

Lavora con SQL Server dalla versione 7.0 e si è occupato d’implementazione e manutenzione di database relazionali in ambito gestionale, ottimizzazione delle prestazioni e problem solving.

Nello staff di UGISS si dedica alla formazione e alla divulgazione in ambito SQL Server e tecnologie a esso collegate, scrivendo articoli e partecipando come speaker ai workshop e alle iniziative del primo e più importante User Group Italiano sulla tecnologia SQL Server.

Ha conseguito la certificazione MCP, MCTS SQL Server. Per il suo contributo nelle comunità tecniche e per la condivisione della propria esperienza con altri, dal 2010 riceve il riconoscimento SQL Server MVP (Microsoft Most Valuable Professional).

Nel corso dell’anno 2011 ha contribuito alla scrittura del libro SQL Server MVP Deep Dives Volume 2 (http://www.manning.com/delaney/).

Leggi Anche

PASSGIVC – Introduzione a SQL Server su Linux

Il prossimo webinar del PASS Italian Virtual Chapter è programmato per mercoledì 20 dicembre 2017 …