RDS SQL Server - 专题分享 - 巧用执行计划缓存之Table Scan

背景引入

执行计划中的Table Scan或者是Clustered Index Scan会导致非常低下的查询性能，尤其是对于大表或者超大表。执行计划缓存是SQL Server内存管理中非常重要的特性，这篇系列文章我们探讨如何从执行计划缓存的角度来发现RDS SQL数据库引擎中的Table Scan行为，以及与之相应SQL查询语句详细信息。

问题分析

其实，我们大家都知道，Table Scan或者Clustered Index Scan是关系型数据库查询性能很差的一种表扫描查询方式，如果在数据库引擎中存在大量的Table Scan行为的话，一般数据库性能都不会好到哪里去。在阿里云RDS SQL产品中，我们可以很简单的查看发生在RDS SQL引擎中的Table Scan频率，方法是：在阿里云控制台 云数据库RDS版 => 实例列表 => 打开对应的RDS SQL实例（在此以RDS SQL 2008R2版本为例）=> 监控与报警 => 引擎监控

往下拉动滚动条，会看到性能指标“平均每秒全表扫描次数”，表示发生在RDS SQL 2008R2数据库引擎中平均每秒表扫描的次数。

在很多次性能优化的案例中，我们告诉客人：“您的RDS 实例中，存在很多Table Scan行为，导致查询效率低下”，这时候客户会立马反问：那您知道我的哪些查询语句导致了Table Scan操作吗？今天这篇文章就是分享这个问题的解法：我们从执行计划缓存的角度来获取哪些查询语句导致了RDS SQL数据库引擎的Table Scan行为。

测试脚本

为了模拟出RDS SQL数据库引擎执行查询语句使用Table Scan的行为，我们创建一张没有任何Index，没有主键的表，接下来初始化10000条数据，然后循环执行查询语句和存储过程。测试的代码如下所示：

USE TestDb
GO

-- create demo table TestTableScan
IF OBJECT_ID('dbo.TestTableScan', 'U') IS NOT NULL
BEGIN
    DROP TABLE dbo.TestTableScan
END
GO

CREATE TABLE dbo.TestTableScan
(
    RowID INT IDENTITY(1,1) NOT NULL
    , OrderID UNIQUEIDENTIFIER NOT NULL
    , ItemID INT NOT NULL
);

-- data init for 10000 records.
;WITH a 
AS (
    SELECT * 
    FROM (VALUES(1),(2),(3),(4),(5),(6),(7),(8),(9),(10)) AS a(a)
), RoundData
AS(
SELECT TOP(10000)
    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
)
INSERT INTO dbo.TestTableScan(OrderID, ItemID)
SELECT 
    OrderID
    ,ItemID = cast(ROUND((1300 * (ItemIDRound*1./cast(replace(ItemIDRound, ItemIDRound, '1' + replicate('0', len(ItemIDRound))) as bigint)) + 101), 0) as int)
FROM RoundData

GO


--Test ADHOC
DECLARE
    @do INT = 0
    ,@loop INT = 5;
WHILE @do < @loop
BEGIN
    SELECT * FROM dbo.TestTableScan AS A
        INNER JOIN dbo.TestTableScan AS B
        ON A.ItemID = B.ItemID

    SET @do = @do + 1;
END
GO

--Call Store Procedure Testing
CREATE PROC dbo.UP_TableScanTesting
AS
BEGIN
    SET NOCOUNT ON
    SELECT * FROM dbo.TestTableScan AS A
        INNER JOIN dbo.TestTableScan AS B
        ON A.ItemID = B.ItemID

    SELECT TOP 1 * FROM sys.objects
    where object_id = @@PROCID
END
GO

EXEC dbo.UP_TableScanTesting
GO

解决方法

首先，我们可以动态视图sys.dm_exec_query_stats中根据每一个查询的plan_handle来统计该查询的执行次数、总共耗时、总的逻辑读、写、总共CPU时间消耗等信息；然后通过执行计划缓存函数sys.dm_exec_query_plan来分析相应的查询语句的详细信息。详情脚本参加如下代码：

-- Generate all query SQL text with "table scan" in cached query plan 
;WITH  XMLNAMESPACES 
    (DEFAULT N'http://schemas.microsoft.com/sqlserver/2004/07/showplan'   
            ,N'http://schemas.microsoft.com/sqlserver/2004/07/showplan' AS ShowPlan)
,EQS 
AS (
    SELECT EQS.plan_handle 
           ,SUM(EQS.execution_count) AS ExecutionCount 
           ,SUM(EQS.total_worker_time) AS TotalWorkTime 
           ,SUM(EQS.total_logical_reads) AS TotalLogicalReads 
           ,SUM(EQS.total_logical_writes) AS TotalLogicalWrites 
           ,SUM(EQS.total_elapsed_time) AS TotalElapsedTime 
           ,MAX(EQS.last_execution_time) AS LastExecutionTime 
     FROM sys.dm_exec_query_stats AS EQS 
     GROUP BY EQS.plan_handle
), info
AS(
    SELECT DISTINCT
        EQS.[ExecutionCount] 
        ,EQS.[TotalWorkTime] 
        ,EQS.[TotalLogicalReads] 
        ,EQS.[TotalLogicalWrites] 
        ,EQS.[TotalElapsedTime] 
        ,EQS.[LastExecutionTime] 
        ,ScanObject = StmtSimple.Node.value('(QueryPlan/RelOp//RelOp//TableScan/Object/@Database)[1]','sysname') + '.' +
                    StmtSimple.Node.value('(QueryPlan/RelOp//RelOp//TableScan/Object/@Schema)[1]','sysname') + '.' +
                    StmtSimple.Node.value('(QueryPlan/RelOp//RelOp//TableScan/Object/@Table)[1]','sysname')
        ,Statement = StmtSimple.Node.value('(@StatementText)[1]', 'nvarchar(max)')
        ,EST.text
        ,ECP.[objtype] AS [ObjectType] 
        ,ECP.[cacheobjtype] AS [CacheObjectType]
        ,EQS.plan_handle
    FROM sys.dm_exec_cached_plans AS ECP
        INNER JOIN EQS
        ON ECP.plan_handle = EQS.plan_handle
        CROSS APPLY sys.dm_exec_sql_text(ECP.[plan_handle]) AS EST
        CROSS APPLY sys.dm_exec_query_plan(ECP.[plan_handle]) AS EQP
        CROSS APPLY EQP.query_plan.nodes('/ShowPlanXML/BatchSequence/Batch/Statements/StmtSimple') AS StmtSimple(Node)
    WHERE StmtSimple.Node.exist('data(QueryPlan/RelOp//RelOp[@PhysicalOp="Table Scan"])') = 1
)
SELECT A.*, EQP.query_plan
FROM info AS A
    CROSS APPLY sys.dm_exec_query_plan(A.[plan_handle]) AS EQP
ORDER BY A.TotalElapsedTime DESC, A.ExecutionCount

执行上面的查询，结果展示如下图所示：

从分析结果的截图来看，我们可以得到很多有用的信息，比如：
每个查询语句的执行次数、总执行耗时、逻辑读写、对应Table Scan的查询Statement和详细的查询Batch语句，以及详细的执行计划等有用信息。

最后总结

这篇文章讨论了如何从执行计划缓存中找到Table Scan查询语句的详情，利用这个方法可以很轻松的找到RDS SQL客户需要的答案。