T-SQL Scripts to Monitor your Resource Pool Stats

Imagine you have a business-critical MS SQL Server serving requests for both OLTP and some batch processing tasks, as data gets pulled by another team. That might be reporting services or extracting data to push to another data warehousing environment, or else. Under this scenario, your SQL Server is treating numerous requests from multiple applications. This in turn affects overall performance and can impact higher priority tasks.

In some cases, applications are business-critical too. They should not be disturbed by other applications. Besides, some users can access an SQL Server via client tools and run ad hoc queries. Even the DBA team may have their own ad hoc queries, especially when it relates to database maintenance.

So, as the database administrator, you face the problem. How to allocate resources to specific processes running in SQL Server, so that the high-priority processes won’t be impacted? The solution should ensure that a business-critical application won’t be impacted at all or impacted less by other running applications.

Introducing SQL Server Resource Governor

According to the Microsoft documentation, SQL Server Resource Governor is a feature that you can use to manage SQL Server workload and system resource consumption. Resource Governor enables you to specify limits on the amount of CPU, physical I/O, and memory that incoming application requests can use.

This very helpful resource workload monitor was introduced in the SQL Server 2008 Enterprise Edition. Now it is available only in the Enterprise and Developer editions.

In a nutshell, Resource Governor assigns a workload group to processes that share the same workload characteristics. All read operations for reporting purposes and ad hoc queries can be grouped into one workload. SQL Server can manage that workload to throttle CPU and memory consumption usage as required.

In my experience as Senior MS SQL database administrator, SQL Server Resource Governor is helpful when you want your SQL resources (such as CPU and memory) to prioritize the OLTP tasks and allocate fewer resources to the data extraction tasks for another data factory or data warehousing environment. However, there are considerations to be aware of before configuring the Resource Governor in your production database server.

Let’s suppose you want to manage the SQL database engine services with the Resource Governor feature. You want to create the Resource Governor pools A and B for each of these workloads. Pool A is configured to use up to 75% of the CPU load, and Pool B should use up to 50% of the CPU load.

When the database engine is under heavy workloads, SQL will manage your CPU consumption within the pool limits. Note that when Pool B is not under heavy usage, Pool A can maximize the use of CPU well above the allowed 70%.

Think of Pools A and B in my scenario as separate SQL server instances running inside your SQL database engine server. it would be similar to running 2 virtual machines inside a host server. These resource pools have to be closely monitored as part of your daily monitoring health check.

Monitor your Resource Pool Stats

When you plan to monitor the resource pool stats, it is best to look at the CPU utilization, the number of user connections, and performance indicators stats, such as how much time is spent on waiting, which tasks are taking longer, etc.

I want to share with you several T-SQL scripts that I use to retrieve the information in regards to the resource pools for the Resource Governor. It’s a good idea to create an Agent job to collect the information during the peak and off-peak times. This way, you can monitor the usage trend.

