RDS SQL Server - 专题分享 - 巧用执行计划缓存之数据类型隐式转换

xiaoxiao2023-06-07 184

摘要

SQL Server数据库基表数据类型隐式转换，会导致Index Scan或者Clustered Index Scan的问题，这篇文章分享如何巧用执行计划缓存来发现数据类型隐式转换的查询语句，从而可以有针对性的优化查询，解决高CPU使用率的问题。

问题引入

数据类型转化是导致SQL Server高CPU使用率的又一大杀手，详情参见之前的云栖社区文章：RDS SQL Server - 最佳实践 - 高CPU使用率系列之数据类型转换。SQL Server对基表数据类型转换会导致Index Scan或者Clustered Index Scan，进而导致IO使用率的大幅上升，最终导致CPU的使用率大幅升高。这篇文章是从执行计划缓存缓存的角度来找出导致数据类型转换的查询语句，进而做有针对性的查询语句优化，来破解高CPU使用率的问题。

测试环境

为了更好的展示从执行计划缓存缓存中找出导致数据类型转化的查询语句，我们先建立测试环境。

-- Create testing database IF DB_ID('TestDb') IS NULL CREATE DATABASE TestDb; GO USE TestDb GO -- create demo table SalesOrder IF OBJECT_ID('dbo.SalesOrder', 'U') IS NOT NULL BEGIN TRUNCATE TABLE dbo.SalesOrder DROP TABLE dbo.SalesOrder END GO CREATE TABLE dbo.SalesOrder ( RowID INT IDENTITY(1,1) NOT NULL , OrderID UNIQUEIDENTIFIER NOT NULL , ItemID INT NOT NULL , UserID INT NOT NULL , OrderQty INT NOT NULL , Price DECIMAL(8,2) NOT NULL , OrderDate DATETIME NOT NULL CONSTRAINT DF_OrderDate DEFAULT(GETDATE()) , LastUpdateTime DATETIME NULL , OrderComment NVARCHAR(100) NULL , CONSTRAINT PK_SalesOrder PRIMARY KEY( OrderID ) ); -- data init for 2 M records. ;WITH a AS ( SELECT * FROM (VALUES(1),(2),(3),(4),(5),(6),(7),(8),(9),(10)) AS a(a) ), RoundData AS( SELECT TOP(2000000) OrderID = NEWID() ,ItemIDRound = abs(checksum(newid())) ,Price = a.a * b.a * 10 ,OrderQty = a.a + b.a + c.a + d.a + e.a + f.a + g.a + h.a FROM a, a AS b, a AS c, a AS d, a AS e, a AS f, a AS g, a AS h ), DATA AS( SELECT OrderID ,ItemID = cast(ROUND((1300 * (ItemIDRound*1./cast(replace(ItemIDRound, ItemIDRound, '1' + replicate('0', len(ItemIDRound))) as bigint)) + 101), 0) as int) ,UserID = cast(ROUND((500 * (ItemIDRound*1./cast(replace(ItemIDRound, ItemIDRound, '1' + replicate('0', len(ItemIDRound))) as bigint)) + 10000), 0) as int) ,OrderQty ,Price = cast(Price AS DECIMAL(8,2)) ,OrderDate = dateadd(day, -cast(ROUND((50 * (ItemIDRound*1./cast(replace(ItemIDRound, ItemIDRound, '1' + replicate('0', len(ItemIDRound))) as bigint)) + 1), 0) as int) ,GETDATE()) FROM RoundData ) INSERT INTO dbo.SalesOrder(OrderID, ItemID, UserID, OrderQty, Price, OrderDate, LastUpdateTime, OrderComment) SELECT OrderID , ItemID , UserID , OrderQty , Price , OrderDate , LastUpdateTime = OrderDate , OrderComment = N'User ' + CAST(UserID AS NVARCHAR(8)) + N' purchased item ' + CAST(ItemID AS NVARCHAR(8)) FROM DATA; GO --===============query USE [TestDb] GO ALTER TABLE dbo.SalesOrder ALTER COLUMN UserID CHAR(8) NULL GO EXEC sys.sp_help 'dbo.SalesOrder' USE [TestDb] GO SELECT ItemID, OrderQty, Price FROM dbo.SalesOrder WHERE UserID = 10057 SELECT TOP 100 ItemID, OrderQty, Price FROM dbo.SalesOrder WHERE UserID = 10058 AND OrderDate >= DATEADD(MONTH, -1, GETDATE()) AND OrderDate <= GETDATE(); USE [TestDb] GO SELECT ItemID, OrderQty, Price FROM dbo.SalesOrder WHERE UserID = 10059 SELECT TOP 100 ItemID, OrderQty, Price FROM dbo.SalesOrder WHERE UserID = 10061 AND OrderDate >= DATEADD(MONTH, -1, GETDATE()) AND OrderDate <= GETDATE();

查看SalesOrder表结构，我们很清楚的看到UserID数据类型是CHAR(8)，而查询WHERE语句中的WHERE UserID = XXXX中，等号右边的数据类型为INT，这会导致SQL Server将数据类型优先级低的数据类型CHAR转化为INT，SQL Server需要将这个表中的200万条记录的UserID从CHAR(8)转化为INT。所以，必须进行Scan操作，从而导致高CPU使用率。

