Tata kelola sumber daya ruang Tempdb

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_MB argumen .

    Gunakan batas tetap saat Anda mengetahui persyaratan penggunaan beban tempdb kerja terlebih dahulu atau kapan tempdb ukurannya tidak berubah.

  • Atur batas persen dengan menggunakan GROUP_MAX_TEMPDB_DATA_PERCENT argumen .

    Gunakan batas persen ketika Anda mungkin mengubah ukuran maksimum dari tempdb waktu ke waktu dan Anda ingin ruang yang tempdb tersedia 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 maksimum tempdb, tempdb ruang 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_PERCENT dan 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 tempdb ulang file data untuk memenuhi persyaratan dan jalankan ALTER RESOURCE GOVERNOR RECONFIGURE lagi. Untuk informasi selengkapnya tentang mengonfigurasi SIZE, , FILEGROWTHdan MAXSIZE, lihat ALTER DATABASE Opsi File dan Grup File.

  • Jika batas persen berlaku, dan Anda menambahkan, menghapus, atau mengubah ukuran tempdb file data, Anda harus menjalankan ALTER RESOURCE GOVERNOR RECONFIGURE untuk memperbarui gubernur sumber daya dengan ukuran tempdb maksimum 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 tempdb sesuai ukuran yang dimaksudkan dan atur FILEGROWTH ke nol.
  • Atur MAXSIZE setiap 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 tempdb ditetapkan untuk grup beban kerja, lalu suatu permintaan (kueri) yang berjalan dalam grup beban kerja tersebut mencoba menyebabkan total konsumsi ruang tempdb oleh 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_count dari tampilan manajemen dinamis (DMV) sys.dm_resource_governor_workload_groups bertambah satu, dan tempdb_data_workload_group_limit_reached peristiwa diperpanjang dipicu.

  • Pengatur sumber daya melacak semua tempdb penggunaan yang dapat dikaitkan dengan grup beban kerja, termasuk tabel sementara, variabel (termasuk variabel tabel), parameter bernilai tabel, tabel nontemporer, kursor, dan tempdb penggunaan selama pemrosesan kueri, seperti penampung, tumpahan, tabel kerja, dan file kerja.

    Konsumsi ruang untuk tabel sementara global dan tabel nontemporer di diperhitungkan di tempdb bawah 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 tempdb untuk setiap kelompok beban kerja terlihat di tampilan katalog sys.resource_governor_workload_groups, dalam kolom group_max_tempdb_data_mb dan group_max_tempdb_data_percent.

    Konsumsi saat ini dan konsumsi puncak ruang oleh grup beban kerja diekspos di tempdb, masing-masing di kolom dan tempdb_data_space_kb.

    Petunjuk / Saran

    tempdb_data_space_kb dan peak_tempdb_data_space_kb kolom 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 tempdb penggunaan oleh setiap grup dari waktu ke waktu untuk menetapkan pola penggunaan representatif, lalu tetapkan batas sesuai kebutuhan.

  • tempdb penggunaan oleh penyimpanan versi, termasuk penyimpanan versi persisten (PVS) saat pemulihan database terakselerasi (ADR) diaktifkan di tempdb, tidak diatur karena versi baris mungkin digunakan oleh permintaan di beberapa grup beban kerja.

  • Konsumsi ruang dalam tempdb diperhitungkan sebagai jumlah halaman data 8-KB yang digunakan. Bahkan jika halaman tidak diisi dengan data sepenuhnya, halaman tersebut menambahkan 8 KB ke tempdb penggunaan grup beban kerja.

  • tempdb akuntansi 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 di tempdb, ruang yang digunakan oleh tabel ini tidak dihitung di bawah grup beban kerja lainnya.

  • tempdb tata kelola sumber daya ruang mengontrol ruang dalam tempdb file data, tetapi bukan ruang disk pada volume yang mendasar. Jika Anda tidak memperbesar file data tempdb terlebih dahulu ke ukuran yang ditentukan, ruang pada volume tempat tempdb berada mungkin terpakai oleh file lain. Jika tidak ada ruang yang tersisa untuk tempdb bertambahnya file data, maka tempdb mungkin kehabisan ruang sebelum batas grup beban kerja pada tempdb konsumsi ruang tercapai.

  • Tata kelola sumber daya ruang dalam tempdb berlaku untuk file data tetapi bukan file log transaksi. Untuk memastikan bahwa log masuk tempdb transaksi tidak menggunakan sejumlah besar ruang, aktifkan ADR di tempdb.

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 tempdb penggunaan ruang/memori oleh tugas yang sedang berjalan. Statistik di sys.dm_db_session_space_usage diperbarui ketika tugas selesai. Statistik dalam sys.dm_resource_governor_workload_groups diperbarui terus-menerus. Untuk mendapatkan tempdb penggunaan 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.
  • 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_usage melaporkan baik dealokasi sinkron maupun dealokasi tertunda yang diantrekan yang mungkin belum selesai saat Anda melakukan kueri pada tampilan ini. sys.dm_resource_governor_workload_groups melaporkan dealokasi halaman ini saat terjadi, meskipun sesi yang menyebabkan dealokasi tersebut ditutup dan tidak lagi ada di sys.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 tempdb batas konsumsi ruang ke nilai kecil atau nol, terutama untuk default grup beban kerja. Jika Anda mengatur batas ini ke nilai kecil atau nol, banyak tugas umum mungkin mulai gagal jika mereka perlu mengalokasikan ruang di tempdb. Misalnya, jika Anda menetapkan batas tetap atau persen ke 0 untuk default grup 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 tempdb penggunaan default grup beban kerja. Jika Anda membatasi tempdb konsumsi ruang oleh default grup beban kerja, kueri mungkin mengembalikan kesalahan 1138. Kesalahan ini terjadi ketika tempdb masih memiliki ruang yang tidak digunakan yang tidak dapat digunakan oleh beban kerja pengguna apa pun.

  • Jumlah GROUP_MAX_TEMPDB_DATA_MB nilai untuk semua grup beban kerja dapat melebihi ukuran maksimum tempdb . Misalnya, jika ukuran maksimum tempdb adalah 100 GB, GROUP_MAX_TEMPDB_DATA_MB batas 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 tempdb dengan menyisakan 20 GB untuk grup beban kerja lainnya. Pada saat yang sama, Anda menghindari kueri yang tidak perlu dibatalkan ketika ruang kosong tempdb masih tersedia karena grup beban kerja A dan B tidak mungkin menggunakan sejumlah tempdb besar ruang pada saat yang sama.

    Demikian pula, jumlah GROUP_MAX_TEMPDB_DATA_PERCENT nilai untuk semua grup beban kerja dapat melebihi 100 persen. Anda dapat mengalokasikan lebih tempdb banyak ruang untuk setiap grup jika Anda tahu bahwa beberapa grup tidak mungkin menyebabkan penggunaan tinggi tempdb secara 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_mb Jika kolom adalah NULL, maka MAXSIZE adalah UNLIMITED.
  • Ketika salah satu filegrowth_mb atau filegrowth_percent nol, maka FILEGROWTH adalah 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