Membuat dan Menguji Fungsi User-Defined Pengklasifikasi

Topik ini menunjukkan cara membuat dan menguji fungsi yang ditentukan pengguna pengklasifikasi (UDF). Langkah-langkahnya melibatkan menjalankan 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 menyimpan waktu mulai dan berakhir yang dapat dievaluasi berdasarkan waktu login. Ini harus dibuat di master karena Resource Governor menggunakan pengikatan skema untuk fungsi pengklasifikasi.

Nota

Sebagai praktik terbaik, Anda tidak boleh menyimpan tabel besar yang sering diperbarui di master.

Fungsi pengklasifikasi memperpanjang waktu masuk. Fungsi yang terlalu kompleks dapat membuat proses masuk menjadi habis waktu 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.

Nota

SQL Server 2008 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  
     DECLARE @strGroup sysname  
     DECLARE @loginTime time  
     SET @loginTime = CONVERT(time,GETDATE())  
     SELECT TOP 1 @strGroup = strGroupName  
          FROM dbo.tblClassificationTimeTable  
          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 s  
     INNER JOIN sys.dm_resource_governor_workload_groups 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 r  
     INNER JOIN sys.dm_resource_governor_workload_groups 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 s  
     INNER JOIN sys.dm_resource_governor_workload_groups 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 r  
     INNER JOIN sys.dm_resource_governor_workload_groups 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. Gunakan TOP 1 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. Hapus normalisasi ke satu tabel jika Anda mempertimbangkan untuk menggunakan beberapa tabel dengan penggabungan.
Cegah pemblokiran pada tabel pencarian.
1. NOLOCK Gunakan petunjuk untuk mencegah pemblokiran atau penggunaan SET LOCK_TIMEOUT dalam fungsi dengan nilai maksimum 1000 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 lengkapi nama tabel sepenuhnya dengan skema. Nama database tidak diperlukan karena harus menjadi database master.
4. Tidak ada pemicu di tabel.
5. Jika Anda memperbarui konten tabel, pastikan untuk menggunakan transaksi dengan tingkat isolasi snapshot untuk mencegah Penulis memblokir Pembaca. Perhatikan bahwa penggunaan isyarat NOLOCK seharusnya juga dapat 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
UBAH PENGELOLA SUMBER DAYA (Transact-SQL)
BUAT KUMPULAN SUMBER DAYA (Transact-SQL)
BUAT GRUP BEBAN KERJA (Transact-SQL)
CREATE FUNCTION (Transact-SQL)
ALTER RESOURCE GOVERNOR (Transact-SQL)

Last updated on 2017-06-13

Bagikan melalui

Membuat dan Menguji Fungsi User-Defined Pengklasifikasi

Untuk membuat fungsi yang ditentukan pengguna pengklasifikasi

Untuk memverifikasi kumpulan sumber daya, grup beban kerja, dan fungsi yang ditentukan pengguna pengklasifikasi

Praktik terbaik untuk menggunakan Tabel Pencarian dalam fungsi pengklasifikasi

Lihat Juga

Sumber Daya Tambahan: