CONVERT_IMPLICIT – jak wyszukać?

Opublikowane przez Dariusz Brejnak w dniu

W poprzednim wpisie opisałem zjawisko CONVERT_IMPLICT. Teraz chcę przedstawić jeden ze skryptów, przy pomocy, którego można wyszukać miejsca występowania tego niepożądanego zjawiska.

Generalnie informację o występowaniu niejawnej konwersji typów danych możemy znaleźć w planie wykonania zapytań i skrypt właśnie przeszukuje te informacje

DECLARE @dbname SYSNAME = QUOTENAME(DB_NAME());
WITH XMLNAMESPACES
   (DEFAULT 'http://schemas.microsoft.com/sqlserver/2004/07/showplan')
SELECT
   stmt.value('(@StatementText)[1]', 'varchar(max)') AS SQL_Batch,
   + 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)') AS The_ColumnReference,
   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,
   cp.usecounts,
   pc.object_id,
   s.name,
   o.name,
   pc.type_desc,
   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 sys.dm_exec_procedure_stats AS pc on cp.plan_handle = pc.plan_handle
JOIN sys.objects o ON o.object_id = pc.object_id
JOIN sys.schemas s on o.schema_id = s.schema_id
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
ORDER BY cp.usecounts DESC

Poniżej jeszcze lekko zmieniony skrypt, który umożliwia „Normalne” wklejenie do Excela.


DECLARE @dbname SYSNAME = QUOTENAME(DB_NAME());
WITH XMLNAMESPACES
   (DEFAULT 'http://schemas.microsoft.com/sqlserver/2004/07/showplan')
SELECT
    Replace(
		 Replace(
			replace(Left(stmt.value('(@StatementText)[1]', 'varchar(max)'),500),char(9),' ')
		,char(10),'')
  ,char(13),'') 
   
   
   AS SQL_Batch,
   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)') AS The_ColumnReference,
   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,
   cp.usecounts,
   pc.object_id,
   s.name,
   o.name,
   pc.type_desc
  -- , 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 sys.dm_exec_procedure_stats AS pc on cp.plan_handle = pc.plan_handle
JOIN sys.objects o ON o.object_id = pc.object_id
JOIN sys.schemas s on o.schema_id = s.schema_id
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
ORDER BY cp.usecounts DESC
WITH XMLNAMESPACES
   (DEFAULT 'http://schemas.microsoft.com/sqlserver/2004/07/showplan')
SELECT
   Left(stmt.value('(@StatementText)[1]', 'varchar(max)'),500) AS SQL_Batch,
   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)') AS The_ColumnReference,
   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,
   cp.usecounts,
   pc.object_id,
   s.name,
   o.name,
   pc.type_desc
  -- , 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 sys.dm_exec_procedure_stats AS pc on cp.plan_handle = pc.plan_handle
JOIN sys.objects o ON o.object_id = pc.object_id
JOIN sys.schemas s on o.schema_id = s.schema_id
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
ORDER BY cp.usecounts DESC
Kategorie: DBADeveloping T-SQL
Tagi:

Dariusz Brejnak

Od prawie trzydziestu lat jest pasjonatem informatyki, a zwłaszcza dziedzin dotyczących baz danych, hurtowni danych oraz ogólnie rozumianej tematyki BI. Jego druga pasja to fotografia http://dariuszbrejnak.pl

Podobne wpisy

Developing T-SQL

Nietypowe formatowanie DateTime

Niedawno zderzyłem się z przypadkiem, gdzie musiałem tak sformatować datę by obcinać czas do pełnych godzin czyli 19:23 na 19:00 i 19:59 też na 19:00. Pierwsze podejście to zabawa z substring, ale stwierdziłem, że można Dowiedz się więcej…

DBA Developing T-SQL

CONVERT_IMPLICIT wyjaśnienie

O CONVERT_IMPLICIT mówimy wtedy, gdy SQL Server napotka w predykatach (klauzula WHERE lub klauzula JOIN) porównanie dwóch różnych typów danych. Wtedy jedna strona wartości zostanie przekonwertowana na typ danych drugiej strony. Ten proces bardzo obciąża Dowiedz się więcej…

DBA MS SQL Server

Nie można usunąć Maintenance Plan – błąd Indeks poza zakresem

Zaobserwowałem problem z planem konserwacji (Maintenance Plan), którego nie mogę usunąć. Podczas próby skasowania pojawia się następujący błąd: Index was out of range. Must be non-negative and less than the size of the collection.Parameter name: Dowiedz się więcej…