Windows Password Tuner Utility

0 views

Skip to first unread message

Rule Uresti

unread,

Aug 3, 2024, 11:56:26 AM8/3/24

to teatigebpe

This article describes how to start and use Database Engine Tuning Advisor in SQL Server. For information about how to view and work with the results after you tune a database, see View and Work with the Output from the Database Engine Tuning Advisor.

The Database Engine Tuning Advisor is not supported for Azure SQL Database or Azure SQL Managed Instance. Instead, consider the strategies recommended in Monitoring and performance tuning in Azure SQL Database and Azure SQL Managed Instance. For Azure SQL Database, see also the Database Advisor performance recommendations for Azure SQL Database.

On first use, a user who is member of the sysadmin fixed server role must initialize the Database Engine Tuning Advisor. This is because several system tables must be created in the msdb database to support tuning operations. Initialization also enables users that are members of the db_owner fixed database role to tune workloads on tables in databases that they own.

You can start the Database Engine Tuning Advisor graphical user interface (GUI) in several different ways to support database tuning in a variety of scenarios. The different ways to start Database Engine Tuning Advisor include: from the Start menu, from the Tools menu in SQL Server Management Studio, from the Query Editor in SQL Server Management Studio, and from the Tools menu in SQL Server Profiler. When you first start Database Engine Tuning Advisor, the application displays a Connect to Server dialog box where you can specify the SQL Server instance to which you want to connect.

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 Start SQL Server in Single-User Mode.

Select a query in the Transact-SQL script, or select the entire script, right-click the selection, and choose Analyze Query in Database Engine Tuning Advisor. The Database Engine Tuning Advisor GUI opens and imports the script as an XML file workload. You can specify a session name and tuning options to tune the selected Transact-SQL queries as your workload.

A workload is a set of Transact-SQL statements that execute against a database or databases that you want to tune. Database Engine Tuning Advisor analyzes these workloads to recommend indexes or partitioning strategies that will improve your server's query performance.

When using a trace table as a workload, that table must exist on the same server where Database Engine Tuning Advisor is tuning. If you create the trace table on a different server, then move it to the server where Database Engine Tuning Advisor is tuning.

Workloads can also be embedded in an XML input file, where you can also specify a weight for each event. For more information about specifying embedded workloads, see Create an XML Input File later in this article.

You can also use the Starting versions of these trace events. For example, SQL:BatchStarting. However, the Completed versions of these trace events include the Duration column, which allows Database Engine Tuning Advisor to more effectively tune the workload. Database Engine Tuning Advisor does not tune other types of trace events. For more information about these trace events, see Stored Procedures Event Category and TSQL Event Category. For information about using the SQL Trace stored procedures to create a trace file workload, see Create a Trace (Transact-SQL).

Database Engine Tuning Advisor submits Showplan requests as part of the tuning process. When a trace table or file that contains the LoginName data column is consumed as a workload, Database Engine Tuning Advisor impersonates the user specified in LoginName. If this user has not been granted the SHOWPLAN permission, which enables the user to execute and produce Showplans for the statements contained in the trace, Database Engine Tuning Advisor will not tune those statements.

Create a new workload by deleting the LoginName column from the events that were not tuned, and then save only the untuned events in a new trace file or table. For more information about deleting data columns from a trace, see Specify Events and Data Columns for a Trace File (SQL Server Profiler) or Modify an Existing Trace (Transact-SQL).

Database Engine Tuning Advisor will tune the new workload because login information is not specified in the trace. If the LoginName does not exist for a statement, Database Engine Tuning Advisor tunes that statement by impersonating the user who started the tuning session (a member of either the sysadmin fixed server role or the db_owner fixed database role).

Make sure that tracing has stopped before using a trace table as a workload for Database Engine Tuning Advisor. Database Engine Tuning Advisor does not support using a trace table to which trace events are still being written as a workload.

On the Database Engine Tuning Advisor GUI, you can tune a database by using the plan cache, workload files, or workload tables. You can use the Database Engine Tuning Advisor GUI to easily view the results of your current tuning session and results of previous tuning sessions. For information about user interface options, see User Interface Descriptions later in this article. For more information about working with the output after you tune a database, see View and Work with the Output from the Database Engine Tuning Advisor.

On the General tab, type a name in Session name to create a new tuning session. You must configure the fields in the General tab before starting a tuning session. It is not necessary to modify the settings of the Tuning Options tab before starting a tuning session.

Select the database or databases that you want to tune, and optionally from Selected Tables, choose one or more tables from each database. To include cache entries for all databases, from Tuning Options, select Advanced Options and then check Include plan cache events from all databases.

Stop Analysis (With Recommendations) stops the tuning session and prompts you to decide whether you want Database Engine Tuning Advisor to generate recommendations based on the analysis done up to this point.

Pausing Database Engine Tuning Advisor is not supported. If you select the Start Analysis toolbar button after selecting either the Stop Analysis or Stop Analysis (With Recommendations) toolbar buttons, Database Engine Tuning Advisor starts a new tuning session.

To search for a workload file or table, select Browse. Database Engine Tuning Advisor assumes that workload files are rollover files. For more information about rollover files, see Limit Trace File and Table Sizes.

When using a trace table as a workload, that table must exist on the same server that Database Engine Tuning Advisor is tuning. If you create the trace table on a different server, move it to the server that Database Engine Tuning Advisor is tuning before using it as your workload.

The dta utility provides a command prompt executable file that you can use to tune databases. It enables you to use Database Engine Tuning Advisor functionality in batch files and scripts. The dta utility takes plan cache entries, trace files, trace tables, and Transact-SQL scripts as workloads. It also takes XML input that conforms to the Database Engine Tuning Advisor XML schema, which is available at this Microsoft Web site.

When using a trace table as a workload, that table must exist on the same server that Database Engine Tuning Advisor is tuning. If you create the trace table on a different server, then move it to the server that Database Engine Tuning Advisor is tuning.

If a tuning session continues running longer than you had anticipated it would run, you can press CTRL+C to stop the tuning session and generate recommendations based on the analysis dta has completed up to this point. You will be prompted to decide whether you want to generate recommendations or not. Press CTRL+C again to stop the tuning session without generating recommendations.

where -E specifies that your tuning session uses a trusted connection (instead of a login ID and password), -D specifies the name of the database you want to tune. By default, the utility connects to the default instance of SQL Server on the local computer. (Use the -S option to specify a remote database as shown in the following procedure, or to specify a named instance.) The -if option specifies the name and path to a workload file (which can be a Transact-SQL script or a trace file), and -s specifies a name for your tuning session.

where -S specifies a remote server name and instance (or a named instance on the local server) and -D specifies the name of the database you want to tune. The -it option specifies the name of the workload table, -U and -P specify the login ID and password to the remote database, -s specifies the tuning session name, and -A specifies the tuning session duration in minutes. By default, the dta utility uses an 8-hour tuning duration. If you would like Database Engine Tuning Advisor to tune a workload for an unlimited amount of time, specify 0 (zero) with the -A option.