Database tempdb

Berlaku untuk: SQL ServerAzure SQL Database Azure SQL Managed Instance

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

Gambaran Umum

Database tempdb sistem adalah sumber daya global yang menyimpan:

• Objek pengguna sementara yang dibuat secara eksplisit. Objek pengguna mencakup tabel dan indeks sementara global atau lokal, prosedur yang disimpan sementara, variabel tabel, tabel yang dikembalikan dalam fungsi bernilai tabel, dan kursor.

• Objek internal yang dibuat mesin database. Meliputi:

  • Tabel kerja untuk menyimpan hasil menengah untuk spool, kursor, urutan, dan penyimpanan objek besar (LOB) sementara.
  • File kerja untuk operasi hash gabungan atau hash agregat.
  • Hasil pengurutan menengah untuk operasi seperti membuat atau membangun ulang indeks (jika SORT_IN_TEMPDB ditentukan), atau kueri , , ORDER BYatau UNION tertentuGROUP BY.

  Setiap objek internal menggunakan minimal sembilan halaman: halaman IAM dan tingkat delapan halaman. Untuk informasi selengkapnya tentang halaman dan jangkauan, lihat Halaman dan luasnya.

• Penyimpanan versi, yang merupakan kumpulan halaman data yang menyimpan baris data yang mendukung fitur untuk penerapan versi baris. Ada dua jenis: penyimpanan versi umum dan penyimpanan versi online-index-build. Penyimpanan versi berisi:

  • Versi baris yang dihasilkan oleh transaksi modifikasi data dalam database yang menggunakan READ COMMITTED melalui isolasi penerapan versi baris atau transaksi isolasi rekam jepret.
  • Versi baris yang dihasilkan oleh transaksi modifikasi data untuk fitur, seperti operasi indeks online, Beberapa Set Hasil Aktif (MARS), dan AFTER pemicu.

Operasi dalam tempdb dicatat minimal sehingga transaksi dapat digulung balik. tempdb dibuat ulang setiap kali SQL Server dimulai sehingga sistem selalu dimulai dengan salinan database yang bersih. Tabel sementara dan prosedur tersimpan dihilangkan secara otomatis saat sambungan terputus, dan tidak ada koneksi yang aktif saat sistem dimatikan.

tempdb tidak pernah memiliki apa pun untuk disimpan dari satu sesi SQL Server ke sesi lainnya. Operasi pencadangan dan pemulihan tidak diperbolehkan 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 default untuk model database. 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

Jumlah file data sekunder tergantung pada jumlah prosesor (logis) pada komputer. Sebagai aturan umum, jika jumlah prosesor logis kurang dari atau sama dengan delapan, gunakan jumlah file data yang sama dengan prosesor logis. Jika jumlah prosesor logis lebih besar dari delapan, gunakan delapan file data. Kemudian jika pertikaian berlanjut, tingkatkan jumlah file data dengan kelipatan empat hingga pertikaian berkurang ke tingkat yang dapat diterima, atau buat perubahan pada beban kerja/kode.

Nilai default untuk jumlah file data didasarkan pada pedoman umum dalam KB 2154845.

