Engine groups and establishing task-to-engine affinity

The following steps illustrate how to use system procedures to create an engine group associated with a user-defined execution class and bind that execution class to user sessions. In this example, the server is used by technical support staff, who must respond as quickly as possible to customer needs, and by managers who are usually compiling reports, and can afford slower response time.

The example uses sp_addengine and sp_addexeclass.

Use sp_addengine to create engine groups and add engines to existing groups. The syntax is:

sp_addengine engine_number, engine_group

Use sp_addexeclass to set the attributes for user-defined execution classes:

sp_addexeclass class_name, base_priority,
                            time_slice, engine_group

1. Create an engine group called DS_GROUP, consisting of engine 3:

   sp_addengine 3, DS_GROUP
   

   Expand the group so that it also includes engines 4 and 5:

   sp_addengine 4, DS_GROUP
   

   sp_addengine 5, DS_GROUP
   

2. Create a user-defined execution class called DS with a priority of “low” and associate it with the DS_GROUP engine group.

   sp_addexeclass DS, LOW, 0, DS_GROUP
   

3. Bind the less critical execution objects to the new execution class.

   For example, you can bind the manager logins, “mgr1,” “mgr2,” and “mgr3,” to the DS execution class using sp_bindexeclass three times:

   sp_bindexeclass mgr1, LG, NULL, DS
   

   sp_bindexeclass mgr2, LG, NULL, DS
   

   sp_bindexeclass mgr3, LG, NULL, DS
   

   The second parameter, “LG”, indicates that the first parameter is a login name. The third parameter, NULL, indicates that the association applies to any application that the login might be running. The fourth parameter, DS, indicates that the login is bound to the DS execution class.

The result of this example is that the technical support group (not bound to an engine group) is given access to more immediate processing resources than the managers.