SQL Server ไม่ใช้ดัชนีที่ไม่ใช่คลัสเตอร์

Aug 28 2020

ฉันมีตารางที่มีแถวประมาณ 470mln ฉันต้องการเลือกข้อมูลตามวันที่ ฉันมีดัชนีสองตัวที่สร้างขึ้นบนตารางนี้ หนึ่งคือคลัสเตอร์หนึ่งและอื่น ๆ ไม่ได้ทำคลัสเตอร์ในคอลัมน์วันที่ (วันที่ถูกจัดเก็บเป็น INT) ฉันมีคำสั่งเลือกง่ายๆ:

select *
from big_table
where [date] BETWEEN 20200820 AND 20200828

ปัญหาคือแผนการสืบค้นใช้การสแกนดัชนีคลัสเตอร์แทนการค้นหาที่ไม่ใช่คลัสเตอร์ + keylookup วางแผนดังนี้:

ค่าประมาณที่สร้างขึ้นในแผนการสืบค้นเป็นเรื่องปกติสถิติเป็นข้อมูลล่าสุด ช่วงวันที่นี้ควรมีแถวประมาณ 5mln เมื่อฉันให้คำใบ้ดัชนีการเลือกนี้จะเสร็จสิ้นภายในสองสามวินาที - โดยไม่มีคำใบ้ใช้เวลาสองถึงสามนาทีจึงจะเสร็จสิ้น

นี่คือ SQL Server 2019 และโดยทั่วไปแล้วฉันสังเกตเห็นว่า db ชอบสแกนดัชนีคลัสเตอร์ที่ใช้คีย์ลุคอัพที่ไม่ใช่คลัสเตอร์ + บนตารางที่ใหญ่กว่า

ฉันไม่อยากใช้คำใบ้เพราะ:

บางครั้งฉันเลือกช่วงที่กว้างขึ้นซึ่งควรจะสแกนดัชนีคลัสเตอร์
ตารางถูกใช้ในมุมมองและฉันไม่สามารถระบุคำใบ้ดัชนีให้กับมุมมองได้

มีคำอธิบายว่าทำไม db ถึงไม่ใช้ NC index ในกรณีนี้?

ลิงก์ไปยังแผนการสืบค้น:

https://www.brentozar.com/pastetheplan/?id=SkaQFqLmD
https://www.brentozar.com/pastetheplan/?id=Hkh7q587P

คำตอบ

3 JoshDarnell Aug 28 2020 at 22:51

ดูเหมือนว่า SQL Server ไม่ได้ใช้ดัชนีนั้นตามค่าเริ่มต้นเนื่องจาก:

เป็นดัชนีที่กรองแล้วและ
คำค้นหาของคุณถูกกำหนดพารามิเตอร์

คุณสามารถดูคำเตือนนี้ได้ใน XML แผนการดำเนินการ:

<UnmatchedIndexes>
  <Parameterization>
    <Object Database="Database1" Schema="Schema1" Table="Object1" Index="Index1" />
  </Parameterization>
</UnmatchedIndexes>
<Warnings UnmatchedIndexes="1" />

SQL Server ไม่ทราบว่าค่าพารามิเตอร์คืออะไร (เนื่องจากอยู่ในตัวแปร) ดังนั้นจึงไม่สามารถใช้ดัชนีที่กรองได้อย่างปลอดภัย

วิธีแก้ปัญหาอย่างหนึ่งคือการใช้คำแนะนำดัชนี (ดังที่คุณกล่าวมาไม่เหมาะอย่างยิ่ง)

อีกวิธีหนึ่งในการแก้ไขปัญหานี้คือการใช้ SQL แบบไดนามิกตามที่อธิบายโดย Jeremiah Peschka ที่นี่:

ดัชนีกรองและ Dynamic SQL

ฉันไม่รู้ว่าดัชนีที่กรองเป็นอย่างไร ... กรองแล้ว คุณอาจหลีกเลี่ยงการฝังลิเทอรัลบนค่าใดค่าหนึ่งในสองค่าเพียงค่าเดียวเพื่อ จำกัด การขยายแคชของแผน

2 DavidBrowne-Microsoft Aug 28 2020 at 22:45

มีคำอธิบายว่าทำไม db ถึงไม่ใช้ NC index ในกรณีนี้?

ต้นทุนโดยประมาณของแผนนั้นต่ำกว่า การสแกนดัชนีคลัสเตอร์ใช้ IO ตามลำดับมากขึ้นและการสแกนดัชนีที่ไม่ใช่คลัสเตอร์ + การค้นหาบุ๊กมาร์กจะใช้ IO แบบสุ่มมากขึ้น ดังนั้นอันไหนเร็วกว่านั้นอาจขึ้นอยู่กับฮาร์ดแวร์ของคุณ

ดูสถิติการรอคิวรี สำหรับดัชนีคลัสเตอร์จะสแกน

        <WaitStats>
          <Wait WaitType="PAGEIOLATCH_SH" WaitTimeMs="3188040" WaitCount="31753"/>
          <Wait WaitType="CXPACKET" WaitTimeMs="566095" WaitCount="6329619"/>
          <Wait WaitType="SOS_SCHEDULER_YIELD" WaitTimeMs="21354" WaitCount="29774"/>
          <Wait WaitType="MEMORY_ALLOCATION_EXT" WaitTimeMs="11994" WaitCount="8679127"/>
          <Wait WaitType="SLEEP_BPOOL_STEAL" WaitTimeMs="7435" WaitCount="439"/>
          <Wait WaitType="LATCH_EX" WaitTimeMs="206" WaitCount="35"/>
          <Wait WaitType="SESSION_WAIT_STATS_CHILDREN" WaitTimeMs="8" WaitCount="6"/>
          <Wait WaitType="ASYNC_NETWORK_IO" WaitTimeMs="5" WaitCount="2"/>
        </WaitStats>
        <QueryTimeStats ElapsedTime="247180" CpuTime="232769"/>

สำหรับดัชนีที่ไม่ใช่คลัสเตอร์นั้น

        <WaitStats>
          <Wait WaitType="CXPACKET" WaitTimeMs="451425" WaitCount="4834017"/>
          <Wait WaitType="PAGEIOLATCH_SH" WaitTimeMs="43202" WaitCount="41863"/>
          <Wait WaitType="SOS_SCHEDULER_YIELD" WaitTimeMs="11453" WaitCount="11288"/>
          <Wait WaitType="MEMORY_ALLOCATION_EXT" WaitTimeMs="2823" WaitCount="4051831"/>
          <Wait WaitType="LCK_M_S" WaitTimeMs="1366" WaitCount="1"/>
          <Wait WaitType="RESERVED_MEMORY_ALLOCATION_EXT" WaitTimeMs="152" WaitCount="37550"/>
          <Wait WaitType="PAGEIOLATCH_UP" WaitTimeMs="49" WaitCount="4"/>
          <Wait WaitType="LATCH_EX" WaitTimeMs="11" WaitCount="14"/>
          <Wait WaitType="LATCH_SH" WaitTimeMs="1" WaitCount="3"/>
        </WaitStats>
        <QueryTimeStats ElapsedTime="67529" CpuTime="119828"/>

แต่ทั้งสองแผนมีราคาแพงมากดังนั้นคุณควรทำอะไรสักอย่าง ตัวเลือก ได้แก่