Untuk memeriksa ukuran saat ini dan parameter pertumbuhan untuk tempdbtampilan tempdb.sys.database_fileskueri .

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 default	Dapat dimodifikasi
ALLOW_SNAPSHOT_ISOLATION	TIDAK AKTIF	Ya
ANSI_NULL_DEFAULT	TIDAK AKTIF	Ya
ANSI_NULLS	TIDAK AKTIF	Ya
ANSI_PADDING	TIDAK AKTIF	Ya
ANSI_WARNINGS	TIDAK AKTIF	Ya
ARITHABORT	TIDAK AKTIF	Ya
AUTO_CLOSE	TIDAK AKTIF	No
AUTO_CREATE_STATISTICS	AKTIF	Ya
AUTO_SHRINK	TIDAK AKTIF	No
AUTO_UPDATE_STATISTICS	AKTIF	Ya
AUTO_UPDATE_STATISTICS_ASYNC	TIDAK AKTIF	Ya
CHANGE_TRACKING	TIDAK AKTIF	No
CONCAT_NULL_YIELDS_NULL	TIDAK AKTIF	Ya
CURSOR_CLOSE_ON_COMMIT	TIDAK AKTIF	Ya
CURSOR_DEFAULT	GLOBAL	Ya
Opsi Ketersediaan Database	DARING MULTI_USER BACA_TULIS	Tidak No Tidak
DATE_CORRELATION_OPTIMIZATION	TIDAK AKTIF	Ya
DB_CHAINING	AKTIF	No
ENKRIPSI	TIDAK AKTIF	No
MIXED_PAGE_ALLOCATION	TIDAK AKTIF	No
NUMERIC_ROUNDABORT	TIDAK AKTIF	Ya
PAGE_VERIFY	CHECKSUM untuk penginstalan baru SQL Server NONE untuk peningkatan SQL Server	Ya
PARAMETERISASI	SEDERHANA	Ya
QUOTED_IDENTIFIER	TIDAK AKTIF	Ya
READ_COMMITTED_SNAPSHOT	TIDAK AKTIF	No
PEMULIHAN	SEDERHANA	No
RECURSIVE_TRIGGERS	TIDAK AKTIF	Ya
Opsi Service Broker	ENABLE_BROKER	Ya
DAPAT DIPERCAYA	TIDAK AKTIF	No

Untuk deskripsi opsi database ini, lihat MENGUBAH Opsi SET DATABASE (Transact-SQL).

tempdb di Azure SQL

Perilaku tempdb di Azure SQL Database berbeda dari perilaku SQL Server, Azure SQL Managed Instance, dan SQL Server di Azure VM.

tempdb di SQL Database

Database tunggal dan terkumpul di Azure SQL Database mendukung tabel sementara global dan prosedur tersimpan sementara global yang dilingkup ke tingkat database, dan disimpan di tempdb. Tabel sementara global dan prosedur tersimpan sementara global dibagikan untuk semua sesi pengguna dalam database yang sama. Sesi pengguna dari database lain tidak dapat mengakses tabel sementara global. Untuk informasi selengkapnya, lihat Tabel sementara global cakupan database (Azure SQL Database).

Untuk database tunggal, setiap database tunggal di server logis memiliki sendiri tempdb. 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.

Untuk database tunggal dan terkumpul di Azure SQL Database, dari semua database sistem, hanya master database dan tempdb database yang dapat diakses. Untuk informasi selengkapnya, lihat Apa itu server logis di Azure?

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

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

tempdb dalam 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 yang sama. Demikian juga, semua database sistem dapat diakses.

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.

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

Batasan

Operasi berikut ini tidak dapat dilakukan pada tempdb database:

• Menambahkan grup file.
• Mencadangkan atau memulihkan database.
• Mengubah koladasi. Kolatasi default adalah kolatasi server.
• Mengubah pemilik database. tempdb dimiliki oleh sa.
• Membuat rekam jepret database.
• Menghilangkan database.
• Menghilangkan pengguna tamu dari database.
• Mengaktifkan Ubah Pengambilan Data.
• Berpartisipasi dalam pencerminan 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.

Izin

Setiap pengguna dapat membuat objek sementara di tempdb. Pengguna hanya dapat mengakses objek mereka sendiri, kecuali mereka menerima izin tambahan. Dimungkinkan untuk mencabut izin koneksi untuk tempdb mencegah pengguna menggunakan tempdb. Kami tidak merekomendasikannya karena beberapa operasi rutin memerlukan penggunaan tempdb.

Mengoptimalkan performa tempdb di SQL Server

Ukuran dan penempatan tempdb fisik database dapat memengaruhi performa sistem. Misalnya, jika ukuran yang ditentukan tempdb terlalu kecil, bagian dari beban pemrosesan sistem mungkin diambil dengan pertumbuhan tempdb otomatis ke ukuran yang diperlukan untuk mendukung beban kerja setiap kali Anda memulai ulang instans SQL Server.

Jika memungkinkan, gunakan inisialisasi file instan untuk meningkatkan kinerja pengoperasian yang bertumbuh untuk file data.

