database tempdb

Berlaku untuk:SQL Server Azure SQL Database Azure SQL Managed Instance Database SQL di Microsoft Fabric

Artikel ini menjelaskan database sistem tempdb, sumber daya global yang tersedia untuk semua pengguna yang terhubung ke instans Mesin Database di SQL Server, Azure SQL Database, atau Azure SQL Managed Instance.

Overview

Database tempdb sistem adalah sumber daya global yang menyimpan:

Objek pengguna yang dibuat secara eksplisit. Mereka meliputi:
- Tabel dan indeks sementara global atau lokal pada tabel ini
- Prosedur tersimpan sementara
- Variabel besar, termasuk variabel tabel
- Tabel yang dikembalikan dalam fungsi bernilai tabel
- Cursors
Objek pengguna yang dapat dibuat dalam database pengguna juga dapat dibuat di tempdb, namun dibuat tanpa jaminan durabilitas, dan dihilangkan saat instans Mesin Database dimulai ulang.
Objek internal yang dibuat mesin database. Mereka meliputi:
- Tabel kerja untuk menyimpan hasil menengah untuk spool, kursor, urutan, dan penyimpanan objek besar (LOB) sementara.
- Berkas kerja untuk menjalankan pencocokan hash atau operasi penggabungan hash.
- Hasil pengurutan menengah untuk operasi seperti membuat atau membangun ulang indeks (jika SORT_IN_TEMPDB ditentukan), atau kueri GROUP BY, ORDER BY, atau UNION tertentu.
Setiap objek internal menggunakan minimal sembilan halaman: satu halaman IAM dan satu ekstensi yang terdiri dari delapan halaman. Untuk informasi selengkapnya tentang halaman dan jangkauan, lihat Halaman dan luasnya.
versi menyimpan, yang merupakan kumpulan halaman data yang menyimpan baris data yang mendukung penerapan versi baris . Repositori versi berisi:
- Versi baris yang dihasilkan oleh transaksi modifikasi data dalam database yang menggunakan transaksi isolasi berbasis versi baris READ COMMITTED atau SNAPSHOT.
- Versi baris yang dihasilkan oleh transaksi modifikasi data untuk fitur, seperti operasi indeks online, Beberapa Himpunan Hasil Aktif (MARS), dan pemicu AFTER.
Dimulai dengan SQL Server 2025 (17.x), ketika pemulihan database terakselerasi (ADR) diaktifkan di tempdb, tempdb berisi dua penyimpanan versi yang berbeda dan independen:
- Penyimpanan versi tradisional, digunakan untuk versi baris yang dihasilkan oleh transaksi dalam database pengguna yang tidak mengaktifkan ADR.
- Penyimpanan versi persisten (PVS), digunakan untuk menyimpan versi baris yang dihasilkan oleh transaksi di tempdb.
Pastikan untuk mengalokasikan ruang disk yang memadai untuk tempdb file data agar berisi kedua penyimpanan versi jika ADR di tempdb diaktifkan. Bergantung pada beban kerja Anda, ukuran tempdb file data mungkin perlu ditingkatkan untuk berisi data PVS.

Untuk informasi selengkapnya tentang tempdb penggunaan ruang oleh penyimpanan versi tradisional, lihat Ruang yang digunakan dalam tempdb. Untuk informasi selengkapnya tentang ruang yang digunakan oleh PVS, lihat Ruang yang digunakan oleh penyimpanan versi persisten (PVS).

Operasi dalam tempdb dicatat secara minimal. tempdb dibuat ulang setiap kali Mesin Database dimulai sehingga sistem selalu dimulai dengan database tempdb kosong. Prosedur tersimpan sementara dan tabel sementara lokal dihilangkan secara otomatis ketika sesi yang membuatnya terputus.

tempdb tidak pernah memiliki apa pun untuk disimpan dari satu periode waktu aktif Mesin Database ke periode waktu aktif lainnya. Operasi pencadangan dan pemulihan tidak diizinkan pada tempdb.

Properti fisik tempdb di SQL Server

Tabel berikut mencantumkan nilai tempdb konfigurasi awal data dan file log di SQL Server. Nilai didasarkan pada standar untuk basis data model. Ukuran file-file ini mungkin sedikit berbeda untuk edisi SQL Server yang berbeda.

File	Nama logika	Nama fisik	Ukuran awal	Pertumbuhan file
Data utama	`tempdev`	`tempdb.mdf`	8 megabyte	Pertumbuhan otomatis sebesar 64 MB hingga disk penuh
File data sekunder	`temp#`	`tempdb_mssql_#.ndf`	8 megabyte	Pertumbuhan otomatis sebesar 64 MB hingga disk penuh
Log	`templog`	`templog.ldf`	8 megabyte	Autogrow sebesar 64 megabyte hingga maksimum 2 terabyte

Semua file data tempdb harus selalu memiliki ukuran awal dan parameter pertumbuhan yang sama.

Jumlah file data tempdb

Bergantung pada versi Mesin Database, konfigurasinya, dan beban kerja, tempdb mungkin memerlukan beberapa file data untuk mengurangi pertikaian alokasi.

Jumlah total file data yang direkomendasikan tergantung pada jumlah prosesor logis pada komputer. Sebagai panduan umum:

Jika jumlah prosesor logis kurang dari atau sama dengan delapan, gunakan jumlah file data yang sama.
Jika jumlah prosesor logis lebih besar dari delapan, gunakan delapan file data.
Jika ketidakcocokan alokasi tempdb masih diamati, tingkatkan jumlah file data dengan kelipatan empat hingga ketidakcocokan menurun ke tingkat yang dapat diterima, atau buat perubahan pada beban kerja.

Untuk informasi selengkapnya, lihat Rekomendasi untuk mengurangi ketidakcocokan alokasi dalam database tempdb SQL Server.

Untuk memeriksa ukuran dan parameter pertumbuhan saat ini untuk tempdb, gunakan tampilan katalog sys.database_files di tempdb.

Memindahkan data tempdb dan file log di SQL Server

Untuk memindahkan tempdb data dan file log, lihat Memindahkan database sistem.

Opsi database untuk tempdb di SQL Server

Tabel berikut ini mencantumkan nilai default untuk setiap opsi database dalam tempdb database dan apakah opsi dapat dimodifikasi. Untuk melihat pengaturan saat ini untuk opsi ini, gunakan tampilan katalog sys.databases .

Opsi database	Nilai standar	Dapat dimodifikasi
`ACCELERATED_DATABASE_RECOVERY`	`OFF`	Yes¹
`ALLOW_SNAPSHOT_ISOLATION`	`OFF`	Yes
`ANSI_NULL_DEFAULT`	`OFF`	Yes
`ANSI_NULLS`	`OFF`	Yes
`ANSI_PADDING`	`OFF`	Yes
`ANSI_WARNINGS`	`OFF`	Yes
`ARITHABORT`	`OFF`	Yes
`AUTO_CLOSE`	`OFF`	No
`AUTO_CREATE_STATISTICS`	`ON`	Yes
`AUTO_SHRINK`	`OFF`	No
`AUTO_UPDATE_STATISTICS`	`ON`	Yes
`AUTO_UPDATE_STATISTICS_ASYNC`	`OFF`	Yes
`AUTOMATIC_TUNING (FORCE_LAST_GOOD_PLAN)`	`OFF`	No
`CHANGE_TRACKING`	`OFF`	No
`COMPATIBILITY_LEVEL`	Tergantung pada versi Mesin Database. Untuk informasi selengkapnya, lihat tingkat kompatibilitas ALTER DATABASE (Transact-SQL).	Yes
`CONCAT_NULL_YIELDS_NULL`	`OFF`	Yes
`CONTAINMENT`	`NONE`	No
`CURSOR_CLOSE_ON_COMMIT`	`OFF`	Yes
`CURSOR_DEFAULT`	`GLOBAL`	Yes
Status database	`ONLINE`	No
Pembaruan database	`READ_WRITE`	No
Akses pengguna database	`MULTI_USER`	No
`DATE_CORRELATION_OPTIMIZATION`	`OFF`	Yes
`DB_CHAINING`	`ON`	No
`DELAYED_DURABILITY`	`DISABLED` Terlepas dari opsi ini, durabilitas tertunda selalu diaktifkan pada `tempdb`.	Yes
`ENCRYPTION`	`OFF`	No
`MIXED_PAGE_ALLOCATION`	`OFF`	No
`NUMERIC_ROUNDABORT`	`OFF`	Yes
`PAGE_VERIFY`	`CHECKSUM` untuk penginstalan baru SQL Server Nilai `PAGE_VERIFY` yang ada mungkin dipertahankan ketika instans SQL Server ditingkatkan.	Yes
`PARAMETERIZATION`	`SIMPLE`	Yes
`QUOTED_IDENTIFIER`	`OFF`	Yes
`READ_COMMITTED_SNAPSHOT`	`OFF`	No
`RECOVERY`	`SIMPLE`	No
`RECURSIVE_TRIGGERS`	`OFF`	Yes
Pialang Layanan	`ENABLE_BROKER`	Yes
`TARGET_RECOVERY_TIME`	60	Yes
`TEMPORAL_HISTORY_RETENTION`	`ON`	Yes
`TRUSTWORTHY`	`OFF`	No

¹ Pengaturan ACCELERATED_DATABASE_RECOVERY ke ON dalam tempdb didukung dimulai dengan SQL Server 2025 (17.x). Di versi SQL Server sebelumnya, memodifikasi ACCELERATED_DATABASE_RECOVERY opsi untuk tempdb database tidak diizinkan.

Untuk deskripsi opsi database ini, lihat OPSI UBAH KUMPULAN DATABASE.

tempdb pada Azure SQL Database

Di Azure SQL Database, beberapa aspek perilaku dan konfigurasi tempdb berbeda dari SQL Server.

Untuk database tunggal, setiap database di server logis memiliki tempdbsendiri. Dalam kumpulan elastis, tempdb adalah sumber daya bersama untuk semua database di kumpulan yang sama tetapi objek sementara yang dibuat oleh satu database tidak terlihat oleh database lain di kumpulan elastis yang sama.

