SQL Server – Limit User Memory Usage

memorysql serversql server 2014

I'm using SQL Server 2014. There are some users who can query the tables. The problem is, that most of the time these queries use a lot of memory. I want to know if there is any way to limit resources per user?

There is not a specific query. I just want to control users. Sometimes they do crazy things.

Best Answer

As stated before, you can use Resource Governer to limit memory usage for specific users.

To summarize shortly the steps you should do:

  1. Create a resource pool. This example has max 30% of memory, be careful with that.

    CREATE RESOURCE POOL UserMemoryPool
WITH
( MIN_MEMORY_PERCENT=0,
MAX_MEMORY_PERCENT=30)
GO

  2. Create a workload group which will use resource pool.

    CREATE WORKLOAD GROUP UserMemoryGroup
USING UserMemoryPool;
GO

  3. You should define a Classifier function to tell SQL Server who is gonna be in this group.

    CREATE FUNCTION dbo.UserClassifier() 
RETURNS SYSNAME WITH SCHEMABINDING
AS
BEGIN
    DECLARE @Workload AS SYSNAME
    IF(SUSER_NAME() = 'UserNameToLimit')/*Specify the user*/
        SET @Workload = 'UserMemoryGroup'
    ELSE
        SET @Workload = 'default'
    RETURN @Workload
END
GO

  4. Lastly to enable Resource Governor

    USE master
GO
ALTER RESOURCE GOVERNOR WITH (CLASSIFIER_FUNCTION = dbo.UserClassifier);
GO
ALTER RESOURCE GOVERNOR RECONFIGURE;

You can find additional info at Resource Governor at MSDN

Related Question