Logo
programming4us
programming4us
programming4us
programming4us
Home
programming4us
XP
programming4us
Windows Vista
programming4us
Windows 7
programming4us
Windows Azure
programming4us
Windows Server
programming4us
Windows Phone
 
Windows Server

SQL Server 2008 R2 : Managing Workloads with the Resource Governor - Monitoring Resource Usage

- How To Install Windows Server 2012 On VirtualBox
- How To Bypass Torrent Connection Blocking By Your ISP
- How To Install Actual Facebook App On Kindle Fire
11/23/2012 5:30:42 PM
SQL Server provides three dynamic management views you can use to view and monitor your Resource Governor configuration:
  • sys.dm_resource_governor_workload_groups— Returns workload group statistics along with the current in-memory configuration of the workload groups.

  • sys.dm_resource_governor_resource_pools— Returns information about current state of your resource pools and resource pool statistics.

  • sys.dm_resource_governor_configuration— Returns the in-memory configuration state of the Resource Governor. Output is the same as the sys.resource_governor_configuration system catalog view.

For example, the following query against the sys.dm_resource_governor_resource_pools DMV returns the configuration settings for each of the pools along with the actual memory allocated:

select name,
       min_cpu_percent as MinCPU,
       max_cpu_percent as MaxCPU,
       min_memory_percent as 'MinMEM%' ,
       max_memory_percent as 'MaxMEM%',
       max_memory_kb as 'MaxMemKB',
       used_memory_kb as 'UsedMemKB',
       target_memory_kb as 'TgtMemKB'
 from sys.dm_resource_governor_resource_pools
GO

name       MinCPU MaxCPU MinMEM% MaxMEM% MaxMemKB UsedMemKB TgtMemKB
---------- ------ ------ ------- ------- -------- --------- --------
internal   0      100    0       100     1556232  8296      1556232
default    0      100    0       100     389064   8336      389064
ReportPool 0      20     0       30      389064   280       389064
OLTPPool   80     100    75      100     1556232  40        1556232

The following example displays statistics on the requests received within the defined workgroups:

select
    cast(g.name as nvarchar(10)) as wg_name,
    cast(p.name as nvarchar(10)) as pool_name,
    total_request_count as totreqcnt,
    active_request_count as actreqcnt,
    g.total_cpu_usage_ms as tot_cpu_use,
    total_cpu_limit_violation_count as tot_clvc,
    g.request_max_cpu_time_sec as req_mcts,
    g.total_reduced_memgrant_count as tot_rmc
 from sys.dm_resource_governor_workload_groups g
    inner join
    sys.dm_resource_governor_resource_pools p
    on p.pool_id = g.pool_id
go
wg_name   pool_name  totreqcnt actreqcnt tot_cpu_use tot_clvc req_mcts tot_rmc
--------- ---------- --------- --------- ----------- -------- -------- -------
internal  internal   0         0         37314       0        0        0
default   default    784       2         97938       0        0        0
ReportWG1 ReportPool 170       1         476016      0        0        0
OLTPWG1   OLTPPool   161       0         1834        0        0        0


					  

Six other DMVs in SQL Server 2008 contain information related to Resource Governor:

  • sys.dm_exec_query_memory_grants— Returns information about the queries that have acquired a memory grant or that still require a memory grant to execute. Resource Governor–related columns in this table are the group_id, pool_id, is_small, and ideal_memory_kb columns.

  • sys.dm_exec_query_resource_semaphores— Returns information about the current query_resource semaphore status, providing general query-execution memory status information. The pool_id column provides a link to Resource Governor information.

  • sys.dm_exec_session— Returns one row per session on SQL Server. The group_id column relates the information to Resource Governor workload groups.

  • sys.dm_exec_requests— Returns information about each request currently executing within SQL Server. The group_id column relates the information to Resource Governor workload groups.

  • sys.dm_exec_cached_plans— Returns a row for each query plan cached by SQL Server in the plan cache. The pool_id column relates the information to Resource Governor resource pools.

  • sys.dm_os_memory_brokers— Returns information about internal allocations within SQL Server that use the Memory Manager. This information includes the following columns for the Resource Governor: pool_id, allocations_db_per_sec, predicted_allocations_kb, and overall_limit_kb.

The following query joins between sys.dm_exec_session and sys.dm_resource_governor_workload_groups to display which sessions are in which workload group:

SELECT
    CAST(g.name as nvarchar(10)) as poolname,
    s.session_id as 'session',
    s.login_time,
    CAST(s.host_name as nvarchar(15)) as host_name,
    CAST(s.program_name AS nvarchar(20)) as program_name
          FROM sys.dm_exec_sessions s
     INNER JOIN sys.dm_resource_governor_workload_groups g
          ON g.group_id = s.group_id
