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 resource governor untuk memberlakukan batas jumlah tempdb total ruang yang digunakan oleh grup beban kerja. Grup beban kerja dapat dikaitkan dengan aplikasi, pengguna, sekelompok pengguna, dll. Ketika permintaan (kueri) mencoba melebihi batas, gubernur sumber daya membatalkannya dengan kesalahan berbeda yang menunjukkan bahwa batas grup beban kerja telah diberlakukan.

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 tentang 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 menggunakan GROUP_MAX_TEMPDB_DATA_MB argumen .

    Batas tetap berguna ketika persyaratan penggunaan beban tempdb kerja diketahui terlebih dahulu, atau ketika tempdb ukuran tidak berubah.

  • Atur batas persen menggunakan GROUP_MAX_TEMPDB_DATA_PERCENT argumen .

    Batas persen berguna jika Anda dapat mengubah ukuran maksimum dari tempdb seiring waktu dan Anda ingin tempdb yang tersedia untuk setiap grup beban kerja berubah secara proporsional tanpa perlu mengonfigurasi ulang gubernur sumber daya. 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 MEMBUAT GRUP BEBAN KERJA atau MENGUBAH GRUP BEBAN KERJA.

Jika batas tetap dan persen ditentukan 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.

Konfigurasi batas persen

Batas persen hanya berlaku ketika tempdb konfigurasi file data memenuhi persyaratan yang dirangkum dalam tabel berikut:

Konfigurasi Deskripsi Ukuran maksimum Tempdb (100%) Batas persen berlaku
- GROUP_MAX_TEMPDB_DATA_MB belum diatur
- Untuk semua file data, MAXSIZE tidak UNLIMITED
- Untuk semua file data, FILEGROWTH bukan nol		 tempdb file data dapat ditumbuhkan secara otomatis hingga ukuran maksimumnya Total nilai MAXSIZE dari semua file data Ya
- GROUP_MAX_TEMPDB_DATA_MB belum diatur
- Untuk semua file data, MAXSIZE adalah UNLIMITED
- Untuk semua file data, FILEGROWTH adalah nol		 tempdb file data diatur ke ukuran yang sudah ditentukan dan tidak dapat bertambah lebih jauh Total nilai SIZE dari semua file data Ya
Semua konfigurasi lainnya Tidak.

Kueri berikut memungkinkan Anda melihat 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.

Jika Anda mengatur GROUP_MAX_TEMPDB_DATA_PERCENT dan menjalankan pernyataan ALTER RESOURCE GOVERNOR RECONFIGURE, tetapi konfigurasi file data tidak memenuhi persyaratan, pernyataan selesai dengan sukses dan batas persentase disimpan, tetapi tidak diberlakukan. Dalam hal ini, Anda menerima pesan peringatan 10989, tingkat keparahan 10, GROUP_MAX_TEMPDB_DATA_PERCENT tidak berlaku karena persyaratan konfigurasi tempdb tidak terpenuhi. Pesan juga dicatat dalam log kesalahan.

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 Opsi UBAH File DATABASE dan Grup File.

Nota

Untuk instans baru SQL Server, file MAXSIZE data adalah UNLIMITED dan FILEGROWTH lebih dari nol, yang berarti bahwa batas persentase tidak berlaku. 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.

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%).

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 pengelola sumber daya diaktifkan dan tempdb batas konsumsi ruang diatur untuk grup beban kerja, dan permintaan (kueri) yang berjalan dalam grup beban kerja mencoba untuk menaikkan total tempdb konsumsi ruang oleh grup melewati batas, permintaan dibatalkan dengan kesalahan 1138, tingkat keparahan 17, Tidak dapat mengalokasikan halaman baru untuk database 'tempdb' karena itu akan melebihi batas yang ditetapkan untuk grup beban kerja '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. Kecuali Anda telah menumbuhkan tempdb file data ke ukuran yang dimaksudkan sebelumnya, ruang pada volume tempat tempdb lokasinya mungkin digunakan 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 yang disediakan oleh DMV ini mungkin tidak sama persis dengan statistik yang disediakan dalam 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 di diperbarui sys.dm_resource_governor_workload_groups terus menerus.
    • Tidak melacak halaman peta alokasi indeks (IAM). Untuk informasi selengkapnya, lihat Pemandu arsitektur halaman dan ekstensi.
  • Setelah baris dihapus, atau ketika tabel, indeks, atau partisi dihilangkan atau dipotong, halaman data mungkin dibatalkan alokasinya oleh proses latar belakang asinkron. Dealokasi halaman ini mungkin terjadi dengan penundaan. sys.dm_resource_governor_workload_groups mencerminkan dealokasi halaman ini saat terjadi, bahkan jika sesi yang menyebabkan dealokasi ini ditutup dan tidak lagi ada di sys.dm_db_session_space_usage.

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 melakukannya, 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 kerja kustom dan fungsi klasifikasi yang menempatkan beban kerja dalam grup mereka sendiri, hindari membatasi penggunaan tempdb oleh grup kerja default. Ini mungkin dapat membatalkan kueri dengan kesalahan 1138 ketika tempdb masih memiliki ruang yang tidak digunakan yang tidak dapat dimanfaatkan oleh beban kerja pengguna mana pun.

  • Diizinkan jumlah nilai GROUP_MAX_TEMPDB_DATA_MB untuk semua grup beban kerja 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.

Konten terkait

  • Last updated on