Pra-alokasikan ruang untuk semua tempdb file dengan mengatur ukuran file ke nilai yang cukup besar untuk mengakomodasi beban kerja umum di lingkungan. Pra-alokasi mencegah tempdb perluasan terlalu sering, yang memengaruhi performa. Database tempdb harus diatur ke autogrow untuk meningkatkan ruang disk untuk pengecualian yang tidak dienkripsi.

File data harus berukuran sama dalam setiap grup file, karena SQL Server menggunakan algoritma pengisian proporsional yang mendukung alokasi dalam file dengan lebih banyak ruang kosong. Memba merupakan tempdb beberapa file data dengan ukuran yang sama memberikan tingkat efisiensi paralel yang tinggi dalam operasi yang menggunakan tempdb.

Atur kenaikan pertumbuhan file ke ukuran yang wajar dan atur ke kenaikan yang sama di semua file data, untuk mencegah tempdb file database tumbuh dengan nilai yang terlalu kecil. Jika pertumbuhan file terlalu kecil dibandingkan dengan jumlah data yang sedang ditulis ke tempdb, tempdb mungkin harus sering diperluas melalui peristiwa pertumbuhan otomatis. Peristiwa pertumbuhan otomatis memberi pengaruh negatif pada kinerja.

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

 SELECT FileName = df.name,
   current_file_size_MB = df.size*1.0/128,
   max_size = CASE df.max_size
     WHEN 0 THEN 'Autogrowth is off.'
     WHEN -1 THEN 'Autogrowth is on.'
     ELSE 'Log file grows to a maximum size of 2 TB.'
   END,
   growth_value =
     CASE
       WHEN df.growth = 0 THEN df.growth
       WHEN df.growth > 0 AND df.is_percent_growth = 0 THEN df.growth*1.0/128.0
       WHEN df.growth > 0 AND df.is_percent_growth = 1 THEN df.growth
     END,
   growth_increment_unit =
     CASE
       WHEN df.growth = 0 THEN 'Size is fixed.'
       WHEN df.growth > 0 AND df.is_percent_growth = 0  THEN 'Growth value is MB.'
       WHEN df.growth > 0 AND df.is_percent_growth = 1  THEN 'Growth value is a percentage.'
     END
FROM tempdb.sys.database_files AS df;
GO

Letakkan tempdb database pada subsistem I/O yang cepat. Gunakan striping disk jika ada banyak disk yang terpasang langsung. Individu atau grup tempdb file data tidak harus berada di disk atau spindle yang berbeda kecuali Anda juga mengalami hambatan I/O.

Letakkan tempdb database pada disk yang berbeda dari disk yang digunakan database pengguna.

Catatan

Meskipun opsi DELAYED_DURABILITY database diatur ke DISABLED untuk tempdb, SQL Server menggunakan penerapan malas untuk menghapus tempdb perubahan log ke disk, karena tempdb dibuat saat startup dan tidak perlu menjalankan proses pemulihan.

Peningkatan performa di tempdb untuk SQL Server

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.
• Pengelogan overhead untuk tempdb dikurangi untuk mengurangi konsumsi bandwidth I/O disk pada tempdb file log.
• Penyiapan menambahkan beberapa tempdb file data selama penginstalan instans baru. Anda dapat menyelesaikan tugas ini dengan menggunakan kontrol input UI baru di bagian Konfigurasi Mesin Database dan parameter /SQLTEMPDBFILECOUNTbaris perintah . Secara default, penyiapan menambahkan file data sebanyak tempdb jumlah prosesor logis atau delapan, mana yang lebih rendah.
• 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 tingkat seragam. Bendera pelacakan 1118 tidak lagi diperlukan. Untuk informasi selengkapnya tentang peningkatan performa di tempdb, lihat artikel blog TEMPDB - File dan Lacak Bendera dan Pembaruan, Oh Saya!.
• Untuk grup file utama, AUTOGROW_ALL_FILES properti diaktifkan dan properti tidak dapat dimodifikasi.

Diperkenalkan di SQL Server 2017 (14.x)

