Tuning Multiple Databases
Applications often access multiple databases to complete their work. Therefore, workloads frequently refer to objects from multiple databases. Database Engine Tuning Advisor can simultaneously tune multiple databases. (This is a new feature since the Index Tuning Wizard of Microsoft SQL Server 2000). Users can specify a set of databases to tune, and Database Engine Tuning Advisor makes recommendations for all selected databases.
Scenario for Tuning Multiple Databases
A view in a database references tables in a second database. The user only wants to tune the second database. To tune only the second database, include the query that references the view in the workload, prefaced by an appropriate USE <first_database> statement, and select the second database to be tuned.
Using the Graphical User Interface to Tune Multiple Databases
Users can specify multiple databases for tuning by selecting all databases that the workload refers to on the General tab. For information about how to tune multiple databases by using the graphical user interface (GUI), see How to: Tune a Database by Using Database Engine Tuning Advisor.
Using the dta Command Line Utility to Tune Multiple Databases
At the command prompt, users can specify multiple databases for tuning with the -D argument, which takes a comma-delimited list of the databases to which the workload refers. Then use the -d option to specify to which database in the list you want the Database Engine Tuning Advisor to connect first. For information about how to tune multiple databases by using the dta command prompt utility, see How to: Tune a Database by Using the dta Utility.