5

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.

  • 1
    Why would you care that the queries do use memory? This very much sounds like a XY problem. If you have other problems that you suspect are caused by lack of RAM, please explain the reasoning with more details. – vonPryz Dec 5 '16 at 10:52
  • 1
    Yes there is a way but did you not tried finding out why certain query is requesting more memory grant/given more memory – Shanky Dec 5 '16 at 13:53
4

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

  • I personally think it is bad advice to just turn on the resource governor to limit resource for a given user. You could be introducing factors that impact all other users/tasks/statements running on the server. Limiting a user to a set of given resources is also limiting the availability of these resources for others. E.g.: If a statement runs for 1000 ms on 8 processors in parallel because of MAX_DOP = 0 and you suddenly limit the user's query to one processor, then you could be at 8000 ms runtime. Other limiting factors will limit other resources for others (tasks/schedulers/threads/workers) – John aka hot2use Dec 5 '16 at 14:48
  • Well, you made your point very clear and i totally agree with you on crazy queries should be analyzed and tuned. But nearly every feature of SQL server has a trade-off. Traces, online index rebuild etc has some disadvantages but it is what it is. Resource governor is the way to limit user resource, not the solution bad queries. – Bahtiyar Samet Çoban Dec 6 '16 at 10:31
0

Please ensure you don't give users the ability to run "ad hoc" queries in a productive environment.

Your statement ...

Sometimes they do crazy things.

... implies that they probably do not know what they are doing. Turning on the Resource Governor in a productive environment could have a negative impact on the production.

Blitz Result: Resource Governor Enabled

If you have to enable the Resource Governor please ensure you read the documentation and understand the implications and impacts this can have.

Resource Governor (2012) (Microsoft MSDN)

The easiest way is to restrict their ability to log in to the server.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.