SQL Server Resource Governor Game

Problem

We found a low priority task blocked a high priority task on a data warehouse SQL Server instance. It caused a delay in high priority reports. We discussed this and decided to investigate the use of the SQL Server Resource Governor system to reduce resources given to low priority tasks. But how could I efficiently gain accurate knowledge of the high priority tasks and users?

Solution

First, I identified the subject matter experts using the system. Then began thinking about how could I gather the information, and have it peer reviewed. I recalled my son had completed a degree in games programming.  It caused me to think, why not make the data gathering a game? Being old school, I wanted to use a paper “board” and tokens. So, I designed a game specifically to gather the high priority tasks and usernames in a “peer review” environment.

Important Term – Pairs

“Pairs” means a user and what they are running. We use that information in the classifier function. A user might run an editor program like SSMS, and we could make that a low priority on the data warehouse instance because the user may run a query and go home. But that same user may have a very important job that runs at night. Therefore, the username alone is not enough information. We need the “pair” to know how to classify them.

Gathering Pair Information

I decided to use a SQL Server Agent job and the Who is Active system to track which user is running what program.

I set the job to run every 8 hours. But you can adjust that to suit your environment. I decided we only need to keep data for one year. The job step details are below.

SQL Agent Job T-SQL

 DECLARE @retention INT = 365,
        @destination_table VARCHAR(500) = 'WhoIsActive',
        @destination_database sysname = 'DBA_Governor_Database',
        @schema VARCHAR(MAX),
        @SQL NVARCHAR(4000),
        @SQL_Delete NVARCHAR(4000),  
        @parameters NVARCHAR(500),
        @exists BIT;
SET @destination_table = @destination_database + '.dbo.' + @destination_table;
--create the logging table
IF OBJECT_ID(@destination_table) IS NULL
    BEGIN;
        EXEC dbo.sp_WhoIsActive @get_transaction_info = 1,
                                @get_outer_command = 1,
                                @get_plans = 1,
                                @return_schema = 1,
                                @schema = @schema OUTPUT;
        SET @schema = REPLACE(@schema, '<table_name>', @destination_table);
        EXEC ( @schema );
    END;
--create index on collection_time
SET @SQL
    = 'USE ' + QUOTENAME(@destination_database)
      + '; IF NOT EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(@destination_table) AND name = N''cx_collection_time'') SET @exists = 0';
SET @parameters = N'@destination_table varchar(500), @exists bit OUTPUT';
EXEC sys.sp_executesql @SQL, @parameters, @destination_table = @destination_table, @exists = @exists OUTPUT;
 
IF @exists = 0
    BEGIN;
        SET @SQL = 'CREATE CLUSTERED INDEX cx_collection_time ON ' + @destination_table + '(collection_time ASC)';
        EXEC ( @SQL );
    END;
--collect activity into logging table
EXEC dbo.sp_WhoIsActive @get_transaction_info = 1,
                        @get_outer_command = 1,
                        @get_plans = 1,
                        @destination_table = @destination_table
                        @show_sleeping_spids = 2;
--purge older data
SET @SQL_Delete
    = 'DELETE FROM ' + @destination_table + ' WHERE collection_time < DATEADD(day, -' + CAST(@retention AS VARCHAR(10))
      + ', GETDATE());';
EXEC ( @SQL_Delete );
 

Pair Information

The “pair” information could be gathered this way later:

SELECT distinct [login_name] ,[program_name]
FROM  [WhoIsActive] 

As an example, here are some made up pairs:

Notice user1 is running the DataWareHouseEngine and the DataWareHouseDesigner and Microsoft SQL Server Management Studio – Query. This is an example of the “Pair” data that needs to be discussed. Ideally the Governor Game will find out which of these “pairs” gets more resources and which less resources. But only in times of contention.

Technical description of how the Resource Governor works

I found no requirement to teach the subject matter experts how the Resource Governor worked at a technical level. Check with your SME’s. Is giving a large technical presentation required? I gave the smallest summary possible.

Try to make a 5-minute summary of how the Resource Governor works. You could make a game question for each point you cover if you think it is important.

