Engine groups and establishing task-to-engine affinity

The following steps illustrate how you can 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.

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

sp_addengine engine_number, engine_group

You set the attributes for user-defined execution classes using sp_addexeclass. The syntax is:

sp_addexeclass class_name, base_priority,
                            time_slice, engine_group

The steps are:

  1. Create an engine group using sp_addengine. This statement creates a group called DS_GROUP, consisting of engine 3:

    sp_addengine 3, DS_GROUP
    

    To expand the group so that it also includes engines 4 and 5, execute sp_addengine two more times for those engine numbers:

    sp_addengine 4, DS_GROUP
    
    sp_addengine 5, DS_GROUP
    
  2. Create a user-defined execution class and associate it with the DS_GROUP engine group using sp_addexeclass.

    This statement defines a new execution class called DS with a priority value of “LOW” and associates it with the engine group DS_GROUP:

    sp_addexeclass DS, LOW, 0, DS_GROUP
    
  3. Bind the less critical execution objects to the new execution class using sp_bindexeclass.

    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.

Figure 4-2 illustrates the associations in this scenario: