Gunakan Resource Governor untuk Membatasi Penggunaan CPU dengan Kompresi Cadangan (Transact-SQL)

  • Artikel

Berlaku untuk:SQL Server

Secara default, mencadangkan menggunakan kompresi secara signifikan meningkatkan penggunaan CPU, dan CPU tambahan yang digunakan oleh proses kompresi dapat berdampak buruk pada operasi bersamaan. Oleh karena itu, Anda mungkin ingin membuat cadangan terkompresi berprioritas rendah dalam sesi yang penggunaan CPU-nya dibatasi oleh Resource Governor saat terjadi ketidakcocokan CPU. Topik ini menyajikan skenario yang mengklasifikasikan sesi pengguna SQL Server tertentu dengan memetakannya ke grup beban kerja Resource Governor yang membatasi penggunaan CPU dalam kasus tersebut.

Penting

Dalam skenario Resource Governor tertentu, klasifikasi sesi mungkin didasarkan pada nama pengguna, nama aplikasi, atau apa pun yang dapat membedakan koneksi. Untuk informasi selengkapnya, lihat Fungsi Pengklasifikasi Resource Governor dan Grup Beban Kerja Resource Governor.

Topik ini berisi serangkaian skenario berikut, yang disajikan secara berurutan:

  1. Menyiapkan Login dan Pengguna untuk Operasi Berprioritas Rendah

  2. Mengonfigurasi Resource Governor untuk Membatasi Penggunaan CPU

  3. Memverifikasi Klasifikasi Sesi Saat Ini (Transact-SQL)

  4. Memadatkan Cadangan Menggunakan Sesi dengan CPU Terbatas

Menyiapkan Login dan Pengguna untuk Operasi Berprioritas Rendah

Skenario dalam topik ini memerlukan login dan pengguna SQL Server berprioritas rendah. Nama pengguna akan digunakan untuk mengklasifikasikan sesi yang berjalan di login dan merutekannya ke grup beban kerja Resource Governor yang membatasi penggunaan CPU.

Prosedur berikut menjelaskan langkah-langkah untuk menyiapkan login dan pengguna untuk tujuan ini, diikuti dengan contoh T-SQL, "Contoh A: Menyiapkan Login dan Pengguna (Transact-SQL)."

Untuk menyiapkan pengguna login dan database untuk mengklasifikasikan sesi

  1. Buat login SQL Server untuk membuat cadangan terkompresi berprioritas rendah.

    Untuk membuat login

  2. Secara opsional, berikan VIEW SERVER STATE untuk login ini.

    Untuk informasi selengkapnya, lihat GRANT Database Principal Permissions (Transact-SQL).

  3. Buat pengguna SQL Server untuk masuk ini.

    Untuk membuat pengguna

  4. Untuk mengaktifkan sesi masuk dan pengguna ini untuk mencadangkan database tertentu, tambahkan pengguna ke peran database db_backupoperator database tersebut. Lakukan ini untuk setiap database yang akan dicadangkan pengguna ini. Secara opsional, tambahkan pengguna ke peran database tetap lainnya.

    Untuk menambahkan pengguna ke peran database tetap

    Untuk informasi selengkapnya, lihat GRANT Database Principal Permissions (Transact-SQL).

Contoh A: Menyiapkan Login dan Pengguna (Transact-SQL)

Contoh berikut hanya relevan jika Anda memilih untuk membuat login dan pengguna SQL Server baru untuk pencadangan berprioritas rendah. Atau, Anda dapat menggunakan login dan pengguna yang ada, jika ada yang sesuai.

Penting

Contoh berikut menggunakan contoh login dan nama pengguna, domain_name\MAX_CPU. Ganti ini dengan nama login dan pengguna SQL Server yang Anda rencanakan untuk digunakan saat membuat cadangan terkompresi berprioritas rendah.

Contoh ini membuat login untuk akun Windows domain_name\MAX_CPU lalu memberikan izin LIHAT STATUS SERVER ke login. Izin ini memungkinkan Anda memverifikasi klasifikasi Resource Governor sesi login. Contoh kemudian membuat pengguna untuk domain_name\MAX_CPU dan menambahkannya ke peran database tetap db_backupoperator untuk database sampel AdventureWorks2022. Nama pengguna ini akan digunakan oleh fungsi pengklasifikasi Resource Governor.

-- Create a SQL Server login for low-priority operations  
USE master;  
CREATE LOGIN [domain_name\MAX_CPU] FROM WINDOWS;  
GRANT VIEW SERVER STATE TO [domain_name\MAX_CPU];  
GO  
-- Create a SQL Server user in AdventureWorks2022 for this login  
USE AdventureWorks2022;  
CREATE USER [domain_name\MAX_CPU] FOR LOGIN [domain_name\MAX_CPU];  
EXEC sp_addrolemember 'db_backupoperator', 'domain_name\MAX_CPU';  
GO

