SQL Server 2019 adaptive เข้าร่วมประสิทธิภาพต่ำ

Question

ฉันอัปเกรด SQL Server จากปี 2016 เป็น 2019 แผนการสืบค้นของแบบสอบถามของฉันเปลี่ยนไปและใช้การรวมแบบปรับได้ แต่น่าเสียดายที่ระยะเวลาของการสืบค้นเพิ่มขึ้นเป็น 1 นาทีจาก 1 วินาทีฉันเปลี่ยนลำดับการเข้าร่วมและปัญหาได้รับการแก้ไขแล้ว

รหัส T-SQL:

SELECT TOP 100 * FROM dbo.APP App
JOIN dbo.PRS p ON App.PartyId=p.PRSId
LEFT JOIN dbo.Country ON p.NationalityId = dbo.Country.CountryId 
LEFT JOIN dbo.EDUBranch b ON app.EducationBranchId=b.EDUBranchId

และแผนการสืบค้นของ: https://www.brentozar.com/pastetheplan/?id=H1cFQxwdP

หลังจากเปลี่ยนลำดับการเข้าร่วม:

SELECT TOP 100 * FROM dbo.APP App
LEFT JOIN dbo.EDUBranch b ON app.EducationBranchId=b.EDUBranchId
JOIN dbo.PRS p ON App.PartyId=p.PRSId
LEFT JOIN dbo.Country ON p.NationalityId = dbo.Country.CountryId

และแผนการสืบค้นของ: https://www.brentozar.com/pastetheplan/?id=SJv1GlPdv

ใครมีความคิดเกี่ยวกับ

1. เหตุใดการเข้าร่วมแบบปรับอัตโนมัติจึงทำให้การสืบค้นช้าลง
2. การเปลี่ยนคำสั่งเข้าร่วมจะเปลี่ยนแผนการดำเนินการอย่างไร

Answer 1

แผนได้อย่างรวดเร็วมีเป้าหมายแถว สิ่งนี้จะจบลงด้วยการสนับสนุนการรวมลูปแบบซ้อนกันซึ่งส่ง 100 แถวไปยังตัวดำเนินการด้านบนค่อนข้างเร็วและตอบสนองการสืบค้น

แผนช้ายังมีเป้าหมายเป็นแถว แต่จริงๆแล้วเฉพาะในตัวดำเนินการเข้าร่วมที่ปรับเปลี่ยนได้ ในกรณีที่การรวมแบบปรับได้จำเป็นต้องรันเป็นการรวมแฮชผลลัพธ์ทั้งหมดจากอินพุตด้านบนจะต้องถูกใช้ไป (ขั้นตอน "สร้าง" ของการรวมแฮช) ดูบล็อกของ Forrest McDaniel เพื่อให้เห็นภาพที่ยอดเยี่ยมเกี่ยวกับวิธีการทำงาน: The Three Physical Joins, Visualized

เหตุใดการเข้าร่วมแบบปรับอัตโนมัติจึงทำให้การสืบค้นช้าลง

ในความเป็นจริงแล้วการรวมแบบปรับได้จะทำงานในรูปแบบการรวมแฮชเนื่องจากเกินเกณฑ์ 88 แถว (โดยมาก) สิ่งนี้นำไปสู่การสืบค้นที่ต้องอ่านทุกแถวจากการdbo.APPเข้าร่วมการจับคู่ทั้งหมดจากdbo.PRS- ประมาณ 30 GB ของการอ่านตามแผนการดำเนินการ

การเปลี่ยนคำสั่งเข้าร่วมจะเปลี่ยนแผนการดำเนินการอย่างไร

เครื่องมือเพิ่มประสิทธิภาพมีความสามารถในการเรียงลำดับการรวมใหม่เพื่อกรองชุดผลลัพธ์ก่อนหน้านี้และมีประสิทธิภาพมากขึ้นตราบใดที่แบบสอบถามยังคงให้ผลลัพธ์ที่ถูกต้อง แต่มันไม่ได้ทำมากนักเมื่อเผชิญกับการผสมผสานของ OUTER และ INNER ดูรายละเอียดเกี่ยวกับคำถามและคำตอบนี้: การกำจัดการรวมชั้นในถูกยับยั้งโดยการรวมภายนอกก่อนหน้านี้

เมื่อคุณเขียนลำดับการเข้าร่วมใหม่ด้วยตนเองจะได้รับอนุญาตสำหรับแผนการที่การเข้าร่วมจะdbo.EDUBranchมาก่อนการเข้าร่วมdbo.Countryซึ่งไม่มีการเข้าร่วมแบบปรับได้ใช้เป้าหมายของแถวที่กล่าวถึงข้างต้นและจะออกมาดีกว่ามาก (ตามที่คุณสังเกตเห็น)