MSSQLSERVER_701

Artikel
03/25/2023

Berlaku untuk:SQL Server

Detail

Atribut	Nilai
Nama Produk	SQL Server
ID Peristiwa	701
Sumber Kejadian	MSSQLSERVER
Komponen	SQLEngine
Nama Simbolis	NOSYSMEM
Teks Pesan	Memori sistem tidak cukup untuk menjalankan kueri ini.

Catatan

Artikel ini difokuskan pada SQL Server. Untuk informasi tentang pemecahan masalah memori di Azure SQL Database, lihat Memecahkan masalah kesalahan kehabisan memori dengan Azure SQL Database.

Penjelasan

Kesalahan 701 terjadi ketika SQL Server gagal mengalokasikan memori yang cukup untuk menjalankan kueri. Memori yang tidak mencukupi dapat disebabkan oleh sejumlah faktor yang mencakup pengaturan sistem operasi, ketersediaan memori fisik, komponen lain menggunakan memori di dalam SQL Server, atau batas memori pada beban kerja saat ini. Di kebanyakan kasus, transaksi yang gagal bukanlah penyebab kesalahan ini. Secara keseluruhan, penyebabnya dapat dikelompokkan menjadi tiga:

Tekanan memori eksternal atau OS

Tekanan eksternal mengacu pada pemanfaatan memori tinggi yang berasal dari komponen di luar proses yang menyebabkan memori yang tidak mencukup untuk SQL Server. Anda harus menemukan apakah aplikasi lain pada sistem mengonsumsi memori dan berkontribusi pada ketersediaan memori yang rendah. SQL Server adalah salah satu dari beberapa aplikasi yang dirancang untuk merespons tekanan memori OS dengan memotong kembali penggunaan memorinya. Ini berarti, jika beberapa aplikasi atau driver meminta memori, OS mengirim sinyal ke semua aplikasi untuk membebaskan memori dan SQL Server akan merespons dengan mengurangi penggunaan memorinya sendiri. Sangat sedikit aplikasi lain yang merespons karena tidak dirancang untuk mendengarkan pemberitahuan tersebut. Jadi jika SQL mulai memotong kembali penggunaan memorinya, kumpulan memorinya berkurang dan komponen mana pun yang membutuhkan memori mungkin tidak mendapatkannya. Anda mulai mendapatkan 701 dan kesalahan terkait memori lainnya. Untuk informasi selengkapnya, lihat Arsitektur Memori SQL Server

Tekanan memori internal, bukan berasal dari SQL Server

Tekanan memori internal mengacu pada ketersediaan memori rendah yang disebabkan oleh faktor-faktor di dalam proses SQL Server. Ada komponen yang dapat berjalan di dalam proses SQL Server yang "eksternal" ke mesin SQL Server. Contohnya termasuk DLL seperti server tertaut, komponen SQLCLR, prosedur yang diperluas (XP), dan Otomatisasi OLE (sp_OA*). Lainnya termasuk anti-virus atau program keamanan lainnya yang menyuntikkan DLL di dalam proses untuk tujuan pemantauan. Masalah atau desain yang buruk dalam salah satu komponen ini dapat menyebabkan konsumsi memori besar. Misalnya, pertimbangkan penembolokan server tertaut 20 juta baris data yang berasal dari sumber eksternal ke dalam memori SQL Server. Sejauh menyangkut SQL Server, tidak ada petugas memori yang akan melaporkan penggunaan memori yang tinggi, tetapi memori yang digunakan di dalam proses SQL Server akan tinggi. Pertumbuhan memori ini dari DLL server tertaut, misalnya, akan menyebabkan SQL Server mulai memotong penggunaan memorinya (lihat di atas) dan akan menciptakan kondisi memori rendah untuk komponen di dalam SQL Server, menyebabkan kesalahan seperti 701.

Tekanan memori internal, berasal dari komponen SQL Server

