Demystification ของกระบวนการเพิ่มประสิทธิภาพ SQL Server

Question

เราต้องการดูรูปแบบทั้งหมดของแผนการสืบค้นที่พิจารณาระหว่างการเพิ่มประสิทธิภาพการสืบค้นโดยเครื่องมือเพิ่มประสิทธิภาพเซิร์ฟเวอร์ SQL SQL Server ให้ข้อมูลเชิงลึกโดยละเอียดโดยใช้querytraceonตัวเลือก ตัวอย่างเช่นQUERYTRACEON 3604, QUERYTRACEON 8615ช่วยให้เราสามารถพิมพ์โครงสร้าง MEMO และQUERYTRACEON 3604, QUERYTRACEON 8619พิมพ์รายการกฎการเปลี่ยนแปลงที่ใช้ระหว่างกระบวนการเพิ่มประสิทธิภาพ เป็นเรื่องที่ดีอย่างไรก็ตามเรามีปัญหาหลายประการเกี่ยวกับผลลัพธ์การติดตาม:

1. ดูเหมือนว่าโครงสร้าง MEMO จะมีเฉพาะตัวแปรสุดท้ายของแผนการสืบค้นหรือตัวแปรที่ถูกเขียนใหม่ในภายหลังในแผนสุดท้าย มีวิธีค้นหาแผนการสืบค้นที่ "ไม่สำเร็จ / ไม่ประสบความสำเร็จ" หรือไม่?
2. ตัวดำเนินการใน MEMO ไม่มีการอ้างอิงถึงส่วน SQL ตัวอย่างเช่นตัวดำเนินการ LogOp_Get ไม่มีการอ้างอิงไปยังตารางเฉพาะ
3. กฎการแปลงไม่มีการอ้างอิงที่แน่นอนถึงตัวดำเนินการ MEMO ดังนั้นเราจึงไม่สามารถแน่ใจได้ว่าตัวดำเนินการใดถูกแปลงโดยกฎการแปลง

ให้ฉันแสดงเป็นตัวอย่างที่ละเอียดกว่านี้ ขอฉันมีโต๊ะเทียมสองตัวAและB:

WITH x AS (
        SELECT n FROM
        (
            VALUES (0), (1), (2), (3), (4), (5), (6), (7), (8), (9)
        ) v(n)
    ),
    t1 AS
    (
        SELECT ones.n + 10 * tens.n + 100 * hundreds.n + 1000 * thousands.n + 10000 * tenthousands.n + 100000 * hundredthousands.n as id  
        FROM x ones, x tens, x hundreds, x thousands, x tenthousands, x hundredthousands
    )
SELECT
    CAST(id AS INT) id, 
    CAST(id % 9173 AS int) fkb, 
    CAST(id % 911 AS int) search, 
    LEFT('Value ' + CAST(id AS VARCHAR) + ' ' + REPLICATE('*', 1000), 1000) AS padding
INTO A
FROM t1;


WITH x AS (
        SELECT n FROM
        (
            VALUES (0), (1), (2), (3), (4), (5), (6), (7), (8), (9)
        ) v(n)
    ),
    t1 AS
    (
        SELECT ones.n + 10 * tens.n + 100 * hundreds.n + 1000 * thousands.n AS id  
        FROM x ones, x tens, x hundreds, x thousands       
    )
SELECT
    CAST(id AS INT) id,
    CAST(id % 901 AS INT) search, 
    LEFT('Value ' + CAST(id AS VARCHAR) + ' ' + REPLICATE('*', 1000), 1000) AS padding
INTO B
FROM t1;

ตอนนี้ฉันเรียกใช้แบบสอบถามง่ายๆ

SELECT a1.id, a1.fkb, a1.search, a1.padding
FROM A a1 JOIN A a2 ON a1.fkb = a2.id
WHERE a1.search = 497 AND a2.search = 1
OPTION(RECOMPILE, 
    MAXDOP 1,
    QUERYTRACEON 3604,
    QUERYTRACEON 8615)
    

ฉันได้ผลลัพธ์ที่ค่อนข้างซับซ้อนซึ่งอธิบายโครงสร้าง MEMO (คุณอาจลองด้วยตัวเอง) มี 15 กลุ่ม นี่คือภาพที่แสดงให้เห็นโครงสร้าง MEMO โดยใช้ต้นไม้

จากแผนภูมิเราอาจสังเกตว่ามีการใช้กฎบางอย่างก่อนที่เครื่องมือเพิ่มประสิทธิภาพจะพบแผนการสืบค้นขั้นสุดท้าย ตัวอย่างเช่นjoin commute( JoinCommute), join to hash join( JNtoHS) หรือEnforce sort( EnforceSort) ดังที่ได้กล่าวไปแล้วคุณสามารถพิมพ์กฎการเขียนใหม่ทั้งหมดที่ใช้โดยเครื่องมือเพิ่มประสิทธิภาพโดยใช้QUERYTRACEON 3604, QUERYTRACEON 8619ตัวเลือก ปัญหา:

