Membuat dan Menguji Fungsi yang Ditentukan Pengguna Pengklasifikasi

Artikel
05/30/2023

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 Editor Kueri SQL Server Management Studio.

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.
1. TOP 1 Gunakan untuk mengembalikan hanya satu baris.
2. Minimalkan jumlah baris dalam tabel.
3. Buat semua baris tabel ada pada satu halaman, atau sejumlah kecil halaman.
4. Konfirmasikan bahwa baris yang ditemukan menggunakan operasi Pencarian Indeks menggunakan kolom pencarian sebanyak mungkin.
5. De-normalisasi ke satu tabel jika Anda mempertimbangkan untuk menggunakan beberapa tabel dengan gabungan.
Cegah pemblokiran pada tabel pencarian.
1. NOLOCK Gunakan petunjuk untuk mencegah pemblokiran atau penggunaan SET LOCK_TIMEOUT dalam fungsi dengan nilai maksimum 1.000 milidetik.
2. Tabel harus ada di database master. (Database master adalah satu-satunya database yang dijamin akan dipulihkan ketika komputer klien mencoba menyambungkan).
3. Selalu sepenuhnya memenuhi syarat nama tabel dengan skema. Nama database tidak diperlukan karena harus menjadi database master.
4. Tidak ada pemicu pada tabel.
5. 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.
6. 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)

Bagikan melalui