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

iSQlServer發表於2008-12-18
函式

本主題說明如何建立和測試分類器使用者定義函式 (UDF)。這些步驟涉及在 SQL Server Management Studio 查詢編輯器中執行 Transact-SQL 語句。建議您先閱讀編寫分類器函式的注意事項，然後再繼續閱讀下面的內容。

下面的過程中顯示的示例說明了建立非常複雜的分類器使用者定義函式的可能性。

在本示例中：

建立了 pProductionProcessing 資源池和 gProductionProcessing 工作負荷組，用於在指定時間範圍內進行生產處理。

建立了 pOffHoursProcessing 資源池和 gOffHoursProcessing 工作負荷組，用於處理不符合生產處理要求的連線。

在 master 中建立了 TblClassificationTimeTable 表，用於儲存可根據登入時間計算的開始和結束時間。必須在 master 中建立該表，因為資源調控器對分類器函式使用了架構繫結。
注意：
作為一種最佳做法，您不應在 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
建立並配置新的資源池和工作負荷組。將每個工作負荷組分配給相應的資源池。

 
--- 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
使用以下查詢獲取資源池和工作負荷組配置。

 
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

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/16436858/viewspace-515928/，如需轉載，請註明出處，否則將追究法律責任。

相關文章