Cara Menyetel Microsoft SQL Server untuk Performa

Untuk mempertahankan penggunanya, aplikasi atau situs web apa pun harus berjalan cepat. Untuk lingkungan misi kritis, penundaan beberapa milidetik dalam mendapatkan informasi dapat menimbulkan masalah besar. Seiring bertambahnya ukuran basis data dari hari ke hari, kita perlu mengambil data secepat mungkin, dan menulis kembali data ke dalam basis data secepat mungkin. Untuk memastikan semua operasi berjalan dengan lancar, kami harus menyesuaikan server database kami untuk kinerja.

Pada artikel ini saya akan menjelaskan prosedur langkah demi langkah untuk penyetelan kinerja dasar pada salah satu server database teratas di pasar: Microsoft SQL Server (disingkat SQL Server).

#1 Menemukan Pelakunya

Seperti halnya perangkat lunak lain, kita perlu memahami bahwa SQL Server adalah program komputer yang kompleks. Jika kita memiliki masalah dengannya, kita perlu menemukan mengapa itu tidak berjalan seperti yang kita harapkan.

Dari SQL Server kita perlu menarik dan mendorong data secepat dan seakurat mungkin. Jika ada masalah, beberapa alasan dasar, dan dua hal pertama yang harus diperiksa, adalah:

• Pengaturan perangkat keras dan instalasi, yang mungkin perlu diperbaiki karena kebutuhan SQL Server bersifat spesifik
• Jika kami telah memberikan kode T-SQL yang benar untuk SQL Server untuk diterapkan

Meskipun SQL Server adalah perangkat lunak berpemilik, Microsoft telah menyediakan banyak cara untuk memahaminya dan menggunakannya secara efisien.

Jika perangkat kerasnya OK dan instalasi telah dilakukan dengan benar, tetapi SQL Server masih berjalan lambat, maka pertama-tama kita perlu mencari tahu apakah ada kesalahan terkait perangkat lunak. Untuk memeriksa apa yang terjadi, kita perlu mengamati bagaimana kinerja utas yang berbeda. Ini dicapai dengan menghitung statistik tunggu dari utas yang berbeda. SQL server menggunakan utas untuk setiap permintaan pengguna, dan utas tidak lain adalah program lain di dalam program kompleks kami yang disebut SQL Server. Penting untuk dicatat bahwa utas ini bukan utas sistem operasi tempat SQL server diinstal; itu terkait dengan utas SQLOS, yang merupakan sistem operasi semu untuk SQL Server.

Statistik tunggu dapat dihitung menggunakan sys.dm_os_wait_stats Dynamic Management View (DMV), yang memberikan informasi tambahan tentang statusnya saat ini. Ada banyak skrip online untuk menanyakan tampilan ini, tetapi favorit saya adalah skrip Paul Randal karena mudah dimengerti dan memiliki semua parameter penting untuk mengamati statistik tunggu:

 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

Saat kita menjalankan skrip ini, kita perlu berkonsentrasi pada baris atas hasil karena mereka disetel terlebih dahulu dan mewakili jenis tunggu maksimum.

Kita perlu memahami tipe menunggu sehingga kita dapat membuat keputusan yang tepat. Untuk mempelajari tentang jenis menunggu yang berbeda, kita dapat pergi ke dokumentasi Microsoft yang sangat baik.

Mari kita ambil contoh di mana kita memiliki terlalu banyak PAGEIOLATCH_XX . Ini berarti utas sedang menunggu halaman data dibaca dari disk ke buffer, yang tidak lain adalah blok memori. Kita harus yakin bahwa kita memahami apa yang sedang terjadi. Ini tidak berarti subsistem I/O buruk atau memori tidak cukup, dan peningkatan subsistem I/O dan memori akan menyelesaikan masalah, tetapi hanya sementara. Untuk menemukan solusi permanen, kita perlu melihat mengapa begitu banyak data yang dibaca dari disk: Jenis perintah SQL apa yang menyebabkan hal ini? Apakah kita membaca terlalu banyak data daripada membaca lebih sedikit data dengan menggunakan filter, seperti klausa where ? Apakah terlalu banyak pembacaan data terjadi karena pemindaian tabel atau pemindaian indeks? Bisakah kita mengonversinya menjadi pencarian indeks dengan menerapkan atau memodifikasi indeks yang ada? Apakah kita menulis kueri SQL yang disalahpahami oleh SQL Optimizer (program lain di dalam program server SQL kita)?

Kita perlu berpikir dari sudut yang berbeda dan menggunakan kasus uji yang berbeda untuk menghasilkan solusi. Masing-masing tipe menunggu di atas membutuhkan solusi yang berbeda. Seorang administrator basis data perlu menelitinya secara menyeluruh sebelum mengambil tindakan apa pun. Tetapi sebagian besar waktu, menemukan kueri T-SQL yang bermasalah dan menyetelnya akan menyelesaikan 60 hingga 70 persen masalah.

#2 Menemukan Pertanyaan Bermasalah

Seperti disebutkan di atas, hal pertama yang dapat kita lakukan adalah mencari kueri yang bermasalah. Kode T-SQL berikut akan menemukan 20 kueri berkinerja terburuk:

 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

