Brad’s Sure Guide to SQL Server Maintenance Plans- P46: SQL Server has a reputation as being a simple database application to install, configure, and maintain. This is a little misleading. SQL Server is a powerful relational database that can handle the needs of the largest organizations and, as such, its proper maintenance almost certainly requires the attention of an experienced DBA. | Chapter 18 Subplans and Precedence In the previous chapters I have often referred to subplans and precedence without giving much more than a cursory explanation of them. In this chapter we will take an in-depth look at each of these important features of the Maintenance Plan Designer. As noted previously a single Maintenance Plan can be made up one or more subplans. Each subplan is made up of one or more maintenance tasks and each subplan can be assigned its own schedule on which to run. Precedence links can be used within a single subplan to control how the tasks within that subplan execute. Using these links we can dictate what happens next in a given subplan based on the outcome of what is called task branching. Tasks on separate subplans execute independently of one another. In other words you cannot modify the action of tasks in one subplan based on the outcome of tasks executed in another subplan. As you devise your Maintenance Plans you ll probably end up using both features creating new subplans to accommodate tasks with conflicting schedule requirements and using precedence links within a given subplan to exert control on the overall behavior of the Maintenance Plan. This chapter will show you how to use both techniques. While it depends on the overall goal of your Maintenance Plans I generally recommend using as few subplans as you can restricting their use to those tasks that really do need to run at different times. Instead of relying on multiple subplans for your Maintenance Plans I want to suggest that you focus your efforts instead on the power of precedence to control how your Maintenance Plans execute. Subplans When you create a Maintenance Plan using the Maintenance Plan Designer you can either lump all the Maintenance Plan tasks into a single subplan that runs according to a single specific schedule or you can spread your Maintenance Plan tasks over one or more subplans each with its own schedule. Let s look at the pros and cons of each option. .