[Atas]

Mengonfigurasi Resource Governor untuk Membatasi Penggunaan CPU

Catatan

Pastikan Resource Governor diaktifkan. Untuk informasi selengkapnya, lihat Mengaktifkan Resource Governor.

Dalam skenario Resource Governor ini, konfigurasi terdiri dari langkah-langkah dasar berikut:

  1. Membuat dan mengonfigurasi kumpulan sumber daya Resource Governor yang membatasi bandwidth CPU rata-rata maksimum yang akan diberikan untuk permintaan di kumpulan sumber daya saat ketidakcocokan CPU terjadi.

  2. Buat dan konfigurasikan grup beban kerja Resource Governor yang menggunakan kumpulan ini.

  3. Buat fungsi pengklasifikasi, yang merupakan fungsi yang ditentukan pengguna (UDF) yang nilai pengembaliannya digunakan oleh Resource Governor untuk mengklasifikasikan sesi sehingga dirutekan ke grup beban kerja yang sesuai.

  4. Daftarkan fungsi pengklasifikasi dengan Resource Governor.

  5. Terapkan perubahan pada konfigurasi dalam memori Resource Governor.

Catatan

Untuk informasi tentang kumpulan sumber daya Resource Governor, grup beban kerja, dan klasifikasi, lihat Resource Governor.

Pernyataan Transact-SQL untuk langkah-langkah ini dijelaskan dalam prosedur, "Untuk mengonfigurasi Resource Governor untuk membatasi penggunaan CPU," yang diikuti oleh contoh prosedur Transact-SQL.

Untuk mengonfigurasi Resource Governor (SQL Server Management Studio)

Mengonfigurasi Resource Governor untuk membatasi penggunaan CPU (Transact-SQL)

  1. Terbitkan pernyataan CREATE RESOURCE POOL untuk membuat kumpulan sumber daya. Contoh untuk prosedur ini menggunakan sintaks berikut:

    CREATE RESOURCE POOL [<pool_name>] WITH ( MAX_CPU_PERCENT = /*replace 10 with the actual value*/10 );

    Nilai adalah bilangan bulat dari 1 hingga 100 yang menunjukkan persentase bandwidth CPU rata-rata maksimum. Nilai yang sesuai tergantung pada lingkungan Anda. Untuk tujuan ilustrasi, contoh dalam topik ini menggunakan 20% persen (MAX_CPU_PERCENT = 20.)

  2. Terbitkan pernyataan CREATE WORKLOAD GROUP untuk membuat grup beban kerja untuk operasi berprioritas rendah yang penggunaan CPU-nya ingin Anda kelola. Contoh untuk prosedur ini menggunakan sintaks berikut:

    CREATE WORKLOAD GROUP [<group_name>] USING [<pool_name>];

  3. Terbitkan pernyataan CREATE FUNCTION untuk membuat fungsi pengklasifikasi yang memetakan grup beban kerja yang dibuat pada langkah sebelumnya kepada pengguna login berprioritas rendah. Contoh untuk prosedur ini menggunakan sintaks berikut:

    CREATE FUNCTION [<schema_name>].[<function_name>]() RETURNS sysname  
WITH SCHEMABINDING  
AS  
BEGIN  
    DECLARE @workload_group_name AS [<sysname>]  
    IF (SUSER_NAME() = '<user_of_low_priority_login>')  
    SET @workload_group_name = '<workload_group_name>'  
    RETURN @workload_group_name  
END;

    Untuk informasi tentang komponen pernyataan ini CREATE FUNCTION , lihat:

  4. Terbitkan pernyataan ALTER RESOURCE GOVERNOR untuk mendaftarkan fungsi pengklasifikasi dengan Resource Governor. Contoh untuk prosedur ini menggunakan sintaks berikut:

    ALTER RESOURCE GOVERNOR WITH (CLASSIFIER_FUNCTION = [<schema_name>].[<function_name>]);

  5. Terbitkan pernyataan ALTER RESOURCE GOVERNOR kedua untuk menerapkan perubahan pada konfigurasi dalam memori Resource Governor, sebagai berikut:

    ALTER RESOURCE GOVERNOR RECONFIGURE;

Contoh B: Mengonfigurasi Resource Governor (Transact-SQL)

