วิธีปรับแต่ง Microsoft SQL Server เพื่อประสิทธิภาพ

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

ในบทความนี้ ผมจะอธิบายขั้นตอนทีละขั้นตอนสำหรับการปรับแต่งประสิทธิภาพขั้นพื้นฐานบนเซิร์ฟเวอร์ฐานข้อมูลชั้นนำในตลาด: Microsoft SQL Server (เรียกสั้นๆ ว่า SQL Server)

#1 ตามหาตัวผู้ร้าย

เช่นเดียวกับซอฟต์แวร์อื่นๆ เราต้องเข้าใจว่า SQL Server เป็นโปรแกรมคอมพิวเตอร์ที่ซับซ้อน ถ้าเรามีปัญหากับมัน เราต้องค้นหาว่าทำไมมันไม่ทำงานตามที่เราคาดไว้

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

• การตั้งค่าฮาร์ดแวร์และการติดตั้ง ซึ่งอาจต้องแก้ไขเนื่องจากความต้องการของ SQL Server มีความเฉพาะเจาะจง
• ถ้าเราให้รหัส T-SQL ที่ถูกต้องสำหรับ SQL Server เพื่อนำไปใช้

แม้ว่า SQL Server จะเป็นซอฟต์แวร์ที่เป็นกรรมสิทธิ์ แต่ Microsoft ได้จัดเตรียมวิธีการมากมายในการทำความเข้าใจและใช้งานอย่างมีประสิทธิภาพ

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

