Sql-Server

“行目標”不同的行為 - 為什麼？

  • October 31, 2019

這是一個有 400+ 百萬行和 16 個非聚集索引、146 列的堆。請不要關注為什麼我不能創建聚集索引。這不在我的控制範圍內，也不在我的問題範圍內。

SQL 伺服器：

Microsoft SQL Server 2012 (SP4-GDR) (KB4057116) - 11.0.7462.6 (X64) 2018 年 1 月 5 日 22:11:56 版權所有 (c) Microsoft Corporation Enterprise Edition：Windows NT 6.3 上基於核心的許可（64 位）（ Build 9600:) (Hypervisor) 數據庫兼容級別：SQL Server 2008(100)

當我執行以下查詢（匿名）時，我得到了這個實際的執行計劃：

SELECT TOP ? *
FROM Schema1.Object1 Object2
WHERE Object2.Column1 LIKE ?
ORDER BY Object2.Column2 DESC

相同的數據庫恢復到 SQL 2017 CU13 並將兼容模式更改為 SQL Server 2012 (110)（以下是完整版本名稱），我得到了這個實際的執行計劃。

Microsoft SQL Server 2017 (RTM-CU13) (KB4466404) - 14.0.3048.4 (X64) 2018 年 11 月 30 日 12:57:58 版權所有 (C) 2017 Microsoft Corporation Enterprise Edition (64-bit) o​​n Windows Server 2016 Standard 10.0 (Build 14393) :)（管理程序）

我完全理解“行目標”在這裡發揮作用。您在第一個計劃中看不到它，因為它之前沒有在查詢計劃中公開。為了確保我可以執行帶有跟踪標誌 4138DISABLE_OPTIMIZER_ROWGOAL的 SQL Server 2008 版本和帶有提示的SQL Server 2012 版本。在這兩種情況下，我都獲得了顯著的性能提升。增益發生是因為優化器選擇了正確的索引並且沒有選擇nested loop join.

問題：

  • 雖然這兩個兼容性級別都在 SQL Server 2014 版本之前，但為什麼EstimatedRowsWithoutRowGoal（我知道您在 2008 年看不到它）不同？
  • 為什麼索引掃描限定 SQL 2008 中的 110 萬行與 SQL 2012 中的 870 萬行？什麼啟發式改變了（即使我沒有使用與 Compatibility 120 一起啟動的新優化器）？

我閱讀了 Paul White 的這篇文章以及文章中給出的所有 8 個連結。另請閱讀更多展示計劃增強功能——Pedro Lopes 的行目標

您的 2 個查詢（和表？）不完全匹配。

sql server 2008 兼容級別查詢返回 143 列，而 sql server 2012 兼容級別查詢 返回146 列。

請驗證這些表中的任何其他差異。

為什麼索引掃描限定 SQL 2008 中的 110 萬行與 SQL 2012 中的 870 萬行？

您的索引掃描正在返回行，按順序排列，Object2.Column2 然後推送他們的書籤

（書籤= column144/Column147在您的匿名計劃中）

到 RID 查找和過濾Object2.Column1。

初步猜測：平局？

這讓我相信Object2.Column2有聯繫，導致首先閱讀不同的行。

兩者都在執行有序預取

 <NestedLoops Optimized="0" WithOrderedPrefetch="1">

…有序預取保留嵌套循環連接內側的鍵順序，因此 SQL Server 確保以正確的順序處理非同步 I/O。…我們的測試查詢具有 ORDER BY 子句，優化器可以通過使用有序預取來避免對其進行排序…

來源

我可能錯了，但我從中得到的是，只要保留了順序，就可以將不同的行提供給RID查找運算符，從而支持 ties 聲明。

您可以嘗試使用唯一順序執行查詢並將這些查詢計劃添加到問題中。

雖然這兩個兼容性級別都在 SQL Server 2014 版本之前，但為什麼 EstimatedRowsWithoutRowGoal（我知道您在 2008 年看不到它）是不同的？

我們在這裡需要詳細的統計資訊。統計數據顯示各表之間的估計不同，從而導致EstimatedRowsWithoutRowGoal

您可以嘗試在執行兩個查詢之前執行完整掃描更新，同時選擇相同數量的列。

測試

在 sql server 實例 (2008) & (2017 with compat 2012) 之間進行測試時

我繼續獲得相同數量的行返回：

2008（兼容性）：

在此處輸入圖像描述

查詢計劃

2012（兼容性）：

在此處輸入圖像描述

查詢計劃

即使更改 Column2 以保存許多重複項，估計值和實際值也保持不變。