Objek dalam tempdb, termasuk tampilan katalog dan tampilan manajemen dinamis (DMV), dapat diakses melalui referensi lintas database ke database tempdb. Misalnya, Anda bisa melakukan kueri pada pandangan sys.database_files.

SELECT file_id,
       type_desc,
       name,
       size,
       max_size,
       growth
FROM tempdb.sys.database_files;

Tabel sementara global di Azure SQL Database berlingkup basis data. Untuk informasi selengkapnya, lihat tabel sementara global dengan lingkup database di Azure SQL Database.

Untuk mempelajari selengkapnya tentang jenis ukuran tempdb di Azure SQL Database, pelajari:

model pembelian vCore: database tunggal, database kumpulan
Model pembelian DTU: database tunggal, database terkumpul

tempdb dalam SQL Managed Instance

Di Azure SQL Managed Instance, beberapa aspek perilaku tempdb dan konfigurasi default berbeda dari SQL Server.

Anda dapat mengonfigurasi jumlah tempdb file, kenaikan pertumbuhannya, dan ukuran maksimumnya. Untuk informasi selengkapnya tentang mengonfigurasi tempdb pengaturan di Azure SQL Managed Instance, lihat Mengonfigurasi pengaturan tempdb untuk Azure SQL Managed Instance.

Azure SQL Managed Instance mendukung objek sementara dengan cara yang sama seperti SQL Server, di mana semua tabel sementara global dan prosedur tersimpan sementara global dapat diakses oleh semua sesi pengguna dalam instans terkelola SQL yang sama.

Untuk mempelajari selengkapnya tentang tempdb ukuran di Azure SQL Managed Instance, tinjau batas sumber daya.

tempdb dalam database SQL di Fabric

Untuk mempelajari selengkapnya tentang tempdb ukuran dalam database SQL di Microsoft Fabric, tinjau bagian batas sumber daya dalam Perbandingan fitur: Azure SQL Database dan database SQL di Microsoft Fabric.

Demikian pula dengan Azure SQL Database, tabel sementara global dalam database SQL di Microsoft Fabric dilingkup database. Untuk informasi selengkapnya, lihat tabel sementara global dengan lingkup database di Azure SQL Database.

Keterbatasan

Operasi berikut ini tidak dapat dilakukan pada tempdb database:

Menambahkan grup file.
Mencadangkan atau memulihkan basis data.
Mengubah koladasi. Kolasi bawaan adalah kolasi server.
Mengubah pemilik database. tempdb dimiliki oleh sa.
Membuat rekam jepret database.
Menghapus database.
Menghilangkan pengguna tamu dari database.
Mengaktifkan Ubah Pengambilan Data.
Berpartisipasi dalam penggandaan database.
Menghapus grup file utama, file data utama, atau file log.
Mengganti nama database atau grup file utama.
Menjalankan DBCC CHECKALLOC.
Menjalankan DBCC CHECKCATALOG.
Mengatur database ke OFFLINE.
Mengatur database atau grup file utama ke READ_ONLY.

Permissions

Setiap pengguna dapat membuat objek sementara di tempdb.

Pengguna hanya dapat mengakses objek non-sementara mereka sendiri di tempdb, kecuali mereka menerima izin tambahan.

Dimungkinkan untuk mencabut izin CONNECT pada tempdb untuk mencegah pengguna atau peran database menggunakan tempdb. Ini tidak disarankan karena banyak operasi memerlukan penggunaan tempdb.

Mengoptimalkan performa tempdb di SQL Server

Ukuran dan penempatan fisik file tempdb dapat memengaruhi performa. Misalnya, jika ukuran awal tempdb terlalu kecil, waktu dan sumber daya mungkin akan diperlukan untuk menumbuhkan tempdb secara otomatis ke ukuran yang diperlukan untuk mendukung beban kerja setiap kali instans Mesin Database dilakukan restart.

Jika memungkinkan, gunakan inisialisasi file instan untuk meningkatkan performa operasi pertumbuhan untuk file data.
- Dimulai dengan SQL Server 2022 (16.x), peristiwa pertumbuhan file log transaksi hingga 64 MB juga dapat memperoleh manfaat dari inisialisasi file instan. Untuk informasi selengkapnya, lihat inisialisasi file instan dan log transaksi.
Pra-alokasikan ruang untuk semua tempdb file dengan mengatur ukuran file ke nilai yang cukup besar untuk mengakomodasi beban kerja umum di lingkungan. Pralokasi mencegah tempdb dari terlalu sering mengalami pertumbuhan otomatis, yang dapat berdampak negatif pada performa.
File-file dalam database tempdb harus diatur agar autogrow untuk memberikan ruang selama peristiwa pertumbuhan yang tidak terencana.
Membalikan tempdb menjadi beberapa file data dengan ukuran yang sama dapat meningkatkan efisiensi operasi yang menggunakan tempdb.
- Untuk menghindari ketidakseimbangan alokasi data, file data harus memiliki ukuran awal dan parameter pertumbuhan yang sama karena Mesin Database menggunakan algoritma pengisian proporsional yang mendukung alokasi dalam file dengan ruang yang lebih kosong.
- Atur kenaikan pertumbuhan file ke ukuran yang wajar, misalnya 64 MB, dan buat kenaikan pertumbuhan sama untuk semua file data untuk mencegah ketidakseimbangan pertumbuhan.
Dimulai dengan SQL Server 2025 (17.x), pertimbangkan untuk mengaktifkan pemulihan database yang dipercepattempdb untuk mendapatkan manfaat pembatalan transaksi instan dan pemotongan log agresif untuk transaksi di tempdb. Untuk informasi selengkapnya, lihat ADR di tempdb.
- Mengaktifkan atau menonaktifkan ADR di tempdb mengharuskan dimulai ulangnya Mesin Database agar perubahan berlaku.