I explained to the subject matter experts (SME’s) how the Classifier function links users and programs to “Work Groups” and “Work Groups” link to “Resource Pools” and Resource Pools have set RAM, CPU and IO limits. I prepared a short summary using PowerPoint.

I explained how the Classifier function can make use of these functions to identify users HOST_NAME(), APP_NAME(), SUSER_NAME(), SUSER_SNAME( [sid] ), IS_SRVROLEMEMBER(), or IS_MEMBER().

I said how the Classifier function can use the APP_NAME() function to identify the application the user is running.

I explained how the “internal” group could not be adjusted. And I explained we would only have 4 groups: internal, minimal, default and important. And that we could only adjust resources to minimal, default and important groups.

Prepare questions

1.  Gather “pairs” of user login name and program name that are running.
   
   • To keep the focus, get “pair” information only from the production system.
   • Remember a username may run a low priority program or a high priority program.
   • Only use “pair” information from other environments if really required.
2. Format and print out the “pair” information as cards for the game.
   • See the sample word document in Next Steps section below. Print it with the questions on one side and “The Governor Game” on the back.
3.  Make up one page of questions use only a few that
   • Are funny
   • Are relevant to your environment in Next Steps section below
   • Not too many questions on “how” the Resource Governor works. That is helpful but not the goal in this situation.
   • See the sample word document
4.  Make or edit the game board
   • Make most squares people land on “go forward” squares or the games goes too long.
   • Only have 1 or 2 “move backwards” style squares.
   • Making the rollback squares funny helps
   • See the sample word document in Next Steps section below

Game Preparation

1.  Gather some simple tokens. Ask people to bring their own or Lego blocks will do.
2.  Make a game day plan
   • It can take an hour or more to play the game.
   • Find a dice app or web site virtual dice and check it works
   • If the game is slow double the numbers on the dice
3.  Think about how to capture the results. I chose to ask the SME’s to classify the pairs into only three categories. Get three well labeled bags to hold each of the ‘pairs’ as they are classified.
   • Minimal
   • Default
   • Important
4. Buy some chocolate
   • To make the chocolate last break it up into one size squares before the game.
   • Put it on a plate and cover it before and after each use.
5. Book a room
6. Invite subject matter experts to your Governor Game

Game Outline

Game Goal

• The goal is to move the top priority task through to completion and making your own token second.

Time

• The game can take an hour or more to finish. But discussion is the goal not finishing.

Rules

Starting the game

1. The person rolling the smallest number starts the game by rolling the dice first.
2. The person rolling the highest value decides on the top priority task.
3. Roll the dice in turns in a clockwise order.

Game play

1. Discussion is the goal. Adjust things to keep discussion flowing.
2. Roll the virtual dice. Get some movement around the board.
3. Pick up a card from the Question pile
   • If it is a “pair” discuss it and place it on the minimal, default or important square
   • If it is a question attempt to answer it and place it under the pile of question cards.
4. Everyone may give their VIEW of the answer.
5. Google the correct answer if stuck.
6. If the decision is still unclear a vote is taken.
7. If your token is, for any reason, ever ahead of the nominated top priority task you must ROLLBACK 10 squares or to the start.

Deciding the winner

1. The person in second place when the top priority task is moved to the finish is called the winner.
2. You do not need to roll an exactly match of the squares required to finish the game.

Things not essential for the game

I prepared a game dictionary and game rule book but found they were not required for the game. But people did read them and that helps the general understanding of how the SQL Server Resource Governor works.

After the game

Confirm your captured information

Ideally your game ended with you having three bags. Each bag has the “pairs” for Important, default and Low priority.

Codify your captured information

Next, add that information to the classifier function.

I will only give minimal guidance here as making the classifier function should be performed by someone who understands your systems.

If ( 
       SUSER_NAME() in (  UserJob101 ) 
   AND
   APP_NAME() like 'DataEngineServiceName'
   )
BEGIN
   Set @WorkloadGroup = 'Important'-- the most resources
END

Conclusion

I have given you a tip on how to gather the “pair” information you need for your classifier function.

Apply the Resource Governor in a safe manner by working in test and development environments first.

Next Steps

• Download the game
• Books Online explains that the governor only activates when there is a contention in resources.
• More Resource Governor articles