Initializing Database Engine Tuning Advisor
On first use, Database Engine Tuning Advisor must be initialized by a user with system administrator permissions. After a user with system administrator permissions has initialized Database Engine Tuning Advisor, any users that are members of the db_owner fixed database role can use Database Engine Tuning Advisor to tune tables on databases they own.
To initialize Database Engine Tuning Advisor, a user with system administrator permissions performs either of the following actions:
- Use the Database Engine Tuning Advisor graphical user interface to connect to an instance of Microsoft SQL Server 2005. See Starting Database Engine Tuning Advisor.
- Use the dta utility to tune the first workload. See How to: Tune a Database by Using the dta Utility.
After a user with system administrator permissions performs either of these actions, any user with system administrator permissions can use Database Engine Tuning Advisor to tune workloads. Initialization also enables users that are members of the db_owner fixed database role to tune workloads on tables only in databases that they own.
Note
Do not start Database Engine Tuning Advisor when SQL Server is running in single-user mode. If you attempt to start it while the server is in single-user mode, an error will be returned and Database Engine Tuning Advisor will not start. For more information about single-user mode, see Starting SQL Server in Single-User Mode.
Dependency on xp_msver Extended Stored Procedure
Database Engine Tuning Advisor depends on the xp_msver extended stored procedure to provide full functionality. This extended stored procedure is turned on by default when you install SQL Server 2005. Database Engine Tuning Advisor uses this extended stored procedure to fetch the number of processors and available memory on the computer where the database that you are tuning resides. If xp_msver is unavailable, Database Engine Tuning Advisor assumes the hardware characteristics of the computer where Database Engine Tuning Advisor is running. If the hardware characteristics of the computer where Database Engine Tuning Advisor is running are not available, one processor and 1024 megabytes (MBs) of memory are assumed.
This dependency affects partitioning recommendations because the number of partitions recommended depends on these two values (number of processors and available memory).
The dependency also affects your tuning results when you use a test server to tune your production server. In this scenario, Database Engine Tuning Advisor uses xp_msver to fetch hardware properties from the production server. After tuning the workload on the test server, Database Engine Tuning Advisor uses these hardware properties to generate a recommendation.
For more information, see Surface Area Configuration and xp_msver (Transact-SQL).
See Also
Concepts
Permissions Required to Run Database Engine Tuning Advisor