Kita perlu berhati-hati dengan hasilnya; meskipun kueri dapat memiliki waktu berjalan rata-rata maksimum, jika dijalankan hanya sekali, efek total pada server rendah dibandingkan dengan kueri yang memiliki waktu berjalan rata-rata sedang dan berjalan banyak kali dalam sehari.

#3 Penyetelan Halus Kueri

Penyesuaian kueri T-SQL adalah konsep penting. Hal mendasar yang harus dipahami adalah seberapa baik kita dapat menulis kueri T-SQL dan mengimplementasikan indeks, sehingga pengoptimal SQL dapat menemukan rencana yang dioptimalkan untuk melakukan apa yang kita inginkan. Dengan setiap rilis baru SQL Server, kami mendapatkan pengoptimal yang lebih canggih yang akan menutupi kesalahan kami dalam menulis kueri SQL yang tidak dioptimalkan, dan juga akan memperbaiki bug yang terkait dengan pengoptimal sebelumnya. Namun, tidak peduli seberapa cerdas pengoptimalnya, jika kita tidak dapat mengatakan apa yang kita inginkan (dengan menulis kueri T-SQL yang tepat), pengoptimal SQL tidak akan dapat melakukan tugasnya.

SQL Server menggunakan algoritma pencarian dan pengurutan lanjutan. Jika kita pandai mencari dan menyortir algoritma, maka sebagian besar waktu kita bisa menebak mengapa SQL Server mengambil tindakan tertentu. Buku terbaik untuk mempelajari lebih lanjut dan memahami algoritme semacam itu adalah The Art of Computer Programming oleh Donald Knuth.

Saat kami memeriksa kueri yang perlu disesuaikan, kami perlu menggunakan rencana eksekusi kueri tersebut sehingga kami dapat mengetahui bagaimana SQL server menafsirkannya.

Saya tidak dapat membahas semua aspek dari rencana eksekusi di sini, tetapi pada tingkat dasar saya dapat menjelaskan hal-hal yang perlu kita pertimbangkan.

• Pertama, kita perlu mencari tahu operator mana yang mengambil sebagian besar biaya kueri.
• Jika operator mengambil banyak biaya, kita perlu mempelajari alasannya. Sebagian besar waktu, pemindaian akan menghabiskan lebih banyak biaya daripada pencarian. Kita perlu memeriksa mengapa pemindaian tertentu (pemindaian tabel atau pemindaian indeks) terjadi alih-alih pencarian indeks. Kita dapat memecahkan masalah ini dengan menerapkan indeks yang tepat pada kolom tabel, tetapi seperti halnya program kompleks lainnya, tidak ada solusi tetap. Misalnya, jika tabelnya kecil maka pemindaian lebih cepat daripada pencarian.
• Ada sekitar 78 operator, yang mewakili berbagai tindakan dan keputusan dari rencana eksekusi SQL Server. Kami perlu mempelajarinya secara mendalam dengan membaca dokumentasi Microsoft, sehingga kami dapat memahaminya dengan lebih baik dan mengambil tindakan yang tepat.

#4 Eksekusi Rencana Penggunaan Kembali

Bahkan jika kami menerapkan indeks yang tepat pada tabel dan menulis kode T-SQL yang baik, jika rencana eksekusi tidak digunakan kembali, kami akan memiliki masalah kinerja. Setelah menyempurnakan kueri, kita perlu memastikan bahwa rencana eksekusi dapat digunakan kembali bila diperlukan. Sebagian besar waktu CPU akan dihabiskan untuk menghitung rencana eksekusi yang dapat dihilangkan, jika kita menggunakan kembali rencana tersebut.

Kita dapat menggunakan kueri di bawah ini untuk mengetahui berapa kali rencana eksekusi digunakan kembali, di mana usecounts menunjukkan berapa kali rencana tersebut digunakan kembali:

 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

Cara terbaik untuk menggunakan kembali rencana eksekusi adalah dengan menerapkan prosedur tersimpan berparameter. Ketika kita tidak dalam posisi untuk mengimplementasikan prosedur tersimpan, kita dapat menggunakan sp_executesql , yang dapat digunakan sebagai gantinya untuk mengeksekusi pernyataan T-SQL ketika satu-satunya perubahan pada pernyataan SQL adalah nilai parameter. SQL Server kemungkinan besar akan menggunakan kembali rencana eksekusi yang dihasilkan dalam eksekusi pertama.

Sekali lagi, seperti halnya program komputer yang kompleks, tidak ada solusi tetap. Terkadang lebih baik menyusun rencana lagi.

Mari kita periksa dua contoh kueri berikut:

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

Mari kita asumsikan kita memiliki indeks yang tidak berkerumun pada kolom name dan setengah dari tabel memiliki nilai sri dan beberapa baris memiliki pal di kolom name . Untuk kueri pertama, SQL Server akan menggunakan pemindaian tabel karena setengah dari tabel memiliki nilai yang sama. Namun untuk query kedua, lebih baik menggunakan index scan karena hanya beberapa baris yang memiliki nilai pal .

