โอ้ แผนการสืบค้นเหล่านั้น แผนการดำเนินการแบบสอบถาม SQL การตีความการดำเนินงานขั้นพื้นฐาน แผนแบบสอบถามถูกสร้างขึ้นเพียงครั้งเดียว

สวัสดีทุกคน! เมื่อเร็ว ๆ นี้ฉันประสบปัญหาในการประมวลผลเอกสารเป็นเวลานาน

ข้อมูลที่ป้อน: การกำหนดค่า “การจัดการองค์กรการผลิต รุ่น 1.3 (1.3.52.1)” เอกสาร “ใบสั่งการชำระเงินที่เข้ามา” ข้อร้องเรียน: การถือครองฐานข้อมูลการทำงานเป็นเวลา 20-30 วินาทีซึ่งน่าสนใจในสำเนาของฐานข้อมูลเอกสารเดียวกันนั้นจะถูกเก็บไว้เป็นเวลา 2-4 วินาที อ่านเกี่ยวกับการสืบสวนและสาเหตุของพฤติกรรมนี้ด้านล่าง

ดังนั้นด้วยความช่วยเหลือ การวัดประสิทธิภาพฉันคิดว่าทุกคนรู้วิธีใช้งานพบผู้กระทำผิด:

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

ตามการวัดประสิทธิภาพ การดำเนินการนี้ใช้เวลา 13 วินาที หากคุณคำนวณค่าเฉลี่ย คุณจะได้รับ 0.5 วินาทีต่อการลงทะเบียน ชั่วนิรันดร์!

ดังที่เราทุกคนรู้ดีว่าเราไม่สามารถเพิ่มประสิทธิภาพการบันทึกได้ แต่มีบางอย่างผิดปกติอย่างชัดเจน
หากต้องการวิเคราะห์เพิ่มเติม ให้เปิด เซิร์ฟเวอร์ SQLผู้สร้างโปรไฟล์และ . สำหรับการวิเคราะห์ฉันใช้คลาสเหตุการณ์:

  • โปรไฟล์สถิติ Showplan
  • โปรไฟล์สถิติ Showplan XML
  • RPC เสร็จสมบูรณ์
  • SQL:แบทช์เสร็จสมบูรณ์.

ในการตั้งค่าการติดตามก็มี กรองตาม SPID:

SPID คือ ID กระบวนการของเซิร์ฟเวอร์ฐานข้อมูล ในกรณีของ 1C โดยพื้นฐานแล้วมันคือการเชื่อมต่อระหว่างเซิร์ฟเวอร์ 1C และ DBMS คุณสามารถดูได้ในคอนโซลการดูแลระบบของเซิร์ฟเวอร์ 1C ในคอลัมน์ "การเชื่อมต่อกับ DBMS"

แสดงว่าถ้า ช่วงเวลานี้การเชื่อมต่อกับฐานข้อมูลจะถูกบันทึกโดยเซสชัน: กำลังทำการเรียก DBMS หรือธุรกรรมเปิดอยู่ หรือมีวัตถุ "ตัวจัดการตารางชั่วคราว" ค้างอยู่ ซึ่งมีการสร้างตารางชั่วคราวอย่างน้อยหนึ่งตาราง

มาเขียนการประมวลผลเพื่อเก็บ SPID โดยจะมีหนึ่งขั้นตอน:

สิ่งสำคัญคือต้องกำหนดอ็อบเจ็กต์การเชื่อมต่อในกรณีของเราคือผู้จัดการตารางชั่วคราวให้เป็นตัวแปรในการประมวลผล เราเปิดการประมวลผล รันขั้นตอน และตราบใดที่เปิดอยู่ SPID จะได้รับการแก้ไข เปิดคอนโซลการดูแลระบบเซิร์ฟเวอร์ 1C:

ดังนั้นเราจึงได้รับ SPID แล้ว เราจึงป้อนค่าลงในตัวกรองและรับการติดตามจากฐานข้อมูลการทำงานปัจจุบันสำหรับเซสชันของเรา เมื่อวิเคราะห์การติดตาม พบว่าการดำเนินการใช้เวลา 11 วินาทีจึงจะเสร็จสมบูรณ์:

สิ่งที่ดึงดูดสายตาของฉันคือจำนวนการอ่าน - 1872578 แต่ฉันไม่ได้ให้ความสำคัญกับเรื่องนี้ในทันทีและเริ่มรู้ว่ากำลังทำอะไรที่นี่และกับโต๊ะไหน

ดำเนินการ sp_executesql <= @P2) AND (T1._Fld1466RRef = @P3)) OR ((T1._Period <= @P4) AND (T1._Fld1466RRef = @P5))) OR ((T1._Period <= @P6) AND (1=0)))’,N’@P1 varbinary(16),@P2 datetime2(3),@P3 varbinary(16),@P4 datetime2(3),@P5 varbinary(16),@P6 datetime2(3)’,0x8A2F00155DBF491211E87F56DD1A416E,’4018-05-31 23:59:59′,0x00000000000000000000000000000000,’4018-05-31 23:59:59′,0x9A95A0369F30F8DB11E46684B4F0A05F,’4018-05-31 23:59:59"

ดังที่คุณเห็นจากการสืบค้น SQL ตารางจะถูกประมวลผล "AccRg1465"นี่คือตารางในทะเบียนการบัญชีที่รองรับตนเอง การแสดงแผนการดำเนินการแบบสอบถามด้วยข้อความ:

ดังที่คุณเห็นจากแผนการดำเนินการของการสืบค้น SQL ไม่มีอะไรเลวร้ายเกิดขึ้น ตาราง “ AccRg1465" มีการใช้การค้นหาดัชนีแบบคลัสเตอร์ทุกที่

ฉันไม่เห็นอะไรผิดปกติกับแผนกราฟิกแม้ว่ามันจะดูป่องเกินไปสำหรับฉัน - มีการควบรวมกิจการและสองลูปซ้อนกันโดยไม่มีเหตุผลที่ชัดเจน จำนวนการอ่านและเวลาดำเนินการอันมหาศาลนี้มาจากไหน?

ดังที่ได้กล่าวไว้ข้างต้น ปัญหาไม่ได้เกิดขึ้นซ้ำในสำเนาใหม่ของฐานข้อมูล สำเนาถูกนำมาจากฐานข้อมูลการทำงานหลังจากที่ปัญหาปรากฏขึ้น ดังนั้นจึงตัดสินใจวิเคราะห์พฤติกรรมใน SQL Server Profiler บนเอกสารเดียวกัน
นี่คือผลลัพธ์:

ข้อความค้นหาใน SQL:

EXEC sp_executesql N"เลือก TOP 1 0x01 จาก dbo._AccRg1465 T1 โดยที่ (T1._RecorderTRef = 0x0000022D และ T1._RecorderRRef = @P1) AND ((((T1._Period<= @P2) AND (T1._Fld1466RRef = @P3)) OR ((T1._Period <= @P4) AND (T1._Fld1466RRef = @P5))) OR ((T1._Period <= @P6) AND (1=0)))" , N"@P1 varbinary(16),@P2 datetime2(3),@P3 varbinary(16),@P4 datetime2(3),@P5 varbinary(16),@P6 datetime2(3)", 0x8A2F00155DBF491211E87F56DD1A416E, "4018-05-31 23:59:59" ,00, "4018-05-31 23:59:59" , 0x9A95A0369F30F8DB11E46684B4F0A05F, "401 31-05-08 23:59:59"

การแสดงแผนแบบสอบถามแบบกราฟิก:

ข้อความคำขอเหมือนกัน แผนการดำเนินการแตกต่างกันอย่างสิ้นเชิง จะเกิดอะไรขึ้น? ฉันผิดเกี่ยวกับสถิติใน SQl แต่ระหว่างการทำงานและสำเนาของฐานข้อมูลจะเหมือนกัน และสถิติจะถูกเก็บไว้ในฐานข้อมูลสำหรับแต่ละตาราง:

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

DBCC ฟลัชโปรซินDB(< database_id >)

ที่ไหน< database_id >เป็นตัวระบุฐานข้อมูล หากต้องการค้นหา ID ฐานข้อมูล คุณต้องเรียกใช้สคริปต์

เลือกชื่อ,database_id จาก sys ฐานข้อมูล

มันจะส่งคืนรายการฐานข้อมูลและตัวระบุให้เรา

เราได้รับร่องรอยอีกครั้ง:

การแสดงแผนแบบสอบถามด้วยข้อความ:

การแสดงแผนแบบสอบถามแบบกราฟิก:

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

ขอขอบคุณสำหรับความสนใจของคุณ!

บทความนี้ช่วยคุณได้หรือไม่?

6 คำตอบ

มีหลายวิธีในการขอรับแผนปฏิบัติการ ซึ่งจะขึ้นอยู่กับสถานการณ์ของคุณ โดยทั่วไป คุณสามารถใช้ SQL Server Management Studio เพื่อขอรับแผนได้ อย่างไรก็ตาม หากด้วยเหตุผลบางประการที่คุณไม่สามารถเรียกใช้การสืบค้นใน SQL Server Management Studio ได้ คุณอาจพบว่าการขอรับแผนผ่าน SQL Server Profiler หรือโดยการตรวจสอบแผนนั้นมีประโยชน์ แคช

วิธีที่ 1 - การใช้ Studio จัดการเซิร์ฟเวอร์ SQL

SQL Server มีคุณสมบัติเรียบร้อยบางประการที่ทำให้การรวบรวมแผนการดำเนินการเป็นเรื่องง่าย เพียงตรวจสอบให้แน่ใจว่าได้เลือกรายการเมนู "รวมแผนการดำเนินการตามจริง" (ที่พบในเมนูแบบสอบถาม) แล้ว และจะเรียกใช้แผนของคุณตามปกติ

หากคุณกำลังพยายามรับแผนการดำเนินการสำหรับคำสั่งใน Stored Procedure คุณจะต้องดำเนินการตาม Stored Procedure เช่นนี้:

ดำเนินการ p_ตัวอย่าง 42

เมื่อการสอบถามของคุณเสร็จสมบูรณ์ คุณจะเห็นแท็บแผนการดำเนินการเพิ่มเติมปรากฏในบานหน้าต่างผลลัพธ์ หากคุณดำเนินการอนุมัติหลายครั้ง คุณอาจเห็นแผนจำนวนมากแสดงในแท็บนี้