Contoh berikut melakukan langkah-langkah berikut dalam satu transaksi:

  1. Membuat kumpulan pMAX_CPU_PERCENT_20 sumber daya.

  2. gMAX_CPU_PERCENT_20 Membuat grup beban kerja.

  3. Membuat fungsi pengklasifikasi rgclassifier_MAX_CPU() , yang menggunakan nama pengguna yang dibuat dalam contoh sebelumnya.

  4. Mendaftarkan fungsi pengklasifikasi dengan Resource Governor.

Setelah melakukan transaksi, contoh menerapkan perubahan konfigurasi yang diminta dalam pernyataan ALTER WORKLOAD GROUP atau ALTER RESOURCE POOL.

Penting

Contoh berikut menggunakan nama pengguna sampel pengguna SQL Server yang dibuat di "Contoh A: Menyiapkan Login dan Pengguna (Transact-SQL)," domain_name\MAX_CPU. Ganti ini dengan nama pengguna login yang Anda rencanakan untuk digunakan untuk membuat cadangan terkompresi berprioritas rendah.

-- Configure Resource Governor.  
USE master;  
-- Create a resource pool that sets the MAX_CPU_PERCENT to 20%.   
CREATE RESOURCE POOL pMAX_CPU_PERCENT_20  
   WITH  
      (MAX_CPU_PERCENT = 20);  
GO  

-- Create a workload group to use this pool.   
CREATE WORKLOAD GROUP gMAX_CPU_PERCENT_20  
USING pMAX_CPU_PERCENT_20;  
GO  

-- Create a classification function.  
-- Note that any request that does not get classified goes into   
-- the 'Default' group.  
CREATE FUNCTION dbo.rgclassifier_MAX_CPU() RETURNS sysname   
WITH SCHEMABINDING  
AS  
BEGIN  
    DECLARE @workload_group_name AS sysname  
      IF (SUSER_NAME() = 'domain_name\MAX_CPU')  
          SET @workload_group_name = 'gMAX_CPU_PERCENT_20'  
    RETURN @workload_group_name  
END;  
GO  
  
-- Register the classifier function with Resource Governor.  
ALTER RESOURCE GOVERNOR WITH (CLASSIFIER_FUNCTION= dbo.rgclassifier_MAX_CPU);  
COMMIT TRAN;  
GO  

-- Start Resource Governor  
ALTER RESOURCE GOVERNOR RECONFIGURE;  
GO

[Atas]

Memverifikasi Klasifikasi Sesi Saat Ini (Transact-SQL)

Secara opsional, masuk sebagai pengguna yang Anda tentukan dalam fungsi pengklasifikasi Anda, dan verifikasi klasifikasi sesi dengan mengeluarkan pernyataan SELECT berikut di Object Explorer:

USE master;  
SELECT sess.session_id, sess.login_name, sess.group_id, grps.name   
FROM sys.dm_exec_sessions AS sess   
JOIN sys.dm_resource_governor_workload_groups AS grps   
    ON sess.group_id = grps.group_id  
WHERE session_id > 50;  
GO

Di panel hasil, kolom nama harus mencantumkan satu atau beberapa sesi untuk nama grup beban kerja yang Anda tentukan dalam fungsi pengklasifikasi Anda.

Catatan

Untuk informasi tentang tampilan manajemen dinamis yang dipanggil oleh pernyataan SELECT ini, lihat sys.dm_exec_sessions (Transact-SQL) dan sys.dm_resource_governor_workload_groups (Transact-SQL).

[Atas]

Memadatkan Cadangan Menggunakan Sesi dengan CPU Terbatas

Untuk membuat cadangan terkompresi dalam sesi dengan CPU maksimum terbatas, masuk sebagai pengguna yang ditentukan dalam fungsi pengklasifikasi Anda. Dalam perintah pencadangan Anda, tentukan WITH COMPRESSION (Transact-SQL) atau pilih Kompres cadangan ( SQL Server Management Studio). Untuk membuat cadangan database terkompresi, lihat Membuat Pencadangan Database Lengkap (SQL Server).

Contoh C: Membuat Cadangan Terkompresi (Transact-SQL)

Contoh BACKUP berikut membuat cadangan lengkap terkompresi database AdventureWorks2022 dalam file cadangan yang baru diformat, Z:\SQLServerBackups\AdvWorksData.bak.

--Run backup statement in the gBackup session.  
BACKUP DATABASE AdventureWorks2022 TO DISK='Z:\SQLServerBackups\AdvWorksData.bak'   
WITH   
   FORMAT,   
   MEDIADESCRIPTION='AdventureWorks2022 Compressed Data Backups',
   DESCRIPTION='First database backup on AdventureWorks2022 Compressed Data Backups media set',
   COMPRESSION;  
GO

[Atas]

Lihat Juga

Membuat dan Menguji Fungsi yang Ditentukan Pengguna Pengklasifikasi
Resource Governor