where g.name in ('default', 'ReportWG1', 'OLTPWG1')
go
poolname   session login_time              host_name       program_name
---------- ------- ----------------------- --------------- --------------------
default    51      2010-05-02 14:31:18.530 LATITUDED830-W7 Microsoft SQL Server
default    52      2010-05-02 14:31:21.990 LATITUDED830-W7 SQLAgent - Generic R
default    53      2010-05-02 14:31:23.533 LATITUDED830-W7 SQLAgent - TSQL JobS
default    55      2010-05-02 14:47:27.250 LATITUDED830-W7 Microsoft SQL Server
ReportWG1  60      2010-05-02 19:06:21.100 LATITUDED830-W7 Microsoft SQL Server
OLTPWG1    54      2010-05-02 21:03:03.020 LATITUDED830-W7 Microsoft SQL Server


					  

You can also monitor CPU and memory resources allocated by the Resource Governor through the Windows Performance Monitor via a couple of new performance counters:

  • SQLServer: Resource Pool Stats

  • SQLServer: Workload Stats

An instance of the SQLServer: Resource Pool Stats counter is available for each of the configured resource pools. Likewise, an instance of the SQLServer: Workload Stats counter is available for each of the configured workload groups (see Figure 1). These performance counters return the same information as that returned by the sys.dm_resource_governor_workload_groups and sys.dm_resource_governor_resource_pools DMVs but enable you to monitor these statistics over time.

Figure 1. Monitoring resource pool and workload group statistics in Performance Monitor.
Other -----------------
- Windows Server 2008 Server Core : Decompressing Files with the Expand Utility, Performing Advanced File Comparison with the FC Utility
- Windows Server 2008 Server Core : Modifying Files with the Edlin Utility, Repairing System Databases with the ESEnTUtl Utility
- Microsoft Dynamics CRM 4.0 : Silverlight - Tools and Resources
- Microsoft Dynamics CRM 4.0 : Infrastructure Design Considerations - Windows SharePoint Integration
- Connecting Dynamics GP to Microsoft Office 2010 : Improving performance by globally turning off Outlook integration
- Connecting Dynamics GP to Microsoft Office 2010 : Skipping the exports by using Prebuilt Excel Reports
- Microsoft Dynamics AX 2009 : Integration with Microsoft Office - Reading Excel files
- Microsoft Dynamics AX 2009 : Integration with Microsoft Office - Creating Excel files
- SQL Server 2008 R2 : Configuring Resource Governor (part 2) - Defining Workload Groups, Creating Workload Groups in T-SQL
- SQL Server 2008 R2 : Configuring Resource Governor (part 1) - Enabling Resource Governor, Defining Resource Pools
 
 
Top 10
- Microsoft Visio 2013 : Adding Structure to Your Diagrams - Finding containers and lists in Visio (part 2) - Wireframes,Legends
- Microsoft Visio 2013 : Adding Structure to Your Diagrams - Finding containers and lists in Visio (part 1) - Swimlanes
- Microsoft Visio 2013 : Adding Structure to Your Diagrams - Formatting and sizing lists
- Microsoft Visio 2013 : Adding Structure to Your Diagrams - Adding shapes to lists
- Microsoft Visio 2013 : Adding Structure to Your Diagrams - Sizing containers
- Microsoft Access 2010 : Control Properties and Why to Use Them (part 3) - The Other Properties of a Control
- Microsoft Access 2010 : Control Properties and Why to Use Them (part 2) - The Data Properties of a Control
- Microsoft Access 2010 : Control Properties and Why to Use Them (part 1) - The Format Properties of a Control
- Microsoft Access 2010 : Form Properties and Why Should You Use Them - Working with the Properties Window
- Microsoft Visio 2013 : Using the Organization Chart Wizard with new data
- First look: Apple Watch

- 3 Tips for Maintaining Your Cell Phone Battery (part 1)

- 3 Tips for Maintaining Your Cell Phone Battery (part 2)
programming4us programming4us
Popular tags
Microsoft Access Microsoft Excel Microsoft OneNote Microsoft PowerPoint Microsoft Project Microsoft Visio Microsoft Word Active Directory Biztalk Exchange Server Microsoft LynC Server Microsoft Dynamic Sharepoint Sql Server Windows Server 2008 Windows Server 2012 Windows 7 Windows 8 windows Phone 7 windows Phone 8
programming4us programming4us
 
programming4us
Natural Miscarriage
programming4us
Windows Vista
programming4us
Windows 7
programming4us
Windows Azure
programming4us
Windows Server
programming4us
Game Trailer