ที่นี่คุณสามารถตรวจสอบแผนการดำเนินการใน SQL Server Management Studio หรือคลิกขวาที่แผนแล้วเลือก "บันทึกแผนการดำเนินการเป็น..." เพื่อบันทึกแผนลงในไฟล์ XML

วิธีที่ 2 - การใช้ตัวเลือก SHOWPLAN

วิธีนี้คล้ายกับวิธีที่ 1 มาก (นี่คือสิ่งที่ SQL Server Management Studio ทำภายใน) อย่างไรก็ตามฉันได้รวมไว้เพื่อความสมบูรณ์หรือหากคุณไม่มี SQL Server Management Studio ที่พร้อมใช้งาน

ก่อนที่จะดำเนินการค้นหา ให้รัน หนึ่งตัวดำเนินการดังต่อไปนี้ คำสั่งจะต้องเป็นคำสั่งเดียวในแพ็คเกจ เช่น คุณไม่สามารถดำเนินการคำสั่งอื่นในเวลาเดียวกัน:

ตั้งค่า SHOWPLAN_TEXT บน SET SHOWPLAN_ALL บน SET SHOWPLAN_XML บน SET STATISTICS PROFILE บน SET STATISTICS XML ON - เป็นตัวเลือกที่แนะนำเพื่อใช้

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

เมื่อเสร็จแล้ว คุณสามารถปิดใช้งานตัวเลือกนี้ได้โดยใช้คำสั่งต่อไปนี้:

ชุด<

การเปรียบเทียบรูปแบบแผนปฏิบัติการ

หากคุณมีความต้องการที่ชัดเจน ฉันขอแนะนำให้ใช้ตัวเลือก STATISTICS XML ตัวเลือกนี้เทียบเท่ากับตัวเลือก "รวมแผนการดำเนินการตามจริง" ใน SQL Server Management Studio และให้ข้อมูลมากที่สุดในรูปแบบที่มีประโยชน์ที่สุด

  • SHOWPLAN_TEXT - แสดงแผนการดำเนินการโดยประมาณตามข้อความพื้นฐานโดยไม่ต้องดำเนินการค้นหา
  • SHOWPLAN_ALL - แสดงแผนการดำเนินการตามข้อความโดยประมาณพร้อมประมาณการต้นทุนโดยไม่ต้องดำเนินการค้นหา
  • SHOWPLAN_XML - แสดงแผนการดำเนินการโดยประมาณตาม XML พร้อมประมาณการต้นทุนโดยไม่ต้องดำเนินการค้นหา ซึ่งเทียบเท่ากับตัวเลือก "แสดงแผนการดำเนินการตัวอย่าง..." ใน SQL Server Management Studio
  • โปรไฟล์สถิติ - ดำเนินการค้นหาและแสดงแผนการดำเนินการจริงตามข้อความ
  • STATISTICS XML - ดำเนินการค้นหาและแสดงแผนการดำเนินการจริงตาม XML ซึ่งเทียบเท่ากับตัวเลือก "รวมแผนการดำเนินการตามจริง" ใน SQL Server Management Studio

วิธีที่ 3 - การใช้ SQL Server Profiler

หากคุณไม่สามารถเรียกใช้แบบสอบถามได้โดยตรง (หรือการสืบค้นของคุณไม่ทำงานช้าเมื่อคุณเรียกใช้โดยตรง - โปรดจำไว้ว่าเราต้องการให้แผนการสืบค้นทำงานได้ไม่ดี) คุณสามารถบันทึกแผนได้โดยใช้ SQL Server Profiler แนวคิดคือการเรียกใช้แบบสอบถามของคุณในขณะที่การติดตามที่รวบรวมเหตุการณ์ "Showplan" รายการใดรายการหนึ่งกำลังทำงานอยู่

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

  • เปิด SQL Server Profiler และสร้างการติดตามใหม่ซึ่งเชื่อมต่อกับฐานข้อมูลที่ต้องการที่คุณต้องการบันทึกการติดตาม
  • บนแท็บ การเลือกเหตุการณ์ ให้เลือกช่องทำเครื่องหมาย แสดงเหตุการณ์ทั้งหมด ทำเครื่องหมายที่ ประสิทธิภาพ -> บรรทัด Showplan XML และดำเนินการติดตาม
  • ในขณะที่การติดตามกำลังทำงานอยู่ ให้ทำทุกสิ่งที่คุณต้องทำเพื่อให้การสืบค้นทำงานช้า
  • รอจนกว่าคำขอจะเสร็จสิ้นและการติดตามหยุดลง
  • หากต้องการบันทึกการติดตาม ให้คลิกขวาที่แผน xml ในโปรไฟล์ SQL Server และเลือก "แยกข้อมูลเหตุการณ์..." เพื่อบันทึกแผนเป็นไฟล์ XML

แผนที่คุณได้รับเทียบเท่ากับตัวเลือก "รวมแผนการดำเนินการตามจริง" ใน SQL Server Management Studio

วิธีที่ 4 - การตรวจสอบแคชแบบสอบถาม

หากคุณไม่สามารถเรียกใช้การสืบค้นได้โดยตรง และคุณยังไม่สามารถบันทึกการติดตามตัวสร้างโปรไฟล์ได้ คุณก็ควรจะสามารถรับแผนโดยประมาณได้โดยการตรวจสอบแผนแคชของการสืบค้น SQL

เราตรวจสอบแคชแผนโดยการสอบถาม SQL Server DMV ด้านล่างนี้เป็นแบบสอบถามพื้นฐานที่จะแสดงรายการแผนแบบสอบถามที่แคชไว้ทั้งหมด (เป็น xml) พร้อมด้วยข้อความ SQL ในฐานข้อมูลส่วนใหญ่ คุณจะต้องเพิ่มเงื่อนไขตัวกรองเพิ่มเติมเพื่อกรองผลลัพธ์ตามแผนที่คุณสนใจ

เลือก UseCounts, Cacheobjtype, Objtype, TEXT, query_plan จาก sys.dm_exec_cached_plans CROSS ใช้ sys.dm_exec_sql_text (plan_handle) CROSS ใช้ sys.dm_exec_query_plan (plan_handle)

เรียกใช้แบบสอบถามนี้และคลิกที่แผน XML เพื่อเปิดแผนในหน้าต่างใหม่ - คลิกขวาและเลือก "บันทึกแผนการดำเนินการเป็น..." เพื่อบันทึกแผนลงในไฟล์ในรูปแบบ XML

หมายเหตุ:

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

คุณไม่สามารถยอมรับแผนการดำเนินการสำหรับขั้นตอนการจัดเก็บที่เข้ารหัสได้

แผนการดำเนินการ "จริง" และ "โดยประมาณ"

แผนการดำเนินการจริงคือแผนที่ที่ SQL Server ดำเนินการแบบสอบถามจริง ในขณะที่แผนการดำเนินการโดยประมาณ SQL Server ทำงานกับสิ่งที่สามารถทำได้โดยไม่ต้องดำเนินการแบบสอบถาม แม้ว่าในเชิงตรรกะแล้ว แผนการดำเนินการจริงจะมีประโยชน์มากกว่ามาก เนื่องจากมีข้อมูลเพิ่มเติมและสถิติเกี่ยวกับสิ่งที่เกิดขึ้นจริงเมื่อมีการดำเนินการแบบสอบถาม นี่เป็นสิ่งสำคัญเมื่อวินิจฉัยปัญหาเมื่อการประเมินเซิร์ฟเวอร์ SQL ถูกปิดใช้งาน (เช่น เมื่อสถิติล้าสมัย)

จะตีความแผนการดำเนินการแบบสอบถามได้อย่างไร?

นี่เป็นหัวข้อที่คุ้มค่าเพียงพอสำหรับหนังสือฟรี

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

ประกาศ @TraceID INT EXEC StartCapture @@SPID, @TraceID เอาต์พุต EXEC sp_help "sys.objects" /*<-- Call your stored proc of interest here.*/ EXEC StopCapture @TraceID

เครื่องมือที่ฉันชื่นชอบในการรับและวิเคราะห์เชิงลึกของแผนการดำเนินการค้นหาคือ Explorer แผนยาม SQL. สะดวก ใช้งานง่าย และสมบูรณ์สำหรับการวิเคราะห์โดยละเอียดและการแสดงแผนการดำเนินการมากกว่า SSMS

นี่คือหน้าจอตัวอย่างเพื่อให้คุณเข้าใจว่าเครื่องมือมีฟังก์ชันอะไรบ้าง:

นี่เป็นเพียงหนึ่งในมุมมองที่มีอยู่ในเครื่องมือ สังเกตชุดแท็บที่ด้านล่างของหน้าต่างแอปพลิเคชันที่ให้คุณรับมุมมองแผนการดำเนินการประเภทต่างๆ และข้อมูลเพิ่มเติมที่เป็นประโยชน์

นอกจากนี้ ฉันไม่ได้สังเกตเห็นข้อจำกัดใด ๆ ในเวอร์ชันฟรีที่ทำให้คุณไม่สามารถใช้งานมันได้ทุกวันหรือบังคับให้คุณซื้อเวอร์ชัน Pro ในที่สุด ดังนั้น หากคุณต้องการใช้เวอร์ชันฟรี ก็ไม่มีอะไรต้องห้าม

นอกจากวิธีการที่อธิบายไว้ในคำตอบก่อนหน้านี้ คุณยังสามารถใช้โปรแกรมดูแผนการดำเนินการฟรีและเครื่องมือเพิ่มประสิทธิภาพคิวรี ApexSQL Plan (ซึ่งฉันเพิ่งเจอ)

คุณสามารถติดตั้งและรวมแผน ApexSQL เข้ากับ SQL Server Management Studio ได้ ดังนั้นจึงสามารถดูแผนการดำเนินการได้โดยตรงจาก SSMS

ดูแผนการดำเนินการที่คาดการณ์ไว้ในแผน ApexSQL

  • คลิกปุ่ม คำขอใหม่ใน SSMS และวางข้อความค้นหาลงในกล่องข้อความค้นหา คลิกขวาและเลือก "แสดงแผนการดำเนินการตัวอย่าง" จากเมนูบริบท

  1. แผนภาพแผนการดำเนินการจะแสดงแท็บการวางแผนการดำเนินการในส่วนผลลัพธ์ จากนั้นคลิกขวาที่แผนการดำเนินการและเลือกตัวเลือก "เปิดในแผน ApexSQL" จากเมนูบริบท

  1. แผนการดำเนินการโดยประมาณจะเปิดขึ้นในแผน ApexSQL และสามารถวิเคราะห์เพื่อเพิ่มประสิทธิภาพการสืบค้นได้

