Limiting Disk Space for Recommendations
You can specify the maximum disk space that the configurations recommended by Database Engine Tuning Advisor can consume. This maximum limit can be specified in both the Database Engine Tuning Advisor graphical user interface (GUI) and the dta command prompt utility. Limiting the disk space that recommended configurations can consume is useful when it is not feasible to add more space and you want to produce recommendations that you can use.
About Specifying a Maximum Disk Space
The maximum disk space limit enables Database Engine Tuning Advisor to create only configuration recommendations that you can actually implement.
Note
If you specify a limit that exceeds your actual disk space, then Database Engine Tuning Advisor may recommend a configuration that cannot be implemented until you add disk space to the server. If this occurs, a warning appears in the tuning log.
The maximum disk space limit is specified in megabytes. When multiple databases are tuned, all of the recommendations for them together (collectively) cannot exceed the maximum space limit that you specify.
If unspecified, Database Engine Tuning Advisor assumes the smaller of the following:
- Three times the current raw data size, which includes the total size of heaps and clustered indexes on tables in the database, or
- The free space on the all attached disk drives plus the raw data size.
The default space limit does not include nonclustered indexes and indexed views.
Specify a Maximum Disk Space with the Graphical User Interface
In the Database Engine Tuning Advisor GUI, on the Tuning Options tab, click Advanced Options. In the Advanced Tuning Options dialog box, check Define max. space for recommendations, and specify in megabytes the maximum space that a recommended configuration can consume. See How to: Tune a Database by Using Database Engine Tuning Advisor.
Specify a Maximum Disk Space with the dta Utility
In the dta utility this limit is specified in megabytes with the –B argument. For example, -B 3000 means that if the recommended configuration is accepted, the total size of all databases selected for tuning (data plus indexes and indexed views) will not exceed 3 gigabytes (GB). See How to: Tune a Database by Using the dta Utility.
Specify a Maximum Disk Space in an XML Input File
You can also specify a space limit for recommended configurations in an XML input file that you can use with the dta command-line utility. To specify a maximum disk space in megabytes for Database Engine Tuning Advisor recommendations in the XML input file, use the StorageBoundInMB subelement under the TuningOptions parent element. For more information about the XML input file, see XML Input File Reference (DTA).
See Also
Concepts
Specifying Physical Design Structures
Limiting Tuning Duration and Events
Scenarios for Choosing Tuning Options
Unsupported Tuning Options
Other Resources
Troubleshooting Database Engine Tuning Advisor