สถิติการรอสามารถคำนวณได้โดยใช้ sys.dm_os_wait_stats Dynamic Management View (DMV) ซึ่งให้ข้อมูลเพิ่มเติมเกี่ยวกับสถานะปัจจุบัน มีสคริปต์ออนไลน์มากมายสำหรับค้นหามุมมองนี้ แต่ที่ฉันชอบคือสคริปต์ของ Paul Randal เพราะมันเข้าใจง่ายและมีพารามิเตอร์ที่สำคัญทั้งหมดในการสังเกตสถิติการรอ:

 WITH [Waits] AS (SELECT [wait_type], [wait_time_ms] / 1000.0 AS [WaitS], ([wait_time_ms] - [signal_wait_time_ms]) / 1000.0 AS [ResourceS], [signal_wait_time_ms] / 1000.0 AS [SignalS], [waiting_tasks_count] AS [WaitCount], 100.0 * [wait_time_ms] / SUM ([wait_time_ms]) OVER() AS [Percentage], ROW_NUMBER() OVER(ORDER BY [wait_time_ms] DESC) AS [RowNum] FROM sys.dm_os_wait_stats WHERE [wait_type] NOT IN ( N'BROKER_EVENTHANDLER', N'BROKER_RECEIVE_WAITFOR', N'BROKER_TASK_STOP', N'BROKER_TO_FLUSH', N'BROKER_TRANSMITTER', N'CHECKPOINT_QUEUE', N'CHKPT', N'CLR_AUTO_EVENT', N'CLR_MANUAL_EVENT', N'CLR_SEMAPHORE', N'DBMIRROR_DBM_EVENT', N'DBMIRROR_EVENTS_QUEUE', N'DBMIRROR_WORKER_QUEUE', N'DBMIRRORING_CMD', N'DIRTY_PAGE_POLL', N'DISPATCHER_QUEUE_SEMAPHORE', N'EXECSYNC', N'FSAGENT', N'FT_IFTS_SCHEDULER_IDLE_WAIT', N'FT_IFTSHC_MUTEX', N'HADR_CLUSAPI_CALL', N'HADR_FILESTREAM_IOMGR_IOCOMPLETION', N'HADR_LOGCAPTURE_WAIT', N'HADR_NOTIFICATION_DEQUEUE', N'HADR_TIMER_TASK', N'HADR_WORK_QUEUE', N'KSOURCE_WAKEUP', N'LAZYWRITER_SLEEP', N'LOGMGR_QUEUE', N'ONDEMAND_TASK_QUEUE', N'PWAIT_ALL_COMPONENTS_INITIALIZED', N'QDS_PERSIST_TASK_MAIN_LOOP_SLEEP', N'QDS_CLEANUP_STALE_QUERIES_TASK_MAIN_LOOP_SLEEP', N'REQUEST_FOR_DEADLOCK_SEARCH', N'RESOURCE_QUEUE', N'SERVER_IDLE_CHECK', N'SLEEP_BPOOL_FLUSH', N'SLEEP_DBSTARTUP', N'SLEEP_DCOMSTARTUP', N'SLEEP_MASTERDBREADY', N'SLEEP_MASTERMDREADY', N'SLEEP_MASTERUPGRADED', N'SLEEP_MSDBSTARTUP', N'SLEEP_SYSTEMTASK', N'SLEEP_TASK', N'SLEEP_TEMPDBSTARTUP', N'SNI_HTTP_ACCEPT', N'SP_SERVER_DIAGNOSTICS_SLEEP', N'SQLTRACE_BUFFER_FLUSH', N'SQLTRACE_INCREMENTAL_FLUSH_SLEEP', N'SQLTRACE_WAIT_ENTRIES', N'WAIT_FOR_RESULTS', N'WAITFOR', N'WAITFOR_TASKSHUTDOWN', N'WAIT_XTP_HOST_WAIT', N'WAIT_XTP_OFFLINE_CKPT_NEW_LOG', N'WAIT_XTP_CKPT_CLOSE', N'XE_DISPATCHER_JOIN', N'XE_DISPATCHER_WAIT', N'XE_TIMER_EVENT') AND [waiting_tasks_count] > 0 ) SELECT MAX ([W1].[wait_type]) AS [WaitType], CAST (MAX ([W1].[WaitS]) AS DECIMAL (16,2)) AS [Wait_S], CAST (MAX ([W1].[ResourceS]) AS DECIMAL (16,2)) AS [Resource_S], CAST (MAX ([W1].[SignalS]) AS DECIMAL (16,2)) AS [Signal_S], MAX ([W1].[WaitCount]) AS [WaitCount], CAST (MAX ([W1].[Percentage]) AS DECIMAL (5,2)) AS [Percentage], CAST ((MAX ([W1].[WaitS]) / MAX ([W1].[WaitCount])) AS DECIMAL (16,4)) AS [AvgWait_S], CAST ((MAX ([W1].[ResourceS]) / MAX ([W1].[WaitCount])) AS DECIMAL (16,4)) AS [AvgRes_S], CAST ((MAX ([W1].[SignalS]) / MAX ([W1].[WaitCount])) AS DECIMAL (16,4)) AS [AvgSig_S] FROM [Waits] AS [W1] INNER JOIN [Waits] AS [W2] ON [W2].[RowNum] <= [W1].[RowNum] GROUP BY [W1].[RowNum] HAVING SUM ([W2].[Percentage]) - MAX ([W1].[Percentage]) < 95; -- percentage threshold GO

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

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

ลองมาดูตัวอย่างที่เรามี PAGEIOLATCH_XX มากเกินไป ซึ่งหมายความว่าเธรดกำลังรอหน้าข้อมูลที่อ่านจากดิสก์ไปยังบัฟเฟอร์ ซึ่งไม่มีอะไรเลยนอกจากบล็อกหน่วยความจำ เราต้องแน่ใจว่าเราเข้าใจสิ่งที่เกิดขึ้น ซึ่งไม่ได้หมายความว่าระบบย่อย I/O ไม่ดีหรือมีหน่วยความจำไม่เพียงพอ และการเพิ่มระบบย่อย I/O และหน่วยความจำจะช่วยแก้ปัญหาได้ แต่จะเป็นการชั่วคราวเท่านั้น ในการค้นหาวิธีแก้ปัญหาแบบถาวร เราต้องดูว่าเหตุใดจึงมีการอ่านข้อมูลจำนวนมากจากดิสก์: คำสั่ง SQL ประเภทใดที่ทำให้เกิดสิ่งนี้ เราอ่านข้อมูลมากเกินไปแทนที่จะอ่านข้อมูลน้อยลงโดยใช้ตัวกรอง เช่น where clauses? มีการอ่านข้อมูลมากเกินไปเนื่องจากการสแกนตารางหรือการสแกนดัชนีหรือไม่ เราสามารถแปลงเป็นการค้นหาดัชนีโดยใช้หรือแก้ไขดัชนีที่มีอยู่ได้หรือไม่ เรากำลังเขียนคำสั่ง SQL ที่เข้าใจผิดโดย SQL Optimizer (โปรแกรมอื่นภายในโปรแกรมเซิร์ฟเวอร์ SQL ของเรา) หรือไม่

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

