RDS SQL Server - 专题分享 - 巧用执行计划缓存之Table Scan-阿里云开发者社区

开发者社区> 风移> 正文

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

简介: # 背景引入 执行计划中的Table Scan或者是Clustered Index Scan会导致非常低下的查询性能,尤其是对于大表或者超大表。执行计划缓存是SQL Server内存管理中非常重要的特性,这篇系列文章我们探讨如何从执行计划缓存的角度来发现RDS SQL数据库引擎中的Table Scan行为,以及与之相应SQL查询语句详细信息。 # 问题分析 其实,我们大家都知道,Table
+关注继续查看

背景引入

执行计划中的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版本为例)=> 监控与报警 => 引擎监控
01.png

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

在很多次性能优化的案例中,我们告诉客人:“您的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

执行上面的查询,结果展示如下图所示:
03.png

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

最后总结

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

版权声明:本文内容由阿里云实名注册用户自发贡献,版权归原作者所有,阿里云开发者社区不拥有其著作权,亦不承担相应法律责任。具体规则请查看《阿里云开发者社区用户服务协议》和《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容,填写侵权投诉表单进行举报,一经查实,本社区将立刻删除涉嫌侵权内容。

相关文章
SQL Server定时自动抓取耗时SQL并归档数据脚本分享
原文:SQL Server定时自动抓取耗时SQL并归档数据脚本分享 SQL Server定时自动抓取耗时SQL并归档数据脚本分享 第一步建库 USE [master] GO CREATE DATABASE [MonitorElapsedHighSQL] GO 第二步创建sp_who3存储过程 -- http://sqlserverplanet.
928 0
ExpandableListView getChildView 不执行,不显示子列表
原因很简单:   在 GroupView 里面不要加入 button 等可点击空间,否则 和 点击 Groupview 展开相冲突。 去掉就好了getGroupView
1426 0
【hibernate 执行方法未插入数据库】hibernate的save方法成功执行,但是未插入到数据库
今天做项目,碰上这个问题: hibernate的save方法成功执行,但是未插入到数据库。 Dao层代码: 1 @Override 2 public void save(T t) { 3 this.
811 0
C# Redis Server分布式缓存编程(二)
在Redis编程中, 实体和集合类型则更加有趣和实用 namespace Zeus.Cache.Redis.Demo { public class Person { public int Id { get; set; } public ...
846 0
SQL Server使用变量和参数以及语句执行时执行计划的差异
T-SQL语句之间传输数据有以下一些途径: 1)本地变量 2)存储过程中的参数 3)应用程序变量 4)参数标记 上面4种变量中,比较常用的是本地变量和存储过程中的参数。 本地变量指在查询前declare参数,并且set设值之后,在查询语句中直接使用声明的参数,而不是直接使用其值。
3311 0
使用mybatis对数据库执行更新操作时,parameterType为某个具体的bean,而bean中传入的参数为null时,抛出异常
使用mybatis对数据库执行更新操作时,parameterType为某个具体的bean,而bean中传入的参数为null时,抛出异常 问题描述 在使用mybatis对数据库执行更新操作时,parameterType为某个具体的bean,而bean中传入的参数为null时,抛出异常如下:org.
1645 0
分享一个SQLSERVER脚本
原文:分享一个SQLSERVER脚本 分享一个SQLSERVER脚本 很多时候我们都需要计算数据库中各个表的数据量很每行记录所占用空间 这里共享一个脚本 CREATE TABLE #tablespaceinfo ( nameinfo VARCHAR(50) , ...
810 0
+关注
风移
阿里云数据库专家,负责SQL Server数据库产品线。SQL Server从业10年,经历过SQL 2000、SQL 2005、SQL 2008、SQL 2008R2、SQL 2012、SQL 2014、SQL 2016和SQL on Linux各个版本。
75
文章
46
问答
文章排行榜
最热
最新
相关电子书
更多
文娱运维技术
立即下载
《SaaS模式云原生数据仓库应用场景实践》
立即下载
《看见新力量:二》电子书
立即下载