建立及測試分類器 User-Defined 函式

本主題說明如何建立及測試分類器用戶定義函數（UDF）。這些步驟牽涉到在 SQL Server Management Studio 查詢編輯器中執行 Transact-SQL 語句。

下列程式所示的範例說明建立相當複雜的分類器用戶定義函式的可能性。

在我們的範例中：

資源集區（pProductionProcessing）和工作負載群組（gProductionProcessing）會在指定的時間範圍內建立供生產處理使用。
系統會建立資源集區（pOffHoursProcessing）和工作負載群組（gOffHoursProcessing），以處理不符合生產處理需求的連線。
數據表（TblClassificationTimeTable）是在 master 中建立，以保存可針對登入時間評估的開始和結束時間。這必須在 master 中建立，因為 Resource Governor 會針對分類器函式使用架構系結。

備註

最佳做法是，您不應該將大型且經常更新的數據表儲存在 master 中。

分類器函式會擴充登入時間。過於複雜的函式可能會導致登入逾時或使快速連線變慢。

建立分類器使用者定義函式

建立和設定新的資源集區和工作負載群組。將每個工作負載群組指派給適當的資源集區。

--- 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

更新記憶體內部組態。

ALTER RESOURCE GOVERNOR RECONFIGURE  
GO

建立數據表，並定義生產處理時間範圍的開始和結束時間。

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

建立分類函數，其使用時間函式和值，這些值可以針對查閱表格中的時間進行評估。如需在分類器函式中使用查閱表格的詳細資訊，請參閱本主題中的。

備註

SQL Server 2008 引進了一組擴充的日期和時間數據類型和函式。如需詳細資訊，請參閱日期和時間數據類型和函式（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

註冊分類器函式並更新記憶體內部組態。

ALTER RESOURCE GOVERNOR with (CLASSIFIER_FUNCTION = dbo.fnTimeClassifier)  
ALTER RESOURCE GOVERNOR RECONFIGURE  
GO

確認資源集區、工作負載群組和分類器用戶定義函式

使用下列查詢取得資源集區和工作負載群組組態。

USE master  
SELECT * FROM sys.resource_governor_resource_pools  
SELECT * FROM sys.resource_governor_workload_groups  
GO

使用下列查詢確認分類器函式存在且已啟用。

--- 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

使用下列查詢取得資源集區和工作負載群組的目前運行時間數據。

SELECT * FROM sys.dm_resource_governor_resource_pools  
SELECT * FROM sys.dm_resource_governor_workload_groups  
GO

使用下列查詢，瞭解每個群組中的會話。

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

使用下列查詢，找出每個群組中的要求。

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

使用下列查詢，找出分類器中執行哪些要求。

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

在分類器函式中使用查閱表格的最佳做法

除非絕對必要，否則請勿使用查閱表格。如果您需要使用查閱表格，可以將它硬式編碼到函式本身;不過，這必須與分類器函式的複雜度和動態變更進行平衡。
限制針對查閱表格執行的 I/O。
1. 使用 TOP 1 只返回一個資料列。
2. 將數據表中的數據列數目降至最低。
3. 讓數據表的所有數據列都存在於單一頁面上，或少量的頁面。
4. 確認使用索引搜尋作業找到的資料列會盡可能使用最多的搜尋欄位。
5. 如果您考慮搭配聯結使用多個數據表，請取消正規化為單一數據表。
避免封鎖查找表。
1. 使用NOLOCK提示來防止封鎖或在SET LOCK_TIMEOUT函式中使用，並設置最大值為1000毫秒。
2. Table（s）必須存在於 master 資料庫中。（master 資料庫是唯一能夠保證在用戶端電腦嘗試連線時復原的資料庫）。
3. 一律使用架構完整限定數據表名稱。不需要資料庫名稱，因為它必須是 master 資料庫。
4. 資料表上沒有觸發器。
5. 如果您要更新數據表內容，請務必使用快照隔離等級交易來防止寫入器封鎖讀取器。請注意，使用 NOLOCK 提示也應該減輕此問題。
6. 如果可能，請在變更數據表內容時停用分類器函式。
  
  警告
  
  強烈建議遵循這些最佳做法。如果發生無法遵循最佳做法的問題，建議您連絡 Microsoft 支持人員，以便主動防止未來發生任何問題。

另請參閱

資源管理員
 啟用資源管理員
 資源管理員資源集區
 資源管理員工作負載群組
 使用範本設定資源管理員
 檢視資源管理員屬性
 ALTER RESOURCE GOVERNOR （Transact-SQL）
CREATE RESOURCE POOL （Transact-SQL）
CREATE WORKLOAD GROUP （Transact-SQL）
CREATE FUNCTION （Transact-SQL）
ALTER RESOURCE GOVERNOR （Transact-SQL）

Last updated on 2017-06-13

共用方式為

建立及測試分類器 User-Defined 函式

建立分類器使用者定義函式

確認資源集區、工作負載群組和分類器用戶定義函式

在分類器函式中使用查閱表格的最佳做法

另請參閱

其他資源