#2 การค้นหาคำค้นหาที่มีปัญหา

ดังที่กล่าวไว้ข้างต้น สิ่งแรกที่เราทำได้คือค้นหาข้อความค้นหาที่มีปัญหา โค้ด T-SQL ต่อไปนี้จะค้นหา 20 ข้อความค้นหาที่มีประสิทธิภาพแย่ที่สุด:

 SELECT TOP 20 total_worker_time/execution_count AS Avg_CPU_Time ,Execution_count ,total_elapsed_time/execution_count as AVG_Run_Time ,total_elapsed_time ,(SELECT SUBSTRING(text,statement_start_offset/2+1,statement_end_offset ) FROM sys.dm_exec_sql_text(sql_handle) ) AS Query_Text FROM sys.dm_exec_query_stats ORDER BY Avg_CPU_Time DESC

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

#3 แบบสอบถามการปรับแต่งอย่างละเอียด

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

SQL Server ใช้อัลกอริธึมการค้นหาและการเรียงลำดับขั้นสูง หากเราเชี่ยวชาญในการค้นหาและจัดเรียงอัลกอริธึม ส่วนใหญ่แล้วเราสามารถเดาได้ว่าทำไม SQL Server จึงดำเนินการบางอย่าง หนังสือที่ดีที่สุดสำหรับการเรียนรู้เพิ่มเติมและทำความเข้าใจอัลกอริทึมดังกล่าวคือ The Art of Computer Programming โดย Donald Knuth

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

ฉันไม่สามารถครอบคลุมทุกด้านของแผนปฏิบัติการที่นี่ แต่ในระดับพื้นฐาน ฉันสามารถอธิบายสิ่งที่เราต้องพิจารณาได้

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

#4 ใช้แผนปฏิบัติการซ้ำ

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

เราสามารถใช้แบบสอบถามด้านล่างเพื่อดูว่ามีการใช้แผนปฏิบัติการซ้ำกี่ครั้ง โดยที่ usecounts แสดงถึงจำนวนครั้งที่มีการใช้แผนซ้ำ:

 SELECT [ecp].[refcounts] , [ecp].[usecounts] , [ecp].[objtype] , DB_NAME([est].[dbid]) AS [db_name] , [est].[objectid] , [est].[text] as [query_ext] , [eqp].[query_plan] FROM sys.dm_exec_cached_plans ecp CROSS APPLY sys.dm_exec_sql_text ( ecp.plan_handle ) est CROSS APPLY sys.dm_exec_query_plan ( ecp.plan_handle ) eqp

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

เช่นเดียวกับโปรแกรมคอมพิวเตอร์ที่ซับซ้อนใดๆ ไม่มีวิธีแก้ปัญหาแบบตายตัว บางครั้งควรรวบรวมแผนใหม่อีกครั้ง

มาตรวจสอบแบบสอบถามตัวอย่างสองตัวอย่างต่อไปนี้:

• select name from table where name = 'sri';
• select name from table where name = 'pal';

สมมติว่าเรามีดัชนีที่ไม่จัดกลุ่มในคอลัมน์ name และครึ่งหนึ่งของตารางมีค่า sri และแถวไม่กี่แถวมี pal ในคอลัมน์ name สำหรับแบบสอบถามแรก SQL Server จะใช้การสแกนตารางเนื่องจากครึ่งหนึ่งของตารางมีค่าเหมือนกัน แต่สำหรับแบบสอบถามที่สอง ควรใช้การสแกนดัชนีเพราะมีเพียงไม่กี่แถวเท่านั้นที่มีค่า pal

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