• Pengalaman Penyiapan SQL meningkatkan panduan untuk alokasi file awal tempdb . Penyiapan SQL memperingatkan pelanggan jika ukuran file awal diatur ke nilai yang lebih besar dari 1 GB dan jika inisialisasi file instan tidak diaktifkan, mencegah penundaan startup instans.
• Sys.dm_tran_version_store_space_usage DMV baru diperkenalkan di SQL Server 2017 untuk melacak penggunaan penyimpanan versi per database. DMV baru ini akan berguna dalam pemantauan tempdb untuk penggunaan penyimpanan versi untuk DBA yang dapat secara proaktif merencanakan tempdb ukuran berdasarkan persyaratan penggunaan penyimpanan versi per database.
• Fitur pemrosesan kueri cerdas baru seperti gabungan adaptif dan umpan balik pemberian memori mengurangi tumpahan memori pada eksekusi kueri berturut-turut, mengurangi pemanfaatan yang tidak perlutempdb.

Diperkenalkan di SQL Server 2019 (15.x)

• Mulai SQL Server 2019 (15.x), SQL Server tidak menggunakan FILE_FLAG_WRITE_THROUGH opsi saat membuka file untuk tempdb memungkinkan throughput disk maksimum. Karena tempdb dibuat ulang pada startup SQL Server, opsi ini tidak diperlukan karena untuk database sistem lain dan database pengguna untuk konsistensi 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 memori menghapus hambatan pada PAGELATCH menunggu di tempdb, dan membuka tingkat skalabilitas baru. Untuk informasi selengkapnya, tonton demo video ini tentang Cara (dan Kapan) Untuk: Metadata TempDB yang Dioptimalkan Memori. Untuk informasi selengkapnya, baca pemantauan dan pemecahan masalah metadata tempdb yang dioptimalkan memori.
• Pembaruan halaman Ruang Kosong Halaman Bersamaan (PFS) mengurangi ketidakcocokan kait patch di semua database, masalah yang paling umum terlihat di tempdb. Peningkatan ini mengubah cara konkurensi dikelola dengan pembaruan PFS sehingga dapat diperbarui di bawah kait bersama, bukan kait eksklusif. Perilaku ini aktif secara default di semua database (termasuk TempDB) yang dimulai dengan SQL Server 2019 (15.x). Untuk informasi selengkapnya tentang halaman PFS, baca Di bawah sampul: 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 tempdb file adalah 8 MB, dengan pertumbuhan otomatis 64 MB. Perilaku ini mirip dengan penginstalan SQL Server default di Windows.

Diperkenalkan di SQL Server 2022 (16.x)

• SQL Server 2022 (16.x) memperkenalkan peningkatan skalabilitas dengan peningkatan konkurensi kait halaman sistem. Pembaruan bersamaan untuk halaman peta alokasi global (GAM) dan halaman peta alokasi global bersama (SGAM) mengurangi ketidakcocokan kait halaman sambil mengalokasikan/membatalkan alokasi halaman dan tingkat data. Penyempurnaan ini berlaku untuk semua database pengguna dan terutama menguntungkan tempdb beban kerja yang berat. Untuk informasi selengkapnya tentang halaman GAM dan SGAM, baca Di bawah sampul: halaman GAM, SGAM, dan PFS. Untuk informasi selengkapnya, tonton Penyempurnaan Konkurensi Latch Halaman Sistem (Ep. 6) | Data Terekspos.

Metadata tempdb yang dioptimalkan memori

Ketidakcocokan tempdb metadata secara historis menjadi penyempitan skalabilitas untuk banyak beban kerja yang berjalan di SQL Server. SQL Server 2019 (15.x) memperkenalkan fitur baru yang merupakan bagian dari keluarga fitur database dalam memori: Metadata TempDB yang dioptimalkan memori.

Fitur ini secara efektif menghapus hambatan ini dan membuka tingkat skalabilitas baru untuk tempdbbeban kerja berat. Di SQL Server 2019 (15.x), tabel sistem yang terlibat dalam mengelola metadata tabel sementara dapat dipindahkan ke tabel yang bebas kait, tidak tahan lama, dan dioptimalkan memori.

Catatan

