You might know that Resource Governor (RG) can be used to allow you to affect resource allocation inside SQL Server. RG was introduced with SQL Server 2008 and requires Enterprise Edition or similar/higher. There are two things you can govern using RG: cpu and memory.
For introductory information on RG, see for instance these BOL topics.
A misconception I see from time to time is that the memory setting is to restrict the Buffer Pool, BP, (mainly used for caching pages from disk). This seems difficult since a page in BP has a cost, but isn’t owned by whoever brought it into cache or who referenced it last time. So, it seems difficult with current implementation of cache handling and ageing to govern the BP memory for RG. What RG does govern is “working memory” for a query. Think memory used for sorting, locks, hashing and such.
We just had such a discussion in the MSDN forums, and I decided to do a test to see if we could show that RG do not govern the BP. Brief outline of the script
- Restrict the BP to 300 MB
- Create two databases (a and b)
- Create a table with size of 255MB in each database
- Create two logins with name a and b, which will be used to execute queries
- The logins end up with two different resource pools (aPool and bPool), where aPool has max mem 80% and bPool has max mem 20%
- A loop which generates queries that are executed using xp_cmdshell and SQLCMD
- The loop first scan over the first approx 9% of data, first login a in database a, then in login b in database b
- Next iteration is then done over the following 9 % of data
- After 11 iteration, we’ve done most data
- Finally check how much memory in the BP each database is using
If RG were to govern the BP, we expect to see database a using lots of more pages than database b. That not what we are seeing. You will probably see that database b is using slightly more memory than a. That is because you done exactly the same queries over exactly the same data for each database and the most recent database you queried was database b (pushing out some of the pages with same cost that database a had in cache). You might want to execute the loop a few times to possibly see things even out a bit.
Seeing is believing, they say, and to me this show that Resource Governor do not govern the Buffer Pool.
If you believe there are faults in how the script does things, feel free to discuss. If you have some alternative script you want us to try, I suggest you open a thread in the MSDN forum (for example) and there post a script which is pretty immediately executable for us(possibly with modifications of file path and server name). Unless the changes you propose are so minor that can be immediately explained in your text.
Use script at own risk.
USE MASTER
SET NOCOUNT ON
--Configure Buffer Pool for max 300 MB memory
EXEC sp_configure 'max server memory', 300
RECONFIGURE
--Drop and create databases a and b
IF DB_ID('a') IS NOT NULL DROP DATABASE a
IF DB_ID('b') IS NOT NULL DROP DATABASE b
GO
CREATE DATABASE a
ON PRIMARY
(NAME = N'a', FILENAME = N'R:\a.mdf' , SIZE = 250MB , FILEGROWTH = 5MB )
LOG ON
( NAME = N'a_log', FILENAME = N'R:\a_log.ldf' , SIZE = 30MB , FILEGROWTH = 5MB)
CREATE DATABASE b
ON PRIMARY
(NAME = N'b', FILENAME = N'R:\b.mdf' , SIZE = 250MB , FILEGROWTH = 5MB )
LOG ON
( NAME = N'b_log', FILENAME = N'R:\b_log.ldf' , SIZE = 30MB , FILEGROWTH = 5MB)
GO
ALTER DATABASE a SET RECOVERY SIMPLE
ALTER DATABASE b SET RECOVERY SIMPLE
--Create tables and populate with data, approx 250 MB each
CREATE TABLE a..t(c1 INT IDENTITY PRIMARY KEY CLUSTERED, c2 INT, c3 CHAR(100))
GO
INSERT INTO a..t (c2, c3)
SELECT TOP (90000) ROW_NUMBER() OVER(ORDER BY a.OBJECT_ID), 'hello'
FROM sys.columns AS a CROSS JOIN sys.columns AS b
GO 25
CREATE TABLE b..t(c1 INT IDENTITY PRIMARY KEY CLUSTERED, c2 INT, c3 CHAR(100))
GO
INSERT INTO b..t (c2, c3)
SELECT TOP (90000) ROW_NUMBER() OVER(ORDER BY a.OBJECT_ID), 'hello'
FROM sys.columns AS a CROSS JOIN sys.columns AS b
GO 25
--Logins
IF SUSER_ID('a') IS NOT NULL DROP LOGIN a
IF SUSER_ID('b') IS NOT NULL DROP LOGIN b
GO
CREATE LOGIN a WITH PASSWORD = 'pwd'
CREATE LOGIN b WITH PASSWORD = 'pwd'
--Users
USE a
IF DATABASE_PRINCIPAL_ID('a') IS NOT NULL DROP USER a
GO
CREATE USER a
GO
GRANT SELECT ON t TO a
GO
USE b
IF DATABASE_PRINCIPAL_ID('b') IS NOT NULL DROP USER b
GO
CREATE USER b
GO
GRANT SELECT ON t TO b
GO
--Configure RG:
USE MASTER
ALTER RESOURCE GOVERNOR WITH (CLASSIFIER_FUNCTION = NULL);
ALTER RESOURCE GOVERNOR RECONFIGURE;
IF EXISTS(SELECT * FROM sys.resource_governor_workload_groups WHERE name = 'aGroup')
DROP WORKLOAD GROUP aGroup
IF EXISTS(SELECT * FROM sys.resource_governor_workload_groups WHERE name = 'bGroup')
DROP WORKLOAD GROUP bGroup
IF EXISTS(SELECT * FROM sys.resource_governor_resource_pools WHERE name = 'aPool')
DROP RESOURCE POOL aPool
IF EXISTS(SELECT * FROM sys.resource_governor_resource_pools WHERE name = 'bPool')
DROP RESOURCE POOL bPool
CREATE RESOURCE POOL aPool WITH (MAX_MEMORY_PERCENT = 80);
CREATE RESOURCE POOL bPool WITH (MAX_MEMORY_PERCENT = 20);
CREATE WORKLOAD GROUP aGroup USING aPool;
CREATE WORKLOAD GROUP bGroup USING bPool;
IF OBJECT_ID ('dbo.MyClassifier') IS NOT NULL DROP FUNCTION dbo.MyClassifier;
GO
CREATE FUNCTION dbo.MyClassifier ()
RETURNS SYSNAME WITH SCHEMABINDING
AS
BEGIN
DECLARE @GroupName SYSNAME;
IF SUSER_SNAME() = 'a'
SET @GroupName = 'aGroup';
ELSE IF SUSER_SNAME() = 'b'
SET @GroupName = 'bGroup';
ELSE SET @GroupName = 'Default';
RETURN @GroupName;
END;
GO
ALTER RESOURCE GOVERNOR WITH (CLASSIFIER_FUNCTION = dbo.MyClassifier);
GO
ALTER RESOURCE GOVERNOR RECONFIGURE;
GO
--Allow xp_cmdshell
EXEC sp_configure 'xp_cmdshell', 1
RECONFIGURE
--Execute queries as A and B to read bunch of data
--We have 2,250,000 in the tables
--We will construct this command: SELECT COUNT(c2) FROM a..t WHERE c1 BETWEEN 1 AND 200000
--The start and end values will differ, with ranges in 200,000 increments. 11 loops will add up to 2,000,000.
DECLARE @v INT = 1, @sql VARCHAR(2000)
WHILE @v <= 2200000
BEGIN
SET @sql = 'SQLCMD /Slocalhost\a /Ua /Ppwd /Q"SELECT COUNT(c2) FROM a..t WHERE c1 BETWEEN ' + CAST(@v AS VARCHAR(20)) + ' AND ' + CAST(@v + 199999 AS VARCHAR(20))+ '"'
EXEC xp_cmdshell @sql, no_output
SET @sql = 'SQLCMD /Slocalhost\a /Ub /Ppwd /Q"SELECT COUNT(c2) FROM b..t WHERE c1 BETWEEN ' + CAST(@v AS VARCHAR(20)) + ' AND ' + CAST(@v + 199999 AS VARCHAR(20))+ '"'
EXEC xp_cmdshell @sql, no_output
SET @v = @v + 200000
END
--Check how many pages from each db in BP:
SELECT
DB_NAME(b.database_id) AS database_name
,(COUNT(*) * 8192) / (1024 * 1024)) AS buffer_count_MB
FROM sys.dm_os_buffer_descriptors AS b
GROUP BY b.database_id
ORDER BY database_name
EXEC sp_configure 'xp_cmdshell', 0
RECONFIGURE
/*--CleanupDROP DATABASE aDROP DATABASE bALTER RESOURCE GOVERNOR WITH (CLASSIFIER_FUNCTION = NULL);ALTER RESOURCE GOVERNOR RECONFIGURE;DROP WORKLOAD GROUP aGroupDROP WORKLOAD GROUP bGroupDROP RESOURCE POOL aPoolDROP RESOURCE POOL bPool*/