从执行计划来看，也的确导致了数据类型的隐式转换：

解决方法

从测试环境部分，我们发现的确导致了数据类型的隐式转换。以下短短100行代码，可以从执行计划缓存中找出导致数据类型隐式转化的查询语句和执行计划的详细信息。

USE testdb GO DECLARE @db_name SYSNAME ; SET @db_name = QUOTENAME(DB_NAME()) ; ;WITH XMLNAMESPACES (DEFAULT 'http://schemas.microsoft.com/sqlserver/2004/07/showplan') , planCache AS( SELECT stmt.value('(@StatementText)[1]', 'varchar(max)') AS stmt, n.t.value('(ScalarOperator/Identifier/ColumnReference/@Database)[1]', 'sysname') AS DatabaseName, REPLACE(REPLACE(n.t.value('(ScalarOperator/Identifier/ColumnReference/@Schema)[1]', 'sysname'), '[', ''), ']', '') AS SchemaName, REPLACE(REPLACE(n.t.value('(ScalarOperator/Identifier/ColumnReference/@Table)[1]', 'sysname'), '[', ''), ']', '') AS ObjectName, REPLACE(REPLACE(n.t.value('(ScalarOperator/Identifier/ColumnReference/@Column)[1]', 'sysname'), '[', ''), ']', '') AS ColumnName, n.t.value('(@DataType)[1]', 'sysname') AS ConvertTo, n.t.value('(@Length)[1]', 'int') AS ConvertToLength, PhysicalOperator.value('(.//Predicate/ScalarOperator/@ScalarString)[1]','nvarchar(max)') AS ScalarString, query_plan, cp.plan_handle 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('//ScalarOperator/Compare/ScalarOperator/Convert[@Implicit="1"]') AS n(t) CROSS APPLY batch.stmt.nodes('.//RelOp[@PhysicalOp=''Index Scan'' or @PhysicalOp=''Clustered Index Scan'']') as RelOp(PhysicalOperator) WHERE n.t.exist('ScalarOperator/Identifier/ColumnReference[@Database=sql:variable("@db_name")][@Schema != ''[sys]'']') = 1 ) SELECT pc.stmt, pc.DatabaseName, pc.SchemaName, pc.ObjectName, pc.ColumnName, ps.UsedPageCount, ix.name AS IndexName, CAST(ps.UsedPageCount/ 128. AS decimal(12,2)) AS UsedSizeMB, ps.TotalRowCount, qs.execution_count * UsedPageCount AS MostLogicalRead, cols.DATA_TYPE AS ConvertFrom, cols.CHARACTER_MAXIMUM_LENGTH AS ConvertFromLength, pc.ConvertTo, pc.ConvertToLength, pc.ScalarString, pc.query_plan, qs.creation_time ,qs.last_execution_time ,qs.execution_count ,qs.total_worker_time ,qs.last_worker_time ,qs.min_worker_time ,qs.max_worker_time ,qs.total_physical_reads ,qs.last_physical_reads ,qs.min_physical_reads ,qs.max_physical_reads ,qs.total_logical_writes ,qs.last_logical_writes ,qs.min_logical_writes ,qs.max_logical_writes ,qs.total_logical_reads ,qs.last_logical_reads ,qs.min_logical_reads ,qs.max_logical_reads ,qs.total_clr_time ,qs.last_clr_time ,qs.min_clr_time ,qs.max_clr_time ,qs.total_elapsed_time ,qs.last_elapsed_time ,qs.min_elapsed_time ,qs.max_elapsed_time ,qs.total_rows ,qs.last_rows ,qs.min_rows ,qs.max_rows FROM planCache AS pc INNER JOIN INFORMATION_SCHEMA.COLUMNS as cols WITH(NOLOCK) ON pc.SchemaName = cols.TABLE_SCHEMA AND pc.ObjectName = cols.TABLE_NAME AND pc.ColumnName = cols.COLUMN_NAME INNER JOIN sys.tables as tb WITH(NOLOCK) ON tb.schema_id = schema_id(pc.SchemaName) AND tb.name = pc.ObjectName INNER JOIN sys.indexes as ix WITH(NOLOCK) ON tb.object_id = ix.object_id LEFT JOIN ( SELECT object_id, index_id, sum(used_page_count) AS UsedPageCount, sum(row_count) AS TotalRowCount FROM sys.dm_db_partition_stats as dps WITH(NOLOCK) GROUP BY object_id,Index_id ) as ps ON ix.object_id = ps.object_id and ix.index_id = ps.index_id left join sys.dm_exec_query_stats qs on pc.plan_handle= qs.plan_handle --DBCC freeproccache

查询结果的一步截图如下：

总结

这篇文章分享了如何从执行计划缓存中找到导致SQL Server数据类型隐式转化的查询语句，为我们针对特定查询语句的优化提供了基础，最终破解高CPU使用率的问题。