--Returns the buffer cache hit ratio 
INSERT INTO DBATools.dbo.ResourcePoolStats 
SELECT GETUTCDATE() as 'ReportDateTime', 
'Buffer Cache Hit Ratio' as 'CounterName', 
ROUND(CAST (A.cntr_value1 AS NUMERIC) / CAST (B.cntr_value2 AS NUMERIC), 3) AS 'CounterValue' 
FROM 
(SELECT cntr_value AS cntr_value1 
FROM sys.dm_os_performance_counters 
WHERE object_name = 'MSSQL$DEV:Buffer Manager' AND counter_name = 'Buffer cache hit ratio') AS A 
, 
(SELECT cntr_value AS cntr_value2 
FROM sys.dm_os_performance_counters 
WHERE object_name = 'MSSQL$DEV:Buffer Manager' AND counter_name = 'Buffer cache hit ratio base') AS B 
--Returns the page life expectancy in minutes 
INSERT INTO DBATools.dbo.ResourcePoolStats 
SELECT GETUTCDATE() as 'ReportDateTime', 
'Page Life Expentancy (Minutes)' AS CounterName, 
round ( (CAST (cntr_value AS NUMERIC) / 60), 1) AS CounterValue 
FROM sys.dm_os_performance_counters
WHERE object_name = 'MSSQL$DEV:Buffer Manager' AND counter_name = 'Page life expectancy' 
--Returns pages read per second 
INSERT INTO DBATools.dbo.ResourcePoolStats 
SELECT GETUTCDATE() as 'ReportDateTime', 'Page Reads/Second' AS CounterName, cntr_value AS CounterValue 
FROM sys.dm_os_performance_counters 
WHERE object_name = 'MSSQL$DEV:Buffer Manager' AND counter_name = 'Page reads/sec' 
--Returns pages written per second 
INSERT INTO DBATools.dbo.ResourcePoolStats 
SELECT GETUTCDATE() as 'ReportDateTime', 'Page writes/Second' AS CounterName, cntr_value AS 'Page writes per Second' FROM sys.dm_os_performance_counters WHERE object_name = 'MSSQL$DEV:Buffer Manager' AND counter_name = 'Page writes/sec' 
--Returns Free list Stall per second 
INSERT INTO DBATools.dbo.ResourcePoolStats 
SELECT GETUTCDATE() as 'ReportDateTime', 'Free list stalls/sec' AS CounterName, cntr_value AS CounterValue 
FROM sys.dm_os_performance_counters 
WHERE object_name = 'MSSQL$DEV:Buffer Manager' AND counter_name = 'Free list stalls/sec' 
--Returns Lazy writes per second 
INSERT INTO DBATools.dbo.ResourcePoolStats 
SELECT GETUTCDATE() as 'ReportDateTime', 'Lazy Writes/Sec' AS CounterName, cntr_value AS CounterValue 
FROM sys.dm_os_performance_counters 
WHERE object_name = 'MSSQL$DEV:Buffer Manager' AND counter_name = 'Lazy writes/sec' 
--Returns Total SQL Server Memory 
INSERT INTO DBATools.dbo.ResourcePoolStats 
SELECT GETUTCDATE() as 'ReportDateTime', 'Total Server Memory(KB)' AS CounterName, cntr_value AS CounterValue 
FROM sys.dm_os_performance_counters 
WHERE object_name = 'MSSQL$DEV:Memory Manager' and counter_name = 'Total Server Memory (KB)' 
--Average Latch Wait Time 
INSERT INTO DBATools.dbo.ResourcePoolStats 
SELECT GETUTCDATE() as 'ReportDateTime', 'Avg Latch Wait Time(ms)' AS CounterName, ROUND(CAST (A.cntr_value1 AS NUMERIC) / CAST (B.cntr_value2 AS NUMERIC), 3) AS CounterValue FROM 
(SELECT cntr_value AS cntr_value1 
FROM sys.dm_os_performance_counters 
WHERE object_name = 'MSSQL$DEV:Latches' and counter_name = 'Average Latch Wait Time (ms)') AS A, 
(SELECT cntr_value AS cntr_value2 
FROM sys.dm_os_performance_counters
WHERE object_name = 'MSSQL$DEV:Latches' AND counter_name = 'Average Latch Wait Time Base') AS B 
-- Returns Pending memory grants 
INSERT INTO DBATools.dbo.ResourcePoolStats 
SELECT GETUTCDATE() as 'ReportDateTime', 'Pending memory grants' AS CounterName, cntr_value AS CounterValue 
FROM sys.dm_os_performance_counters 
WHERE object_name = 'MSSQL$DEV:Resource Pool Stats' and counter_name = 'Pending memory grants count' 
-- Returns Pending Disk IO Count 
INSERT INTO DBATools.dbo.ResourcePoolStats 
SELECT GETUTCDATE() as 'ReportDateTime', 'Pending Disk IO Count' AS CounterName, [pending_disk_io_count] AS CounterValue 
FROM sys.dm_os_schedulers 
-- Returns the number of user connections 
INSERT INTO DBATools.dbo.ResourcePoolStats 
SELECT GETUTCDATE() as 'ReportDateTime', 'User Connections' AS CounterName, cntr_value AS CounterValue 
FROM sys.dm_os_performance_counters 
WHERE object_name = 'MSSQL$DEV:General Statistics' and counter_name = 'User Connections' 
--Returns CPU Utilization Percentage 
INSERT INTO DBATools.dbo.ResourcePoolStats 
SELECT GETUTCDATE() as 'ReportDateTime', 'CPU Utilization' AS CounterName, (ROUND(CAST (A.cntr_value1 AS NUMERIC), 3))*100 AS [CounterValue] 
--(ROUND(CAST (A.cntr_value1 AS NUMERIC)/CAST (B.cntr_value2 AS NUMERIC), 3))*100 AS [CounterValue] 
FROM 
(SELECT cntr_value AS cntr_value1 
FROM sys.dm_os_performance_counters 
WHERE object_name = 'MSSQL$DEV:Resource Pool Stats' and counter_name = 'CPU usage %') AS A, (SELECT cntr_value AS cntr_value2 
FROM sys.dm_os_performance_counters 
WHERE object_name = 'MSSQL$DEV:Resource Pool Stats' and counter_name = 'CPU usage % base') AS B 

As you might notice in the above scripts, my SQL instance is named MSSQL$DEV. Remove it and replace it with your current SQL instance name. The DBATools is a database I created to use for my reporting purposes.

I prefer putting all my monitoring reports into one separate user database. You can also stick to the same practice.

Conclusion

SQL Server Resource Governor is a handy tool for mission-critical databases where you can’t avoid sharing workloads. Although the feature is not cheap due to its availability in advanced editions only, it’s still worth using when your organization has the budget to purchase the Enterprise edition for the production environment. Happy learning!