โปรดทราบว่าแม้หลังจากใช้กระบวนงานที่เก็บไว้หรือ sp_executesql มีบางครั้งที่แผนการดำเนินการจะไม่ถูกนำมาใช้ซ้ำ พวกเขาเป็น:

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

#5 การลบดัชนีที่ไม่จำเป็น

หลังจากปรับแต่งการสืบค้นข้อมูลอย่างละเอียดแล้ว เราจำเป็นต้องตรวจสอบว่าดัชนีใช้งานอย่างไร การบำรุงรักษาดัชนีต้องใช้ CPU และ I/O จำนวนมาก ทุกครั้งที่เราแทรกข้อมูลลงในฐานข้อมูล SQL Server จำเป็นต้องอัปเดตดัชนีด้วย ดังนั้นจึงควรลบออกหากไม่ได้ใช้

เซิร์ฟเวอร์ SQL ให้ dm_db_index_usage_stats DMV แก่เราเพื่อค้นหาสถิติดัชนี เมื่อเราเรียกใช้โค้ด T-SQL ด้านล่าง เราจะได้รับสถิติการใช้งานสำหรับดัชนีต่างๆ หากเราพบดัชนีที่ไม่ได้ใช้เลย หรือใช้น้อยครั้ง เราสามารถวางดัชนีเหล่านี้เพื่อเพิ่มประสิทธิภาพได้

 SELECT OBJECT_NAME(IUS.[OBJECT_ID]) AS [OBJECT NAME], DB_NAME(IUS.database_id) AS [DATABASE NAME], I.[NAME] AS [INDEX NAME], USER_SEEKS, USER_SCANS, USER_LOOKUPS, USER_UPDATES FROM SYS.DM_DB_INDEX_USAGE_STATS AS IUS INNER JOIN SYS.INDEXES AS I ON I.[OBJECT_ID] = IUS.[OBJECT_ID] AND I.INDEX_ID = IUS.INDEX_ID

#6 การติดตั้งเซิร์ฟเวอร์ SQL และการตั้งค่าฐานข้อมูล

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

เมื่อเราซื้อ Storage Area Network (SAN) ผู้ขายอาจให้คำแนะนำเกี่ยวกับวิธีการตั้งค่า แต่ข้อมูลนี้ไม่ได้เป็นประโยชน์เสมอไป เราจำเป็นต้องมีการสนทนาโดยละเอียดกับฝ่ายฮาร์ดแวร์และเครือข่ายของเราเกี่ยวกับวิธีเก็บข้อมูลและล็อกไฟล์แยกจากกันและด้วยวิธีที่เหมาะสมที่สุด

#7 Don't Overload SQL Server

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

• การผลิต
• การพัฒนา
• การทดสอบ
• วิเคราะห์

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

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

#8 บันทึกธุรกรรม tempdb และหน่วยความจำ

ไฟล์บันทึกต้องมีพื้นที่ว่างเพียงพอสำหรับการทำงานปกติ เนื่องจากการดำเนินการ autogrow ในไฟล์บันทึกใช้เวลานานและอาจบังคับให้การดำเนินการอื่นๆ รอจนกว่าจะเสร็จสิ้น ในการค้นหาขนาดไฟล์บันทึกสำหรับแต่ละฐานข้อมูลและจำนวนที่ใช้ เราสามารถใช้ DBCC SQLPERF(logspace)

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

ดังที่กล่าวไว้ก่อนหน้านี้ เราต้องตรวจสอบให้แน่ใจว่าเซิร์ฟเวอร์ SQL ทำงานบนเครื่องแยก เราจำเป็นต้องเก็บหน่วยความจำไว้สำหรับระบบปฏิบัติการ บวกกับหน่วยความจำบางส่วนหากเป็นส่วนหนึ่งของคลัสเตอร์ ดังนั้นในกรณีส่วนใหญ่ประมาณ 2GB ควรทำ

บทสรุป:

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

อ่านเพิ่มเติมในบล็อก Toptal Engineering:

• การแก้ปัญหาคอขวดด้วยดัชนี SQL และพาร์ติชั่น
• Oracle ไปยัง SQL Server และ SQL Server ไปยัง Oracle Migration Guide