Untuk memeriksa ukuran saat ini dan parameter pertumbuhan untuk tempdb, gunakan kueri berikut:

SELECT name AS file_name,
       type_desc AS file_type,
       size * 8.0 / 1024 AS size_mb,
       max_size * 8.0 / 1024 AS max_size_mb,
       CAST (IIF (max_size = 0, 0, 1) AS BIT) AS is_autogrowth_enabled,
       CASE WHEN growth = 0 THEN growth
            WHEN growth > 0 AND is_percent_growth = 0 THEN growth * 8.0 / 1024
            WHEN growth > 0 AND is_percent_growth = 1 THEN growth
       END AS growth_increment_value,
       CASE WHEN growth = 0 THEN 'Autogrowth is disabled.'
            WHEN growth > 0 AND is_percent_growth = 0 THEN 'Megabytes'
            WHEN growth > 0 AND is_percent_growth = 1 THEN 'Percent'
       END AS growth_increment_value_unit
FROM tempdb.sys.database_files;

Letakkan tempdb database pada subsistem I/O yang cepat. File data individual atau grup file data tempdb tidak perlu berada di disk yang berbeda kecuali Anda mengalami penyempitan I/O tingkat disk.

Jika ada persaingan I/O antara tempdb dan database pengguna, letakkan file tempdb pada disk yang berbeda dari disk yang digunakan oleh database pengguna.

Note

Untuk meningkatkan performa, durabilitas tertunda selalu diaktifkan pada tempdb meskipun opsi database DELAYED_DURABILITY diatur ke DISABLED. Karena tempdb dibuat ulang saat startup, tempdb tidak melalui proses pemulihan dan tidak memberikan jaminan durabilitas.

Peningkatan tempdb untuk SQL Server

Diperkenalkan di SQL Server 2025 (17.x)

Tempdb tata kelola sumber daya ruang memberlakukan batas jumlah tempdb total ruang yang digunakan oleh aplikasi atau beban kerja pengguna. Ini meningkatkan keandalan dan menghindari pemadaman dengan mencegah kueri atau beban kerja yang berlebihan mengonsumsi sejumlah besar ruang di tempdb. Untuk informasi selengkapnya, lihat Tata kelola sumber daya ruang Tempdb.
Pemulihan database yang dipercepat didukung di tempdb, menyediakan pembatalan transaksi instan dan pemotongan log agresif untuk transaksi di tempdb.

Diperkenalkan di SQL Server 2022 (16.x)

memperkenalkan peningkatan skalabilitas dengan penyempurnaan konkurensi pengait halaman sistem. Pembaruan bersamaan untuk halaman peta alokasi global (GAM) dan halaman peta alokasi global bersama (SGAM) mengurangi kontensi kait halaman saat mengalokasikan/membatalkan alokasi halaman data dan ekstensi. Penyempurnaan ini berlaku untuk semua database pengguna dan terutama menguntungkan beban kerja berat di tempdb. Untuk informasi selengkapnya tentang halaman GAM dan SGAM, lihat Di bawah sampul: halaman GAM, SGAM, dan PFS. Untuk informasi selengkapnya, tonton Penyempurnaan Konkurensi Latch Halaman Sistem (Ep. 6) | Data Terekspos.

Diperkenalkan di SQL Server 2019 (15.x)

Mesin Database tidak menggunakan opsi FILE_FLAG_WRITE_THROUGH saat membuka file tempdb untuk memungkinkan throughput disk maksimum. Karena tempdb dibuat ulang saat startup, opsi ini tidak diperlukan untuk memberikan durabilitas data. Untuk informasi selengkapnya tentang FILE_FLAG_WRITE_THROUGH, lihat Pengelogan dan algoritma penyimpanan data yang memperluas keandalan data di SQL Server.
Metadata TempDB yang dioptimalkan untuk memori menghilangkan kontensi metadata objek sementara di tempdb.
Pembaruan halaman Ruang Kosong Halaman Bersamaan (PFS) mengurangi perselisihan kait halaman di semua database, masalah yang paling sering terjadi di tempdb. Peningkatan ini mengubah manajemen konkurensi pembaruan halaman PFS sehingga dapat diperbarui di bawah kait bersama, bukan kait eksklusif. Perilaku ini aktif secara default di semua database (termasuk tempdb) dimulai dengan SQL Server 2019 (15.x). Untuk informasi selengkapnya tentang halaman PFS, baca Di balik layar: halaman GAM, SGAM, dan PFS.
Secara default, penginstalan baru SQL Server di Linux membuat beberapa tempdb file data, berdasarkan jumlah inti logis (dengan hingga delapan file data). Ini tidak berlaku untuk peningkatan versi minor atau utama di tempat. Setiap file data tempdb adalah 8 MB, dengan pertumbuhan otomatis 64 MB. Perilaku ini mirip dengan penginstalan SQL Server default di Windows.

