139
技術社區[雲棲]
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'https://schemas.microsoft.com/sqlserver/2004/07/showplan'
,N'https://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客戶需要的答案。
最後更新：2017-08-29 10:03:09