Saat ini fitur metadata TempDB yang dioptimalkan memori tidak tersedia di Azure SQL Database atau Azure SQL Managed Instance.

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

Mengonfigurasi dan menggunakan metadata tempdb yang dioptimalkan memori

Untuk ikut serta dalam fitur baru ini, gunakan skrip berikut:

ALTER SERVER CONFIGURATION SET MEMORY_OPTIMIZED TEMPDB_METADATA = ON;

Perubahan konfigurasi ini memerlukan mulai ulang layanan agar berlaku.

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

SELECT SERVERPROPERTY('IsTempdbMetadataMemoryOptimized');

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

Untuk melindungi server dari potensi kondisi di luar memori, Anda dapat mengikat tempdb ke kumpulan sumber daya. Ini dilakukan melalui ALTER SERVER perintah daripada langkah-langkah yang biasanya Anda ikuti untuk mengikat kumpulan sumber daya ke database.

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

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

Batasan tempdb yang dioptimalkan memori

• Mengaktifkan dan menonaktifkan fitur tidak dinamis. Karena perubahan intrinsik yang perlu dilakukan pada struktur tempdb, hidupkan ulang diperlukan untuk mengaktifkan atau menonaktifkan fitur.

• Satu transaksi tidak diizinkan untuk mengakses tabel yang dioptimalkan memori dalam lebih dari satu database. Setiap transaksi yang melibatkan tabel yang dioptimalkan memori dalam database pengguna tidak akan dapat mengakses tempdb tampilan sistem dalam transaksi yang sama. Jika Anda mencoba mengakses tempdb tampilan sistem dalam transaksi yang sama dengan tabel yang dioptimalkan memori dalam database pengguna, Anda menerima kesalahan berikut:

  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.
  

  Contoh:

  BEGIN TRAN;
  
  SELECT *
  FROM tempdb.sys.tables;  -----> Creates a user in-memory OLTP transaction in tempdb
  
  INSERT INTO <user database>.<schema>.<mem-optimized table>
  VALUES (1); ----> Tries to create a user in-memory OLTP transaction in the user database but will fail
  
  COMMIT TRAN;
  

• Kueri terhadap tabel yang dioptimalkan memori tidak mendukung petunjuk penguncian dan isolasi, sehingga kueri terhadap tampilan katalog yang dioptimalkan tempdb memori tidak akan menghormati penguncian dan petunjuk isolasi. Seperti halnya tampilan katalog sistem lainnya di SQL Server, semua transaksi terhadap tampilan sistem berada dalam READ COMMITTED (atau dalam hal ini, READ COMMITTED SNAPSHOT) isolasi.

• Indeks penyimpan kolom tidak dapat dibuat pada tabel sementara saat metadata TempDB yang dioptimalkan memori diaktifkan.

• Karena keterbatasan indeks penyimpan kolom, penggunaan prosedur tersimpan sp_estimate_data_compression_savings sistem dengan COLUMNSTORE parameter kompresi data atau COLUMNSTORE_ARCHIVE tidak didukung saat metadata TempDB yang dioptimalkan memori diaktifkan.

• Prosedur tersimpan sistem tersedia untuk secara manual menyebabkan mesin dalam memori melepaskan memori yang terkait dengan baris data dalam memori yang dihapus yang memenuhi syarat untuk pengumpulan sampah. Ini dapat membantu memecahkan masalah metadata tempdb tertentu yang dioptimalkan memori (HkTempDB) dari kesalahan memori. Untuk informasi selengkapnya, lihat sys.sp_xtp_force_gc (Transact-SQL).

Catatan

Batasan ini hanya berlaku saat Anda mereferensikan tempdb tampilan sistem. Anda dapat membuat tabel sementara dalam transaksi yang sama saat Anda mengakses tabel yang dioptimalkan memori dalam database pengguna, jika diinginkan.

Perencanaan kapasitas untuk tempdb di SQL Server

Menentukan ukuran yang sesuai untuk tempdb di lingkungan produksi SQL Server tergantung pada banyak faktor. Seperti yang dijelaskan sebelumnya, faktor-faktor ini termasuk beban kerja yang ada dan fitur SQL Server yang digunakan.

