19th Chapter--Managing resources using the Resource Governor (3)--monitoring resource regulators

Preface:

After you configure the resource governor for each application, you need to monitor the resource governor. You may need to monitor the use of resource pools and how many requests are allocated to a particular resource pool. You may also want to monitor the activity of the internal and default pools.

In this article, you demonstrate the use of different login accounts (Aw_webappuser and Aw_reportappuser) and monitor CPU and memory resource usage.

preparatory work:

Some of the scripts used in this article have been written in the first installment of this series. It's not a drag. The text will execute the same statement through different accounts to simulate different requests.

steps:

1, open the "Reliability and Performance Monitor" →perfmon.exe

2. Open the Performance Monitor view

3. Add counter

4. Select Sqlserver:resourcepoolstats CPU Usage target% in the Add option.

5, because the machine installed a SQLServer2008 and a SQLServer2012, so you need to find a named instance is 2012 of the counter:

6, now open SSMs, use Aw_webappuser login, this is form 1

7, open the new Form 2, use Aw_reportappuser login.

8, in Form 1, that is, use the Aw_webappuser login form to enter the code, and execute:

Use AdventureWorks2012
go
 
SELECT  SalesOrderID,
        salesorderdetailid,
        Carriertrackingnumber,
        OrderQty,
        ProductID,
        Specialofferid,
        UnitPrice,
        unitpricediscount,
        linetotal,
        rowguid,
        ModifiedDate
from    sales.salesorderdetail to SalesOrderID go
SELECT  SalesOrderID,
        sum (linetotal),
        row_number () over (order by SUM (linetotal) DESC, SalesOrderID)
FROM
  sales.salesorderdetail
GROUP by SalesOrderID

9. In Form 2, execute the following code:

Use AdventureWorks2012
go
 
SELECT  SalesOrderID,
        salesorderdetailid,
        Carriertrackingnumber,
        OrderQty,
        ProductID,
        Specialofferid,
        UnitPrice,
        unitpricediscount,
        linetotal,
        rowguid,
        ModifiedDate
from    sales.salesorderdetail to SalesOrderID go
SELECT  SalesOrderID,
        sum (linetotal),
        row_number () over (order by SUM (linetotal) DESC, SalesOrderID)
FROM
  sales.salesorderdetail
GROUP by SalesOrderID

10, view the counter icon:

Expanded information:

If you want to map a workload group for a particular session, you can associate sys.dm_exec_sessions with Sys.dm_resource_governor_workload_groups, associate as group_id, and further associate sys.dm _resource_governor_workload_groups and Sys.dm_resource_governor_resource_pools, association listed as pool_id.