Diperkenalkan di SQL Server 2017 (14.x)

Pengalaman Penyiapan SQL meningkatkan panduan untuk alokasi awal berkas tempdb. Penyiapan SQL memperingatkan pelanggan jika ukuran file awal diatur ke nilai yang lebih besar dari 1 GB atau jika inisialisasi file instan tidak diaktifkan, untuk mencegah penundaan memulai instans.
Tampilan manajemen dinamis sys.dm_tran_version_store_space_usage melacak penggunaan penyimpanan versi per database. DMV ini berguna untuk DBA yang ingin merencanakan ukuran tempdb secara proaktif berdasarkan persyaratan penggunaan penyimpanan versi per database.
Pemrosesan kueri cerdas, dengan fitur seperti penggabungan adaptif dan umpan balik alokasi memori, mengurangi pemakaian memori berlebih pada eksekusi kueri berturut-turut, sehingga mengurangi penggunaan sumber daya tempdb.

Diperkenalkan di SQL Server 2016 (13.x)

Tabel sementara dan variabel tabel di-cache. Penembolokan memungkinkan operasi yang menghilangkan dan membuat objek sementara berjalan dengan sangat cepat. Penembolokan juga mengurangi alokasi halaman dan pertikaian metadata.
Protokol kait halaman alokasi ditingkatkan untuk mengurangi jumlah UP (pembaruan) kait yang digunakan.
Pengurangan overhead pencatatan untuk tempdb dilakukan untuk mengurangi konsumsi bandwidth I/O disk pada file log tempdb.
Selama instalasi instance baru, SQL Setup menambahkan beberapa file data tempdb. Tinjau rekomendasi dan konfigurasikan tempdb Anda di halaman Konfigurasi Mesin Database pada SQL Setup, atau gunakan parameter baris perintah /SQLTEMPDBFILECOUNT. Secara bawaan, SQL Setup menambahkan file data tempdb sebanyak jumlah prosesor logis atau delapan, mana yang lebih sedikit.
Ketika ada beberapa tempdb file data, semua file ditumbuhi secara otomatis pada saat yang sama dan dengan jumlah yang sama, tergantung pada pengaturan pertumbuhan. bendera pelacakan 1117 tidak lagi diperlukan. Untuk informasi selengkapnya, baca perubahan -T1117 dan -T1118 untuk TEMPDB dan database pengguna.
Semua alokasi dalam tempdb menggunakan ekstensi seragam. bendera pelacakan 1118 tidak lagi diperlukan. Untuk informasi selengkapnya tentang peningkatan performa di tempdb, lihat artikel blog TEMPDB - Berkas-berkas dan Trace Flag serta Pembaruan, Oh My!.
Properti AUTOGROW_ALL_FILES selalu diaktifkan untuk grup file PRIMARY.

Metadata TempDB yang dioptimalkan untuk memori

Kontensi metadata objek sementara telah selama ini menjadi penghambat untuk skala banyak beban kerja SQL Server. Untuk mengatasinya, SQL Server 2019 (15.x) memperkenalkan fitur yang merupakan bagian dari keluarga fitur basis data dalam memori: Metadata TempDB yang dioptimalkan untuk memori.

Mengaktifkan fitur metadata TempDB yang dioptimalkan memori menghapus hambatan ini untuk beban kerja yang sebelumnya dibatasi oleh ketidakcocokan metadata objek sementara di dalam tempdb. Dimulai dengan SQL Server 2019 (15.x), tabel sistem yang terlibat dalam mengelola metadata objek sementara dapat menjadi tabel yang bebas kait, tidak tahan lama, dan dioptimalkan memori.

Tip

Karena keterbatasan saat ini, sebaiknya aktifkan metadata TempDB yang dioptimalkan Memori hanya ketika ketidakcocokan metadata objek terjadi dan berdampak signifikan pada beban kerja Anda.

Kueri diagnostik berikut mengembalikan satu atau beberapa baris jika terjadi pertikaian metadata objek sementara. Setiap baris mewakili tabel sistem , dan mengembalikan jumlah sesi yang bersaing untuk akses ke tabel tersebut pada saat kueri diagnostik ini dijalankan.

SELECT OBJECT_NAME(dpi.object_id, dpi.database_id) AS system_table_name,
       COUNT(DISTINCT (r.session_id)) AS session_count
