internal pool:<\/strong> The pool that SQL Server uses for its internal work.<\/p>\n default pool:<\/strong> This is the first user defined pool in the resource governor. If the Resource Governor enabled, Undirected connections to the “user defined resource pool” come to this pool.<\/p>\n You can think of this as a container that can be used to redirect queries with the same criteria to the related resource pool. One or more workload groups can be redirected to 1 resource pool.<\/p>\n With Classifier Function, requests sent by sessions are checked and directed to the specified workload group.<\/p>\n To better understand Resource Governor, let’s make an example about cpu, memory and disk limitations.<\/p>\n First, we activate Resource Governor through SSMS as follows.<\/p>\n We then create the resource pool to be limited, using the following query. We will make sure that the queries from this resource pool do not exceed 20% of the maximum CPU , 30% of the maximum memory, and 1000 of the IOPS per volume.<\/p>\n With the CAP_CPU_PERCENT parameter, we say that it can use up to 80% of the maximum, even with cpu idle.<\/p>\n What is the difference between\u00a0 CAP_CPU_PERCENT MAX_CPU_PERCENT?<\/strong><\/p>\n When we set the MAX_CPU_PERCENT parameter for a resource pool, if the cpu is idle, resource pool could exceed the max cpu limit reserved for it and it could use the whole CPU. Therefore, there could be short-term problems when other applications or other queries in the operating system needed CPU.<\/p>\n When we set the CAP_CPU_PERCENT parameter, we set the maximum CPU that the resource pool can use, even if it is cpu idle. In this way, other applications or queries other than the resource pool do not experience this problem.<\/p>\n In the example, with the AFFINITY SCHEDULER parameter, we specify that the resource pool will use schedulers eighth, and from 12 to 16.<\/p>\n We create the related workload group using the following script.<\/p>\n Then we need to create the classifier function that will redirect the report related queries to the related workload group. In the following example, we redirect the following users to the ReportGroup workload group.<\/p>\n Finally, we are running the following script for the resource governor to work with the Class_Func function we created above.<\/p>\n If two workload groups shares a resource pool , you can assign a priority to one of the workload groups as follows.<\/p>\n You can configuru other settings for Workload Groups as follows.<\/p>\n You can see the sessions in the worklad groups using the following query.<\/p>\n You can see the queries in the worklad groups using the following query.<\/p>\nWorkload Group<\/h3>\n
Classification<\/h3>\n
![\"\"](\"https:\/\/dbtut.com\/wp-content\/uploads\/2019\/03\/img_5c8c16e29bc48.png\")
<\/p>\nCREATE RESOURCE POOL [ReportPool] \r\nWITH(\r\n\t\tMAX_CPU_PERCENT=20, \r\n\t\tMAX_MEMORY_PERCENT=30,\r\n\t\tCAP_CPU_PERCENT = 80, \r\n\t\tMAX_IOPS_PER_VOLUME=1000,\r\n\t\tAFFINITY SCHEDULER = (8, 12 TO 16)\r\n\t)\r\n<\/pre>\n
CREATE WORKLOAD GROUP [ReportGroup] \r\nUSING [ReportPool]\r\nGO\r\nALTER RESOURCE GOVERNOR RECONFIGURE;\r\nGO\r\n<\/pre>\n
\n
CREATE FUNCTION Class_Func() RETURNS SYSNAME WITH SCHEMABINDING\r\nAS\r\nBEGIN\r\n DECLARE @workload_group sysname;\r\n \r\n IF ( SUSER_SNAME() = 'ReportUser')\r\n SET @workload_group = 'ReportGroup';\r\n ELSE IF (APP_NAME() = 'MyApplication')\r\n\t SET @workload_group = 'ReportGroup';\r\n ELSE IF (HOST_NAME() = 'MyServer')\r\n\t SET @workload_group = 'ReportGroup';\r\n RETURN @workload_group;\r\nEND;\r\n<\/pre>\n
USE master\r\nGO\r\nALTER RESOURCE GOVERNOR WITH (CLASSIFIER_FUNCTION = dbo.Class_Func);\r\nGO\r\nALTER RESOURCE GOVERNOR RECONFIGURE;\r\n<\/pre>\n
ALTER WORKLOAD GROUP ReportGroup\r\nWITH (IMPORTANCE = High)\r\n<\/pre>\n
ALTER WORKLOAD GROUP ReportGroup\r\nWITH \r\n(\r\n--Determines the maximum percentage of memory that a single query can use in the pool. \r\nREQUEST_MAX_MEMORY_GRANT_PERCENT=20, \r\n--The information that the CPU threshold is exceeded is triggered after 5 seconds. The query is not stopped. \r\nREQUEST_MAX_CPU_TIME_SEC = 5,\r\n--Specifies the maximum wait time for a query to take memory. \r\n--In this example, if the query does not take memory for 5 seconds, it will be timeout. \r\nREQUEST_MEMORY_GRANT_TIMEOUT_SEC=5,\r\n--Specifies the maxdop setting that a query can run.\r\nMAX_DOP = 1, \r\n--Limits the number of queries that can run concurrently in the workload group. \r\n--In this example, maximum of 100 queries can run in this workload group at the same time.\r\nGROUP_MAX_REQUESTS = 100\r\n)\r\n<\/pre>\n
SELECT s.session_id,s.login_name, g.name\r\nFROM sys.dm_exec_sessions AS s \r\nINNER JOIN sys.dm_resource_governor_workload_groups AS g \r\n ON g.group_id = s.group_id \r\n\twhere session_id>60\r\nORDER BY g.name; \r\nGO \r\n<\/pre>\n
SELECT sder.group_id, wg.name, sder.status, sder.session_id, sder.request_id, \r\n sder.start_time, sder.command, sder.sql_handle, st.text \r\nFROM sys.dm_exec_requests AS sder \r\nINNER JOIN sys.dm_resource_governor_workload_groups AS wg \r\n ON wg.group_id = sder.group_id \r\nCROSS APPLY sys.dm_exec_sql_text(sder.sql_handle) AS st \r\nORDER BY wg.name; \r\n<\/pre>\n\n