1. เราอาจพบJNtoSM( Join to sort merge) เขียนกฎในรายการ 8619 แต่ผู้ประกอบการจัดเรียงผสานไม่ได้อยู่ในโครงสร้าง MEMO ฉันเข้าใจว่าการผสานการเรียงลำดับอาจมีราคาแพงกว่า แต่ทำไมจึงไม่อยู่ใน MEMO
2. จะรู้ได้อย่างไรว่าLogOp_Getตัวดำเนินการใน MEMO อ้างอิงถึงตาราง A หรือตาราง B?
3. หากฉันเห็นกฎGetToIdxScan - Get -> IdxScanในรายการ 8619 จะแมปกับตัวดำเนินการ MEMO ได้อย่างไร

มีทรัพยากรจำนวน จำกัด เกี่ยวกับเรื่องนี้ ฉันได้อ่านบล็อกโพสต์ของ Paul White เกี่ยวกับกฎการเปลี่ยนแปลงและ MEMO อย่างไรก็ตามคำถามข้างต้นยังคงไม่มีคำตอบ ขอบคุณสำหรับความช่วยเหลือ

Answer 1

ฉันจะพยายามตอบคำถามของคุณ:

1. ดูเหมือนว่าโครงสร้าง MEMO จะมีเพียงตัวแปรสุดท้ายของแผนการสืบค้นหรือรูปแบบที่ถูกเขียนใหม่ในภายหลัง มีวิธีค้นหาแผนการสืบค้นที่ "ไม่สำเร็จ / ไม่เป็นผล" หรือไม่?

ไม่น่าเศร้าที่ไม่มีทางทำเช่นนั้น @Ronaldo วางลิงค์ที่ดีในความคิดเห็น คำแนะนำของฉันคือใช้ไฟล์Include Live Query Statistics

และลองดูว่าคุณเห็นแผนการสืบค้นข้อมูลที่แตกต่างกันหรือไม่ การใช้งานtop 10, top 1000หรือ*และคุณจะเห็นว่าแผนการแบบสอบถามที่แตกต่างกันจะนำเสนอ คุณยังสามารถใช้query hintและบังคับแผนแบบสอบถามของคุณเป็นรูปแบบอื่นได้ โดยพื้นฐานแล้ว"ทำแผนการค้นหาที่ถูกทิ้งของคุณเอง"

2. ตัวดำเนินการใน MEMO ไม่มีการอ้างอิงถึงส่วน SQL ตัวอย่างเช่นตัวดำเนินการ LogOp_Get ไม่มีการอ้างอิงไปยังตารางเฉพาะ

ใช้QUERYTRACEON 8605ฉันสามารถดูการอ้างอิงถึงตาราง:

3. กฎการแปลงไม่มีการอ้างอิงที่แม่นยำถึงตัวดำเนินการ MEMO ดังนั้นเราจึงไม่สามารถแน่ใจได้ว่าตัวดำเนินการใดถูกแปลงโดยกฎการแปลง

ฉันไม่เห็นGetToIdxScan - Get -> IdxScanข้อความค้นหาที่คุณระบุ คำแนะนำของฉันคือใช้ Use QUERYTRACEON 8605หรือQUERYTRACEON 8606ควรมีการอ้างอิงที่นั่น

แก้ไข:

ดังนั้น"... เป็นไปได้ไหมที่จะดูข้อมูลเพิ่มเติมเกี่ยวกับแผนผู้สมัครใน SQL Server"

คำตอบคือไม่เนื่องจากไม่มีแผนการสืบค้นข้อมูลผู้สมัครอื่น ๆ ในความเป็นจริงเป็นความเข้าใจผิดทั่วไปที่ SQL Server ส่งคืนแผนแบบสอบถามที่ดีที่สุดให้คุณ SQL Server ไม่สามารถคำนวณวิธีแก้ปัญหาที่เป็นไปได้ทั้งหมดสำหรับคุณนั่นจะใช้เวลา ... ฉันไม่รู้ ... นาที ... ? ชั่วโมง ... ? การคำนวณทุกวิธีแก้ปัญหานั้นเป็นไปไม่ได้

แต่ถ้าคุณต้องการตรวจสอบว่าเหตุใดแผนการสืบค้นของคุณจึงเลือกรูปแบบนั้นคุณสามารถใช้ได้:

• SET SHOWPLAN_ALL ON : และ SQL Server จะส่งคืนโครงสร้างตรรกะของการคำนวณทุกครั้งของแผนแบบสอบถามของคุณ

• DBCC SHOW_STATISTICS('A', 'PK_A'): ซึ่งจะแสดงสถิติเกี่ยวกับตารางเป้าหมายและข้อ จำกัด ฉันสร้างคีย์เพื่อแสดงผลลัพธ์โดยปกติแล้วคุณจะเห็นข้อมูลเพิ่มเติมหากตารางของคุณถูกสอบถามบ่อยขึ้น

• USE HINT('force_legacy_cardinality_estimation') : จะช่วยให้คุณสามารถใช้การประมาณค่าคาร์ดินาลลิตี้ในอดีตได้ดังนั้นคุณจึงสามารถตรวจสอบได้ว่าแผนการค้นหาของคุณอาจเร็วขึ้นหรือไม่ด้วยการประมาณค่าจำนวนสมาชิกเดิม