A few posts back I mentioned that I was speaking with someone that wanted to use the new Resource Governor feature in SQL Server 2008 to “throttle back” only one portion of a Maintenance Plan, using the Wizard to create one. The problem he had was that the Maintenance Plan Wizard creates a single Agent Job for the entire plan, and yet the Resource Governor requires that you assign a user, connection or other distinguishing element to a Resource Pool, which in turn can be granted certain restrictions on CPU and Memory resources. Since the entire plan was assigned a single user name, he couldn’t throttle just a part – he had to throttle it all.
The answer comes in the second (or first, if you checked a box to bypass the Welcome panel) screen in the Wizard. The radio button you want to check is called “Separate schedules for each task”. Once you do that, you can then navigate over to the Agent Job associated with that task and set it to use a different user.
Alternatively, you can also do your maintenance manually, and set up Jobs or some other construct, and use the Resource Governor that way. In many shops where there are many servers or complicated maintenance plans, creating maintenance steps manually is common.