ประสิทธิภาพการสืบค้นที่สม่ำเสมอตลอดเวลา

Question

เรากำลังดำเนินการโหลดแอปพลิเคชันอย่างเข้มข้น (การดำเนินการหลายพันครั้ง / วินาที) กับฐานข้อมูล SQL Server ที่มีข้อมูลค่อนข้างน้อย บางตารางมีหลายพันล้านแถวหลายตารางมีส่วนแทรกและการอัปเดตมากมาย

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

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

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

คำถามของฉันคือทำไมสิ่งนี้จึงเกิดขึ้นและเราจะทำอะไรได้บ้าง?

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

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

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

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

คำถามติดตามผล: การดมพารามิเตอร์ดูเหมือนจะเกี่ยวข้องกับขั้นตอนการจัดเก็บเท่านั้นถูกต้องหรือไม่?

Answer 1

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

โดยไม่คำนึงถึงข้างต้นฉันขอแนะนำให้คุณอ่านบทความของ Erlandในหัวข้อนี้

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

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

พยายามระบุหนึ่งคำค้นหา ดูว่าคุณมีแผนหนึ่งหรือหลายแผนสำหรับแบบสอบถาม ทดสอบและOPTIMIZE FOR / หรือ RECOMPILEตัวเลือก "global" ตัวเดียวในระดับฐานข้อมูลที่คุณมีคือการปิดใช้งานการดมพารามิเตอร์สำหรับฐานข้อมูล ซึ่งหมายความว่าเครื่องมือเพิ่มประสิทธิภาพจะปรับให้เหมาะสมเนื่องจากไม่มีเงื่อนงำของค่า ทั้งหมดนี้และอีกมากมายในบทความของ Erland

การดมพารามิเตอร์ไม่ได้ใช้กับกระบวนงานที่จัดเก็บไว้เท่านั้น นอกจากนี้ยังนำไปใช้กับ SQL ที่กำหนดพารามิเตอร์ (โดยทั่วไปจะดำเนินการโดยใช้sp_executesql) ซึ่งน่าจะเป็นเรื่องปกติในปัจจุบันมากกว่าโพรซีเดอร์ที่เก็บไว้

Answer 2

_{คำตอบที่สร้างขึ้นจากความคิดเห็น}

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

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

อีกวิธีหนึ่งในการ "แก้ไข" แผนการดำเนินการคือการใช้ Query Store (ฉันคิดว่ามันเริ่มต้นด้วย SQL Server 2016) และ "แก้ไข" แผนที่จะใช้ อาจมีข้อเสียบางประการหากข้อมูลมีการเปลี่ยนแปลงมาก (เนื่องจาก SQL Server จะไม่สามารถสร้างแผนการที่ดีขึ้นได้) แต่สามารถแก้ไขปัญหาประเภทนั้นได้ (ฉันมีแบบสอบถามที่ทำงานด้วยแผนการดำเนินการแก้ไขตั้งแต่ 2 ปีที่แล้วและไม่ได้ ไม่มีปัญหาการดมพารามิเตอร์ตั้งแต่นั้นมา) - Dominique Boucher