Meskipun kueri serupa, rencana eksekusi yang sama mungkin bukan solusi yang baik. Sebagian besar waktu itu akan menjadi kasus yang berbeda, jadi kami perlu menganalisis semuanya dengan cermat sebelum kami memutuskan. Jika kita tidak ingin menggunakan kembali rencana eksekusi, kita selalu dapat menggunakan opsi "kompilasi ulang" dalam prosedur tersimpan.

Ingatlah bahwa bahkan setelah menggunakan prosedur tersimpan atau sp_executesql , ada kalanya rencana eksekusi tidak akan digunakan kembali. Mereka:

• Saat indeks yang digunakan oleh kueri berubah atau dijatuhkan
• Ketika statistik, struktur, atau skema tabel yang digunakan oleh kueri berubah
• Saat kami menggunakan opsi "kompilasi ulang"
• Ketika ada sejumlah besar penyisipan, pembaruan, atau penghapusan
• Saat kami mencampur DDL dan DML dalam satu kueri

#5 Menghapus Indeks yang Tidak Perlu

Setelah menyempurnakan kueri, kita perlu memeriksa bagaimana indeks digunakan. Pemeliharaan indeks membutuhkan banyak CPU dan I/O. Setiap kali kita memasukkan data ke dalam database, SQL Server juga perlu memperbarui indeks, jadi lebih baik untuk menghapusnya jika tidak digunakan.

SQL server memberi kami dm_db_index_usage_stats DMV untuk menemukan statistik indeks. Ketika kami menjalankan kode T-SQL di bawah ini, kami mendapatkan statistik penggunaan untuk indeks yang berbeda. Jika kami menemukan indeks yang tidak digunakan sama sekali, atau jarang digunakan, kami dapat menghapusnya untuk mendapatkan kinerja.

 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 Instalasi SQL Server Dan Pengaturan Basis Data

Saat menyiapkan database, kita perlu menyimpan data dan file log secara terpisah. Alasan utamanya adalah menulis dan mengakses file data tidak berurutan, sedangkan menulis dan mengakses file log berurutan. Jika kami meletakkannya di drive yang sama, kami tidak dapat menggunakannya secara optimal.

Saat kami membeli Storage Area Network (SAN), vendor mungkin memberi kami beberapa rekomendasi tentang cara menyiapkannya, tetapi informasi ini tidak selalu membantu. Kami perlu melakukan diskusi mendetail dengan perangkat keras dan jaringan kami tentang cara menyimpan data dan file log secara terpisah dan dengan cara yang dioptimalkan.

#7 Jangan Membebani SQL Server

Tugas utama dari setiap database administrator adalah memastikan server produksi berjalan dengan lancar dan melayani pelanggan dengan sebaik mungkin. Untuk mewujudkannya, kita perlu memelihara database terpisah (jika mungkin, pada mesin terpisah) untuk lingkungan berikut:

• Produksi
• Perkembangan
• Pengujian
• analitis

Untuk database produksi, kita memerlukan database dengan mode pemulihan penuh, dan untuk database lain, mode pemulihan sederhana sudah cukup.

Pengujian pada database produksi akan membebani log transaksi, indeks, CPU, dan I/O. Itulah mengapa kita perlu menggunakan database terpisah untuk produksi, pengembangan, pengujian, dan analisis. Jika memungkinkan, gunakan mesin terpisah untuk setiap database, karena akan mengurangi beban pada CPU dan I/O.

#8 Log transaksi, tempdb, dan memori

File log perlu memiliki ruang kosong yang cukup untuk operasi normal karena operasi autogrow pada file log memakan waktu dan dapat memaksa operasi lain untuk menunggu hingga selesai. Untuk mengetahui ukuran file log untuk setiap database dan berapa banyak yang digunakan, kita dapat menggunakan DBCC SQLPERF(logspace) .

Cara terbaik untuk mengatur tempdb adalah meletakkannya di disk terpisah. Kami perlu menjaga ukuran awal sebesar yang kami mampu karena ketika mencapai situasi autogrow, kinerja akan menurun.

Seperti disebutkan sebelumnya, kita perlu memastikan bahwa SQL server berjalan pada mesin yang terpisah, lebih disukai yang tidak memiliki aplikasi lain di dalamnya. Kita perlu menyimpan beberapa memori untuk sistem operasi, ditambah lagi jika itu adalah bagian dari sebuah cluster, jadi dalam kebanyakan kasus sekitar 2GB harus dilakukan.

Kesimpulan:

Prosedur dan saran yang dibahas di sini hanya untuk penyetelan kinerja dasar. Jika kita mengikuti langkah-langkah ini, kita mungkin rata-rata mendapatkan peningkatan kinerja sekitar 40 hingga 50 persen. Untuk melakukan penyetelan kinerja SQL Server tingkat lanjut, kita perlu menggali lebih dalam ke setiap langkah yang dibahas di sini.

Bacaan Lebih Lanjut di Blog Teknik Toptal:

• Memecahkan Kemacetan Dengan Indeks dan Partisi SQL
• Panduan Migrasi Oracle ke SQL Server dan SQL Server ke Oracle