Catatan
Akses ke halaman ini memerlukan otorisasi. Anda dapat mencoba masuk atau mengubah direktori.
Akses ke halaman ini memerlukan otorisasi. Anda dapat mencoba mengubah direktori.
Berlaku untuk: SQL Server 2025 (17.x) dan versi yang lebih baru
Saat Anda mengaktifkan tata kelola sumber daya ruang tempdb, Anda meningkatkan keandalan dan menghindari gangguan dengan mencegah kueri atau beban kerja yang tidak terkendali mengonsumsi sejumlah besar ruang di tempdb.
Dimulai dengan SQL Server 2025 (17.x), Anda dapat menggunakan gubernur sumber daya untuk memberlakukan batas jumlah tempdb total ruang yang digunakan oleh grup beban kerja. Ketika sebuah permintaan (kueri) mencoba melampaui batas, Resource Governor membatalkannya dengan pesan kesalahan khusus yang menunjukkan bahwa batas grup beban kerja sedang diterapkan.
Akibatnya, Anda dapat mempartisi ruang bersama tempdb di antara berbagai beban kerja. Misalnya, Anda dapat menetapkan batas yang lebih tinggi untuk grup beban kerja yang digunakan oleh aplikasi misi penting, dan menetapkan batas bawah untuk default grup beban kerja yang digunakan oleh semua beban kerja lainnya.
Untuk contoh konfigurasi langkah demi langkah, lihat Tutorial: Contoh untuk mengonfigurasi tata kelola sumber daya ruang tempdb.
Mulai menggunakan pengelola sumber daya
Resource governor menyediakan kerangka kerja yang fleksibel untuk menetapkan batas ruang yang berbeda untuk aplikasi, pengguna, grup pengguna, dll yang berbeda tempdb . Anda juga dapat mengatur batas berdasarkan logika kustom.
Jika Anda baru mengenal gubernur sumber daya di SQL Server, lihat gubernur sumber daya untuk mempelajari konsep dan kemampuannya.
Untuk panduan konfigurasi gubernur sumber daya dan praktik terbaik, lihat Tutorial: Contoh konfigurasi gubernur sumber daya dan praktik terbaik.
Mengatur batas konsumsi ruang tempdb
Anda dapat membatasi tempdb konsumsi ruang oleh grup beban kerja dengan salah satu dari dua cara:
Atur batas tetap dengan menggunakan
GROUP_MAX_TEMPDB_DATA_MBargumen .Gunakan batas tetap saat Anda mengetahui persyaratan penggunaan beban
tempdbkerja terlebih dahulu atau kapantempdbukurannya tidak berubah.Atur batas persen dengan menggunakan
GROUP_MAX_TEMPDB_DATA_PERCENTargumen .Gunakan batas persen ketika Anda mungkin mengubah ukuran maksimum dari
tempdbwaktu ke waktu dan Anda ingin ruang yangtempdbtersedia untuk setiap grup beban kerja berubah secara proporsional tanpa mengubah konfigurasi grup beban kerja. Misalnya, jika Anda meningkatkan Azure VM yang menjalankan SQL Server dan meningkatkan ukuran maksimumtempdb,tempdbruang yang tersedia untuk setiap kelompok tugas dengan batas persentase juga meningkat.
Untuk informasi selengkapnya tentang GROUP_MAX_TEMPDB_DATA_MB argumen dan GROUP_MAX_TEMPDB_DATA_PERCENT , lihat CREATE WORKLOAD GROUP atau ALTER WORKLOAD GROUP.
Jika Anda menentukan batas tetap dan persen untuk grup beban kerja yang sama, batas tetap lebih diutamakan daripada batas persen.
Pada instans SQL Server tertentu, Anda dapat memiliki campuran grup beban kerja dengan batas tetap, batas persen, atau tidak ada batasan pada tempdb konsumsi ruang. Untuk melihat batas efektif, lihat contoh Lihat batas ruang tempdb efektif per grup beban kerja .
Konfigurasi batas persen
Batas persentase memerlukan konfigurasi khusus tempdb , seperti yang dijelaskan dalam tabel berikut. Saat Anda menjalankan pernyataan ALTER RESOURCE GOVERNOR RECONFIGURE, batas persentase hanya berlaku ketika persyaratan terpenuhi:
| Konfigurasi | Settings | Ukuran maksimum Tempdb (100%) | Batas persen berlaku |
|---|---|---|---|
tempdb file data dapat ditumbuhkan secara otomatis hingga ukuran maksimumnya |
-
GROUP_MAX_TEMPDB_DATA_MB belum diatur- Untuk semua berkas data, MAXSIZE tidak UNLIMITED- Untuk semua file data, FILEGROWTH bukan nol |
Total nilai MAXSIZE dari semua file data |
Ya |
tempdb berkas data telah dialokasikan sebelumnya hingga mencapai ukuran yang ditentukan dan tidak dapat bertambah besar lagi |
-
GROUP_MAX_TEMPDB_DATA_MB belum diatur- Untuk semua file data, MAXSIZE adalah UNLIMITED- Untuk semua file data, FILEGROWTH adalah nol |
Total nilai SIZE dari semua file data |
Ya |
| Semua konfigurasi lainnya | Tidak. |
Untuk melihat konfigurasi tempdb Anda, lihat contoh Melihat konfigurasi file data tempdb.
Saat menggunakan batas persen, pertimbangkan hal berikut:
Jika Anda menetapkan
GROUP_MAX_TEMPDB_DATA_PERCENTdan mengeksekusi pernyataan ALTER RESOURCE GOVERNOR RECONFIGURE, tetapi konfigurasi berkas data tidak memenuhi persyaratan, pernyataan tersebut berhasil dijalankan dan batas persentase disimpan, tetapi tidak diterapkan. Dalam hal ini, Anda menerima pesan peringatan 10989, tingkat keparahan 10, yang juga dicatat dalam log kesalahan:GROUP_MAX_TEMPDB_DATA_PERCENT is not in effect because tempdb configuration requirements aren't met.Agar batas persen efektif, konfigurasi
tempdbulang file data untuk memenuhi persyaratan dan jalankanALTER RESOURCE GOVERNOR RECONFIGURElagi. Untuk informasi selengkapnya tentang mengonfigurasiSIZE, ,FILEGROWTHdanMAXSIZE, lihat ALTER DATABASE Opsi File dan Grup File.Jika batas persen berlaku, dan Anda menambahkan, menghapus, atau mengubah ukuran
tempdbfile data, Anda harus menjalankanALTER RESOURCE GOVERNOR RECONFIGUREuntuk memperbarui gubernur sumber daya dengan ukurantempdbmaksimum baru (100%).
Nota
Untuk instans SQL Server baru, file MAXSIZE data adalah UNLIMITED dan FILEGROWTH lebih besar dari nol, yang berarti bahwa batas persentase tidak efektif. Untuk menggunakan batas persen, Anda harus:
- Siapkan file data
tempdbsesuai ukuran yang dimaksudkan dan aturFILEGROWTHke nol. - Atur
MAXSIZEsetiap file data ke nilai terbatas.
Untuk setiap tempdb volume file data, pastikan bahwa jumlah MAXSIZE nilai untuk file pada volume kurang dari atau sama dengan ruang disk yang tersedia pada volume. Misalnya, jika volume memiliki ruang kosong 100 GB dan memiliki dua tempdb file data, buat MAXSIZE dari setiap file 50 GB atau kurang.
Cara kerjanya
Bagian ini menjelaskan tempdb tata kelola sumber daya ruang secara rinci.
Saat halaman data dialokasikan dan dialokasikan kembali, gubernur sumber daya mempertahankan catatan penggunaan ruang yang dikonsumsi oleh setiap kelompok beban kerja.
Jika Resource Governor diaktifkan dan batas konsumsi ruang
tempdbditetapkan untuk grup beban kerja, lalu suatu permintaan (kueri) yang berjalan dalam grup beban kerja tersebut mencoba menyebabkan total konsumsi ruangtempdboleh grup tersebut melebihi batas, permintaan itu dibatalkan dengan kesalahan 1138, keparahan 17:Could not allocate a new page for database 'tempdb' because that would exceed the limit set for workload group 'workload-group-name'".Ketika permintaan dihentikan dengan kode kesalahan 1138, nilai pada kolom
total_tempdb_data_limit_violation_countdari tampilan manajemen dinamis (DMV) sys.dm_resource_governor_workload_groups bertambah satu, dantempdb_data_workload_group_limit_reachedperistiwa diperpanjang dipicu.Pengatur sumber daya melacak semua
tempdbpenggunaan yang dapat dikaitkan dengan grup beban kerja, termasuk tabel sementara, variabel (termasuk variabel tabel), parameter bernilai tabel, tabel nontemporer, kursor, dantempdbpenggunaan selama pemrosesan kueri, seperti penampung, tumpahan, tabel kerja, dan file kerja.Konsumsi ruang untuk tabel sementara global dan tabel nontemporer di diperhitungkan di
tempdbbawah grup beban kerja yang menyisipkan baris pertama ke dalam tabel, bahkan jika sesi di grup beban kerja lain menambahkan, memodifikasi, atau menghapus baris dalam tabel yang sama.Batas konsumsi yang dikonfigurasi
tempdbuntuk setiap kelompok beban kerja terlihat di tampilan katalog sys.resource_governor_workload_groups, dalam kolomgroup_max_tempdb_data_mbdangroup_max_tempdb_data_percent.Konsumsi saat ini dan konsumsi puncak ruang oleh grup beban kerja diekspos di
tempdb, masing-masing di kolom dantempdb_data_space_kb.Petunjuk / Saran
tempdb_data_space_kbdanpeak_tempdb_data_space_kbkolom dalam sys.dm_resource_governor_workload_groups tetap terjaga meskipun tidak ada batasan pada pemakaian ruang yang ditetapkan.Anda dapat membuat fungsi pengklasifikasi dan grup beban kerja tanpa mengatur batas apa pun pada awalnya. Pantau
tempdbpenggunaan oleh setiap grup dari waktu ke waktu untuk menetapkan pola penggunaan representatif, lalu tetapkan batas sesuai kebutuhan.tempdbpenggunaan oleh penyimpanan versi, termasuk penyimpanan versi persisten (PVS) saat pemulihan database terakselerasi (ADR) diaktifkan ditempdb, tidak diatur karena versi baris mungkin digunakan oleh permintaan di beberapa grup beban kerja.Konsumsi ruang dalam
tempdbdiperhitungkan sebagai jumlah halaman data 8-KB yang digunakan. Bahkan jika halaman tidak diisi dengan data sepenuhnya, halaman tersebut menambahkan 8 KB ketempdbpenggunaan grup beban kerja.tempdbakuntansi ruang dipertahankan selama masa pakai grup beban kerja. Jika grup beban kerja dihapus saat tabel sementara global atau tabel nontemporer yang datanya dihitung dalam grup beban kerja ini tetap ada ditempdb, ruang yang digunakan oleh tabel ini tidak dihitung di bawah grup beban kerja lainnya.tempdbtata kelola sumber daya ruang mengontrol ruang dalamtempdbfile data, tetapi bukan ruang disk pada volume yang mendasar. Jika Anda tidak memperbesar file datatempdbterlebih dahulu ke ukuran yang ditentukan, ruang pada volume tempattempdbberada mungkin terpakai oleh file lain. Jika tidak ada ruang yang tersisa untuktempdbbertambahnya file data, makatempdbmungkin kehabisan ruang sebelum batas grup beban kerja padatempdbkonsumsi ruang tercapai.Tata kelola sumber daya ruang dalam
tempdbberlaku untuk file data tetapi bukan file log transaksi. Untuk memastikan bahwa log masuktempdbtransaksi tidak menggunakan sejumlah besar ruang, aktifkan ADR ditempdb.
Perbedaan dengan pelacakan ruang tingkat sesi
DMV sys.dm_db_session_space_usage menyediakan tempdb statistik alokasi dan dealokasi ruang untuk setiap sesi. Bahkan jika hanya ada satu sesi dalam grup beban kerja, statistik penggunaan ruang dari DMV ini mungkin tidak sama persis dengan statistik dari tampilan sys.dm_resource_governor_workload_groups , karena alasan berikut:
- Tidak seperti
sys.dm_resource_governor_workload_groups,sys.dm_db_session_space_usage:- Tidak mencerminkan
tempdbpenggunaan ruang/memori oleh tugas yang sedang berjalan. Statistik disys.dm_db_session_space_usagediperbarui ketika tugas selesai. Statistik dalamsys.dm_resource_governor_workload_groupsdiperbarui terus-menerus. Untuk mendapatkantempdbpenggunaan ruang oleh tugas yang sedang berjalan terkait dengan sesi, gunakan sys.dm_db_task_space_usage. - Tidak melacak halaman peta alokasi indeks (IAM). Untuk informasi selengkapnya, lihat Panduan arsitektur halaman dan tingkat.
- Tidak mencerminkan
- Saat baris dihapus, atau ketika tabel, indeks, atau partisi dihapus atau dikosongkan, mesin basis data membebaskan alokasi halaman data. Dealokasi dapat terjadi secara sinkron sebagai bagian dari pernyataan, atau dapat ditunda dan dilakukan secara asinkron oleh sesi sistem.
sys.dm_db_session_space_usagemelaporkan baik dealokasi sinkron maupun dealokasi tertunda yang diantrekan yang mungkin belum selesai saat Anda melakukan kueri pada tampilan ini.sys.dm_resource_governor_workload_groupsmelaporkan dealokasi halaman ini saat terjadi, meskipun sesi yang menyebabkan dealokasi tersebut ditutup dan tidak lagi ada disys.dm_db_session_space_usage.
Untuk informasi lebih lanjut, lihat contoh Melihat perkiraan penggunaan ruang tempdb sesi.
Praktik terbaik untuk tata kelola sumber daya ruang tempdb
Sebelum mengonfigurasi tempdb tata kelola sumber daya ruang, pertimbangkan praktik terbaik berikut:
Tinjau praktik terbaik umum untuk pengelola sumber daya.
Untuk sebagian besar skenario, hindari mengatur
tempdbbatas konsumsi ruang ke nilai kecil atau nol, terutama untukdefaultgrup beban kerja. Jika Anda mengatur batas ini ke nilai kecil atau nol, banyak tugas umum mungkin mulai gagal jika mereka perlu mengalokasikan ruang ditempdb. Misalnya, jika Anda menetapkan batas tetap atau persen ke 0 untukdefaultgrup beban kerja, Anda mungkin tidak dapat membuka Object Explorer di SQL Server Management Studio (SSMS).Kecuali Anda membuat grup beban kerja kustom dan fungsi pengklasifikasi yang menempatkan beban kerja di grup khusus mereka, hindari membatasi
tempdbpenggunaandefaultgrup beban kerja. Jika Anda membatasitempdbkonsumsi ruang olehdefaultgrup beban kerja, kueri mungkin mengembalikan kesalahan 1138. Kesalahan ini terjadi ketikatempdbmasih memiliki ruang yang tidak digunakan yang tidak dapat digunakan oleh beban kerja pengguna apa pun.Jumlah
GROUP_MAX_TEMPDB_DATA_MBnilai untuk semua grup beban kerja dapat melebihi ukuran maksimumtempdb. Misalnya, jika ukuran maksimumtempdbadalah 100 GB,GROUP_MAX_TEMPDB_DATA_MBbatas untuk grup beban kerja A dan grup beban kerja B masing-masing bisa 80 GB.Pendekatan ini tetap mencegah setiap grup beban kerja dari mengonsumsi semua ruang di
tempdbdengan menyisakan 20 GB untuk grup beban kerja lainnya. Pada saat yang sama, Anda menghindari kueri yang tidak perlu dibatalkan ketika ruang kosongtempdbmasih tersedia karena grup beban kerja A dan B tidak mungkin menggunakan sejumlahtempdbbesar ruang pada saat yang sama.Demikian pula, jumlah
GROUP_MAX_TEMPDB_DATA_PERCENTnilai untuk semua grup beban kerja dapat melebihi 100 persen. Anda dapat mengalokasikan lebihtempdbbanyak ruang untuk setiap grup jika Anda tahu bahwa beberapa grup tidak mungkin menyebabkan penggunaan tinggitempdbsecara bersamaan.
Examples
Lihat konfigurasi file data tempdb
Kueri berikut menunjukkan konfigurasi file data saat ini tempdb :
SELECT file_id,
name,
size * 8. / 1024 AS size_mb,
IIF (max_size = -1, NULL, max_size * 8. / 1024) AS maxsize_mb,
IIF (is_percent_growth = 0, growth * 8. / 1024, NULL) AS filegrowth_mb,
IIF (is_percent_growth = 1, growth, NULL) AS filegrowth_percent
FROM sys.master_files
WHERE database_id = 2
AND type_desc = 'ROWS';
Untuk file tertentu dalam tataan hasil:
-
maxsize_mbJika kolom adalahNULL, makaMAXSIZEadalahUNLIMITED. - Ketika salah satu
filegrowth_mbataufilegrowth_percentnol, makaFILEGROWTHadalah nol.
Lihat batas ruang tempdb aktual untuk setiap grup beban kerja
Kueri berikut menunjukkan batas konsumsi ruang efektif tempdb untuk setiap grup beban kerja. Batas dikembalikan dalam megabyte untuk konfigurasi batas tetap atau batas persen .
group_effective_limit_mb Jika kolom adalah NULL, itu berarti salah satu hal berikut:
- Tidak ada batas tetap atau persentase yang dikonfigurasi.
- Persyaratan untuk menggunakan konfigurasi batas persen tidak terpenuhi.
SELECT wg.group_id,
wg.name,
tf.tempdb_max_size_mb,
CASE
WHEN wg.group_max_tempdb_data_mb IS NOT NULL
THEN wg.group_max_tempdb_data_mb
WHEN wg.group_max_tempdb_data_percent IS NOT NULL AND tf.tempdb_max_size_mb IS NOT NULL
THEN 0.01 * wg.group_max_tempdb_data_percent * tf.tempdb_max_size_mb
ELSE NULL END AS group_effective_limit_mb
FROM sys.resource_governor_workload_groups AS wg
CROSS APPLY (
SELECT IIF (SUM(IIF (max_size <> -1
AND growth > 0, 1, 0)) = COUNT(1) /* autogrow up to the maxsize */
OR SUM(IIF (max_size = -1
AND growth = 0, 1, 0)) = COUNT(1), /* pregrown and fixed */
SUM(IIF (growth = 0, size, max_size)) * 8 / 1024., NULL) AS tempdb_max_size_mb
FROM sys.master_files
WHERE database_id = 2
AND type_desc = 'ROWS'
) AS tf;
Lihat perkiraan penggunaan ruang tempdb sesi
Contoh ini menunjukkan cara menemukan kontributor teratas untuk penggunaan ruang tempdb dalam sebuah grup beban kerja.
Kueri melaporkan tempdb penggunaan ruang pengguna oleh suatu sesi dalam megabyte, dan juga melaporkannya sebagai persentase dari total tempdb penggunaan ruang oleh kelompok beban kerja. Penggunaan yang dilaporkan bersifat perkiraan. Untuk informasi lebih lanjut, lihat Perbedaan dengan pelacakan ruang tingkat sesi.
WITH task_space AS
(
SELECT database_id,
session_id,
SUM(user_objects_alloc_page_count - user_objects_dealloc_page_count) AS user_net_pages
FROM sys.dm_db_task_space_usage
WHERE database_id = 2
GROUP BY database_id, session_id
),
session_space AS
(
SELECT s.session_id,
s.user_objects_alloc_page_count - s.user_objects_dealloc_page_count
- s.user_objects_deferred_dealloc_page_count + COALESCE(t.user_net_pages, 0)
AS user_net_pages
FROM sys.dm_db_session_space_usage AS s
LEFT HASH JOIN task_space AS t
ON t.database_id = s.database_id
AND
t.session_id = s.session_id
WHERE s.database_id = 2
)
SELECT u.session_id,
s.login_time,
g.name AS workload_group_name,
s.login_name,
s.host_name,
s.program_name,
CAST(u.user_net_pages * 8.0 / 1024 AS decimal(19, 3)) AS user_tempdb_usage_mb,
CAST(u.user_net_pages * 8.0 * 100.0 / NULLIF(g.tempdb_data_space_kb, 0) AS decimal(19, 2))
AS approximate_group_usage_percent
FROM session_space AS u
INNER JOIN sys.dm_exec_sessions AS s
ON s.session_id = u.session_id
LEFT JOIN sys.dm_resource_governor_workload_groups AS g
ON g.group_id = s.group_id
WHERE s.is_user_process = 1
ORDER BY user_tempdb_usage_mb DESC, u.session_id;
Langkah selanjutnya
Konten terkait
- Pengelola Sumber Daya
- Tutorial: Contoh konfigurasi Resource Governor dan praktik terbaik
- ALTER RESOURCE GOVERNOR (Transact-SQL)
- CREATE WORKLOAD GROUP (Transact-SQL)
- ALTER WORKLOAD GROUP (Transact-SQL)
- DROP WORKLOAD GROUP (Transact-SQL)
- sys.resource_governor_workload_groups (T-SQL)
- sys.dm_resource_governor_workload_groups (T-SQL)