FROM sys.dm_exec_requests AS r
CROSS APPLY sys.fn_PageResCracker(r.page_resource) AS prc
CROSS APPLY sys.dm_db_page_info(prc.db_id, prc.file_id, prc.page_id, 'LIMITED') AS dpi
WHERE dpi.database_id = 2
      AND dpi.object_id IN (3, 9, 34, 40, 41, 54, 55, 60, 74, 75)
      AND UPPER(r.wait_type) LIKE N'PAGELATCH[_]%'
GROUP BY dpi.object_id, dpi.database_id;

Tonton video tujuh menit ini untuk gambaran umum tentang cara dan waktu penggunaan fitur metadata TempDB yang dioptimalkan memori:

Note

Saat ini, fitur metadata TempDB yang dioptimalkan memori tidak tersedia di Azure SQL Database, database SQL di Microsoft Fabric, dan Azure SQL Managed Instance.

Mengonfigurasi dan menggunakan metadata TempDB yang dioptimalkan untuk memori

Bagian berikut mencakup langkah-langkah untuk mengaktifkan, mengonfigurasi, memverifikasi, dan menonaktifkan fitur metadata TempDB yang dioptimalkan memori.

Enable

Untuk mengaktifkan fitur ini, gunakan skrip berikut:

ALTER SERVER CONFIGURATION SET MEMORY_OPTIMIZED TEMPDB_METADATA = ON;

Untuk informasi selengkapnya, lihat ALTER SERVER. Perubahan konfigurasi ini memerlukan mulai ulang layanan agar berlaku.

Anda dapat memverifikasi apakah tempdb dioptimalkan untuk memori atau tidak dengan menggunakan perintah T-SQL berikut:

SELECT SERVERPROPERTY('IsTempdbMetadataMemoryOptimized');

Jika nilai yang dikembalikan adalah 1 dan restart telah terjadi setelah mengaktifkan fitur, maka fitur diaktifkan.

Jika server gagal memulai karena alasan apa pun setelah Anda mengaktifkan metadata TempDB yang dioptimalkan untuk memori, Anda dapat melewati fitur ini dengan memulai instans Mesin Database menggunakan konfigurasi minimal dan opsi startup -f. Anda kemudian dapat menonaktifkan fitur dan menghapus opsi -f untuk memulai ulang Mesin Database dalam mode normal.

Ikat ke kumpulan sumber daya untuk membatasi penggunaan memori

Untuk melindungi server dari potensi masalah kehabisan memori, kami sarankan Anda mengaitkan tempdb dengan gubernur sumber daya di kumpulan sumber daya yang membatasi memori yang digunakan oleh metadata TempDB yang dioptimalkan untuk memori. Contoh skrip berikut membuat kumpulan sumber daya dan mengatur memori maksimumnya menjadi 20%, memungkinkan gubernur sumber daya , dan mengikat tempdb ke kumpulan sumber daya.

Contoh ini menggunakan 20% sebagai batas memori untuk tujuan demonstrasi. Nilai optimal di lingkungan Anda mungkin lebih besar atau lebih kecil tergantung pada beban kerja Anda, dan dapat berubah dari waktu ke waktu jika beban kerja berubah.

CREATE RESOURCE POOL tempdb_resource_pool
WITH (MAX_MEMORY_PERCENT = 20);

ALTER RESOURCE GOVERNOR RECONFIGURE;

ALTER SERVER CONFIGURATION
SET MEMORY_OPTIMIZED TEMPDB_METADATA = ON
(RESOURCE_POOL = 'tempdb_resource_pool');

Perubahan ini memerlukan mulai ulang layanan agar perubahan berlaku, bahkan jika metadata TempDB yang dioptimalkan memori sudah diaktifkan.

Memverifikasi pengikatan kumpulan sumber daya dan memantau penggunaan memori

Untuk memverifikasi bahwa tempdb terikat ke kumpulan sumber daya dan untuk memantau statistik penggunaan memori untuk kumpulan, gunakan kueri berikut:

; WITH resource_pool
AS (SELECT p.pool_id,
           p.name,
           p.max_memory_percent,
           dp.max_memory_kb,
           dp.target_memory_kb,
           dp.used_memory_kb,
           dp.out_of_memory_count
    FROM sys.resource_governor_resource_pools AS p
         INNER JOIN sys.dm_resource_governor_resource_pools AS dp
             ON p.pool_id = dp.pool_id)
SELECT SERVERPROPERTY('IsTempdbMetadataMemoryOptimized') AS is_tempdb_memory_optimized_metadata_enabled,
       rp.name AS resource_pool_name,
       rp.max_memory_percent,
       rp.max_memory_kb,
       rp.target_memory_kb,
       rp.used_memory_kb,
       rp.out_of_memory_count
FROM sys.databases AS d
     LEFT OUTER JOIN resource_pool AS rp
         ON d.resource_pool_id = rp.pool_id
WHERE d.name = 'tempdb';

Menghapus pengikatan kumpulan sumber daya