Tekanan memori internal yang berasal dari komponen di dalam Mesin SQL Server juga dapat menyebabkan kesalahan 701. Ada ratusan komponen, dilacak melalui petugas memori, yang mengalokasikan memori di SQL Server. Anda harus mengidentifikasi petugas memori mana yang bertanggung jawab atas alokasi memori terbesar untuk dapat menyelesaikan ini lebih lanjut. Misalnya, jika Anda menemukan bahwa petugas memori OBJECTSTORE_LOCK_MANAGER menunjukkan alokasi memori besar, Anda perlu memahami lebih lanjut mengapa Lock Manager mengkonsumsi begitu banyak memori. Anda mungkin menemukan ada kueri yang memperoleh sejumlah besar kunci dan mengoptimalkannya dengan menggunakan indeks, atau mempersingkat transaksi yang menahan kunci untuk jangka waktu yang lama, atau memeriksa apakah eskalasi kunci dinonaktifkan. Setiap petugas memori atau komponen memiliki cara unik untuk mengakses dan menggunakan memori. Untuk informasi selengkapnya, lihat jenis petugas memori dan deskripsinya.

Tindakan pengguna

Jika kesalahan 701 muncul sesekali atau untuk jangka waktu singkat, mungkin ada masalah memori berumur pendek yang menyelesaikannya sendiri. Anda mungkin tidak perlu mengambil tindakan dalam kasus tersebut. Namun, jika kesalahan terjadi beberapa kali, pada beberapa koneksi dan bertahan selama periode detik atau lebih lama, ikuti langkah-langkah untuk memecahkan masalah lebih lanjut.

Daftar berikut menguraikan langkah-langkah umum yang akan membantu dalam memecahkan masalah kesalahan memori.

Alat diagnostik dan pengambilan

Alat diagnostik yang akan memungkinkan Anda mengumpulkan data pemecahan masalah adalah Monitor Performa, sys.dm_os_memory_clerks, dan DBCC MEMORYSTATUS.

Konfigurasikan dan kumpulkan penghitung berikut dengan Monitor Performa:

Memori:Tersedia MB
Proses:Set Kerja
Proses:Byte Privat
SQL Server:Memory Manager: (semua penghitung)
SQL Server:Manajer Buffer: (semua penghitung)

Kumpulkan output berkala dari kueri ini pada SQL Server yang terkena dampak

SELECT pages_kb, type, name, virtual_memory_committed_kb, awe_allocated_kb
FROM sys.dm_os_memory_clerks
ORDER BY pages_kb DESC

Pssdiag atau SQL LogScout

Cara alternatif dan otomatis untuk mengambil titik data ini adalah dengan menggunakan alat seperti PSSDIAG atau SQL LogScout.

Jika Anda menggunakan Pssdiag, konfigurasikan untuk menangkap pengumpul Perfmon dan pengumpul Kesalahan Memori Diagnostik Kustom\SQL
Jika Anda menggunakan SQL LogScout, konfigurasikan untuk mengambil skenario Memori

Bagian berikut menjelaskan langkah-langkah yang lebih rinci untuk setiap skenario - tekanan memori eksternal atau internal.

Tekanan eksternal: diagnostik dan solusi

Untuk mendiagnosis kondisi memori yang rendah pada sistem di luar proses SQL Server, kumpulkan penghitung pemantauan performa. Selidiki apakah aplikasi atau layanan selain SQL Server menggunakan memori di server ini dengan melihat penghitung ini:

Memori:Tersedia MB
Proses:Set Kerja
Proses:Byte Privat

Berikut adalah contoh pengumpulan log Perfmon menggunakan PowerShell

clear
$serverName = $env:COMPUTERNAME
$Counters = @(
   ("\\$serverName" +"\Memory\Available MBytes"),
   ("\\$serverName" +"\Process(*)\Working Set"),
   ("\\$serverName" +"\Process(*)\Private Bytes")
)

Get-Counter -Counter $Counters -SampleInterval 2 -MaxSamples 1 | ForEach-Object  {
$_.CounterSamples | ForEach-Object 	  {
   [pscustomobject]@{
      TimeStamp = $_.TimeStamp
      Path = $_.Path
      Value = ([Math]::Round($_.CookedValue, 3)) }
}
}

Tinjau log Peristiwa Sistem dan cari kesalahan terkait memori (misalnya, memori virtual rendah).
Tinjau log Peristiwa Aplikasi untuk masalah memori terkait aplikasi.