Kami menyarankan agar Anda menganalisis beban kerja yang ada dengan melakukan tugas berikut di lingkungan pengujian SQL Server:

• Atur autogrow aktif untuk tempdb.
• Jalankan kueri individual atau file pelacakan beban kerja dan pantau tempdb penggunaan ruang.
• Jalankan operasi pemeliharaan indeks seperti membangun ulang indeks, dan memantau tempdb ruang.
• Gunakan nilai penggunaan ruang 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 dapat tempdb menyebabkan gangguan yang signifikan di lingkungan produksi SQL Server. Hal itu juga dapat mencegah aplikasi yang berjalan dari menyelesaikan operasi. Anda dapat menggunakan tampilan manajemen dinamis sys.dm_db_file_space_usage untuk memantau ruang disk yang digunakan dalam tempdb file.

Misalnya, empat contoh skrip berikut menemukan jumlah ruang kosong di tempdb, jumlah ruang yang digunakan oleh penyimpanan versi, jumlah ruang yang digunakan oleh objek internal, dan jumlah ruang yang digunakan oleh objek pengguna:

 -- Determining the amount of free space in tempdb
SELECT SUM(unallocated_extent_page_count) AS [free pages],
  (SUM(unallocated_extent_page_count)*1.0/128) AS [free space in MB]
FROM tempdb.sys.dm_db_file_space_usage;

-- Determining the amount of space used by the version store
SELECT SUM(version_store_reserved_page_count) AS [version store pages used],
  (SUM(version_store_reserved_page_count)*1.0/128) AS [version store space in MB]
FROM tempdb.sys.dm_db_file_space_usage;

-- Determining the amount of space used by internal objects
SELECT SUM(internal_object_reserved_page_count) AS [internal object pages used],
  (SUM(internal_object_reserved_page_count)*1.0/128) AS [internal object space in MB]
FROM tempdb.sys.dm_db_file_space_usage;

-- Determining the amount of space used by user objects
SELECT SUM(user_object_reserved_page_count) AS [user object pages used],
  (SUM(user_object_reserved_page_count)*1.0/128) AS [user object space in MB]
FROM tempdb.sys.dm_db_file_space_usage;

Untuk memantau alokasi halaman atau aktivitas dealokasi di tempdb tingkat sesi atau tugas, Anda dapat menggunakan tampilan manajemen dinamis sys.dm_db_session_space_usage dan sys.dm_db_task_space_usage . Tampilan ini dapat membantu Anda mengidentifikasi kueri besar, tabel sementara, atau variabel tabel yang menggunakan banyak tempdb ruang disk. Anda juga dapat menggunakan beberapa penghitung untuk memantau ruang kosong yang tersedia di tempdb dan sumber daya yang menggunakan tempdb.

Misalnya, gunakan skrip berikut untuk mendapatkan ruang yang tempdb dikonsumsi oleh objek internal di semua tugas yang sedang berjalan di setiap sesi:

-- Obtaining the space consumed by internal objects in all currently running tasks in each session
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 skrip berikut untuk menemukan ruang yang tempdb digunakan oleh objek internal dalam sesi saat ini, untuk tugas yang sedang berjalan dan selesai:

-- Obtaining the space consumed by internal objects in the current session for both running and completed tasks
SELECT R2.session_id,
  R1.internal_objects_alloc_page_count
  + SUM(R2.internal_objects_alloc_page_count) AS session_internal_objects_alloc_page_count,
  R1.internal_objects_dealloc_page_count
  + SUM(R2.internal_objects_dealloc_page_count) AS session_internal_objects_dealloc_page_count
FROM sys.dm_db_session_space_usage AS R1
INNER JOIN sys.dm_db_task_space_usage AS R2 ON R1.session_id = R2.session_id
GROUP BY R2.session_id, R1.internal_objects_alloc_page_count,
  R1.internal_objects_dealloc_page_count;

Konten terkait

• Opsi SORT_IN_TEMPDB Untuk Indeks
• Database sistem
• sys.databases
• sys.master_files
• Memindahkan file database