Untuk menghapus pengikatan kumpulan sumber daya sambil menjaga metadata TempDB yang dioptimalkan memori diaktifkan, jalankan perintah berikut dan mulai ulang layanan:

ALTER SERVER CONFIGURATION
SET MEMORY_OPTIMIZED TEMPDB_METADATA = ON;

Disable

Untuk menonaktifkan metadata TempDB yang dioptimalkan memori, jalankan perintah berikut dan mulai ulang layanan:

ALTER SERVER CONFIGURATION
SET MEMORY_OPTIMIZED TEMPDB_METADATA = OFF;

Batasan metadata TempDB yang dioptimalkan untuk memori

Mengaktifkan atau menonaktifkan fitur metadata TempDB yang dioptimalkan untuk memori memerlukan memulai ulang.
Dalam kasus tertentu, Anda mungkin mengamati penggunaan memori yang tinggi oleh clerk memori MEMORYCLERK_XTP yang menyebabkan kesalahan kehabisan memori pada beban kerja Anda.

Untuk melihat penggunaan memori oleh petugas MEMORYCLERK_XTP relatif terhadap semua petugas memori lainnya dan relatif terhadap memori server target, jalankan kueri berikut:
```
SELECT SUM(IIF (type = 'MEMORYCLERK_XTP', pages_kb, NULL)) / 1024. AS memoryclerk_xtp_pages_mb,
       SUM(IIF (type <> 'MEMORYCLERK_XTP', pages_kb, NULL)) / 1024. AS other_pages_mb,
       SUM(committed_target_kb) / 1024. AS committed_target_memory_mb
FROM sys.dm_os_memory_clerks
    CROSS JOIN sys.dm_os_sys_info;
```
Jika memori MEMORYCLERK_XTP tinggi, Anda dapat mengurangi masalah sebagai berikut:
- Ikat database tempdb ke kumpulan sumber daya yang membatasi konsumsi memori oleh metadata TempDB yang dioptimalkan untuk memori. Untuk informasi selengkapnya, lihat Mengonfigurasi dan memanfaatkan metadata tempdb yang dioptimalkan memori.
- Prosedur tersimpan sistem dapat dijalankan secara berkala untuk merilis memori MEMORYCLERK_XTP yang tidak lagi diperlukan. Untuk informasi selengkapnya, lihat sys.sp_xtp_force_gc (Transact-SQL).
Untuk informasi selengkapnya, lihat metadata tempdb yang dioptimalkan memori (HkTempDB) dari kesalahan memori.
Saat Anda menggunakan In-Memory OLTP, satu transaksi tidak diizinkan untuk mengakses tabel yang dioptimalkan memori dalam lebih dari satu database. Karena itu, setiap transaksi baca atau tulis yang melibatkan tabel yang dioptimalkan memori dalam database pengguna juga tidak dapat mengakses tampilan sistem tempdb dalam transaksi yang sama. Jika ini terjadi, Anda menerima kesalahan 41317:
```
A user transaction that accesses memory optimized tables or natively compiled modules cannot access more than one user database or databases model and msdb, and it cannot write to master.
```
Batasan ini juga berlaku untuk skenario lain di mana satu transaksi mencoba mengakses tabel yang dioptimalkan memori dalam lebih dari satu database.

Misalnya, Anda mungkin mendapatkan kesalahan 41317 jika Anda mengkueri tampilan katalog sys.stats dalam database pengguna yang berisi tabel yang dioptimalkan memori. Ini terjadi karena kueri mencoba mengakses statistik data pada tabel yang dioptimalkan memori dalam database pengguna dan metadata yang dioptimalkan memori di tempdb.

Contoh skrip berikut memunculkan kesalahan ini ketika metadata TempDB yang dioptimalkan untuk memori diaktifkan.
```
BEGIN TRAN;

-- Create an In-memory OLTP transaction that accesses a system view in tempdb
SELECT name
FROM tempdb.sys.tables;

-- An attempt to create an In-memory OLTP transaction in the user database fails
INSERT INTO <user database>.<schema>.<memory-optimized table>
VALUES (1);

COMMIT TRAN;
```
Note

Batasan ini tidak berlaku untuk tabel sementara. Anda dapat membuat tabel sementara dalam transaksi yang sama yang mengakses tabel yang dioptimalkan memori dalam database pengguna.
Kueri terhadap tampilan katalog sistem selalu menggunakan tingkat isolasi READ COMMITTED. Ketika metadata TempDB yang dioptimalkan memori diaktifkan, kueri terhadap tampilan katalog sistem di tempdb menggunakan tingkat isolasi SNAPSHOT. Dalam kedua kasus, petunjuk penguncian tidak diterapkan.
Indeks penyimpanan kolom tidak dapat dibuat pada tabel sementara ketika metadata TempDB yang dioptimalkan memori diaktifkan.
- Sebagai konsekuensinya, penggunaan prosedur tersimpan sistem sp_estimate_data_compression_savings dengan parameter kompresi data COLUMNSTORE atau COLUMNSTORE_ARCHIVE tidak didukung ketika metadata TempDB yang dioptimalkan memori diaktifkan.