UPDATE dbo.Object1
SET Object1.Column2
= Object1.Column2 % 400;

正如預期的那樣，將額外的列添加到 compat 2012 db 中的表時，估計值保持不變

ALTER TABLE dbo.Object1 
ADD ExtraColumn1 int default(1) with values;
ALTER TABLE dbo.Object1 
ADD ExtraColumn2 int default(2) with values;
ALTER TABLE dbo.Object1 
ADD ExtraColumn3 int default(3) with values;

啟用跟踪標誌的情況相同

結論

這種類型排除了我之前關於數據關係的陳述，但也表明我無法用我的數據集（小得多）複製你的問題。

即使我將整個設置column2為相同的值，返回的行數也有 0 的差異。數據差異似乎是造成這種情況的原因。

使用的數據集

CREATE DATABASE TEST
GO
USE [master]
GO
ALTER DATABASE [TEST] MODIFY FILE ( NAME = N'TEST', FILEGROWTH = 1048576KB )
GO
ALTER DATABASE [TEST] MODIFY FILE ( NAME = N'TEST_log', FILEGROWTH = 1048576KB )
GO
alter database test set recovery simple
GO
backup database test to disk = 'NUL'

GO

use TEST
go

CREATE TABLE dbo.Object1(
Column1 int,
Column2 int,
Column3 int,
Column4 int,
Column5 int,
Column6 int,
Column7 int,
Column8 int,
Column9 int,
Column10 int,
Column11 int,
Column12 int,
Column13 int,
Column14 int,
Column15 int,
Column16 int,
Column17 int,
Column18 int,
Column19 int,
Column20 int,
column1_varchar varchar(20));

CREATE INDEX Index1
on dbo.Object1(column15,Column16,Column17)
CREATE INDEX Index2
on dbo.Object1(Column2,Column10,Column19)


SELECT top(1000000)  --1M
row_number() over(order by (select null)) as rownum
INTO #temp
FROM master..spt_values spt1
cross apply master..spt_values spt2
cross apply master..spt_values spt3


INSERT INTO dbo.Object1
WITH(TABLOCK)
SELECT 
rownum,
rownum,
rownum,
rownum,
rownum,
rownum,
rownum,
rownum,
rownum,
rownum,
rownum,
rownum,
rownum,
rownum,
rownum,
rownum,
rownum,
rownum,
rownum,
rownum,
'ABC'+ CAST(rownum as varchar(20)) 
FROM #temp  
GO 3

根據我在原始問題中提供的資訊，我可以期待 Randi Vertongen 的答案是最好的。我只是寫這個作為答案，因為我不能把這個評論給其他讀者。免責聲明：Randi 和我在這裡聊過這個。

一旦一條資訊將幫助您跟隨。在我的帶有 where 子句的前 100 個結果集中，所有結果都屬於829000514900column2 中的值。

數據集在兩種環境之間的堆中是完全不同的。我決定根據 column2 進行滾動求和。

這是我發現的。我相信仍然存在的小差異是由於[Forwarded Record][2]在堆中。

我在兩種環境中都執行了以下查詢。

SELECT 
   column2, 
   COUNT(0) AS CountColumn2, 
   SUM (COUNT(column2)) OVER (ORDER BY column2 DESC) AS RunningTotal
FROM dbo.object1
GROUP BY column2 
ORDER BY column2 DESC

SQL Server 2008 環境的結果：

實際讀取行數=1176085

colum2          CountColumn2    RunningTotal
829000515000    79              1175804
829000514900    159             1175963
829000514800    631             1176594

SQL Server 2012 環境的結果：

實際讀取的行數=8891415

colum2          CountColumn2            RunningTotal
829000515000    79                      8892962
829000514900    159                     8893121
829000514800    631                     8893752

引用自：https://dba.stackexchange.com/questions/252262

相關問答

Sql-Server

OPTION FORCE ORDER 提高性能直到刪除行

  • June 30, 2021
檢視問答
Sql-Server

如何優化這個 Select MAX() SQL 查詢？

  • February 3, 2020
檢視問答
Sql-Server

尋求幫助優化自聯接

  • May 13, 2019
檢視問答
Sql-Server

謂詞的直接值產生不太好的計劃

  • April 9, 2019
檢視問答
Sql-Server

如何教優化器在數據記錄主/明細表上使用索引而不是 fts 連接？

  • August 9, 2018
檢視問答
Sql-Server

如何刪除兩個表連接以優化速度的鍵查找？

  • November 12, 2016
檢視問答