Membuat dan Menguji Fungsi yang Ditentukan Pengguna Pengklasifikasi
Berlaku untuk: SQL Server Azure SQL Managed Instance
Topik ini menunjukkan cara membuat dan menguji fungsi yang ditentukan pengguna pengklasifikasi (UDF). Langkah-langkahnya melibatkan eksekusi pernyataan Transact-SQL di SQL Server Management Studio Editor Kueri.
Contoh yang ditunjukkan dalam prosedur berikut menggambarkan kemungkinan untuk membuat fungsi yang ditentukan pengguna pengklasifikasi yang cukup kompleks.
Dalam contoh kami:
Kumpulan sumber daya (pProductionProcessing) dan grup beban kerja (gProductionProcessing) dibuat untuk pemrosesan produksi selama rentang waktu tertentu.
Kumpulan sumber daya (pOffHoursProcessing) dan grup beban kerja (gOffHoursProcessing) dibuat untuk menangani koneksi yang tidak memenuhi persyaratan untuk pemrosesan produksi.
Tabel (TblClassificationTimeTable) dibuat di master untuk menahan waktu mulai dan berakhir yang dapat dievaluasi terhadap waktu masuk. Ini harus dibuat di master karena Resource Governor menggunakan pengikatan skema untuk fungsi pengklasifikasi.
Catatan
Sebagai praktik terbaik, Anda tidak boleh menyimpan tabel besar yang sering diperbarui di master.
Fungsi pengklasifikasi memperpanjang waktu masuk. Fungsi yang terlalu kompleks dapat menyebabkan waktu masuk habis atau memperlambat koneksi cepat.
Untuk membuat fungsi yang ditentukan pengguna pengklasifikasi
Buat dan konfigurasikan kumpulan sumber daya dan grup beban kerja baru. Tetapkan setiap grup beban kerja ke kumpulan sumber daya yang sesuai.
--- Create a resource pool for production processing --- and set limits. USE master; GO CREATE RESOURCE POOL pProductionProcessing WITH ( MAX_CPU_PERCENT = 100, MIN_CPU_PERCENT = 50 ); GO --- Create a workload group for production processing --- and configure the relative importance. CREATE WORKLOAD GROUP gProductionProcessing WITH ( IMPORTANCE = MEDIUM ) --- Assign the workload group to the production processing --- resource pool. USING pProductionProcessing; GO --- Create a resource pool for off-hours processing --- and set limits. CREATE RESOURCE POOL pOffHoursProcessing WITH ( MAX_CPU_PERCENT = 50, MIN_CPU_PERCENT = 0 ); GO --- Create a workload group for off-hours processing --- and configure the relative importance. CREATE WORKLOAD GROUP gOffHoursProcessing WITH ( IMPORTANCE = LOW ) --- Assign the workload group to the off-hours processing --- resource pool. USING pOffHoursProcessing; GO
Perbarui konfigurasi dalam memori.
ALTER RESOURCE GOVERNOR RECONFIGURE; GO
Buat tabel dan tentukan waktu mulai dan berakhir untuk rentang waktu pemrosesan produksi.
USE master; GO CREATE TABLE tblClassificationTimeTable ( strGroupName sysname not null, tStartTime time not null, tEndTime time not null ); GO --- Add time values that the classifier will use to --- determine the workload group for a session. INSERT into tblClassificationTimeTable VALUES('gProductionProcessing', '6:35 AM', '6:15 PM'); GO
Buat fungsi pengklasifikasi yang menggunakan fungsi waktu dan nilai yang dapat dievaluasi terhadap waktu dalam tabel pencarian. Untuk informasi tentang menggunakan Tabel Pencarian dalam fungsi pengklasifikasi, lihat "Praktik terbaik untuk menggunakan Tabel Pencarian dalam fungsi pengklasifikasi" dalam topik ini.
Catatan
SQL Server 2008 (10.0.x) memperkenalkan serangkaian jenis dan fungsi data tanggal dan waktu yang diperluas. Untuk informasi selengkapnya, lihat Jenis dan Fungsi Data Tanggal dan Waktu (Transact-SQL).
CREATE FUNCTION fnTimeClassifier() RETURNS sysname WITH SCHEMABINDING AS BEGIN /* We recommend running the classifier function code under snapshot isolation level OR using NOLOCK hint to avoid blocking on lookup table. In this example, we are using NOLOCK hint. */ DECLARE @strGroup sysname DECLARE @loginTime time SET @loginTime = CONVERT(time,GETDATE()) SELECT TOP 1 @strGroup = strGroupName FROM dbo.tblClassificationTimeTable WITH(NOLOCK) WHERE tStartTime <= @loginTime and tEndTime >= @loginTime IF(@strGroup is not null) BEGIN RETURN @strGroup END --- Use the default workload group if there is no match --- on the lookup. RETURN N'gOffHoursProcessing' END; GO
Daftarkan fungsi pengklasifikasi dan perbarui konfigurasi dalam memori.
ALTER RESOURCE GOVERNOR with (CLASSIFIER_FUNCTION = dbo.fnTimeClassifier); ALTER RESOURCE GOVERNOR RECONFIGURE; GO
Untuk memverifikasi kumpulan sumber daya, grup beban kerja, dan fungsi yang ditentukan pengguna pengklasifikasi
Dapatkan kumpulan sumber daya dan konfigurasi grup beban kerja dengan menggunakan kueri berikut.
USE master; SELECT * FROM sys.resource_governor_resource_pools; SELECT * FROM sys.resource_governor_workload_groups; GO
Verifikasi bahwa fungsi pengklasifikasi ada dan diaktifkan dengan menggunakan kueri berikut.
--- Get the classifier function Id and state (enabled). SELECT * FROM sys.resource_governor_configuration; GO --- Get the classifer function name and the name of the schema --- that it is bound to. SELECT object_schema_name(classifier_function_id) AS [schema_name], object_name(classifier_function_id) AS [function_name] FROM sys.dm_resource_governor_configuration;
Dapatkan data runtime saat ini untuk kumpulan sumber daya dan grup beban kerja dengan menggunakan kueri berikut.
SELECT * FROM sys.dm_resource_governor_resource_pools; SELECT * FROM sys.dm_resource_governor_workload_groups; GO
Cari tahu sesi apa yang ada di setiap grup dengan menggunakan kueri berikut.
SELECT s.group_id, CAST(g.name as nvarchar(20)), s.session_id, s.login_time, CAST(s.host_name as nvarchar(20)), CAST(s.program_name AS nvarchar(20)) FROM sys.dm_exec_sessions AS s INNER JOIN sys.dm_resource_governor_workload_groups AS g ON g.group_id = s.group_id ORDER BY g.name; GO
Cari tahu permintaan mana yang ada di setiap grup dengan menggunakan kueri berikut.
SELECT r.group_id, g.name, r.status, r.session_id, r.request_id, r.start_time, r.command, r.sql_handle, t.text FROM sys.dm_exec_requests AS r INNER JOIN sys.dm_resource_governor_workload_groups AS g ON g.group_id = r.group_id CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) AS t ORDER BY g.name; GO
Cari tahu permintaan apa yang berjalan di pengklasifikasi dengan menggunakan kueri berikut.
SELECT s.group_id, g.name, s.session_id, s.login_time, s.host_name, s.program_name FROM sys.dm_exec_sessions AS s INNER JOIN sys.dm_resource_governor_workload_groups AS g ON g.group_id = s.group_id AND 'preconnect' = s.status ORDER BY g.name; GO SELECT r.group_id, g.name, r.status, r.session_id, r.request_id, r.start_time, r.command, r.sql_handle, t.text FROM sys.dm_exec_requests AS r INNER JOIN sys.dm_resource_governor_workload_groups AS g ON g.group_id = r.group_id AND 'preconnect' = r.status CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) AS t ORDER BY g.name; GO
Praktik terbaik untuk menggunakan Tabel Pencarian dalam fungsi pengklasifikasi
Jangan gunakan tabel pencarian kecuali itu benar-benar diperlukan. Jika Anda perlu menggunakan tabel pencarian, tabel tersebut dapat dikodekan secara permanen ke dalam fungsi itu sendiri; namun, ini perlu diimbangi dengan kompleksitas dan perubahan dinamis fungsi pengklasifikasi.
Batasi I/O yang dilakukan untuk tabel pencarian.
TOP 1
Gunakan untuk mengembalikan hanya satu baris.Minimalkan jumlah baris dalam tabel.
Buat semua baris tabel ada pada satu halaman, atau sejumlah kecil halaman.
Konfirmasikan bahwa baris yang ditemukan menggunakan operasi Pencarian Indeks menggunakan kolom pencarian sebanyak mungkin.
De-normalisasi ke satu tabel jika Anda mempertimbangkan untuk menggunakan beberapa tabel dengan gabungan.
Cegah pemblokiran pada tabel pencarian.
NOLOCK
Gunakan petunjuk untuk mencegah pemblokiran atau penggunaanSET LOCK_TIMEOUT
dalam fungsi dengan nilai maksimum 1.000 milidetik.Tabel harus ada di database master. (Database master adalah satu-satunya database yang dijamin akan dipulihkan ketika komputer klien mencoba menyambungkan).
Selalu sepenuhnya memenuhi syarat nama tabel dengan skema. Nama database tidak diperlukan karena harus menjadi database master.
Tidak ada pemicu pada tabel.
Jika Anda memperbarui konten tabel, pastikan untuk menggunakan transaksi tingkat isolasi rekam jepret dalam fungsi pengklasifikasi untuk mencegah Writer memblokir Pembaca. Perhatikan bahwa menggunakan
NOLOCK
petunjuk juga harus mengurangi hal ini.Jika memungkinkan, nonaktifkan fungsi pengklasifikasi saat mengubah konten tabel.
Peringatan
Kami sangat menyarankan untuk mengikuti praktik terbaik ini. Jika ada masalah yang mencegah Anda mengikuti praktik terbaik, kami sarankan Anda menghubungi Dukungan Microsoft sehingga Anda dapat secara proaktif mencegah masalah di masa mendatang.
Lihat Juga
Resource Governor
Mengaktifkan Resource Governor
Kumpulan Sumber Daya Resource Governor
Grup Beban Kerja Resource Governor
Mengonfigurasi Resource Governor Menggunakan Templat
Lihat Properti Resource Governor
ALTER RESOURCE GOVERNOR (Transact-SQL)
BUAT KUMPULAN SUMBER DAYA (Transact-SQL)
CREATE WORKLOAD GROUP (Transact-SQL)
CREATE FUNCTION (Transact-SQL)
ALTER RESOURCE GOVERNOR (Transact-SQL)