Berikut adalah contoh skrip PowerShell untuk mengkueri log Peristiwa Sistem dan Applicaiton untuk kata kunci "memori". Jangan ragu untuk menggunakan string lain seperti "sumber daya" untuk pencarian Anda:
```
Get-EventLog System -ComputerName "$env:COMPUTERNAME" -Message "*memory*"
Get-EventLog Application -ComputerName "$env:COMPUTERNAME" -Message "*memory*"
```
Atasi masalah kode atau konfigurasi apa pun untuk aplikasi atau layanan yang kurang penting untuk mengurangi penggunaan memori mereka.
Jika aplikasi selain SQL Server menggunakan sumber daya, coba hentikan atau jadwalkan ulang aplikasi ini, atau pertimbangkan untuk menjalankannya di server terpisah. Langkah-langkah ini akan menghilangkan tekanan memori eksternal.

Tekanan memori internal, bukan berasal dari SQL Server: diagnostik dan solusi

Untuk mendiagnosis tekanan memori internal yang disebabkan oleh modul (DLL) di dalam SQL Server, gunakan pendekatan berikut:

Jika SQL Server tidak* menggunakan Halaman Terkunci dalam Memori (API AWE), sebagian besar memorinya tercermin dalam penghitung Byte Proses:Privat (SQLServr instans) di Monitor Performa. Penggunaan memori keseluruhan yang berasal dari dalam mesin SQL Server tercermin dalam penghitung SQL Server:Memory Manager: Total Server Memory (KB ). Jika Anda menemukan perbedaan signifikan antara nilai Proses:Byte Privat dan SQL Server:Memory Manager: Total Server Memory (KB), maka perbedaan tersebut kemungkinan berasal dari DLL (server tertaut, XP, SQLCLR, dll.). Misalnya jika Byte privat adalah 300 GB dan Total Memori Server adalah 250 GB, maka sekitar 50 GB memori keseluruhan dalam proses berasal dari luar mesin SQL Server.
Jika SQL Server menggunakan Halaman Terkunci dalam Memori (API AWE), maka lebih sulit untuk mengidentifikasi masalah karena Monitor performa tidak menawarkan penghitung AWE yang melacak penggunaan memori untuk proses individual. Penggunaan memori keseluruhan yang berasal dari dalam mesin SQL Server tercermin dalam penghitung SQL Server:Memory Manager: Total Server Memory (KB ). Nilai Proses Umum :Byte Privat dapat bervariasi antara 300 MB dan 1-2 GB secara keseluruhan. Jika Anda menemukan penggunaan Proses:Byte Privat yang signifikan di luar penggunaan umum ini, maka perbedaannya kemungkinan berasal dari DLL (server tertaut, XP, SQLCLR, dll.). Misalnya, jika penghitung byte privat adalah 5-4 GB dan SQL Server menggunakan Halaman Terkunci dalam Memori (AWE), maka sebagian besar byte Privat mungkin berasal dari luar mesin SQL Server. Ini adalah teknik perkiraan.
Gunakan utilitas Tasklist untuk mengidentifikasi DLL apa pun yang dimuat di dalam ruang SQL Server:
```
tasklist /M /FI "IMAGENAME eq sqlservr.exe"
```
Anda juga dapat menggunakan kueri ini untuk memeriksa modul yang dimuat (DLL) dan melihat apakah ada sesuatu yang tidak diharapkan berada di sana
```
SELECT * FROM sys.dm_os_loaded_modules
```
Jika Anda menduga bahwa modul Server Tertaut menyebabkan konsumsi memori yang signifikan, maka Anda dapat mengonfigurasinya untuk kehabisan proses dengan menonaktifkan opsi Izinkan pemrosesan . Lihat Membuat Server Tertaut untuk informasi selengkapnya. Tidak semua penyedia OLEDB server tertaut mungkin kehabisan proses; hubungi produsen produk untuk informasi lebih lanjut.
Dalam kasus yang jarang terjadi bahwa objek otomatisasi OLE digunakan (sp_OA*), Anda dapat mengonfigurasi objek untuk dijalankan dalam proses di luar SQL Server dengan mengatur konteks = 4 (Lokal (.exe) server OLE saja.). Untuk informasi selengkapnya, lihat sp_OACreate.

Penggunaan memori internal oleh mesin SQL Server: diagnostik dan solusi

Mulai kumpulkan penghitung pemantauan performa untuk SQL Server:SQL Server:Buffer Manager, SQL Server: Memory Manager.