การดูแผนการดำเนินการตามจริงในแผน ApexSQL

หากต้องการดูแผนการดำเนินการสืบค้นจริง ให้ไปที่ขั้นตอนที่สองที่กล่าวถึงก่อนหน้านี้ แต่ตอนนี้ เมื่อแผนโดยประมาณปรากฏขึ้น ให้คลิกปุ่ม "จริง" บนแถบริบบิ้นหลักในแผน ApexSQL

หลังจากคลิกปุ่มจริง แผนการดำเนินการจริงจะแสดงพร้อมตัวอย่างโดยละเอียดของพารามิเตอร์ต้นทุน พร้อมด้วยข้อมูลแผนการดำเนินการอื่นๆ

ข้อมูลเพิ่มเติมเกี่ยวกับการดูแผนการดำเนินการสามารถดูได้จากลิงก์นี้

สามารถรับแผนแบบสอบถามได้จากเซสชันเหตุการณ์ขยายผ่านเหตุการณ์ query_post_execution_showplan นี่คือตัวอย่างเซสชัน XEvent:

/* สร้างผ่านเทมเพลต "การติดตามรายละเอียดแบบสอบถาม" */ สร้างเซสชันเหตุการณ์บนเซิร์ฟเวอร์ เพิ่มเหตุการณ์ sqlserver.query_post_execution_showplan (ACTION (package0.event_sequence, sqlserver.plan_handle, sqlserver.query_hash, sqlserver.query_plan_hash, sqlserver.session_id, sqlserver.sql_text, sqlserver.tsql_ frame, sqlserver.tsql_stack)) / * ลบเหตุการณ์ใด ๆ ต่อไปนี้ (หรือรวมเหตุการณ์เพิ่มเติม) ตามต้องการ */ เพิ่มเหตุการณ์ sqlserver.error_reported (ACTION (package0.event_sequence, sqlserver.client_app_name, sqlserver.database_id, sqlserver.plan_handle, sqlserver.query_hash, sqlserver.query_plan_hash, sqlserver.session_id, sqlserver.sql_text, sqlserver.t sql_frame, sqlserver.tsql_stack ) โดยที่ (.(.,(4)) และ .(.,(0)))) เพิ่มเหตุการณ์ sqlserver.module_end(SET collect_statement=(1) ACTION(package0.event_sequence,sqlserver.client_app_name,sqlserver.database_id,sqlserver . plan_handle,sqlserver.query_hash,sqlserver.query_plan_hash,sqlserver.session_id,sqlserver.sql_text,sqlserver.tsql_frame,sqlserver.tsql_stack) WHERE (.(.,(4)) และ .(.,(0))) เพิ่มเหตุการณ์ sqlserver.rpc_completed (ACTION (package0.event_sequence, sqlserver.client_app_name, sqlserver.database_id, sqlserver.plan_handle, sqlserver.query_hash, sqlserver.query_plan_hash, sqlserver.session_id, sqlserver.sql_text, sqlserver.tsql_ frame, sqlserver.tsql_stack) โดยที่ (. ( .,(4)) และ .(.,(0))) เพิ่มเหตุการณ์ sqlserver.sp_statement_completed(SET collect_object_name=(1) ACTION(package0.event_sequence,sqlserver.client_app_name,sqlserver.database_id,sqlserver.plan_handle,sqlserver query_hash, sqlserver.query_plan_hash, sqlserver.session_id, sqlserver.sql_text, sqlserver.tsql_frame, sqlserver.tsql_stack) WHERE (.(.,(4)) และ .(.,(0)))) เพิ่มเหตุการณ์ sqlserver.sql_batch_completed( การดำเนินการ (package0.event_sequence, sqlserver.client_app_name, sqlserver.database_id, sqlserver.plan_handle, sqlserver.query_hash, sqlserver.query_plan_hash, sqlserver.session_id, sqlserver.sql_text, sqlserver.tsql_frame, sqlserver.tsql_st ack) WHERE (.(.,( 4 )) และ .(.,(0)))) เพิ่มเหตุการณ์ sqlserver.sql_statement_completed(ACTION(package0.event_sequence,sqlserver.client_app_name,sqlserver.database_id,sqlserver.plan_handle,sqlserver.query_hash,sqlserver.query_plan_hash,sqlserver.session_id , sqlserver.sql_text,sqlserver.tsql_frame,sqlserver.tsql_stack) WHERE (.(.,(4)) AND .(.,(0))) เพิ่มแพ็คเกจเป้าหมาย 0.ring_buffer ด้วย (MAX_MEMORY=4096 KB,EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS,MAX_DISPATCH_L ATENCY =30 วินาที,MAX_EVENT_SIZE=0 KB,MEMORY_PARTITION_MODE=NONE,TRACK_CAUSALITY=ON,STARTUP_STATE=OFF) GO

เมื่อสร้างเซสชันแล้ว (ใน SSMS) ให้ไปที่ Object Browser และไปที่ Manage | กิจกรรมขยาย | เซสชัน คลิกขวาที่เซสชัน "GetExecutionPlan" และเรียกใช้ คลิกขวาแล้วเลือก "ดูข้อมูลสด"

จากนั้นเปิดหน้าต่างแบบสอบถามใหม่และเรียกใช้แบบสอบถามอย่างน้อยหนึ่งรายการ นี่คือหนึ่งสำหรับ AdventureWorks:

ใช้ AdventureWorks; ไปเลือก p.Name AS ProductName, NonDiscountSales = (OrderQty * UnitPrice), Discounts = ((OrderQty * UnitPrice) * UnitPriceDiscount) จากการผลิต Product AS p INNER JOIN Sales.SalesOrderDetail AS sod ON p.ProductID = sod.ProductID ORDER BY ชื่อผลิตภัณฑ์ DESC; ไป

หลังจากผ่านไปหนึ่งหรือสองนาที คุณจะเห็นผลลัพธ์บางส่วนในแท็บ "GetExecutionPlan: Live Data" เลือกหนึ่งในเหตุการณ์ query_post_execution_showplan ในกริด จากนั้นคลิกแท็บ แผนการสืบค้น ด้านล่างของกริด มันควรมีลักษณะดังนี้:

แก้ไข: รหัส XEvent และภาพหน้าจอถูกสร้างขึ้นจาก SQL/SSMS 2012 w/SP2 หากคุณใช้ SQL 2008/R2 คุณสามารถตั้งค่าสคริปต์เพื่อรันได้ แต่เวอร์ชันนี้ไม่มี GUI ดังนั้นคุณจะต้องแตกไฟล์ showplan XML บันทึกเป็นไฟล์ *.sqlplan และเปิดใน SSMS มันยุ่งยาก. XEvents ไม่มีอยู่ใน SQL 2005 หรือรุ่นก่อนหน้า ดังนั้น หากคุณไม่ได้ใช้ SQL 2012 หรือใหม่กว่า ฉันขอแนะนำคำตอบอื่นที่โพสต์ไว้ที่นี่เป็นอย่างยิ่ง

แบ่งปัน

การเพิ่มประสิทธิภาพแบบสอบถามใน SQL Server 2005, สถิติฐานข้อมูล SQL Server 2005, สร้างสถิติ, อัปเดตสถิติ, ตั้งค่าหมายเลขเป็น, แผนการดำเนินการแบบสอบถาม, จำนวนการอ่านเชิงตรรกะ, คำแนะนำของเครื่องมือเพิ่มประสิทธิภาพ, MAXDOP, ปรับให้เหมาะสมสำหรับ, แผนการดำเนินการบทช่วยสอน (คู่มือแผน), sp_create_plan_guide

หากวิธีการเพิ่มประสิทธิภาพอื่น ๆ ทั้งหมดหมดลงแล้ว นักพัฒนาและผู้ดูแลระบบ SQL Server จะมีการสำรองครั้งสุดท้ายไว้ใช้ - ปรับการดำเนินการของแบบสอบถามแต่ละรายการให้เหมาะสม ตัวอย่างเช่น หากงานของคุณต้องการเร่งการสร้างรายงานเฉพาะเจาะจง คุณสามารถวิเคราะห์แบบสอบถามที่ใช้ในการสร้างรายงานนี้ และลองเปลี่ยนแผนหากไม่เหมาะสมที่สุด

ผู้เชี่ยวชาญหลายคนมีทัศนคติที่ไม่ชัดเจนต่อการเพิ่มประสิทธิภาพคิวรี ในอีกด้านหนึ่ง การทำงานของโมดูลซอฟต์แวร์ Query Optimizer ซึ่งสร้างแผนการดำเนินการแบบสอบถาม ทำให้เกิดการวิพากษ์วิจารณ์อย่างยุติธรรมมากมายทั้งใน SQL Server 2000 และ SQL Server 2005 Query Optimizer มักจะเลือกไม่ใช่แผนการดำเนินการแบบสอบถามที่เหมาะสมที่สุดและในบางสถานการณ์จะสูญเสีย ไปยังโมดูลที่คล้ายกันจาก Oracle และ Informix ในทางกลับกัน การเพิ่มประสิทธิภาพแบบสอบถามด้วยตนเองเป็นกระบวนการที่ต้องใช้แรงงานมาก คุณสามารถใช้เวลามากมายในการเพิ่มประสิทธิภาพดังกล่าวและท้ายที่สุดพบว่าไม่มีสิ่งใดได้รับการปรับให้เหมาะสม: แผนการที่เสนอครั้งแรกโดย Query Optimizer กลายเป็นแผนที่เหมาะสมที่สุด (สิ่งนี้เกิดขึ้นในกรณีส่วนใหญ่) นอกจากนี้ อาจเกิดขึ้นได้ว่าแผนการดำเนินการแบบสอบถามที่คุณสร้างด้วยตนเองหลังจากผ่านไประยะหนึ่ง (หลังจากเพิ่มข้อมูลใหม่ลงในฐานข้อมูล) จะกลายเป็นประสิทธิภาพไม่ดีและจะลดประสิทธิภาพเมื่อดำเนินการค้นหา

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

สถิติ- นี่คือข้อมูลบริการพิเศษเกี่ยวกับการกระจายข้อมูลในคอลัมน์ของตาราง ตัวอย่างเช่น ลองจินตนาการว่าข้อความค้นหากำลังดำเนินการซึ่งควรจะส่งคืน Ivanovs ทั้งหมดที่อาศัยอยู่ในเมืองเซนต์ปีเตอร์สเบิร์ก สมมติว่า 90% ของระเบียนในตารางนี้มีค่าเท่ากันในคอลัมน์ เมือง - "เซนต์ปีเตอร์สเบิร์ก". แน่นอนจากมุมมองของการดำเนินการค้นหาจะทำกำไรได้มากกว่าในการเลือก Ivanov ทั้งหมดในตาราง (เห็นได้ชัดว่าพวกเขาจะไม่ใช่ 90%) จากนั้นตรวจสอบค่าของคอลัมน์ เมืองสำหรับแต่ละบันทึกที่เลือก อย่างไรก็ตาม หากต้องการทราบว่าค่าในคอลัมน์มีการกระจายอย่างไร คุณต้องเรียกใช้คิวรีก่อน ดังนั้น SQL Server จะเริ่มต้นการดำเนินการของแบบสอบถามดังกล่าวอย่างอิสระ จากนั้นจึงจัดเก็บข้อมูลเกี่ยวกับการกระจายข้อมูล (ซึ่งเรียกว่าสถิติ) ไว้ในตารางบริการฐานข้อมูล

สำหรับฐานข้อมูล SQL Server 2005 การตั้งค่าเริ่มต้นคือ AUTO_CREATE_STATISTICSและ AUTO_UPDATE_สถิติ. ในกรณีนี้ สถิติสำหรับคอลัมน์ฐานข้อมูลจะถูกสร้างขึ้นและอัปเดตโดยอัตโนมัติ สำหรับฐานข้อมูลที่ใหญ่ที่สุดและสำคัญที่สุด การดำเนินการเพื่อสร้างและอัพเดตสถิติอาจรบกวนประสบการณ์ผู้ใช้ในปัจจุบัน ดังนั้นสำหรับฐานข้อมูลดังกล่าว บางครั้งพารามิเตอร์เหล่านี้จึงถูกปิดใช้งาน และการดำเนินการเพื่อสร้างและอัปเดตสถิติจะดำเนินการด้วยตนเองในเวลากลางคืน คำสั่งที่ใช้สำหรับสิ่งนี้คือ สร้างสถิติและ อัปเดตสถิติ.

ตอนนี้เรามาพูดถึงการเพิ่มประสิทธิภาพแบบสอบถาม

สิ่งแรกที่ต้องทำคือค้นหาคำค้นหาที่เกี่ยวข้องกับการปรับให้เหมาะสมเป็นหลัก วิธีที่ง่ายที่สุดในการทำเช่นนี้คือด้วยความช่วยเหลือของผู้สร้างโปรไฟล์ การตั้งค่าตัวกรองสำหรับระยะเวลาของคำขอ (filter ระยะเวลาในหน้าต่าง แก้ไขกรอง(แก้ไขตัวกรอง) ซึ่งสามารถเปิดได้โดยใช้ปุ่ม คอลัมน์ตัวกรองบนแท็บ กิจกรรมการคัดเลือกหน้าต่างคุณสมบัติเซสชันการติดตาม) ตัวอย่างเช่น ตัวเลือกสำหรับการเพิ่มประสิทธิภาพอาจรวมข้อความค้นหาที่มีเวลาดำเนินการมากกว่า 5 วินาที คุณยังสามารถใช้ข้อมูลเคียวรีที่มาจาก Database Tuning Advisor

ถัดไป คุณต้องตรวจสอบว่ามีการตั้งค่าพารามิเตอร์สำหรับการเชื่อมต่อ ขั้นตอนการจัดเก็บ และฟังก์ชันของคุณหรือไม่ จำนวน. คุณสามารถติดตั้งได้โดยใช้คำสั่ง ตั้งหมายเลขเป็น. เมื่อตั้งค่าพารามิเตอร์นี้ ประการแรก การส่งคืนจากเซิร์ฟเวอร์และการแสดงข้อมูลเกี่ยวกับจำนวนแถวในผลลัพธ์การสืบค้นจะถูกปิดใช้งาน (เช่น แถว "ไม่มีแถวที่ได้รับผลกระทบ"บนแท็บ ข้อความ(ข้อความ C) สำหรับการทำงานกับโค้ดเมื่อดำเนินการตามคำขอใน Management Studio) ประการที่สอง การส่งข้อความเซิร์ฟเวอร์พิเศษถูกปิดใช้งาน DONE_IN_PROCซึ่งจะถูกส่งกลับตามค่าเริ่มต้นสำหรับขั้นตอนการจัดเก็บแต่ละขั้นตอน เมื่อเรียกกระบวนงานที่เก็บไว้ส่วนใหญ่ คุณเพียงต้องการผลลัพธ์ของการดำเนินการเท่านั้น และไม่มีใครสนใจจำนวนแถวที่ประมวลผลในแต่ละขั้นตอน ดังนั้นการตั้งค่าพารามิเตอร์ จำนวนสำหรับขั้นตอนการจัดเก็บสามารถปรับปรุงประสิทธิภาพได้อย่างจริงจัง ความเร็วการดำเนินการของการสืบค้นปกติก็เพิ่มขึ้นเช่นกัน แต่ในระดับที่น้อยกว่า (มากถึง 10%)

หลังจากนี้ คุณสามารถเริ่มต้นทำงานกับแผนการดำเนินการแบบสอบถามได้

วิธีที่ง่ายที่สุดในการดูแผนการดำเนินการแบบสอบถามคือจาก SQL Server Management Studio เพื่อรับข้อมูลเกี่ยวกับแผนการดำเนินการแบบสอบถามที่คาดไว้ คุณสามารถใช้เมนูได้ แบบสอบถาม(แบบสอบถาม) เลือกคำสั่ง แสดงโดยประมาณการดำเนินการวางแผน(แสดงแผนการดำเนินการที่คาดหวัง) หากคุณต้องการทราบแผนจริงสำหรับการดำเนินการค้นหา คุณสามารถตั้งค่าพารามิเตอร์ในเมนูเดียวกันก่อนดำเนินการได้ รวมแท้จริงการดำเนินการวางแผน(รวมแผนการดำเนินการจริง) ในกรณีนี้ หลังจากดำเนินการค้นหาแล้ว แท็บอื่นจะปรากฏขึ้นในหน้าต่างผลลัพธ์ใน SQL Server Management Studio การดำเนินการวางแผน(Execution Plan) ซึ่งจะแสดงแผนการดำเนินการสืบค้นจริง เมื่อคุณวางเมาส์เหนือขั้นตอนใด ๆ คุณจะได้รับข้อมูลเพิ่มเติมเกี่ยวกับขั้นตอนนั้น (รูปที่ 11.15)

ข้าว. 11.15.แผนการดำเนินการแบบสอบถามใน SQL Server Management Studio

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

ขั้นแรก ในหน้าต่าง Management Studio ให้รันคำสั่ง ตั้งค่าสถิติ IO เปิด. ด้วยเหตุนี้ หลังจากดำเนินการตามคำขอแต่ละครั้ง ข้อมูลเพิ่มเติมจะแสดงขึ้น ในนั้นเราสนใจค่าของพารามิเตอร์เพียงตัวเดียว - การอ่านเชิงตรรกะ. พารามิเตอร์นี้หมายถึงจำนวนการอ่านแบบลอจิคัลเมื่อดำเนินการค้นหา เช่น จำนวนการอ่านที่ต้องดำเนินการเมื่อดำเนินการค้นหาที่กำหนดโดยไม่คำนึงถึงอิทธิพลของแคช (จำนวนการอ่านจากทั้งแคชและดิสก์) นี่คือพารามิเตอร์ที่สำคัญที่สุด จำนวนการอ่านทางกายภาพ (อ่านจากดิสก์เท่านั้น) ไม่ใช่ข้อมูลที่เป็นตัวแทนมากนัก เนื่องจากขึ้นอยู่กับว่ามีการเข้าถึงตารางเหล่านี้ก่อนหน้านี้หรือไม่ สถิติเวลายังแปรผันและขึ้นอยู่กับการดำเนินการอื่นที่เซิร์ฟเวอร์กำลังดำเนินการในขณะนั้น แต่จำนวนการอ่านเชิงตรรกะเป็นตัวบ่งชี้ที่เป็นกลางที่สุด ซึ่งได้รับอิทธิพลจากปัจจัยเพิ่มเติมน้อยที่สุด

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

มีคำแนะนำเกี่ยวกับเครื่องมือเพิ่มประสิทธิภาพมากมายใน SQL Server 2005 คุณสามารถอ่านข้อมูลเกี่ยวกับพวกเขาได้ใน Books Online (ในรายการบนแท็บ ดัชนี(ดัชนี) จะต้องเลือก แบบสอบถามคำแนะนำ [SQLเซิร์ฟเวอร์ ](คำแนะนำแบบสอบถาม) เข้าร่วมคำแนะนำ(เข้าร่วมคำแนะนำ) หรือ โต๊ะคำแนะนำ [SQLเซิร์ฟเวอร์ ](คำแนะนำตาราง)) คำแนะนำที่ใช้บ่อยที่สุดคือ:

ถาม โนล็อค, โรว์ล็อค, แพ็กล็อค, ทาบล็อก, โฮลล็อค, READCOMMITTEDLOCK, อัพล็อค, เอ็กซ์ล็อค- คำแนะนำเหล่านี้ใช้เพื่อจัดการล็อค (ดูหัวข้อ 11.5.7);

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

ถาม บังคับสั่ง- การเข้าร่วมตารางเมื่อดำเนินการค้นหาจะดำเนินการตามลำดับที่ตารางเหล่านี้แสดงอยู่ในแบบสอบถาม

ถาม MAXDOP(จากระดับสูงสุดของความขนาน - ระดับสูงสุดของการทำให้คำขอขนานกัน) - เมื่อใช้คำแนะนำนี้ จำนวนโปรเซสเซอร์สูงสุดที่สามารถใช้เพื่อดำเนินการตามคำขอจะถูกระบุ โดยทั่วไปคำใบ้นี้จะใช้ในสองสถานการณ์:

· เมื่อเกิดจากการสลับระหว่างโปรเซสเซอร์ ( บริบทการสลับ) ความเร็วของการดำเนินการสืบค้นจะลดลงอย่างมาก ลักษณะการทำงานนี้เป็นเรื่องปกติสำหรับ SQL Server 2000 บนระบบมัลติโปรเซสเซอร์

· เมื่อคุณต้องการให้คำขอจำนวนมากมีผลกระทบน้อยที่สุดต่อประสบการณ์ผู้ใช้ปัจจุบัน

ถาม เพิ่มประสิทธิภาพเพื่อ- คำแนะนำนี้ช่วยให้คุณระบุว่าคำขอได้รับการปรับให้เหมาะสมสำหรับค่าเฉพาะของพารามิเตอร์ที่ส่งไป (ตัวอย่างเช่น สำหรับค่าตัวกรองสำหรับ ที่ไหน);

ถาม ใช้แผน- นี่คือโอกาสที่ทรงพลังที่สุด เมื่อใช้คำใบ้ดังกล่าว คุณสามารถกำหนดแผนการดำเนินการคิวรีได้อย่างชัดเจนโดยส่งแผนเป็นค่าสตริงในรูปแบบ XML คำใบ้ ใช้แผนปรากฏใน SQL Server 2005 เท่านั้น (ในเวอร์ชันก่อนหน้านี้มีความเป็นไปได้ที่จะกำหนดแผนการดำเนินการสืบค้นอย่างชัดเจน แต่ทำได้โดยใช้วิธีอื่น) สามารถเขียนแผนในรูปแบบ XML ได้ด้วยตนเองหรือสามารถสร้างได้โดยอัตโนมัติ (เช่น โดยการคลิกขวาบนหน้าจอกราฟิกพร้อมกับแผนปฏิบัติการที่แสดงในรูปที่ 11.15 และเลือกคำสั่งในเมนูบริบท บันทึกการดำเนินการวางแผนเช่น(บันทึกแผนปฏิบัติการเป็น))

SQL Server 2005 แนะนำคุณลักษณะใหม่ที่สำคัญที่ช่วยให้คุณสามารถเปลี่ยนแผนการดำเนินการแบบสอบถามด้วยตนเอง โดยไม่ต้องแก้ไขข้อความแบบสอบถาม บ่อยครั้งเกิดขึ้นว่ารหัสคำขอไม่สามารถเปลี่ยนแปลงได้: รหัสนั้นเดินสายเข้าไปในรหัสของแอปพลิเคชันที่คอมไพล์แล้ว เพื่อต่อสู้กับปัญหานี้ SQL Server 2005 ได้แนะนำกระบวนงานที่เก็บไว้ sp_create_plan_guide. ช่วยให้คุณสร้างสิ่งที่เรียกว่า คู่มือแผนการดำเนินการ (วางแผนคำแนะนำ) ซึ่งจะนำไปใช้กับคำค้นหาที่ตรงกันโดยอัตโนมัติ

หากคุณกำลังวิเคราะห์การสืบค้นที่ส่งไปยังฐานข้อมูลโดยแอปพลิเคชัน คุณควรคำนึงถึงประเด็นต่อไปนี้ก่อน:

ความถี่ที่การดำเนินการปรากฏในแผนการดำเนินการแบบสอบถาม โต๊ะสแกน(สแกนเต็มตาราง) อาจกลายเป็นว่าการเข้าถึงตารางโดยใช้ดัชนีจะมีประสิทธิภาพมากขึ้น

Q ไม่ว่าจะใช้เคอร์เซอร์ในโค้ดหรือไม่ เคอร์เซอร์นั้นเรียบง่ายมากในแง่ของไวยากรณ์ของโปรแกรม แต่ไม่มีประสิทธิภาพอย่างยิ่งในแง่ของประสิทธิภาพ บ่อยครั้งคุณสามารถหลีกเลี่ยงการใช้เคอร์เซอร์ได้โดยใช้โครงสร้างวากยสัมพันธ์อื่นๆ และได้รับความเร็วเพิ่มขึ้นอย่างมาก

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

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

บางครั้งการสืบค้นจะส่งคืนข้อมูลมากกว่าที่แอปพลิเคชันต้องการจริง ๆ (คอลัมน์หรือแถวเพิ่มเติม) แน่นอนว่าสิ่งนี้ไม่ได้ปรับปรุงประสิทธิภาพการผลิต

หากแอปพลิเคชันส่งคำสั่งไปยังเซิร์ฟเวอร์ ดำเนินการดังนั้นจึงสมเหตุสมผลที่จะคิดถึงการแทนที่ด้วยการเรียกโพรซีเดอร์ที่เก็บไว้ sp_executesql. มีข้อดีด้านประสิทธิภาพมากกว่าคำสั่งปกติ ดำเนินการ;

บางครั้งการปรับปรุงประสิทธิภาพสามารถทำได้โดยการขจัดความจำเป็นในการคอมไพล์ขั้นตอนที่เก็บไว้ใหม่ และสร้างแผนการดำเนินการแบบสอบถามใหม่ คุณต้องใส่ใจกับการใช้พารามิเตอร์ พยายามอย่าผสมคำสั่ง DML และ DDL ในโค้ดของขั้นตอนการจัดเก็บ และตรวจสอบให้แน่ใจว่าพารามิเตอร์การเชื่อมต่อ ตั้งค่า ANSI_DEFAULTS, ตั้งค่า ANSI_NULLS, ตั้งค่า ANSI_PADDING, ตั้งค่า ANSI_WARNINGSและ ตั้งค่า CONCAT_NULL_YIELDS_NULLไม่มีการเปลี่ยนแปลงระหว่างคำขอ (การเปลี่ยนแปลงใด ๆ ในพารามิเตอร์ดังกล่าวจะทำให้แผนการดำเนินการเก่าเป็นโมฆะ) โดยทั่วไป ปัญหาอาจเกิดขึ้นเมื่อพารามิเตอร์เหล่านี้ถูกตั้งค่าในระดับคำขอแต่ละรายการหรือในรหัสขั้นตอนที่เก็บไว้

โปรดทราบว่าไม่ว่าในกรณีใด การสร้างแผนดำเนินการสืบค้นด้วยตนเองและการใช้คำแนะนำเป็นทางเลือกสุดท้ายและควรหลีกเลี่ยงหากเป็นไปได้

แผนการดำเนินการแบบสอบถาม SQLหรือแผนการสืบค้น คือลำดับขั้นตอนหรือคำสั่ง DBMS ที่จำเป็นในการดำเนินการสืบค้น SQL ในแต่ละขั้นตอน การดำเนินการที่เริ่มต้นขั้นตอนการดำเนินการสืบค้น SQL จะดึงแถวของข้อมูลที่สามารถสร้างผลลัพธ์สุดท้ายหรือใช้สำหรับการประมวลผลเพิ่มเติม คำแนะนำแผนการดำเนินการแบบสอบถาม SQL จะแสดงเป็นลำดับของการดำเนินการที่ดำเนินการโดยคำสั่ง DBMS FOR SQL SELECT, INSERT, ลบและ อัปเดต. โดยทั่วไปเนื้อหาของแผนแบบสอบถามจะแสดงในโครงสร้างแบบต้นไม้และมีข้อมูลต่อไปนี้:

  • ลำดับการเชื่อมต่อแหล่งข้อมูล (ตาราง มุมมอง ฯลฯ)
  • วิธีการเข้าถึงแหล่งข้อมูลแต่ละแหล่ง
  • วิธีการเชื่อมต่อแหล่งข้อมูล
  • การดำเนินการจำกัดการเลือกข้อมูล การเรียงลำดับ และการรวมกลุ่ม
  • ต้นทุนและความร้ายแรงของการดำเนินการแต่ละครั้ง
  • การใช้การแบ่งพาร์ติชันและการขนานที่เป็นไปได้ ข้อมูลที่จัดทำโดยแผนการดำเนินการแบบสอบถาม SQL ช่วยให้นักพัฒนาสามารถดูวิธีการและวิธีการที่เครื่องมือเพิ่มประสิทธิภาพเลือกเพื่อดำเนินการ SQL

การตีความแผนการดำเนินการสืบค้น SQL

การแสดงแผนการดำเนินการของการสืบค้น SQL ขึ้นอยู่กับเครื่องมือและเครื่องมือการพัฒนา ซึ่งอาจเป็นส่วนหนึ่งของ DBMS ซึ่งมีการสืบค้นที่น่าสนใจสำหรับการวิเคราะห์ หรือแยกเป็นผลิตภัณฑ์ซอฟต์แวร์เชิงพาณิชย์หรือแจกจ่ายอย่างเสรีซึ่งไม่เกี่ยวข้องโดยตรงกับ DBMS เฉพาะเจาะจง ผู้ผลิต การใช้เครื่องมือการแสดงภาพแผนแบบสอบถามอย่างใดอย่างหนึ่งมักจะไม่ส่งผลกระทบอย่างมีนัยสำคัญต่อการรับรู้สิ่งที่อธิบายแผนแบบสอบถามที่นำเสนอ ปัจจัยที่กำหนดในกระบวนการวิเคราะห์เส้นทางที่เครื่องมือเพิ่มประสิทธิภาพจะใช้เมื่อดำเนินการค้นหาเฉพาะคือความสามารถในการตีความข้อมูลที่แสดงในแผนแบบสอบถามได้อย่างถูกต้อง

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

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

มาดูข้อมูลที่จัดทำโดยแผนดำเนินการสืบค้น SQL กันดีกว่า ตัวอย่างที่ให้มาดำเนินการในสภาพแวดล้อม Oracle DBMS Oracle SQL Developer ถูกใช้เป็นเครื่องมือสำหรับดำเนินการสืบค้นและแสดงแผนการสืบค้น SQL แบบเห็นภาพ ส่วนของแผนการสืบค้น SQL จะแสดงในรูป 10.11.

ฉันว่าฉันปฏิบัติการ

  • 0RDER_ITEMS

PR0DUCT_INF0RMATI0N_PK ข้อมูลผลิตภัณฑ์

เลือกคำสั่ง เรียงลำดับตาม NESTED LOOPS NESTED LOOPS การเข้าถึงตาราง ดัชนีแบบเต็ม การเข้าถึงตารางสแกนที่ไม่ซ้ำใคร การเข้าถึงโดย INDEX ROWID

ข้าว. 10.11. ส่วนของแผนการดำเนินการสืบค้น SQL ในสภาพแวดล้อม Oracle DBMS

การใช้กฎความสัมพันธ์ของการดำเนินการตามแผนแบบสอบถาม เราสามารถกำหนดคำอธิบายที่เป็นทางการได้ดังต่อไปนี้

การดำเนินการ 0 คือรากของแผนผังแผนแบบสอบถาม รูทมีลูกหนึ่งคน: การดำเนินการ 1

ปฏิบัติการ 1 - ปฏิบัติการมีลูกหนึ่งคน: ปฏิบัติการ 2

ปฏิบัติการที่ 2 - ปฏิบัติการมีลูกสองคน: ปฏิบัติการที่ 3 และปฏิบัติการที่ 6

ปฏิบัติการที่ 3 - ปฏิบัติการมีลูกสองคน: ปฏิบัติการที่ 4 และปฏิบัติการที่ 5

ปฏิบัติการที่ 4 - ปฏิบัติการไม่มีลูก

ปฏิบัติการที่ 5 - ปฏิบัติการไม่มีลูก

ปฏิบัติการที่ 6 - ปฏิบัติการไม่มีลูก

การโต้ตอบระหว่างผู้ปกครองและรองระหว่างการดำเนินการตามแผนแบบสอบถามจะแสดงในรูปที่ 1 10.12.

การดำเนินการที่ดำเนินการในแผนการสืบค้นสามารถแบ่งออกเป็นสามประเภท: แบบสแตนด์อโลน การดำเนินการรวมที่ไม่ถูกผูกไว้ และการดำเนินการการรวมที่เชื่อมโยง (รูปที่ 10.13)

อัตโนมัติ

การดำเนินงานไม่เกี่ยวข้อง

การดำเนินงานที่เกี่ยวข้อง

การดำเนินงาน

สมาคม

สมาคม

ข้าว. 10.12.


ข้าว. 10.13.

การดำเนินงานอัตโนมัติ -สิ่งเหล่านี้คือการดำเนินงานที่มีการดำเนินงานย่อยมากที่สุดหนึ่งรายการ

กฎต่อไปนี้ซึ่งดำเนินการด้วยตนเองสามารถกำหนดได้ดังนี้

  • 2. การดำเนินการย่อยแต่ละรายการจะดำเนินการเพียงครั้งเดียวเท่านั้น
  • 3. การดำเนินการย่อยแต่ละรายการจะส่งกลับผลลัพธ์ไปยังการดำเนินการหลัก

ในรูป รูปที่ 10.14 แสดงแผนสำหรับการสืบค้นต่อไปนี้:

เลือก o.order_id ,o.order_status จากคำสั่งซื้อ o เรียงลำดับตาม o.order_status

แบบสอบถามนี้มีการดำเนินการแบบสแตนด์อโลนเท่านั้น

โดยคำนึงถึงกฎสำหรับการดำเนินการอัตโนมัติดังต่อไปนี้ ลำดับการดำเนินการจะเป็นดังนี้

  • 1. ตามกฎของการดำเนินการอัตโนมัติหมายเลข 1 ต่อไปนี้ การดำเนินการที่มี ID = 2 จะถูกดำเนินการก่อน แถวทั้งหมดของตารางคำสั่งซื้อจะถูกอ่านตามลำดับ
  • 2. ถัดไป ดำเนินการด้วย ID = 1 แถวที่ส่งคืนโดยการดำเนินการที่มี ID = 2 จะถูกจัดเรียงตามเงื่อนไขของคำสั่งการเรียงลำดับ ORDER BY
  • 3. ดำเนินการด้วย ID = 0 ชุดข้อมูลผลลัพธ์จะถูกส่งกลับ

การดำเนินงานของสหภาพที่ไม่ผูกมัด

การดำเนินงานของสหภาพที่ไม่ผูกมัดเป็นการดำเนินการที่มีการดำเนินการย่อยอย่างอิสระมากกว่าหนึ่งรายการ ตัวอย่าง: HASH JOIN, MERGE JOIN, INTERSECTION, MINUS, UNION ALL

กฎต่อไปนี้ซึ่งงานการดำเนินการรวมที่ไม่ถูกผูกไว้สามารถกำหนดได้ดังนี้

  • 1. การดำเนินการย่อยจะถูกดำเนินการก่อนการดำเนินการหลัก
  • 2. การดำเนินการย่อยจะดำเนินการตามลำดับ โดยเริ่มจากค่า ID การดำเนินการที่น้อยที่สุดโดยเรียงจากน้อยไปมากของค่าเหล่านี้
  • 3. ก่อนที่การดำเนินการย่อยที่ตามมาแต่ละครั้งจะเริ่มต้น การดำเนินการปัจจุบันจะต้องเสร็จสมบูรณ์อย่างสมบูรณ์
  • 4. การดำเนินการย่อยแต่ละรายการจะดำเนินการเพียงครั้งเดียว โดยไม่คำนึงถึงการดำเนินการย่อยอื่นๆ
  • 5. การดำเนินการย่อยแต่ละรายการจะส่งกลับผลลัพธ์ไปยังการดำเนินการหลัก

ในรูป รูปที่ 10.15 แสดงแผนสำหรับการสืบค้นต่อไปนี้:

เลือก o.order_id จากคำสั่งซื้อ o UNION ALL

เลือก oi.order_id จาก order_items oi

แบบสอบถามนี้มีการดำเนินการรวมที่ไม่ถูกผูกไว้ ยูเนี่ยนทั้งหมด การดำเนินการอีกสองรายการที่เหลือเป็นแบบอัตโนมัติ

ข้าว. 10.15. การดำเนินการเข้าร่วมที่ไม่ถูกผูกไว้ แผนการสืบค้น

1 เลือกข้อความ I

เมื่อพิจารณากฎสำหรับการติดตามการดำเนินการรวมที่ไม่เกี่ยวข้อง ลำดับของการดำเนินการจะเป็นดังนี้

  • 1. ตามกฎข้อ 1 และ 2 ของการดำเนินการรวมที่ไม่เกี่ยวข้องต่อไปนี้ การดำเนินการที่มี ID = 2 จะถูกดำเนินการก่อน แถวทั้งหมดของตารางคำสั่งซื้อจะถูกอ่านตามลำดับ
  • 2. ตามกฎข้อ 5 การดำเนินการที่มี ID = 2 จะส่งคืนแถวของการดำเนินการหลักที่มี ID = 1 ที่อ่านในขั้นตอนที่ 1
  • 3. การดำเนินการด้วย ID = 3 จะเริ่มดำเนินการเฉพาะเมื่อการดำเนินการที่มี ID = 2 สิ้นสุดลง
  • 4. หลังจากเสร็จสิ้นการดำเนินการด้วย ID = 2 การดำเนินการด้วย ID = 3 จะเริ่มต้นขึ้น แถวทั้งหมดของตาราง order_items จะถูกอ่านตามลำดับ
  • 5. ตามกฎข้อ 5 การดำเนินการที่มี ID = 3 จะส่งคืนแถวของการดำเนินการหลักที่มี ID = 1 ที่อ่านในขั้นตอนที่ 4
  • 6. การดำเนินการที่มี ID = 1 จะสร้างชุดผลลัพธ์ของข้อมูลตามข้อมูลที่ได้รับจากการดำเนินการย่อยทั้งหมด (โดยมี ID = 2 และ ID = 3)
  • 7. ดำเนินการด้วย ID = 0 ชุดข้อมูลผลลัพธ์จะถูกส่งกลับ

ดังนั้นจึงสังเกตได้ว่าการดำเนินการรวมที่เป็นอิสระดำเนินการการดำเนินการย่อยตามลำดับ

การดำเนินการเข้าร่วมที่เชื่อมโยง

การดำเนินการเข้าร่วมที่เชื่อมโยง -การดำเนินการเหล่านี้เป็นการดำเนินการที่มีการดำเนินงานย่อยมากกว่าหนึ่งรายการ โดยการดำเนินการอย่างใดอย่างหนึ่งจะควบคุมการดำเนินการของรายการอื่นๆ ตัวอย่าง: ลูปที่ซ้อนกัน อัปเดต

กฎต่อไปนี้ซึ่งงานการดำเนินการการรวมแบบลูกโซ่สามารถกำหนดได้ดังต่อไปนี้

  • 1. การดำเนินการย่อยจะถูกดำเนินการก่อนการดำเนินการหลัก
  • 2. การดำเนินการย่อยที่มีหมายเลขการดำเนินการ (ID) ต่ำสุดจะควบคุมการดำเนินการของการดำเนินการย่อยที่เหลือ
  • 3. การดำเนินการย่อยที่มีการดำเนินการหลักทั่วไปจะถูกดำเนินการโดยเริ่มจากค่าต่ำสุดของรหัสการดำเนินการโดยเรียงจากน้อยไปมากของค่าเหล่านี้ การดำเนินการย่อยที่เหลือจะไม่ถูกดำเนินการตามลำดับ
  • 4. ดำเนินการเฉพาะการดำเนินการลูกแรกเพียงครั้งเดียว การดำเนินการย่อยอื่นๆ ทั้งหมดจะถูกดำเนินการหลายครั้งหรือไม่ดำเนินการเลย

ในรูป รูปที่ 10.16 แสดงแผนสำหรับการสืบค้นต่อไปนี้:

จาก order_items oi คำสั่งซื้อเกี่ยวกับ

โดยที่ o.order_id= oi.order_id

และ oi.product_id>100

และ o.customer_id ระหว่าง 100 ถึง 1,000

แบบสอบถามนี้มีการดำเนินการรวมที่เกี่ยวข้อง NESTED LOOPS

ฉันว่าฉันปฏิบัติการ

เลือกใบแจ้งยอด |

ข้าว. 10.16. การดำเนินการเข้าร่วมที่เชื่อมโยง แผนแบบสอบถาม

เมื่อพิจารณากฎสำหรับการดำเนินการรวมแบบลูกโซ่ ลำดับของการดำเนินการจะเป็นดังนี้

  • 1. ตามกฎข้อ 1 และ 2 ของการดำเนินการรวมแบบลูกโซ่ต่อไปนี้ การดำเนินการด้วย ID = 2 จะต้องดำเนินการก่อน อย่างไรก็ตาม การดำเนินการด้วย 1D = 2 และ 1D = 3 เป็นแบบอัตโนมัติ และตามกฎข้อ 1 ของการดำเนินการแบบอัตโนมัติต่อไปนี้ การดำเนินการด้วย ID = 2 จะดำเนินการก่อน ID = 3 ช่วงดัชนี ORDCUSTOMERIX จะถูกดูตามเงื่อนไข: o รหัสลูกค้าระหว่าง 100 ถึง 1,000
  • 2. การดำเนินการที่มี ID=3 จะส่งคืนการดำเนินการหลัก (ที่มี Ш=2) รายการของตัวระบุแถว Rowld ที่ได้รับในขั้นตอนที่ 1
  • 3. การดำเนินการด้วย ID = 2 อ่านแถวในตารางคำสั่งซื้อซึ่งค่า Rowld ตรงกับรายการค่า Rowld ที่ได้รับในขั้นตอนที่ 2
  • 4. การดำเนินการที่มี ID = 2 จะส่งคืนแถวการอ่านของการดำเนินการหลัก (โดยมี ID = 1)
  • 5. สำหรับแต่ละแถวที่ส่งคืนโดยการดำเนินการที่มี ID = 2 การดำเนินการลูกที่สอง (ที่มี ID = 4) ของการดำเนินการจะถูกดำเนินการ ลูปที่ซ้อนกัน นั่นคือ สำหรับแต่ละแถวที่ส่งคืนโดยการดำเนินการที่มี ID = 2 จะมีการสแกนตาราง order_items ตามลำดับแบบเต็มเพื่อค้นหารายการที่ตรงกันในแอตทริบิวต์การรวม
  • 6. ขั้นตอนที่ 5 ทำซ้ำหลายครั้งตามจำนวนแถวที่ส่งคืนโดยการดำเนินการที่มี ID = 2
  • 7. การดำเนินการที่มี ID = 1 ส่งคืนผลลัพธ์ของการดำเนินการหลัก (โดยมี ID = 0)
  • 8. ดำเนินการด้วย ID = 0 ชุดข้อมูลผลลัพธ์จะถูกส่งกลับ

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

เลือก cust_first_name ลูกค้า_ชื่อ,

COUNT(DISTINCT oi.product_id) เป็น product_qty,

SUM(oi.quantity* oi.unit_price) เป็นยอดรวมจาก oe.orders o ลูกค้าเข้าร่วมภายใน c ON

o.customer_id=c.customer_id

INNER JOIN oe.order_items oi ON o.order_id= oi.order_id จัดกลุ่มตาม cust_first_name

ลำดับการดำเนินการของแผนการสืบค้นนี้แสดงไว้ในรูปที่ 1 10.17.

เลือกคำชี้แจง I

จัดเรียงกลุ่มตาม วายจี

การเข้าถึงตารางเต็ม

สแกนช่วงดัชนี

การเข้าถึงตารางโดยดัชนี ROWIDd

การเข้าถึงตารางเต็ม

ข้าว. 10.17. แผนแบบสอบถาม ลำดับการดำเนินงาน

ให้เราอธิบายแนวทางที่เป็นไปได้ในการตีความแผนการดำเนินการของแบบสอบถาม 80b ที่แสดงในรูปที่ 1 10.17. แนวทางนี้ประกอบด้วยสองขั้นตอนหลัก: การแยกย่อยการดำเนินการออกเป็นบล็อก และการกำหนดลำดับของการดำเนินการ

ในขั้นแรกจำเป็นต้องแยกย่อยการดำเนินการที่ทำออกเป็นบล็อก เมื่อต้องการทำเช่นนี้ เราจะค้นหาการดำเนินการของสหภาพทั้งหมด เช่น การดำเนินงานที่มีการดำเนินงานย่อยมากกว่าหนึ่งรายการ (ในรูปที่ 10.17 คือการดำเนินงาน 2, 3 และ 4) และแยกการดำเนินงานย่อยเหล่านี้ออกเป็นบล็อก ดังตัวอย่างในรูปนี้ 10.17 เราได้รับปฏิบัติการจากสหภาพ 3 ครั้งและปฏิบัติการ 7 ช่วง

ในขั้นตอนที่สองจะมีการกำหนดลำดับการดำเนินการของบล็อกการดำเนินการ ในการดำเนินการนี้ คุณจะต้องใช้กฎสำหรับการดำเนินการต่อไปนี้ตามที่อธิบายไว้ข้างต้น ขอให้เราพิจารณาชุดการพิจารณาเกี่ยวกับการดำเนินการแต่ละครั้งที่เกี่ยวข้องกับหมายเลขประจำตัว (III)

การดำเนินการ Ш = 0 เป็นอิสระและเป็นพาเรนต์ของการดำเนินการ сШ = 1

การดำเนินการ Yu = 1 นั้นเป็นอิสระเช่นกัน เป็นพาเรนต์ของการดำเนินการ W = 2 และถูกดำเนินการก่อนการดำเนินการ Y = 0

การดำเนินการ GO = 2 เป็นการดำเนินการแบบรวมที่ไม่เกี่ยวข้องและเป็นการดำเนินการหลักสำหรับการดำเนินการ Yu = 3, Yu = 8 การดำเนินการ GO = 2 จะดำเนินการก่อนการดำเนินการ GO = 1

การดำเนินการ GO = 3 เป็นการดำเนินการแบบสหภาพที่เชื่อมโยง ซึ่งเป็นการดำเนินการหลักสำหรับการดำเนินการ GO = 4, GO = 7 การดำเนินการ GO = 3 จะดำเนินการก่อนการดำเนินการ GO = 2

การดำเนินการ GO = 4 เป็นการดำเนินการแบบสหภาพที่เชื่อมโยง ซึ่งเป็นการดำเนินการหลักสำหรับการดำเนินการ GO = 5, GO = 6 การดำเนินการ GO = 4 จะดำเนินการก่อนการดำเนินการ GO = 3

การดำเนินการ GO = 5 เป็นการดำเนินการอัตโนมัติ ดำเนินการก่อนการดำเนินการ GO = 4

การดำเนินการ GO = 6 เป็นการดำเนินการอัตโนมัติ ดำเนินการก่อนการดำเนินการ GO = 5

การดำเนินการ GO = 7 เป็นการดำเนินการอัตโนมัติที่ดำเนินการหลังจากการดำเนินการบล็อกการดำเนินการ "C"

การดำเนินการ GO = 8 เป็นการดำเนินการอัตโนมัติที่ดำเนินการหลังจากบล็อกการดำเนินการ "E"

จากการให้เหตุผลข้างต้นและกฎต่อไปนี้ เรากำหนดลำดับของการดำเนินการที่ทำ:

  • 1. ดำเนินการอัตโนมัติ GO = 5 ก่อน โปรดดูกฎสำหรับลำดับของการดำเนินการรวมที่เกี่ยวข้อง อ่านตารางทั้งหมดตามลำดับ
  • 2. ผลลัพธ์ของการดำเนินการ GO = 5 - แถวของตารางที่อ่าน - ถูกถ่ายโอนไปยังการดำเนินการ GO = 4
  • 3. ดำเนินการ GO = 4: สำหรับแต่ละแถวที่ส่งคืนโดยการดำเนินการ GO = 5 การดำเนินการ GO = 6 จะดำเนินการ นั่นคือ ช่วงดัชนีจะถูกสแกนเทียบกับแอตทริบิวต์การรวม รับรายการตัวระบุแถว Yaou1s1
  • 4. ผลลัพธ์ของการดำเนินการ GO = 4 จะถูกถ่ายโอนไปยังการดำเนินการ GO = 3 นั่นคือรายการตัวระบุแถว Kosh1s1 จะถูกถ่ายโอน
  • 5. มีการดำเนินการ GO = 3: สำหรับแต่ละค่า11оу1с1ที่ส่งคืนอันเป็นผลมาจากการดำเนินการของบล็อกการดำเนินการ "C" การดำเนินการ GO = 7 จะดำเนินการเช่น แถวของตารางถูกอ่านจากรายการตัวระบุแถวที่กำหนด ITMI ซึ่งได้รับหลังจากดำเนินการ Ш = 4
  • 6. ดำเนินการอัตโนมัติ GO = 8 - การอ่านตามลำดับของทั้งตาราง
  • 7. การดำเนินการรวมที่ไม่เกี่ยวข้อง GO = 2 ดำเนินการ: การรวมจะดำเนินการโดยการแฮชผลลัพธ์ของบล็อกการดำเนินการ "E" และ "E"
  • 8. ผลลัพธ์ของการดำเนินการ GO = 2 จะถูกโอนไปยังการดำเนินการ GO = 1
  • 9. ดำเนินการผสานที่ไม่เกี่ยวข้อง GO = 1: ข้อมูลที่ได้รับจากการดำเนินการ GO = 2 จะถูกรวบรวมและจัดเรียง
  • 10. ดำเนินการ GO = 0 ชุดข้อมูลผลลัพธ์จะถูกส่งกลับ

กฎต่อไปนี้ที่กำหนดไว้สำหรับการดำเนินการประเภทหลักสามารถใช้ได้กับแผนส่วนใหญ่สำหรับการดำเนินการสืบค้น BSGO อย่างไรก็ตาม มีโครงสร้างที่ใช้ในการสืบค้น BSGO ที่บ่งบอกถึงการละเมิดลำดับการดำเนินการที่อธิบายไว้ในกฎต่อไปนี้ สถานการณ์ดังกล่าวสามารถเกิดขึ้นได้จากการใช้ เช่น เคียวรีย่อยหรือเพรดิเคต anti-join ไม่ว่าในกรณีใด กระบวนการตีความแผนการดำเนินการของการสืบค้น BSGO ไม่ได้หมายความถึงเพียงการใช้กฎจำนวนหนึ่งเท่านั้น ซึ่งจะทำให้มั่นใจได้ถึงการวิเคราะห์ที่แม่นยำที่สุดว่าตัวเพิ่มประสิทธิภาพจะทำอะไรเมื่อดำเนินการสืบค้น BSGO คำขอ BSGO อื่นจะเป็นกรณีเฉพาะเสมอ และวิธีการดำเนินการใน DBMS ขึ้นอยู่กับหลายปัจจัย รวมถึงเวอร์ชันของ DBMS เวอร์ชันและประเภทของระบบปฏิบัติการที่ใช้งานอินสแตนซ์ DBMS ฮาร์ดแวร์ที่ใช้ คุณสมบัติของผู้เขียนแบบสอบถาม 80b ฯลฯ

1 msdevcon.ru #msdevcon

3 Olontsev Sergey SQL Server MCM, MVP Kaspersky Lab

4 ภาษาแบบสอบถามที่มีโครงสร้าง

5 ตัวอย่างแบบสอบถามเลือก pers.firstname, pers.lastname, emp.jobtitle, emp.nationalidnumber จาก HumanResources.Employee เป็น emp เข้าร่วมภายใน Person.Person ตาม pers.businessentityid = emp.businessentityid โดยที่ pers.firstname = N"John" และ emp.hiredate >= " "

6 แผนผังคิวรีแบบลอจิคัล Project pers.firstname, pers.lastname, emp.jobtitle, emp.nationalidnumber D A T A Filter Join pers.firstname = N"John" และ emp.hiredate >= " " pers.businessentityid = emp.businessentityid Person.Person as pers รับข้อมูล รับข้อมูล HumanResources.Employee เป็น emp

7 แผนการสืบค้น แสดงวิธีการดำเนินการสืบค้น T-SQL ในระดับกายภาพ

8 หลายวิธี

9 DEMO แผนง่ายๆ เลือกข้อมูลทั้งหมดจากตาราง วิธีรับแผนแบบสอบถาม

11 วิธีการของตัวดำเนินการ Init() วิธีการของตัวดำเนินการ Init() ทำให้ตัวดำเนินการทางกายภาพเริ่มต้นตัวเองและเตรียมโครงสร้างข้อมูลที่จำเป็น ตัวดำเนินการทางกายภาพสามารถรับการเรียก Init() ได้หลายครั้ง แม้ว่าโดยปกติจะได้รับเพียงการโทรเดียวก็ตาม GetNext() วิธีการ GetNext() ทำให้ตัวดำเนินการทางกายภาพได้รับแถวแรกของข้อมูลหรือแถวถัดไป ตัวดำเนินการทางกายภาพอาจได้รับการเรียก GetNext() จำนวนมากหรือไม่ได้รับเลย เมธอด GetNext() ส่งคืนข้อมูลหนึ่งแถว และจำนวนครั้งที่เรียกใช้จะถูกระบุด้วยค่า ActualRows ในผลลัพธ์ของคำสั่ง Showplan Close() เมื่อเรียกใช้เมธอด Close() ตัวดำเนินการทางกายภาพจะทำการล้างข้อมูลและปิด ตัวดำเนินการทางกายภาพได้รับการเรียกเพียงครั้งเดียวเพื่อปิด ()

12 ปฏิสัมพันธ์ระหว่างตัวดำเนินการ ตัวดำเนินการ 1 ตัวดำเนินการ 2 ตัวดำเนินการ 3

13 ปฏิสัมพันธ์ระหว่างตัวดำเนินการ 1. ขอตัวดำเนินการแถว 1 ตัวดำเนินการ 2 ตัวดำเนินการ 3

14 ปฏิสัมพันธ์ระหว่างตัวดำเนินการ 1. แถวคำขอ 2. แถวคำขอ ผู้ประกอบการ 1 ผู้ประกอบการ 2 ผู้ประกอบการ 3

15 การโต้ตอบระหว่างตัวดำเนินการ 1. แถวคำขอ 2. ตัวดำเนินการแถวคำขอ 1 ตัวดำเนินการ 2 ตัวดำเนินการ 3 3. ส่งแถว

16 การโต้ตอบระหว่างตัวดำเนินการ 1. แถวคำขอ 2. ตัวดำเนินการแถวคำขอ 1 ตัวดำเนินการ 2 ตัวดำเนินการ 3 4. ส่งแถว 3. ส่งแถว

17 การโต้ตอบระหว่างตัวดำเนินการ 1. แถวคำขอ 2. ตัวดำเนินการแถวคำขอ 1 ตัวดำเนินการ 2 ตัวดำเนินการ 3 4. ส่งแถว 3. ส่งแถว

18 DEMO Operator TOP หรือเหตุใดจึงดีกว่าที่จะเรียกโอเปอเรเตอร์ว่าตัววนซ้ำ

ไม่มี 19 ตาราง!

20 HoBT หน้า 1 หน้า 2 หน้า 3 หน้า 4 แถว 1 แถว 3 แถว 5 แถว 7 แถว 2 แถว 4 แถว 6 แถว 8

21 หน้า HoBT หน้า หน้า หน้า หน้า หน้า หน้า

22 ตัวดำเนินการเข้าถึงข้อมูล DEMO สแกน ค้นหา ค้นหา

23 ใครมีตารางเดียวในฐานข้อมูล?

24 Nested Loops, Hash Join และ Merge Join

25 เข้าร่วมตัวดำเนินการ Nested Loops การรวมภายใน, การรวมด้านนอกซ้าย, การรวมกึ่งซ้าย, การรวมกึ่งต่อต้านซ้าย, การรวมเข้าร่วมภายใน, การรวมด้านนอกด้านซ้าย, การรวมกึ่งซ้าย, การรวมต่อต้านกึ่งซ้าย, การรวมด้านนอกขวา, การรวมกึ่งขวา, การรวมกึ่งต่อต้านขวา , union Hash เข้าร่วมการดำเนินการเชิงตรรกะทุกประเภท

26 DEMO Join, เรียงลำดับและโอเปอเรเตอร์แรก Nested Loops, Merge Join, Hash Join, Sort, โอเปอเรเตอร์แรก

27 คำเตือน

28 ข้อผิดพลาดและคำเตือน DEMO ในแผนการสืบค้น

29 ฉันรู้ว่าฉันไม่รู้อะไรเลย โสกราตีส

30 DEMO ตัวอย่างเล็กๆ น้อยๆ ของสิ่งที่ไม่ชัดเจน

31 การวินิจฉัยแผนแบบสอบถาม -- แบบสอบถาม 10 อันดับแรกที่ใช้ CPU มากที่สุดและแผนของพวกเขาเลือกสตริงย่อยด้านบน (10) (t.text, qs.statement_start_offset / 2 กรณีเมื่อ qs.statement_end_offset = -1 จากนั้น len (t.text) อื่น ๆ (qs.statement_end_offset - qs.statement_start_offset) / 2 end), qs.execution_count, โยน (qs.total_worker_time / เป็นทศนิยม (18, 2)) เป็น Total_worker_time_ms, ส่ง (qs.total_worker_time * 1. / qs.execution_count / เป็นทศนิยม (18, 2)) เป็น avg_worker_time_ms, ส่ง (p.query_plan เป็น xml) เป็น query_plan จาก sys.dm_exec_query_stats เป็น qs cross ใช้ sys.dm_exec_sql_text(qs.sql_handle) เป็น t cross ใช้ sys.dm_exec_text_query_plan(qs.plan_handle, qs.statement_start_offset , qs.statement_end_offset) ตามลำดับ p โดย qs.total_worker_time desc; ไป

32 เทคนิคในการอ่านแผนการสืบค้นขนาดใหญ่ ลองแบ่งพวกมันออกเป็นบล็อกเชิงตรรกะแล้วค่อยๆ วิเคราะห์ ใน SSMS เมื่อมีการแสดงแผนแบบกราฟิก ปุ่มจะปรากฏขึ้นที่มุมขวาล่างเพื่อให้นำทางผ่านแผนการสอบถามได้ง่ายขึ้น คุณสามารถใช้ XQuery\XPath

33 DEMO แผนการสืบค้นขนาดใหญ่

35 สาธิต SQL Sentry Plan Explorer

36 มาสรุปตัวดำเนินการรายแรก ระดับการปรับให้เหมาะสม เวลาในการคอมไพล์ ขนาดในพารามิเตอร์แคช ค่าการคอมไพล์ เหตุผลสำหรับต้นทุนการยกเลิกก่อนกำหนดของตัววนซ้ำ ดูอันดับแรกที่ตัวดำเนินการที่มีต้นทุนสูงสุด โปรดทราบว่าสิ่งเหล่านี้เป็นเพียงค่าประมาณ (แม้ในแผนการดำเนินการจริง)

37 มาสรุป Bookmark\Key Lookup กัน หากมีน้อยก็มีแนวโน้มว่าจะไม่มีปัญหา หากมีจำนวนมาก การสร้างดัชนีครอบคลุมจะช่วยกำจัดพวกมันได้ คำเตือน คุณต้องตรวจสอบสาเหตุที่เกิดขึ้นและดำเนินการหากจำเป็น

38 มาสรุปการเชื่อมต่อระหว่างโอเปอเรเตอร์ (กระแสข้อมูล) ยิ่งการเชื่อมต่อหนาขึ้น ข้อมูลก็ยิ่งส่งผ่านระหว่างโอเปอเรเตอร์เหล่านี้ได้มากขึ้นเท่านั้น เป็นเรื่องที่ควรให้ความสนใจเป็นพิเศษหากในบางขั้นตอนกระแสข้อมูลเพิ่มขึ้นอย่างรวดเร็ว ลำดับของการเข้าร่วมตาราง ยิ่งสตรีมข้อมูลมีขนาดเล็กลงเท่าไร การเข้าร่วมตารางก็จะง่ายขึ้นเท่านั้น ดังนั้น ก่อนอื่น คุณต้องรวมตารางเหล่านั้นซึ่งกระแสข้อมูลที่ได้จะน้อยลง

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

40 คำถาม

41 รายชื่อติดต่อ Olontsev Sergey Kaspersky Lab

42 2013 ไมโครซอฟต์ คอร์ปอเรชั่น สงวนลิขสิทธิ์. Microsoft, Windows, Windows Vista และชื่อผลิตภัณฑ์อื่นๆ เป็นหรืออาจเป็นเครื่องหมายการค้าจดทะเบียนและ/หรือเครื่องหมายการค้าในสหรัฐอเมริกา และ/หรือประเทศอื่นๆ ข้อมูลในที่นี้มีวัตถุประสงค์เพื่อให้ข้อมูลเท่านั้น และแสดงถึงมุมมองปัจจุบันของ Microsoft Corporation ณ วันที่นำเสนอนี้ เนื่องจาก Microsoft ต้องตอบสนองต่อสภาวะตลาดที่เปลี่ยนแปลง จึงไม่ควรตีความว่าเป็นข้อผูกพันในส่วนของ Microsoft และ Microsoft ไม่สามารถรับประกันความถูกต้องของข้อมูลใดๆ ที่ให้ไว้หลังจากวันที่นำเสนอนี้ MICROSOFT ไม่รับประกันทั้งโดยชัดแจ้ง โดยนัย หรือตามกฎหมาย เกี่ยวกับข้อมูลในการนำเสนอนี้