Perencanaan kapasitas untuk tempdb di SQL Server

Menentukan ukuran yang sesuai untuk tempdb tergantung pada banyak faktor. Faktor-faktor ini termasuk beban kerja dan fitur Mesin Database yang digunakan.

Kami menyarankan agar Anda menganalisis konsumsi ruang tempdb dengan melakukan tugas berikut di lingkungan pengujian tempat Anda dapat mereproduksi beban kerja umum Anda:

Aktifkan untuk file tempdb. Semua file data tempdb harus memiliki ukuran awal dan konfigurasi pertumbuhan otomatis yang sama.
Reprodukan beban kerja dan pantau penggunaan ruang tempdb.
Jika Anda menggunakan pemeliharaan indeks berkala, jalankan pekerjaan pemeliharaan dan pantau ruang tempdb.
Gunakan nilai ruang maksimum yang digunakan dari langkah-langkah sebelumnya untuk memprediksi total penggunaan beban kerja Anda. Sesuaikan nilai ini untuk aktivitas bersamaan yang diproyeksikan, lalu atur ukuran tempdb yang sesuai.

Memantau penggunaan tempdb

Kehabisan ruang disk di tempdb dapat menyebabkan gangguan yang signifikan dan waktu henti aplikasi. Anda dapat menggunakan tampilan manajemen dinamis sys.dm_db_file_space_usage untuk memantau ruang yang digunakan dalam file tempdb.

Misalnya, contoh skrip berikut menemukan:

Ruang kosong di tempdb (tidak mempertimbangkan ruang disk kosong yang mungkin tersedia untuk pertumbuhan tempdb).
Ruang yang digunakan oleh penyimpanan versi konvensional.
- Untuk memantau ukuran penyimpanan versi persisten (PVS) saat pemulihan database yang dipercepat (ADR) diaktifkan di tempdb, lihat Memeriksa ukuran PVS.
Ruang yang digunakan oleh objek internal.
Ruang yang digunakan oleh objek pengguna.

SELECT SUM(unallocated_extent_page_count) * 8.0 / 1024 AS tempdb_free_data_space_mb,
       SUM(version_store_reserved_page_count) * 8.0 / 1024 AS tempdb_version_store_space_mb,
       SUM(internal_object_reserved_page_count) * 8.0 / 1024 AS tempdb_internal_object_space_mb,
       SUM(user_object_reserved_page_count) * 8.0 / 1024 AS tempdb_user_object_space_mb
FROM tempdb.sys.dm_db_file_space_usage;

Untuk memantau alokasi halaman atau aktivitas dealokasi di tempdb di tingkat sesi atau tugas, Anda dapat menggunakan sys.dm_db_session_space_usage dan sys.dm_db_task_space_usage tampilan manajemen dinamis. Tampilan ini dapat membantu Anda mengidentifikasi kueri, tabel sementara, atau variabel tabel yang menggunakan ruang tempdb dalam jumlah besar.

Misalnya, gunakan contoh skrip berikut untuk mendapatkan ruang tempdb yang dialokasikan dan dibatalkan alokasinya oleh objek internal di semua tugas yang sedang berjalan di setiap sesi:

SELECT session_id,
       SUM(internal_objects_alloc_page_count) AS task_internal_objects_alloc_page_count,
       SUM(internal_objects_dealloc_page_count) AS task_internal_objects_dealloc_page_count
FROM sys.dm_db_task_space_usage
GROUP BY session_id;

Gunakan contoh skrip berikut untuk menemukan tempdb yang dialokasikan dan ruang yang saat ini digunakan oleh objek internal dan pengguna untuk setiap sesi dan permintaan, untuk tugas yang sedang berjalan dan selesai:

; WITH tempdb_space_usage
AS (SELECT session_id,
           request_id,
           user_objects_alloc_page_count + internal_objects_alloc_page_count AS tempdb_allocations_page_count,
           user_objects_alloc_page_count + internal_objects_alloc_page_count - user_objects_dealloc_page_count - internal_objects_dealloc_page_count AS tempdb_current_page_count
    FROM sys.dm_db_task_space_usage
    UNION ALL
    SELECT session_id,
           NULL AS request_id,
           user_objects_alloc_page_count + internal_objects_alloc_page_count AS tempdb_allocations_page_count,
           user_objects_alloc_page_count + internal_objects_alloc_page_count - user_objects_dealloc_page_count - user_objects_deferred_dealloc_page_count - internal_objects_dealloc_page_count AS tempdb_current_page_count
    FROM sys.dm_db_session_space_usage)
SELECT session_id,
       COALESCE (request_id, 0) AS request_id,
       SUM(tempdb_allocations_page_count * 8) AS tempdb_allocations_kb,
       SUM(IIF (tempdb_current_page_count >= 0, tempdb_current_page_count, 0) * 8) AS tempdb_current_kb
FROM tempdb_space_usage
GROUP BY session_id, COALESCE (request_id, 0)
ORDER BY session_id, request_id;

Saran dan Komentar

Apakah halaman ini membantu?

Last updated on 2025-11-18