Kueri DMV petugas memori SQL Server beberapa kali untuk melihat di mana konsumsi memori tertinggi terjadi di dalam mesin:

SELECT pages_kb, type, name, virtual_memory_committed_kb, awe_allocated_kb
FROM sys.dm_os_memory_clerks
ORDER BY pages_kb DESC

Atau, Anda dapat mengamati output DBCC MEMORYSTATUS yang lebih rinci dan caranya berubah ketika Anda melihat pesan kesalahan ini.
```
DBCC MEMORYSTATUS
```
Jika Anda mengidentifikasi pelaku yang jelas di antara petugas memori, fokuslah pada mengatasi spesifik konsumsi memori untuk komponen tersebut. Berikut adalah beberapa contoh:
- Jika MEMORYCLERK_SQLQERESERVATIONS petugas memori mengkonsumsi memori, identifikasi kueri yang menggunakan hibah memori besar dan optimalkan melalui indeks, tulis ulang (hapus ORDER menurut misalnya), atau terapkan petunjuk kueri.
- Jika sejumlah besar rencana kueri ad hoc di-cache, maka petugas memori CACHESTORE_SQLCP akan menggunakan memori dalam jumlah besar. Identifikasi kueri non-parameter yang rencana kuerinya tidak dapat digunakan kembali dan parameterisasi dengan mengonversi ke prosedur tersimpan, atau dengan menggunakan sp_executesql, atau dengan menggunakan parameterisasi FORCED.
- Jika penyimpanan cache rencana objek CACHESTORE_OBJCP mengonsumsi banyak memori, lakukan hal berikut: identifikasi prosedur, fungsi, atau pemicu tersimpan mana yang menggunakan banyak memori dan mungkin mendesain ulang aplikasi. Biasanya ini mungkin terjadi karena sejumlah besar database atau skema dengan ratusan prosedur di masing-masing.
- Jika petugas memori OBJECTSTORE_LOCK_MANAGER menunjukkan alokasi memori besar, identifikasi kueri yang menerapkan banyak kunci dan optimalkan dengan menggunakan indeks. Mempersingkat transaksi yang menyebabkan kunci tidak dirilis untuk jangka panjang dalam tingkat isolasi tertentu, atau periksa apakah eskalasi kunci dinonaktifkan.

Bantuan cepat yang dapat membuat memori tersedia

Tindakan berikut dapat membebaskan beberapa memori dan membuatnya tersedia untuk SQL Server:

Periksa parameter konfigurasi memori SQL Server berikut dan pertimbangkan untuk meningkatkan memori server maks jika memungkinkan:
- memori server maks
- memori server min
  
  Perhatikan pengaturan yang tidak biasa. Koreksi seperlunya. Memperhitungkan peningkatan persyaratan memori. Pengaturan default tercantum dalam opsi konfigurasi memori Server.
Jika Anda belum mengonfigurasi memori server maks terutama dengan Halaman Terkunci dalam Memori, pertimbangkan untuk mengatur ke nilai tertentu untuk mengizinkan beberapa memori untuk OS. Lihat Opsi konfigurasi Halaman Terkunci di Server memori .
Periksa beban kerja kueri: jumlah sesi bersamaan, saat ini menjalankan kueri dan lihat apakah ada aplikasi yang kurang penting yang mungkin dihentikan sementara atau dipindahkan ke SQL Server lain.
Jika Anda menjalankan SQL Server pada komputer virtual (VM), pastikan memori untuk VM tidak di-overcommitted. Untuk ide tentang cara mengonfigurasi memori untuk VM, lihat blog Virtualisasi ini - Mengatasi memori dan cara mendeteksinya dalam VM dan Pemecahan Masalah performa komputer virtual ESX/ESXi (overcommitment memori)
Anda dapat menjalankan perintah DBCC berikut untuk membebaskan beberapa cache memori SQL Server.
- DBCC FREESYSTEMCACHE
- DBCC FREESESSIONCACHE
- DBCC FREEPROCCACHE
Jika Anda menggunakan Resource Governor, kami sarankan Anda memeriksa kumpulan sumber daya atau pengaturan grup beban kerja dan melihat apakah mereka tidak membatasi memori terlalu drastis.
Jika masalah berlanjut, Anda harus menyelidiki lebih lanjut dan mungkin meningkatkan sumber daya server (RAM).